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:

4 thoughts on “Manage Multiple MySQL Servers using PHPmyAdmin

  1. Hi!

    Thanks for thee excellent tutorial.

    I went through all the steps, but in the end, myphpadmin still only works on local host. I do not see the “Server Choice” menu. Is this only on the latest version of phpmyadmin? I am at 3.3.10.

    I set-up the phpymyadmin for local host and one remote host.

    I tried adding $cfg[‘LeftDisplayServers’] = true; to the config.inc.php file and this caused a parse error. Is this required to show the “Server Choice” menu?

    Thanks

    Reply

    1. PMA version 3.3.10 is supported with multiple mysql server management. I dont think $cfg[‘LeftDisplayServers’] is applicable in the PMA. The server choice menu will read the array value from your config.inc.php. If the $i is higher than 1, then it will list the $cfg[‘Servers’][$i][‘host’] value at the server choice. Thats all.

      Reply

  2. When I switch the server by the menu, the input fields do not update, what was wrong?

    Reply

  3. Hi, the number or the “ip” addresses attached to the mysql servers or the country names, are those the ip addresses of the vps or the mysql server. Because I installed mysql on my vps and now using the ip for my vps to connect to a separate phpmyadmin but it says it cant connect.

    Thank you!!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *