Cheap VPS & Xen Server


Residential Proxy Network - Hourly & Monthly Packages

Managing Multiple MySQL Servers From One phpMyAdmin Installation (Using SSL Encryption)


This tutorial explains how you can manage multiple MySQL servers from one phpMyAdmin installation. For security reasons, communication between phpMyAdmin and any remote MySQL server is using SSL encryption (this is not necessary for a local MySQL server since communication between phpMyAdmin and MySQL is not leaving the server). phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the World Wide Web. phpMyAdmin supports a wide range of operations with MySQL.

I do not issue any guarantee that this will work for you!

 

1 Preliminary Note

In this tutorial I will show how to manage two MySQL servers – one local (local.example.com with the IP address 192.168.0.100 – this is the server where phpMyAdmin is installed) and one remote server (remote.example.com with the IP address 192.168.0.101) – from a phpMyAdmin instance. I’m assuming that phpMyAdmin is already installed (either manually or through your distribution’s package manager) and working (i.e., you should already be able to manage the local MySQL server through phpMyAdmin) – I will not cover phpMyAdmin installation here.

This tutorial is based on Debian Wheezy/Ubuntu 12.04. For other distributions, you might have to adjust some paths, but the principle is the same.

 

2 Enabling SSL Support On The Remote MySQL Server

remote.example.com:

Log into MySQL…

mysql -u root -p

… and run the following command on the MySQL shell:

show variables like ‘%ssl%’;

If the output is as follows (both have_openssl and have_ssl show DISABLED)…

mysql> show variables like ‘%ssl%’;
+—————+———-+
| Variable_name | Value    |
+—————+———-+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+—————+———-+
7 rows in set (0.00 sec)

mysql>

… it means that MySQL was compiled with SSL support, but it’s currently not enabled. To enable it, leave the MySQL shell first…

quit;

… and open /etc/mysql/my.cnf:

vi /etc/mysql/my.cnf

Scroll down to the * Security Features section (within the [mysqld] section) and add a line with the word ssl to it:

[...]
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
ssl
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
[...]

Restart MySQL…

/etc/init.d/mysql restart

… and check again if SSL is now enabled:

mysql -u root -p

show variables like ‘%ssl%’;

Output should be as follows which means that SSL is now enabled:

mysql> show variables like ‘%ssl%’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| have_openssl  | YES   |
| have_ssl      | YES   |
| ssl_ca        |       |
| ssl_capath    |       |
| ssl_cert      |       |
| ssl_cipher    |       |
| ssl_key       |       |
+—————+——-+
7 rows in set (0.00 sec)

mysql>

Type…

quit;

… to leave the MySQL shell.

Make sure that OpenSSL is installed:

apt-get install openssl

Now check your OpenSSL version:

root@remote:~# openssl version
OpenSSL 1.0.1c 10 May 2012
root@remote:~#

If you – like me – have OpenSSL version 1.0.1c, the certificates generated by OpenSSL will cause errors like

Sep 18 17:02:15 remote mysqld: SSL error: Unable to get private key from ‘/etc/mysql/newcerts/server-key.pem’
Sep 18 17:02:15 remote mysqld: 120918 17:02:15 [Warning] Failed to setup SSL
Sep 18 17:02:15 remote mysqld: 120918 17:02:15 [Warning] SSL error: Unable to get private key

(in /var/log/syslog on remote.example.com) and

root@local:/etc/mysql/newcerts# mysql –ssl-ca=/etc/mysql/newcerts/ca-cert.pem –ssl-cert=/etc/mysql/newcerts/client-cert.pem –ssl-key=/etc/mysql/newcerts/client-key.pem -h remote.example.com -u root -p
Enter password:
ERROR 2026 (HY000): SSL connection error: protocol version mismatch
root@local:/etc/mysql/newcerts#

(on local.example.com when you try to establish an encrypted connection to remote.example.com), at least with MySQL 5.5; see:

  • https://bugs.launchpad.net/percona-server/+bug/1007164
  • http://bugs.mysql.com/bug.php?id=64870
  • http://forums.mysql.com/read.php?11,400856,401127#msg-401127

To solve this issue, we simply build our own OpenSSL (this is not needed if your OpenSSL version is older than 1.0.1):

cd /tmp
wget http://www.openssl.org/source/openssl-0.9.8x.tar.gz
tar xvfz openssl-0.9.8x.tar.gz
cd openssl-0.9.8x
./config –prefix=/usr/local/openssl-0.9.8
make
make install

