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

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>
Follow me on Twitter
Last edited 09/18/2012

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

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.