How to Configure MySQL SSL Connections

Overview

This document provides instructions on how to configure your server to utilize SSL for MySQL® database connections. By securing your MySQL connections, you can prevent malicious users from intercepting your MySQL traffic. To successfully complete this tutorial, you must have root access to both the client and host servers.

Configure MySQL SSL Connections

Create the Directory for SSL Keys

To create the directory that will store the SSL keys, follow these steps:

1. Log in to your server via SSH. For more information, refer to our SSH Access documentation.
2. Create an SSL key storage directory that MySQL can access. For example, execute the command mkdir /mysql_keys to create a directory named mysql_keys.

Generate SSL Keys

Note:

• In the following examples, /mysql_keys represents the key storage directory.
• The Common Name for the client and server certificates and keys must be distinct from the Common Name of the CA certificate. Using identical Common Names will result in an error similar to: ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

To generate the SSL keys, perform the following steps:

1. Execute 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

2. Execute 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

3. Execute 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

Modify the MySQL Configuration

To edit the MySQL configuration, follow these steps:

1. Open the /etc/my.cnf file using your preferred text editor.
2. Insert the following lines into 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

3. Insert the following lines into the [client] section of the my.cnf file:

   Note: If the [client] section does not already exist, you must add it.

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

   Your updated my.cnf file should resemble the following example:

   [mysqld]
       max_connections=500
       log-slow-queries
   
       max_allowed_packet=268435456
       open_files_limit=10000
       default-storage-engine=MyISAM
       innodb_file_per_table=1
       performance-schema=0
   
       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
   
   [client]
       ssl-mode=REQUIRED
       ssl-cert=/mysql_keys/client-cert.pem
       ssl-key=/mysql_keys/client-key.pem

   Note:

   • The ssl-mode=REQUIRED setting establishes an encrypted connection if the server supports it. If the server cannot create an encrypted connection, the connection will fail. For more information on additional options for the ssl-mode setting, consult MySQL’s ssl-mode documentation.
   • MySQL introduced the ssl-mode setting in MySQL 5.7. If you are running MySQL 5.6 or an older version, replace ssl-mode=REQUIRED with ssl.
4. Save your changes to the /etc/my.cnf file and close your text editor.
5. Execute the following command to update the file permissions for the /mysql_keys directory and its contents:

   chown -Rf mysql. /mysql_keys

6. Run the /scripts/restartsrv_mysql script to restart MySQL.

Test the SSL Configuration

To verify the SSL configuration, follow these steps:

1. To view MySQL’s active SSL configuration, execute the following command:

   mysql -e "show variables like '%ssl%';"

   The output should resemble the following example:

   +---------------+------------------------+
   | Variable_name | Value                  |
   +---------------+------------------------+
   | have_openssl  | YES                    |
   | have_ssl      | YES                    |
   | ssl_ca        | /mysql_keys/ca-cert.pem |
   | ssl_capath    |                        |
   | ssl_cert      | /mysql_keys/server-cert.pem |
   | ssl_cipher    | DHE-RSA-AES256-SHA     |
   | ssl_key       | /mysql_keys/server-key.pem  |
   +---------------+------------------------+

2. To check a local connection to MySQL, execute the following command (replace example with the actual account username):

   mysql -u example -p

3. When prompted, enter the password for the MySQL user account.
4. After successfully connecting, run the status command. The output will be similar to this example:

   mysql> status
   --------------
   mysql  Ver 14.14 Distrib 5.5.42, for Linux (x86_64) using readline 5.1
   
   Connection id:        19
   Current database:
   Current user:        example@localhost
   SSL:            Cipher in use is DHE-RSA-AES256-SHA
   Current pager:        stdout
   Using outfile:        ''
   Using delimiter:    ;
   Server version:        5.5.42-cll MySQL Community Server (GPL)
   Protocol version:    10
   Connection:        Localhost via UNIX socket
   Server characterset:    latin1
   Db     characterset:    latin1
   Client characterset:    utf8
   Conn.  characterset:    utf8
   UNIX socket:        /var/lib/mysql/mysql.sock
   Uptime:            4 min 36 sec
   Threads: 1  Questions: 67  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 27  Queries per second avg: 0.242
   --------------