Afterwards you will find your new OpenSSL binary in /usr/local/openssl-0.9.8/bin/openssl.

Now we create the CA, server, and client certificates that we need for the SSL connections. I create these certificates in the directory /etc/mysql/newcerts which I have to create first:

mkdir /etc/mysql/newcerts && cd /etc/mysql/newcerts

Create CA certificate (I’m using /usr/local/openssl-0.9.8/bin/openssl here; if your system’s OpenSSL version is older than 1.0.1, you can simply use openssl):

/usr/local/openssl-0.9.8/bin/openssl genrsa 2048 > ca-key.pem

/usr/local/openssl-0.9.8/bin/openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

Create server certificate:

/usr/local/openssl-0.9.8/bin/openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem

/usr/local/openssl-0.9.8/bin/openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Create client certificate:

/usr/local/openssl-0.9.8/bin/openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem

/usr/local/openssl-0.9.8/bin/openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

The output of…

ls -l

… should now look as follows:

root@remote:/etc/mysql/newcerts# ls -l
total 32
-rw-r–r– 1 root root 1346 Sep 18 17:52 ca-cert.pem
-rw-r–r– 1 root root 1679 Sep 18 17:52 ca-key.pem
-rw-r–r– 1 root root 1099 Sep 18 17:53 client-cert.pem
-rw-r–r– 1 root root 1679 Sep 18 17:53 client-key.pem
-rw-r–r– 1 root root 956 Sep 18 17:53 client-req.pem
-rw-r–r– 1 root root 1099 Sep 18 17:53 server-cert.pem
-rw-r–r– 1 root root 1679 Sep 18 17:53 server-key.pem
-rw-r–r– 1 root root 956 Sep 18 17:53 server-req.pem
root@remote:/etc/mysql/newcerts#

We must now transfer ca-cert.pem, client-cert.pem, and client-key.pem to the local MySQL server (on local.example.com); before we do this, we create the directory /etc/mysql/newcerts on local.example.com:

local.example.com:

mkdir /etc/mysql/newcerts

Back on remote.example.com, we can transfer the three files to local.example.com as follows:

remote.example.com:

scp /etc/mysql/newcerts/ca-cert.pem root@local.example.com:/etc/mysql/newcerts

scp /etc/mysql/newcerts/client-cert.pem root@local.example.com:/etc/mysql/newcerts

scp /etc/mysql/newcerts/client-key.pem root@local.example.com:/etc/mysql/newcerts

Next, open /etc/mysql/my.cnf

vi /etc/mysql/my.cnf

… and modify the * Security Features section; uncomment the ssl-ca, ssl-cert, and ssl-key lines and fill in the correct values:

[...]
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
ssl
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
ssl-cert=/etc/mysql/newcerts/server-cert.pem
ssl-key=/etc/mysql/newcerts/server-key.pem
[...]

Restart MySQL:

/etc/init.d/mysql restart

Now log into MySQL again…

mysql -u root -p

… and check that SSL is enabled and that the correct certificates go loaded:

show variables like ‘%ssl%’;

mysql> show variables like ‘%ssl%’;
+—————+————————————-+
| Variable_name | Value                               |
+—————+————————————-+
| have_openssl  | YES                                 |
| have_ssl      | YES                                 |
| ssl_ca        | /etc/mysql/newcerts/ca-cert.pem     |
| ssl_capath    |                                     |
| ssl_cert      | /etc/mysql/newcerts/server-cert.pem |
| ssl_cipher    |                                     |
| ssl_key       | /etc/mysql/newcerts/server-key.pem  |
+—————+————————————-+
7 rows in set (0.00 sec)

mysql>

Don’t leave the MySQL shell. We are now going to create a MySQL root user that is allowed to connect from local.example.com to remote.example.com and is required to use SSL:

CREATE USER ‘root’@’192.168.0.100’ IDENTIFIED BY ‘mysqlrootpassword’;
GRANT ALL PRIVILEGES ON * . * TO ‘root’@’192.168.0.100’ IDENTIFIED BY ‘mysqlrootpassword’ WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

GRANT USAGE ON *.* TO ‘root’@’192.168.0.100’ REQUIRE SSL;

CREATE USER ‘root’@’local.example.com’ IDENTIFIED BY ‘mysqlrootpassword’;
GRANT ALL PRIVILEGES ON * . * TO ‘root’@’local.example.com’ IDENTIFIED BY ‘mysqlrootpassword’ WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

