SSL Connections in MySQL 5.7

SSL Connections

SSL ConnectionsThis blog post looks at SSL connections and how they work in MySQL 5.7.

Recently I was working on an SSL implementation with MySQL 5.7, and I made some interesting discoveries. I realized I could connect to the MySQL server without specifying the SSL keys on the client side, and the connection is still secured by SSL. I was confused and I did not understand what was happening.

In this blog post, I am going to show you why SSL works in MySQL 5.7, and it worked previously in MySQL 5.6.

Let’s start with an introduction of how SSL worked in 5.6.

SSL in MySQL 5.6

The documentation for SSL in MySQL 5.6 is quite detailed, and it explains how SSL works. But first let’s make one thing clear: MySQL supports secure (encrypted) connections between clients and the server using the TLS (Transport Layer Security) protocol. TLS is sometimes referred to as SSL (Secure Sockets Layer), but MySQL doesn’t actually use the SSL protocol for secure connections because it provides weak encryption.

So when we/someone says MySQL is using SSL, it really means that it is using TLS. You can check which protocol you use:

show status like 'Ssl_version';
| Variable_name | Value |
| Ssl_version | TLSv1.2 |

So how does it work? Let me quote a few lines from the MySQL documentation:

TLS uses encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect data change, loss, or replay. TLS also incorporates algorithms that provide identity verification using the X509 standard. X509 makes it possible to identify someone on the Internet. In basic terms, there should be some entity called a “Certificate Authority” (or CA) that assigns electronic certificates to anyone who needs them. Certificates rely on asymmetric encryption algorithms that have two encryption keys (a public key and a secret key). A certificate owner can present the certificate to another party as proof of identity. A certificate consists of its owner’s public key. Any data encrypted using this public key can be decrypted only using the corresponding secret key, which is held by the owner of the certificate.

It works with key pairs (private and public): the server has the private keys and the client has the public keys. Here is a link showing how we can generate these keys.

MySQL 5.6 Client

When the server is configured with SSL, the client has to have the client certificates. Once it gets those, it can connect to the server using SSL:

mysql -utestuser -p -h192.168.0.1 -P3306 --ssl-key=/root/mysql-ssl/client-key.pem --ssl-cert=/root/mysql-ssl/client-cert.pem

We have to specify the key and the certificate. Otherwise, we cannot connect to the server with SSL. So far so good, everything works as the documentation says. But how does it work in MySQL 5.7?

SSL in MySQL 5.7

The documentation was very confusing to me, and did not help much. It described how to create the SSL keys the same way (and even the server and client configuration) as in MySQL 5.6. So if everything is the same, why I can connect without specifying the client keys? I did not have an answer for that. One of my colleagues (Alexey Poritskiy) found the answer after digging through the manuals for a while, and it finally clearly described this behavior:

As of MySQL 5.7.3, a client need specify only the --ssl option to obtain an encrypted connection. The connection attempt fails if an encrypted connection cannot be established. Before MySQL 5.7.3, the client must specify either the --ssl-ca option, or all three of the --ssl-ca, --ssl-key, and --ssl-cert options.

These lines are in the “CREATE USER” syntax manual.

After this, I re-read the SSL documentation and found the following:

5.7.3: On the client side, an explicit --ssl option is no longer advisory but prescriptive. Given a server enabled to support secure connections, a client program can require a secure conection by specifying only the --ssl option. The connection attempt fails if a secure connection cannot be established. Other --ssl-xxx options on the client side mean that a secure connection is advisory (the connection attempt falls back to an unencrypted connection if a secure connection cannot be established).

I still think the SSL manual could be more expressive and detailed.

Before MySQL 5.7.3, it used key pairs. After that, it works a bit similar to websites (HTTPS): the client does not need the keys and the connection still can be secure. (I would still like to see more detailed documentation how it really works.)

Is This Good for Us?

In my opinion, this is a really good feature, but it didn’t get a lot of publicity. Prior to 5.7.3, if we wanted to use SSL we had to create the keys and use the client keys in every client application. It’s doable, but it is just a pain — especially if we are using different keys for every server with many users.

With this feature we can have a different key for every server, but on the client side we only have to enable the SSL connection. Most of the clients use it as the default if SSL is available.

I am pretty sure if people knew about this feature they would use it more often.


