By default when you install MySQL server, it will only allow connections from the local system for users with the correct credentials regardless of the transport protocol. If you want to add another layer of security, you can enable SSL/TLS certificate settings and force all users to connect securely. For this tutorial, we’re going to be creating a self-signed certificate to configure with MySQL. To get started with configuring MySQL with SSL/TLS certificates, follow the steps below:

Create SSL Cert

Since we’re creating self-signed certificates, simply run the commands below to create a directory where the cert files will be created. After creating the directory, change it and begin creating your self-signed certificates. Now that the directory is created and you have changed into it, run the commands below to create the CA certificate and private key. Create a CA key and CA cert The commands above will generate a 2048-bit key length and create a new 1-year (365 days) private key. You may increase the key length and expiration date if you want for the private key. While creating the private key, you’ll be prompted for details of the key you’re generating. Next, create a private key for the server. You’ll be prompted as above. Type the details that you want to include with the cert. Create a server private key When you’re done above, export the server’s private key to an RSA-type key using the commands below: After all the above, run the commands below to generate an SSL cert using the commands below: Generate SSL/TLS cert In the directory, you should have these files:

ca-cert.pem ca-key.pem server-cert.pem server-key.pem server-req.pem

Configure MySQL SSL/TLS Connection

Now that you’re created a self-signed certificate, go to MySQL and configure it to connect over SSL/TLS. Then make MySQL the user owner of the directory above. When you’re done, open the MySQL configuration file. Then add the highlighted lines to enable SSL/TLS After adding the lines above, restart MySQL. The require_secure_transport = ON option forces all users to connect over SSL. Next, connect to MySQL via SSL and verify SSL/TLS are loaded by running the command below: Then run the query below: It should show similar lines as below: The “have_ssl” in MySQL says whether SSL support is available, while “have_openssl” says, specifically, whether OpenSSL is compiled in.
So, if you have MySQL built with YaSSL, have_ssl will be YES, while have_openssl will be NO. To connect via the client over SSL/TLS, run the commands below: Then show the cipher being used: It should display similar lines as below: After enabling SSL/TLS, you should begin creating users and requiring SSL/TLS to log in. Exit and you’re done. To force all connections to use SSL regardless of what a user configuration is, run the SQL statement below. This example forces the root user to use SSL before connecting. Check MySQL status to view the current configuration Conclusion: This post showed you how to configure MySQL server to connect over SSL/TLS. If you find an error above, please use the form below to report it.