Manage Multiple MySQL Servers using PHPmyAdmin

In my environment, I have 5 different MySQL database servers running separately under different geographical location. Since it run standalone and not in cluster mode, I need to have one platform to manage these database servers altogether.

PHPmyAdmin is able to do this, with some changes on the configuration files. You just need to allow the MySQL user and host on every database server to be connected to. The setup that I am going to do will be as below:

Variables that I used as below:

Web Server: Apache 2.2.19
PHP: 5.3.2
Web Server IP: 212.77.103.146
PHPmyAdmin directory: /var/www/html/pma
User: pmaroot
Password: passdb432^^

1. Download PHPmyAdmin PHP source at http://www.phpmyadmin.net/home_page/downloads.php . In this case, I will download this version phpMyAdmin-3.4.7-english.tar.gz. I assume you download the installer under /usr/local/src directory. We will need to rename the folder and paste it into directory that has been setup inside Apache to put PHPmyAdmin files:

$ cd /usr/local/src
$ tar -xzf phpMyAdmin-3.4.7-english.tar.gz
$ mv phpMyAdmin-3.4.7-english /var/www/html/pma
2. Now lets create another root user just to manage databases using PHPmyAdmin. Execute following command in all MySQL database servers:

mysql> CREATE USER 'pmaroot'@'%' IDENTIFIED BY 'passdb432^^';
mysql> GRANT ALL PRIVILEGES ON *.* TO pmaroot@'%';
3. Make sure all database servers are listening to all IP which accessible from outside. To simplify this, just remove or comment if you find following lines in your my.cnf file (usually located under /etc) :

#bind-address=127.0.0.1
#bind-address=localhost
4. To differentiate our MySQL servers easily, better we add the servers’ hostname into PHPmyAdmin server /etc/hosts file. Based on diagram above, I will add following line into the web server /etc/hosts:

china.mysql 118.144.76.16
usa.mysql 209.85.227.26
spain.mysql 84.236.148.11
singapore.mysql 202.156.14.10
colombia.mysql 190.0.39.34

5. So now we need to create PHPmyAdmin configuration files to include all databases server as above. Copy the configuration example as below to the active configuration file:

$ cd /var/www/html/pma
$ cp config.sample.inc.php config.inc.php
6. Open config.inc.php using text editor and add following value (actually you can put anything for the blowfish secret):

$cfg['blowfish_secret'] = 'youcanputanyphraseinsidethisquote'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

7. Inside this file you will also see following line:

/*
* First server
*/
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['AllowNoPassword'] = false;
Copy those whole line for another 4 times and change the appropriate host value. Example as below:

/*
* First server
*/
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'china.mysql';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

/*
* Second server
*/
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'usa.mysql';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

/*
* Third server
*/
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'spain.mysql';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

/*
* Fourth server
*/
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'singapore.mysql';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

/*
* Fifth server
*/
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'colombia.mysql';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

Done. Now I should be able to open the PHPmyAdmin via web browser at http://212.77.103.146/pma . I can select my MySQL servers I want to connect and access it using pmaroot user as created above. Screenshot as below:

Src: http://blog.secaserver.com/2011/10/manage-multiple-mysql-servers-phpmyadmin/