How to Configure an SSL Certificate for MySQL

Introduction

This article describes how to configure your server to use SSL for MySQL database connections to allow users to encrypt their connections when using applications such as MySQL workbench.

Procedure

1. SSH into the server or open "Terminal" in WHM as the 'root' user.
2. Create an SSL key storage directory that MySQL can access.

   mkdir /mysql_keys

   The commands in this article assume the SSL key storage directory is /mysql_keys.
3. Run the following commands to create the Certificate Authority (CA) keys.

   openssl genrsa 2048 > /mysql_keys/ca-key.pem

   openssl req -new -x509 -nodes -days 3650 -key /mysql_keys/ca-key.pem > /mysql_keys/ca-cert.pem

   The Common Name of the certificates and keys for the client and server must be different from the Common Name of the CA certificate. 
4. Run the following commands to create the server SSL key and certificate.

   openssl req -newkey rsa:2048 -days 3650 -nodes -keyout /mysql_keys/server-key.pem > /mysql_keys/server-req.pem

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

   openssl rsa -in /mysql_keys/server-key.pem -out /mysql_keys/server-key.pem

5. Run the following commands to create the client SSL key and certificate.

   openssl req -newkey rsa:2048 -days 3650 -nodes -keyout /mysql_keys/client-key.pem > /mysql_keys/client-req.pem

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

   openssl rsa -in /mysql_keys/client-key.pem -out /mysql_keys/client-key.pem

6. Run the following command to update the file permissions of the /mysql_keys directory and its files.

   chown -Rf mysql. /mysql_keys

7. Backup the /etc/my.cnf file.

   cp -p /etc/my.cnf{,.bak}

8. Open /etc/my.cnf with a text editor, such as nano or vim.
9. Insert the following lines in the [mysqld] section of the my.cnf file.

   ssl
   ssl-cipher=DHE-RSA-AES256-SHA
   ssl-ca=/mysql_keys/ca-cert.pem
   ssl-cert=/mysql_keys/server-cert.pem
   ssl-key=/mysql_keys/server-key.pem

10. Insert the following lines in the [client] section of the my.cnf file. If the [client] section doesn't exist, add the [client] section.

    ssl-mode=REQUIRED
    ssl-cert=/mysql_keys/client-cert.pem
    ssl-key=/mysql_keys/client-key.pem

    For MariaDB, use "ssl" instead of "ssl-mode=REQUIRED."
11. Save the changes to my.cnf and exit the text editor.
12. Restart MySQL.

    /scripts/restartsrv_mysql

Additional Resources

How to Configure MySQL SSL Connections