How to Configure MySQL SSL Connections

ssl mysql

Valid for versions 120 through 122

Version:

88

120

124

132

Last modified: 2024 August 14

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.

Important:

You must possess root access to the client and host servers to complete this tutorial.

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:

Log in to your server via SSH. For more information, read our SSH Access documentation.
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

Note:

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

To create the SSL keys, perform the following steps:

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

1
2


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

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

1
2
3


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

Run the following commands to create the client SSL key and certificate:

1
2
3


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

Edit the MySQL configuration

To edit the MySQL configuration, perform the following steps:

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

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

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.

Save your changes to the /etc/my.cnf file and exit your text editor.
Run the following command to update the file permissions of the /mysql_keys directory and its files:
```
chown -Rf mysql. /mysql_keys
```
Run the /scripts/restartsrv_mysql script to restart MySQL.

Test the SSL configuration

To test the SSL configuration, perform the following steps:

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

To check a local connection to MySQL, run the following command, where example represents the cPanel account username:
```
mysql -u example -p
```
When the system prompts you, enter the MySQL user account password.

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

Note:

This step is optional.

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

Navigate to cPanel’s Manage My Databases interface (cPanel » Home » Databases » Manage My Databases) and create your database user.
Add the remote server’s IP address to cPanel’s Remote Database Access interface (cPanel » Home » Databases » Remote Database Access).
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.
Copy the client SSL certificate to the remote MySQL server’s /mysql directory.
On the remote MySQL server, open the /etc/my.cnf file with your preferred text editor.

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.

Save your changes to the /etc/my.cnf file and exit your text editor.
Run the following command to update the permissions for the /mysql_keys directory and its files:
```
chown -Rf mysql. /mysql_keys
```
Restart the MySQL server.

Note:

If the remote MySQL server runs cPanel & WHM, use the /scripts/restartsrv_mysql script to restart MySQL.

Test the remote MySQL connection

To test the remote MySQL connection, perform the following steps:

Log in to the remote MySQL server via SSH. For more information, read our SSH Access documentation.
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.

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