Check MySQL has SSL Support

mysql --ssl --help

                ssl           TRUE

 

show variables like ‘%ssl%’;

                have_openssl    YES

                have_ssl              YES

 

 

Generate SSL Certificates

mkdir /etc/mysql-ssl

cd /etc/mysql-ssl

 

CA

openssl genrsa 2048 > ca-key.pem

openssl req -new -x509 -nodes -days 9000 -key ca-key.pem > ca-cert.pem

 

Server

openssl req -newkey rsa:2048 -days 9000 -nodes -keyout server-key.pem > server-req.pem

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

 

Client

openssl req -newkey rsa:2048 -days 9000 -nodes -keyout client-key.pem > client-req.pem

openssl x509 -req -in client-req.pem -days 9000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

 

 

Configure the MySQL Server to use SSL Encryption

[mysqld]

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

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

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

 

 

Configure the MySQL Clients to use SSL Encryption

[client]

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

ssl-cert=/etc/mysql-ssl/client-cert.pem

ssl-key=/etc/mysql-ssl/client-key.pem

 

 

Create MySQL User that is Required to use SSL

GRANT SELECT, INSERT, UPDATE, DELETE on mydb.* to ‘ssluser’@’host’ IDENTIFIED BY ‘secretpass’ REQUIRE SSL;

FLUSH PRIVILEGES;

 

 

 

Login to MySQL using SSL Encryption

Using command line parameters without /etc/my.cnf [client] section

mysql--ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem –ussluser –p

 

Using the [client] section in /etc/my.cnf

mysql –ussluser –p

 

 

Configure PHP MySQL Connections to use SSL

NOTE: Uses /etc/my.cnf [client] section

mysql_pconnect(‘serverhost’, ‘ssluser’, ‘secretpass’, MYSQL_CLIENT_SSL);

 

 

Using SSL in MySQL Query Browser and MySQL Administrator

Add the following parameters under the advanced parameters tab:

USE_SSL               YES

SSL_CA                 ca-cert.pem

SSL_CERT             client-cert.pem

SSL_KEY               client-key.pem

Press the down arrow to add a new parameter

 

MySQL Replication

stop slave;

change master to

master_ssl=1,

master_ssl_ca=’/etc/mysql-ssl/ca-cert.pem’,

master_ssl_cert=’/etc/mysql/ssl/client-cert.pem’,

master_ssl_key=’/etc/mysql/ssl/client-key.pem’;

start slave;

show slave status;

 

Check SSL Encryption is Working

At a MySQL prompt type:

show status like ‘%ssl%’;

                Ssl_cipher           DHE-RSA-AES256-SHA

\s

Cipher in use is DHE-RSA-AES256-SHA

 

 

 

Share this blog post on social media:

Social Links

Disclaimer

All advice, installation/configuration how to guides, troubleshooting and other information on this website are provided as-is with no warranty or guarantee. Whilst the information provided is correct to the best of my knowledge, I am not reponsible for any issues that may arise using this information, and you do so at your own risk. As always before performing anything; check, double check, test and always ensure you have a backup.

Copyright ©2016 Andy Barnes - Please do not copy any content including images without prior consent!

Designed and Hosted by Andy Barnes