The implementation of SSL in an MySQL server encrypts all data going back and forth between a server and a client, thereby preventing potential eavesdropping or data sniffing in wide area networks or within data centers. In addition, SSL also provides identify verification by means of SSL certificates, which can protect users against possible phishing attacks.
In this article, we will show you how to enable SSL on MySQL server. Note that the same procedure is also applicable to MariaDB server.
Creating Server SSL Certificate and Private Key
We have to create an SSL certificate and private key for an MySQL server, which will be used when connecting to the server over SSL.
First, create a temporary working directory where we will keep the key and certificate files.
Make sure that OpenSSL is installed on your system where an MySQL server is running. Normally all Linux distributions have OpenSSL installed by default. To check if OpenSSL is installed, use the following command.
OpenSSL 1.0.1f 6 Jan 2014
Now go ahead and create the CA private key and certificate. The following commands will create ca-key.pem and ca-cert.pem.
The second command will ask you several questions. It does not matter what you put in these field. Just fill out those fields.
The next step is to create a private key for the server.
This command will ask several questions again, and you can put the same answers which you have provided in the previous step.
Next, export the server's private key to RSA-type key with this command below.
Finally, generate a server certificate using the CA certificate.
Configuring SSL on MySQL Server
After the above procedures, we should have a CA certificate, a server's private key and its certificate. The next step is to configure our MySQL server to use the key and certificates.
Before configuring the MySQL server, check whether the SSL options are enabled or disabled. For that, log in to the MySQL server, and type the query below.
The result of this query will look like the following.
Note that the default value of 'have_openssl' and 'have_ssl' variables is 'disabled' as shown above. To enable SSL in the MySQL server, go ahead and follow the steps below.
1. Copy or move ca-cert.pem, server-cert.pem, and server-key.pem under /etc directory.
2. Open my.cnf of the server using a text editor. Add or un-comment the lines that look like below in [mysqld] section. These should point to the key and certificates you placed in /etc/mysql-ssl.
[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
3. In my.cnf, also find "bind-address = 127.0.0.1", and change it to:
bind-address = *
That way, you can connect to the MySQL server from another host.
4. Restart MySQL service.
You can check whether the SSL configuration is working or not by examining the MySQL error log file (e.g., /var/log/mysql/mysql.log). If no warning or error is shown in the error log (like the screenshot below), it means that SSL configuration works okay.
Another way to verify SSL configuration is by re-running the 'have_%ssl' query inside the MySQL server.
Creating a User with SSL Privilege
After the server-side SSL configuration is finished, the next step is to create a user who has a privilege to access the MySQL server over SSL. For that, log in to the MySQL server, and type:
Replace 'ssluser' (username) and 'dingdong' (password) with your own.
If you want to give a specific ip address (e.g., 192.168.2.8) from which the user will access the server, use the following query instead.
Configure SSL on MySQL Client
Now that MySQL server-side configuration is done, let's move to the client side. For MySQL client, we need to create a new key and certificate based on server's CA key and certificate.
Run the following commands on the MySQL server host where the server's CA key and certificate reside.
Similar to server-side configuration, the above command will ask several questions. Just fill out the fields like we did before.
We also need to convert the generated client key into RSA type as follows.
Finally we need to create a client certificate using the server's CA key and certificate.
Now transfer the ca-cert.pem, client-cert.pem, and client-key.pem files to to any host where you want to run MySQL client.
On the client host, use the following command to connect to the MySQL server with SSL.
After typing the ssluser's password, you will see the MySQL prompt as usual.
To check whether you are on SSL, type status command at the prompt.
If you are connected over SSL, it will show you the cipher information in the SSL field as shown below.
If you do not want to specify client certificate and key information in the command line, you can create ~/.my.cnf file, and put the following information under [client] section.
[client] ssl-ca=/path/to/ca-cert.pem ssl-cert=/path/to/client-cert.pem ssl-key=/path/to/client-key.pem
With that, you can simply use the following command line to connect to the server over SSL.