First thing to note: The community build of MySQL does not support SSL. You either need the Enterprise or you need to build it yourself.
To check if SSL is enabled on your build:
log into MySQL via any account
mysql> show variables like 'have_ssl'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_ssl | YES | +---------------+-------+ 1 row in set (0.01 sec)
If it says YES, then MySQL is compiled with SSL support, otherwise a new binary will need to be generated.
Once MySQL has SSL, then you can set the configuration options in the MySQL Configuration/INI file as such (with path’s obviously modified):
[mysqld] ssl-ca=/usr/mysql-5.0.84/ssl/ca-cert.pem ssl-cert=/usr/mysql-5.0.84/ssl/mysql.cert ssl-key=/usr/mysql-5.0.84/ssl/mysql.key ssl-cipher=ALL
(The last line enables all SSL cipher modes except NULL encryption)
This assumes you have the CA Public Certificate saved as ca-cert.pem, and Public/Private key-certificate pair for your mysql server. That will be another post
To test the functionality, log in to MySQL using an administrator account
mysql> create database test; mysql> grant all privileges on test.* to test@localhost identified by 'testpassword' require ssl;
Then you can attempt to log in to the server as the test user:
mysql -u test -p --ssl-ca=ssl/cacert.pem --ssl-cipher=ALL
Without the ssl-cipher line, you get an SSL connection error because it does not know how to encrypt the connection that both parties can communicate; the CA certificate is required to activate the SSL connection and to validate the server, AFAICT.
You don’t technically need to use “ALL” for the cipher entries. There are a number of choices that you can select, but for the purposes of demonstration, ALL was the simplest.
Part 2 will cover more detailed user restrictions. This setup effectively only require that the connection get SSL encryption (confidentiality), but does not validate the user (authenticity).
Part 3 will implement replication over SSL.