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)...

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:

3 Comment(s)

Comments

Great tutorial! Wat startled me was that client certificates were being ignored, I could connect to the database server using an SSL connection without defining client certificates.

The was caused by "REQUIRE SSL" which happens to only require an encrypted connection without checking any certificates. Granting permissions using ie. "REQUIRE X509" requires at least a client key and cert file.