Some good advice in an age of people, companies, and governments avaricious to acquire / store / use / sell your personal information: use encryption wherever possible when communicating over networks.
Here is a nice concise guide to the basics of getting SSL working on MySQL.
First login to MySQL and check for SSL support:
# mysql -p
mysql> show variables like '%ssl%';
You should see "DISABLED" at this point, since you have not set it up yet. (If the response says anything other then "DISABLED" or "YES", then your MySQL server has probably been compiled without SSL support. Not a problem on Debian....)
Then Enable SSL Support in the Server:
FOR MySQL 5.5 YOU MUST USE A VERSION OF OPENSSL LESS THAN 1.0 TO CREATE THE FOLLOWING CERTIFICATES. Otherwise, when you try to login with the MySQL client using SSL, you will see this kind of error:
# mysql -uuser -ppassword --ssl-ca=/etc/mysql/ca-cert.pem
ERROR 2026 (HY000): SSL connection error: protocol version mismatch
I found an Ubuntu Lucid server which had a sufficiently old version of openssl to do the job.
First create the CA certificate:
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3601 -key ca-key.pem > ca-cert.pem
Now create the server certificate:
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout mysql-server-key.pem > mysql-server-req.pem
openssl x509 -req -in mysql-server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > mysql-server-cert.pem
Now fix up the permissions of the SSL certs if necessary, and add this to the [mysqld] block of your /etc/mysql/my.cnf:
Note that client certificates are not necessary unless you WANT the server to authenticate the client. Also note that on Debian MySQL logging seems to go to syslog, not to the visible /var/log/mysql* log files.
After restarting MySQL,
mysql> show variables like 'have_ssl';
should result in a "YES".
Now Get MySQL clients Working:
Test a client using SSL on MySQL localhost. Create a temporary user for the test:
mysql> GRANT ALL on databasename.* TO 'ssluser'@'localhost' IDENTIFIED BY 'thispassword' REQUIRE SSL;From a terminal on the MySQL server, try logging in with this user:
mysql -ussluser -p --ssl-ca=/etc/mysql/cacert.pem
Once logged in, issue this MySQL command:
mysql> SHOW STATUS LIKE 'Ssl_cipher';
If you get anything other than a blank in the 'Value' column, SSL is working! Delete the test user:
mysql> DELETE FROM mysql.user WHERE user='ssluser' and host='localhost';And still on the MySQL server, create a user for remote access, from a specific IP address only:
mysql> GRANT ALL on databasename.* TO 'SSLremote'@'220.127.116.11' IDENTIFIED BY 'thispassword' REQUIRE SSL;On the remote client (IP address 18.104.22.168) presumably your desktop, try to login over SSL:
mysql -uSSLremote -pthispassword -hwww.mysqlserverhost.com --ssl-ca=/home/user/cacert.pem
If it works, mission accomplished!