Difference between revisions of "MySQL SSL"

Revision as of 04:13, 3 February 2016

Setting up MySQL with SSL allows secure encrypted connections between the MySQL server the MySQL client. This guide will walk you through setting up SSL for MySQL on your VPS.

1. Generate server and client certificates. You will need to generate 2 certificates pairs, one for the server and one for the client. The official MySQL documentation has a guide on generating the server and client certificates: http://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html
2. Once your certificates are generated, create a directory on the server to store the server certificates such as C:\MySQL\mysql-ssl (Windows) or /etc/mysql-ssl (Linux) and copy the server-key.pem, server-cert.pem and ca.pem files there.
3. Edit the my.ini file (Windows) or my.cnf file (Linux). Under the [mysqld] section add the following:

   my.ini on Windows

   ssl-ca="C:/MySQL/mysql-ssl/ca-cert.pem"
   ssl-cert="C:/MySQL/mysql-ssl/server-cert.pem"
   ssl-key="C:/MySQL/mysql-ssl/server-key.pem"

   my.cnf on Linux

   ssl-ca=/etc/mysql-ssl/ca-cert.pem
   ssl-cert=/etc/mysql-ssl/server-cert.pem
   ssl-key=/etc/mysql-ssl/server-key.pem

4. Restart MySQL server
5. Test if MySQL now has SSL enabled by running this query at the MySQL Prompt

   show global variables like 'have_%ssl';

6. If you get the following output, it means SSL is properly enabled on your MySQL server

   mysql> show global variables like 'have_%ssl';
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | have_openssl  | YES   |
   | have_ssl      | YES   |
   +---------------+-------+
   2 rows in set (0.00 sec)

7. If the value is not set to yes, you will need to look at the MySQL error log for more information. The MySQL log is normally found in C:\MySQL\data\mysqld.err (Windows) or /var/log/mysqld.log (Linux).
8. To create a user that can only connect to your MySQL server using SSL, run the following query at the MySQL prompt:

   GRANT ALL PRIVILEGES ON *.* TO `user_name`@`%` IDENTIFIED BY 'password' REQUIRE SSL;

9. You will need to use the generated client certificate/key pairs (from Step 1) to be connect MySQL using SSL