Optional: Create a User and Enable Remote Access

Note: This step is optional.

To create a user and enable remote access, follow these steps:

1. Navigate to the MySQL® Databases interface (cPanel » Home » Databases » MySQL® Databases) and create your database user.
2. Add the remote server’s IP address to the Remote MySQL® interface (cPanel » Home » Databases » Remote MySQL®).
3. On the remote MySQL server, create an SSL key storage directory that MySQL can access. For instance, use the mkdir /mysql_keys command to create a mysql_keys directory.

   Note: You can skip this step if the /mysql_keys directory already exists on the remote MySQL server.

4. Copy the client SSL certificate to the remote MySQL server’s /mysql_keys directory.
5. On the remote MySQL server, open the /etc/my.cnf file with your preferred text editor.
6. Insert the following lines into the [client] section of the my.cnf file:

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

   Note: If the [client] section does not exist, you must add the section.

   Your updated /etc/my.cnf file should resemble the following example:

   [mysqld]
       max_connections=500
       log-slow-queries
   
       max_allowed_packet=268435456
       open_files_limit=10000
       default-storage-engine=MyISAM
       innodb_file_per_table=1
       performance-schema=0
   
   
   [client]
       ssl-mode=REQUIRED
       ssl-cert=/mysql_keys/client-cert.pem
       ssl-key=/mysql_keys/client-key.pem

   Note:

   • The ssl-mode=REQUIRED setting will establish an encrypted connection if the server supports encrypted connections. If the server cannot create an encrypted connection, the connection will fail. For more information on additional options for the ssl-mode setting, refer to MySQL’s ssl-mode documentation.
   • MySQL introduced the ssl-mode setting in MySQL 5.7. If you are running MySQL 5.6 or an older version, replace ssl-mode=REQUIRED with ssl.
7. Save your changes to the /etc/my.cnf file and exit your text editor.
8. Execute the following command to update the permissions for the /mysql_keys directory and its files:

   chown -Rf mysql. /mysql_keys

9. Restart the MySQL server.

   Note: If the remote MySQL server runs a control panel like cPanel & WHM, use the /scripts/restartsrv_mysql script to restart MySQL.

Test the Remote MySQL Connection

To verify the remote MySQL connection, follow these steps:

1. Log in to the remote MySQL server via SSH. For more information, read our SSH Access documentation.
2. Use the mysql command to remotely connect to the MySQL server. The following example uses the IP address 192.168.0.1 for the destination MySQL server and example for the MySQL user:

   mysql -u example -h 192.168.0.1 -p

   The mysql command will connect you to the remote MySQL server and prompt for the MySQL user password.

3. After connecting to the remote MySQL server, enter the status command. The output should resemble the following example:

   mysql> status
   --------------
   mysql  Ver 14.14 Distrib 5.5.42, for Linux (x86_64) using readline 5.1
   
   Connection id:        19
   Current database:
   Current user:        example@localhost
   SSL:            Cipher in use is DHE-RSA-AES256-SHA
   Current pager:        stdout
   Using outfile:        ''
   Using delimiter:    ;
   Server version:        5.5.42-cll MySQL Community Server (GPL)
   Protocol version:    10
   Connection:        Localhost via UNIX socket
   Server characterset:    latin1
   Db     characterset:    latin1
   Client characterset:    utf8
   Conn.  characterset:    utf8
   UNIX socket:        /var/lib/mysql/mysql.sock
   Uptime:            4 min 36 sec
   Threads: 1  Questions: 67  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 27  Queries per second avg: 0.242
   --------------

Additional Resources

• Guide to SSL
• How to Configure MySQL SSL Connections
• SSL/TLS
• SSL/TLS Key Types
• SSL/TLS Status
• The cPanel Glossary