MySQL Tutorial => Setup for Debian-based systems

Example

(This assumes MySQL has been installed and that sudo is being used.)

Generating a CA and SSL keys

Make sure OpenSSL and libraries are installed:

apt-get -y install openssl
apt-get -y install libssl-dev

Next make and enter a directory for the SSL files:

mkdir /home/ubuntu/mysqlcerts
cd /home/ubuntu/mysqlcerts

To generate keys, create a certificate authority (CA) to sign the keys (self-signed):

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

The values entered at each prompt won't affect the configuration. Next create a key for the server, and sign using the CA from before:

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem

openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Then create a key for a client:

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

To make sure everything was set up correctly, verify the keys:

openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

Adding the keys to MySQL

Open the MySQL configuration file. For example:

vim /etc/mysql/mysql.conf.d/mysqld.cnf

Under the [mysqld] section, add the following options:

ssl-ca = /home/ubuntu/mysqlcerts/ca.pem
ssl-cert = /home/ubuntu/mysqlcerts/server-cert.pem
ssl-key = /home/ubuntu/mysqlcerts/server-key.pem

Restart MySQL. For example:

service mysql restart

Test the SSL connection

Connect in the same way, passing in the extra options ssl-ca, ssl-cert, and ssl-key, using the generated client key. For example, assuming cd /home/ubuntu/mysqlcerts:

mysql --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -h 127.0.0.1 -u superman -p

After logging in, verify the connection is indeed secure:

[email protected] [None]> SHOW VARIABLES LIKE '%ssl%';
+---------------+-----------------------------------------+
| Variable_name | Value                                   |
+---------------+-----------------------------------------+
| have_openssl  | YES                                     |
| have_ssl      | YES                                     |
| ssl_ca        | /home/ubuntu/mysqlcerts/ca.pem          |
| ssl_capath    |                                         |
| ssl_cert      | /home/ubuntu/mysqlcerts/server-cert.pem |
| ssl_cipher    |                                         |
| ssl_crl       |                                         |
| ssl_crlpath   |                                         |
| ssl_key       | /home/ubuntu/mysqlcerts/server-key.pem  |
+---------------+-----------------------------------------+

You could also check:

[email protected] [None]> STATUS;
...
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
...

Enforcing SSL

This is via GRANT, using REQUIRE SSL:

GRANT ALL PRIVILEGES ON *.* TO 'superman'@'127.0.0.1' IDENTIFIED BY 'pass' REQUIRE SSL;
FLUSH PRIVILEGES;

Now, superman must connect via SSL.

If you don't want to manage client keys, use the client key from earlier and automatically use that for all clients. Open MySQL configuration file, for example:

vim /etc/mysql/mysql.conf.d/mysqld.cnf

Under the [client] section, add the following options:

ssl-ca = /home/ubuntu/mysqlcerts/ca.pem
ssl-cert = /home/ubuntu/mysqlcerts/client-cert.pem
ssl-key = /home/ubuntu/mysqlcerts/client-key.pem

Now superman only has to type the following to login via SSL:

mysql -h 127.0.0.1 -u superman -p

Connecting from another program, for example in Python, typically only requires an additional parameter to the connect function. A Python example:

import MySQLdb
ssl = {'cert': '/home/ubuntu/mysqlcerts/client-cert.pem', 'key': '/home/ubuntu/mysqlcerts/client-key.pem'}
conn = MySQLdb.connect(host='127.0.0.1', user='superman', passwd='imsoawesome', ssl=ssl)

References and further reading:

PDF - Download MySQL for free

Previous Next

MySQL

Fastest Entity Framework Extensions

Example

Generating a CA and SSL keys

Adding the keys to MySQL

Test the SSL connection

Enforcing SSL

References and further reading:

Got any MySQL Question?

MySQL

MySQL SSL Connection Setup Setup for Debian-based systems

Fastest Entity Framework Extensions

Example

Generating a CA and SSL keys

Adding the keys to MySQL

Test the SSL connection

Enforcing SSL

References and further reading:

Got any MySQL Question?