How to Configure MySQL SSL Connections

Overview

This document describes how to configure your server to use SSL for MySQL® database connections. After you secure your MySQL connections, malicious users cannot intercept your MySQL traffic.

Configure MySQL SSL connections

Create the directory to store the SSL keys.

To create the directory that will contain the SSL keys, perform the following steps:

1. Log in to your server via SSH. For more information, read our SSH Access documentation.

2. Create an SSL key storage directory that MySQL can access. For example, run the mkdir /mysql_keys command to create a mysql_keys directory.

Create the SSL keys.

To create the SSL keys, perform the following steps:

1. Run the following commands to create the Certificate Authority (CA) keys:

   1 2	openssl genrsa 2048 > /mysql_keys/ca-key.pem openssl req -sha1 -new -x509 -nodes -days 3650 -key /mysql_keys/ca-key.pem > /mysql_keys/ca-cert.pem

2. Run the following commands to create the server SSL key and certificate:

   1 2 3

   openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout /mysql_keys/server-key.pem > /mysql_keys/server-req.pem openssl x509 -sha1 -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. Run the following commands to create the client SSL key and certificate:

   1 2 3

   openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout /mysql_keys/client-key.pem > /mysql_keys/client-req.pem openssl x509 -sha1 -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

Edit the MySQL configuration.

To edit the MySQL configuration, perform the following steps:

1. Open the /etc/my.cnf file with your preferred text editor.

2. Insert the following lines in the [mysqld] section of the my.cnf file:

   1 2 3 4 5

   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 in the [client] section of the my.cnf file:

   Note:

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

   1 2 3 4

   [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:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

   [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 will create an encrypted connection if the server supports encrypted connections. If the server cannot create an encrypted connection, the connection will fail. For more information about additional options for the ssl-mode setting, read MySQL’s ssl-mode documentation.

   • MySQL introduced the ssl-mode setting in MySQL 5.7. If you run MySQL 5.6 or older, replace ssl-mode=REQUIRED with ssl.

4. Save your changes to the /etc/my.cnf file and exit your text editor.

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

   chown -Rf mysql. /mysql_keys

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

Test the SSL configuration.

To test the SSL configuration, perform the following steps:

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

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

   The output will resemble the following example:

   1 2 3 4 5 6 7 8 9 10 11

   +---------------+------------------------+ | 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, run the following command, where example represents the cPanel account username:

   mysql -u example -p

3. When the system prompts you, enter the MySQL user account password.

4. After you connect, run the status command. The output will resemble the following example:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

   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 --------------

Create a user and allow remote access.

To create a user and allow remote access, perform the following steps:

1. Navigate to cPanel’s MySQL Databases interface (cPanel >> Home >> Databases >> MySQL Databases) and create your database user.

2. Add the remote server’s IP address to WHM’s 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 example, use the mkdir /mysql_keys command to create a mysql_keys directory.

   Note:

   You can skip this step if you have already created the /mysql_keys directory on the remote MySQL server.

4. Copy the client SSL certificate to the remote MySQL server’s /mysql directory.

5. On the remote MySQL server, open the /etc/my.cnf file with your preferred text editor.

6. Insert the following lines in the [client] section of the my.cnf file:

   1 2 3 4

   [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:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   [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 create an encrypted connection if the server supports encrypted connections. If the server cannot create an encrypted connection, the connection will fail. For more information about additional options for the ssl-mode setting, read MySQL’s ssl-mode documentation.

   • MySQL introduced the ssl-mode setting in MySQL 5.7. If you run MySQL 5.6 or older, replace ssl-mode=REQUIRED with ssl.

7. Save your changes to the /etc/my.cnf file and exit your text editor.

8. Run 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.

Test the remote MySQL connection.

To test the remote MySQL connection, perform the following 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 request the MySQL user password.

3. After you connect to the remote MySQL server, enter the status command. The output will resemble the following example:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

   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 --------------