GRANT USAGE ON *.* TO ‘root’@’local.example.com’ REQUIRE SSL;

FLUSH PRIVILEGES;

Now you can leave the MySQL shell:

quit;

That’s it – we now have a MySQL root user that is allowed to connect from local.example.com to remote.example.com, but is required to use SSL encryption.

3 Configuring phpMyAdmin

local.example.com:

Before we proceed with configuring phpMyAdmin, let’s first check if we can really connect from local.example.com to the MySQL server on remote.example.com (through sn SSL connection):

mysql –ssl-ca=/etc/mysql/newcerts/ca-cert.pem –ssl-cert=/etc/mysql/newcerts/client-cert.pem –ssl-key=/etc/mysql/newcerts/client-key.pem -h remote.example.com -u root -p

You will be asked for the password, and if all goes well, you will be granted access:

root@local:/etc/mysql/newcerts# mysql –ssl-ca=/etc/mysql/newcerts/ca-cert.pem –ssl-cert=/etc/mysql/newcerts/client-cert.pem –ssl-key=/etc/mysql/newcerts/client-key.pem -h remote.example.com -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 101
Server version: 5.5.24-8-log (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

Type

quit;

to leave the MySQL shell.

Now we come to the part where we configure phpMyAdmin – I assume that you can already use phpMyAdmin to connect to the local MySQL server, and now we want to configure it so that we can connect to remote.example.com as well using SSL (on the phpMyAdmin login screen, there will be a dropdown menu from which you can select the server that you want to connect to).

Before phpMyAdmin can use SSL to talk to the remote MySQL server, we need to modify its sources a bit – open mysqli.dbi.lib.php (on Debian and Ubuntu, it’s /usr/share/phpmyadmin/libraries/dbi/mysqli.dbi.lib.php if you have installed phpMyAdmin through apt):

vi /usr/share/phpmyadmin/libraries/dbi/mysqli.dbi.lib.php

Find the following section…

[...]
    /* Optionally enable SSL */
    if ($GLOBALS['cfg']['Server']['ssl'] && defined('MYSQLI_CLIENT_SSL')) {
        $client_flags |= MYSQLI_CLIENT_SSL;
    }
[...]

… and modify it as follows:

[...]
    /* Optionally enable SSL */
    if ($GLOBALS['cfg']['Server']['ssl'] && defined('MYSQLI_CLIENT_SSL')) {
        mysqli_ssl_set($link, $GLOBALS['cfg']['Server']['key'], $GLOBALS['cfg']['Server']['cert'], $GLOBALS['cfg']['Server']['ca'], $GLOBALS['cfg']['Server']['capath'], $GLOBALS['cfg']['Server']['cipher']);
        $client_flags |= MYSQLI_CLIENT_SSL;
    }
[...]

Next open config.inc.php – if you are on Debian/Ubuntu and have installed phpMyAdmin through apt, it’s /etc/phpmyadmin/config.inc.php:

vi /etc/phpmyadmin/config.inc.php

Find the following section:

[...]
/* Configure according to dbconfig-common if enabled */
if (!empty($dbname)) {
    /* Authentication type */
    $cfg['Servers'][$i]['auth_type'] = 'cookie';
    /* Server parameters */
    if (empty($dbserver)) $dbserver = 'localhost';
    $cfg['Servers'][$i]['host'] = $dbserver;

    if (!empty($dbport) || $dbserver != 'localhost') {
        $cfg['Servers'][$i]['connect_type'] = 'tcp';
        $cfg['Servers'][$i]['port'] = $dbport;
    }
    //$cfg['Servers'][$i]['compress'] = false;
    /* Select mysqli if your server has it */
    $cfg['Servers'][$i]['extension'] = 'mysqli';
    /* Optional: User for advanced features */
    $cfg['Servers'][$i]['controluser'] = $dbuser;
    $cfg['Servers'][$i]['controlpass'] = $dbpass;
    /* Optional: Advanced phpMyAdmin features */
    $cfg['Servers'][$i]['pmadb'] = $dbname;
    $cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
    $cfg['Servers'][$i]['relation'] = 'pma_relation';
    $cfg['Servers'][$i]['table_info'] = 'pma_table_info';
    $cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
    $cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
    $cfg['Servers'][$i]['column_info'] = 'pma_column_info';
    $cfg['Servers'][$i]['history'] = 'pma_history';
    $cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
    $cfg['Servers'][$i]['tracking'] = 'pma_tracking';
    $cfg['Servers'][$i]['userconfig'] = 'pma_userconfig';

    /* Uncomment the following to enable logging in to passwordless accounts,
     * after taking note of the associated security risks. */
    // $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

    /* Advance to next server for rest of config */
    $i++;
}
[...]

Add the following section below it for localhost (= local.example.com):

[...]
/* localhost.example.com */
/* 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'] = 'mysqli';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|performance_schema|test)';
$i++;
[...]

(In the $cfg[‘Servers’][$i][‘hide_db’] line you can specify all databases that you do not want to show up in phpMyAdmin – like information_schema, performance_schema, and test.)

And for remote.example.com, add the following section:

[...]
/* remote.example.com */
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'remote.example.com';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['ssl'] = true;
$cfg['Servers'][$i]['key'] = '/etc/mysql/newcerts/client-key.pem';
$cfg['Servers'][$i]['cert'] = '/etc/mysql/newcerts/client-cert.pem';
$cfg['Servers'][$i]['ca'] = '/etc/mysql/newcerts/ca-cert.pem';
$cfg['Servers'][$i]['capath'] = NULL;
$cfg['Servers'][$i]['cipher'] = NULL;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|performance_schema|test)';
$i++;
[...]

