Manage Multiple MySQL Servers using PHPmyAdmin Rumi, April 19, 2013April 19, 2013 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/ Related Administrations Configurations (Linux) PHPMyAdmin
Remote Administering pfsense March 15, 2016 To open the firewall GUI up completely, create a firewall rule to allow remote firewall administration – do not create a port forward or any other NAT configuration. Example Firewall Rule Setup Firewall > Rules, WAN Tab Action: pass Interface: WAN Protocol: TCP Source: Any (or restrict by IP/subnet) Destination:… Read More
Understanding RAID March 25, 2016 I always try to share what I learn, and a few days back was looking for a single page short cut summary notes on various RAID level for the storage units. Found this article quite resourceful and exactly a single paged document that I was looking for. Now sharing the… Read More
Perfect Proxmox Template with Cloud Image and Cloud Init January 28, 2023January 30, 2023 Instructions Choose your Ubuntu Cloud Image. Here in this example will use ubuntu cloud-init image. Download Ubuntu (replace with the url of the one you chose from above) wget https://cloud-images.ubuntu.com/focal/current/focal-server-cloudimg-amd64.img Create a new virtual machine qm create 8000 –memory 2048 –core 2 –name ubuntu-cloud –net0 virtio,bridge=vmbr0 Import the downloaded Ubuntu… Read More