• Artikel
  • Keselamatan
  • Profil
    • Sejarah
  • Blogroll

Rere Setyawan's Blog

~ Permulaan hikmat adalah takut akan TUHAN, dan mengenal Yang Mahakudus adalah pengertian.

Rere Setyawan's Blog

Tag Archives: mysql server

Manage multiple MySql server using single phpmyadmin

10 Monday Nov 2014

Posted by reresetyawan in Database, MySQL

≈ Leave a comment

Tags

mysql server, phpmyadmin

As a System Administrator, we tend to manage a number of MYSQL Server instance. So we need to find a better way of managing this set of mysql server into much a easier and centralized way. In my environment, I have 5 different MySQL database servers running separately under different server 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:

phpmyadmin-multiple-mysql

Inorder to install phpmyadmin on ubuntu, you need 3 main packages apache , MySQL and php has to be installed and configured properly.
1. Apache2 installation and configuration
2. Mysql installation
3. PHP installation

I’m assuming you have already installed a running LAMP on your server.
After installing apache2, mysql and php, you can continue with the below steps to install and configure phpmyadmin

SET UP

The steps in this tutorial require the user to have root privileges on your VPS.

Variables being used in this tutorial are:
Web Server IP: 192.168.1.150
PHPmyAdmin directory: /etc/phpmyadmin
User: phpmaroot
Password: pmapass123!

Install phpmyadmin

We will going to install phpmyadmin on svr1.webserver, and this server will going to manage our MYSQL servers

apt-get install phpmyadmin

Create root user to manage phpmyadmin

This step is optional, ofcourse we may use root to login on phpmyadmin.

mysql> CREATE USER 'phpmaroot'@'%' IDENTIFIED BY 'pmapass123!';
mysql> GRANT ALL PRIVILEGES ON *.* TO phpmaroot@'%';

Modify my.cnf of mysql servers

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

Make MySql accessible

To differentiate our MySQL servers easily, better we add the servers’ hostname into Web Server/PHPmyAdmin server /etc/hosts file. Based on my requirements above, I will add following line into the web server /etc/hosts:

vi /etc/hosts
root@i-svr:/etc/phpmyadmin# vi /etc/hosts
127.0.0.1 localhost
127.0.1.1 i-svr.dev i-svr

svr2.mysql 192.168.1.90
svr3.mysql 192.168.1.91
svr4.mysql 192.168.1.92
svr5.mysql 192.168.1.93
svr6.mysql 192.168.1.94

Modify phpmyadmin configuration file

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:

cp /etc/phpmyadmin/config.inc.php /etc/phpmyadmin/config.inc.php.bak

always make a backup

Find and uncomment the line below:

//$cfg['Servers'][$i]['AllowNoPassword'] = TRUE;
$cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

Inside this file you will also see following line:

/* 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';

Append the code below on top of the above lines:

/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'svr2.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'] = 'svr3.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'] = 'svr4.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'] = 'svr5.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'] = 'svr6.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;

Now you should be able to open the PHPmyAdmin via web browser at http://192.168.1.150/phpmyadmin . You can select MySQL servers you want to connect and access it using phpmaroot user as created above.

 

Advertisement

Subscribe

  • Entries (RSS)
  • Comments (RSS)

Archives

  • May 2015
  • November 2014
  • September 2010
  • July 2010
  • March 2010
  • February 2010

Categories

  • Cloud
  • How to
    • Control Panel
      • Ajenti V
    • Database
      • MySQL
    • handle Error
    • Linux
      • Debian
      • Ubuntu
    • Web Server
      • Nginx
    • Windows 8.1
  • OpenStack Horizon
  • Religious
    • Kotbah
      • Benny Hinn
      • Kathryn Johanna Kulhman
    • Lirik Lagu
      • Barat
        • Healing Worship
      • Indonesia
        • LifeTreeBand
        • Rayakan 6
  • Uncategorized

Meta

  • Register
  • Log in

Blog at WordPress.com.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • Rere Setyawan's Blog
    • Already have a WordPress.com account? Log in now.
    • Rere Setyawan's Blog
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...