I tested it with many client applications, and all worked without specifying the client keys. I only had issues with the MySQL 5.6 client, even though the server was 5.7. In that case, I had to specify the client keys. Without them, I couldn’t connect to the server.

It is possible some older applications won’t support this feature.


This is a great feature, easy to use it and it makes SSL implementations much easier. But I think the documentation should be clearer and more precise in describing how this new feature actually works. I already submitted a request to update the documentation.


Setting up MySQL SSL and secure connections

There are different articles on how to setup MySQL with SSL but it’s sometimes difficult to end up with a good simple one. Usually, setting up MySQL SSL is not really a smooth process due to such factors like “it’s not your day”, something is broken apparently or the documentation lies :) I am going to provide the brief instructions on how to setup MySQL with SSL, SSL replication and how to establish secure connections from the console and scripts showing the working examples.

SSLQuick links:


Setup SSL on MySQL

1. Generate SSL certificates according to the example 1. Use the different Common Name for server and client certificates.

2. For the reference, I store the generated files under /etc/mysql-ssl/

3. Add the following lines to /etc/my.cnf under [mysqld] section:


4. Restart MySQL.

5. Create an user to permit only SSL-encrypted connection:


Establish secure connection from console

1. If the client is on a different node, copy /etc/mysql-ssl/ from the server to that node.

2. Add the following lines to /etc/my.cnf under [client]:


3. Test a secure connection:

[root@centos6 ~]# mysql -u ssluser -ppass -sss -e ‘\s’ | grep SSL
SSL: Cipher in use is DHE-RSA-AES256-SHA

Setup SSL replication

1. Establish a secure connection from the console on slave like described above, to make sure SSL works fine.

2. On Master add “REQUIRE SSL” to the replication user:

GRANT USAGE ON *.* to ‘repl’@’%’ REQUIRE SSL;

3. Change master options and restart slave:



Establish secure connection from PHP

1. Install php and php-mysql packages. I use the version >=5.3.3, otherwise, it may not work.

2. Create the script:

[root@centos6 ~]# cat mysqli-ssl.php
mysqli_ssl_set($conn, ‘/etc/mysql-ssl/client-key.pem’, ‘/etc/mysql-ssl/client-cert.pem’, NULL, NULL, NULL);
if (!mysqli_real_connect($conn, ’′, ‘ssluser’, ‘pass’)) { die(); }
$res = mysqli_query($conn, ‘SHOW STATUS like “Ssl_cipher”‘);

3. Test it:

[root@centos6 ~]# php mysqli-ssl.php
[0] => Ssl_cipher
[1] => DHE-RSA-AES256-SHA

Establish secure connection from Python

1. Install MySQL-python package.

2. Create the script:

[root@centos6 ~]# cat mysql-ssl.py
#!/usr/bin/env python
import MySQLdb
ssl = {‘cert’: ‘/etc/mysql-ssl/client-cert.pem’, ‘key’: ‘/etc/mysql-ssl/client-key.pem’}
conn = MySQLdb.connect(host=’′, user=’ssluser’, passwd=’pass’, ssl=ssl)
cursor = conn.cursor()
cursor.execute(‘SHOW STATUS like “Ssl_cipher”‘)
print cursor.fetchone()

3. Test it:

[root@centos6 ~]# python mysql-ssl.py
(‘Ssl_cipher’, ‘DHE-RSA-AES256-SHA’)


Alternative local SSL connection setup
If you connect locally to the server enabled for SSL you can also establish a secure connection this way:
1. Create ca.pem:

cd /etc/mysql-ssl/
cat server-cert.pem client-cert.pem > ca.pem

2. Have only the following ssl- lines in /etc/my.cnf under [client]:


Error with “ssl-ca” on local connections
If you left the line “ssl-ca=/etc/mysql-ssl/ca-cert.pem” under [client] section in /etc/my.cnf on the server enabled for SSL and try to establish local SSL connection, you will get “ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)”.

Discrepancy in documentation
http://dev.mysql.com/doc/refman/5.5/en/using-ssl-connections.html says “A client can connect securely like this: shell> mysql –ssl-ca=ca-cert.pem” which does not work with “REQUIRE SSL”. You still have to supply the client cert and key for any or a combined client+server cert for a local secure connection.

The post Setting up MySQL SSL and secure connections appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com