Make sure you use mysqli in $cfg[‘Servers’][$i][‘extension’], set $cfg[‘Servers’][$i][‘ssl’] to true and specify the SSL files as shown. My complete servers section looks as follows:

[...]
/* Configure according to dbconfig-common if enabled */
if (!empty($dbname)) {
    /* Authentication type */
    $cfg['Servers'][$i]['auth_type'] = 'cookie';
    /* Server parameters */
    if (empty($dbserver)) $dbserver = 'localhost';
    $cfg['Servers'][$i]['host'] = $dbserver;

    if (!empty($dbport) || $dbserver != 'localhost') {
        $cfg['Servers'][$i]['connect_type'] = 'tcp';
        $cfg['Servers'][$i]['port'] = $dbport;
    }
    //$cfg['Servers'][$i]['compress'] = false;
    /* Select mysqli if your server has it */
    $cfg['Servers'][$i]['extension'] = 'mysqli';
    /* Optional: User for advanced features */
    $cfg['Servers'][$i]['controluser'] = $dbuser;
    $cfg['Servers'][$i]['controlpass'] = $dbpass;
    /* Optional: Advanced phpMyAdmin features */
    $cfg['Servers'][$i]['pmadb'] = $dbname;
    $cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
    $cfg['Servers'][$i]['relation'] = 'pma_relation';
    $cfg['Servers'][$i]['table_info'] = 'pma_table_info';
    $cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
    $cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
    $cfg['Servers'][$i]['column_info'] = 'pma_column_info';
    $cfg['Servers'][$i]['history'] = 'pma_history';
    $cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
    $cfg['Servers'][$i]['tracking'] = 'pma_tracking';
    $cfg['Servers'][$i]['userconfig'] = 'pma_userconfig';

    /* Uncomment the following to enable logging in to passwordless accounts,
     * after taking note of the associated security risks. */
    // $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

    /* Advance to next server for rest of config */
    $i++;
}

/* localhost.example.com */
/* 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'] = 'mysqli';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|performance_schema|test)';
$i++;

/* remote.example.com */
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'remote.example.com';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['ssl'] = true;
$cfg['Servers'][$i]['key'] = '/etc/mysql/newcerts/client-key.pem';
$cfg['Servers'][$i]['cert'] = '/etc/mysql/newcerts/client-cert.pem';
$cfg['Servers'][$i]['ca'] = '/etc/mysql/newcerts/ca-cert.pem';
$cfg['Servers'][$i]['capath'] = NULL;
$cfg['Servers'][$i]['cipher'] = NULL;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|performance_schema|test)';
$i++;

/* End of servers configuration
 */
[...]

That’s it – on the phpMyAdmin login screen, there should now be a dropdown menu from which you can select the MySQL server you want to connect to (localhost or remote.example.com), and if you select a remote MySQL server (like remote.example.com), phpMyAdmin will connect to it through an SSL connection.

  • phpMyAdmin: http://www.phpmyadmin.net/
  • MySQL: http://www.mysql.com/

Comments

comments