This 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
--ssloption 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-caoption, or all three of the--ssl-ca,--ssl-key, and--ssl-certoptions.
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
--ssloption 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--ssloption. The connection attempt fails if a secure connection cannot be established. Other--ssl-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).xxx
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.
Limitations
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.
Conclusion
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.
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.
Quick links: