8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

MySQL : Configure SSL Connections

This article describes how to enable SSL connections to MySQL.

Server Configuration
User Creation
SSL Connections From Clients

Server Configuration

Log on to the server and check the current SSL configuration.

# mysql --user=root --password

mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_ssl      | DISABLED |
+---------------+----------+
1 row in set (0.00 sec)

mysql>

This means the installation supports SSL, but it is currently disabled.

Create the relevant certificates as described in Example 1 on the Setting Up SSL Certificates and Keys for MySQL page of the documentation. The text below is a reproduction of that method.

# Create location for certificates
mkdir -p /home/mysql/certs/
cd /home/mysql/certs/

# Create CA certificate
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 \
        -key ca-key.pem -out ca-cert.pem

# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
openssl req -newkey rsa:2048 -days 3600 \
        -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 \
        -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
openssl req -newkey rsa:2048 -days 3600 \
        -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 \
        -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

# Verify certificates
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

When prompted I used the following details.

Country Name (2 letter code) [XX]:GB
State or Province Name (full name) []:West Midlands
Locality Name (eg, city) [Default City]:Birmingham
Organization Name (eg, company) [Default Company Ltd]:My Company
Organizational Unit Name (eg, section) []:My Department
**** Only use the ".localdomain" on the first certificate! ****
Common Name (eg, your name or your server's hostname) []:ol6-mysql.localdomain
Email Address []:me@mycompany.com

When prompted for the extra attributes, I supplied a password, but no optional company name.

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:MyPassword1
An optional company name []:

Add the following into the "/etc/my.cnf" file, under the "[mysqld]" section.

# SSL Settings
ssl-ca=/home/mysql/certs/ca-cert.pem
ssl-cert=/home/mysql/certs/server-cert.pem
ssl-key=/home/mysql/certs/server-key.pem

Add in the following client section to the "/etc/my.cnf" file.

[client]
# SSL Settings
ssl-ca=/home/mysql/certs/ca-cert.pem
ssl-cert=/home/mysql/certs/client-cert.pem
ssl-key=/home/mysql/certs/client-key.pem

Restart the mysqld service.

# service mysqld restart

Check the SSL configuration of the server again.

# mysql --user=root --password

mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

SSL connectivity is now enabled on the server.

User Creation

Once SSL is configured, any connection to MySQL can optionally choose to use SSL or X509. The use of SSL can be forced using REQUIRE. Using REQUIRE SSL means the client must have access to the "ca-cert.pem" certificate. Using "REQUIRE X509" means the client also needs access to the client certificate and key. We can test this using the following users.

DROP USER 'ssltest'@'%';
CREATE USER 'ssltest'@'%' IDENTIFIED BY 'MyPassword1';
GRANT USAGE ON *.* TO 'ssltest'@'%' REQUIRE ssl;
FLUSH PRIVILEGES;

DROP USER 'x509test'@'%';
CREATE USER 'x509test'@'%' IDENTIFIED BY 'MyPassword1';
GRANT USAGE ON *.* TO 'x509test'@'%' REQUIRE X509;
FLUSH PRIVILEGES;

SSL Connections From Clients

The presence of the SSL configuration in the "[client]" section of the "my.cnf" file means we can test the connection very simply from the server.

$ mysql --user=ssltest --password

Once connected, we can see the connection is using SSL by issuing the following command.

mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+
1 row in set (0.00 sec)

mysql>

Alternatively, using "/s" from the command prompt will include the SSL configuration amonst other things.

mysql> \s
...
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
...
mysql>

Attempting to access the user without SSL will result in an error.

$ mysql --user=ssltest --password --ssl=0
Enter password:
ERROR 1045 (28000): Access denied for user 'ssltest'@'localhost' (using password: YES)
-bash-4.1$

From a client machine, we need access to the "ca-cert.pem" to make a connection to the "ssltest" user.

C:> mysql --host=myserver --user=ssltest --password ^
      --ssl-ca=c:\ca-cert.pem

Making a connection to the "x509test" user requires the client certificate and key also.

C:> mysql --host=myserver --user=x509test --password ^
      --ssl-ca=c:\ca-cert.pem --ssl-cert=c:\client-cert.pem --ssl-key=c:\client-key.pem

Instead of applying them to the command line, the SSL details can be added to a local option file. For example, a file called "C:\my.cnf" could be created with the following contents.

[client]
# SSL Settings
ssl-ca=C:\ca-cert.pem
ssl-cert=C:\client-cert.pem
ssl-key=C:\client-key.pem

Connections could now be made as follows.

C:> mysql --defaults-extra-file=c:\my.cnf --host=myserver --user=ssltest --password

For more information see:

Hope this helps. Regards Tim...

Back to the Top.