The official MySQL documentation takes us through the steps of setting up SSL. We generate a Certificate Authority key and corresponding private key, ca.pem and ca-key.pem. Then, we create a private key and certificate for the server, server-key.pem and server-cert.pem, signed by the generated Certificate Authority. Finally, we do the same for the client, client-key.pem and client-cert.pem, signed by the same Certificate Authority. The server-req.pem and client-req.pem are Certificate Signing Requests created as part of the signing process, and we don’t need them past this point. This leaves us with six useful files: ca-key.pem, ca.pem, server-key.pem, server-cert.pem, client-key.pem, and client-cert.pem.
If all we want is encryption, we don’t need all the files. We can look to how SSL works for the web. The web browser gets the server’s certificate and checks it against a list of validated Certificate Authorities. Using the server’s certificate, the browser can set up encrypted communication with the server by encrypting a message that only the server can decrypt using the server’s private key. To recap, the key steps are: 1) Get the server’s certificate, 2) Validate the certificate against a Certificate Authority, and 3) Setup a secure connection by using the server’s public key contained within the certificate. In this scenario, we can see that client-key.pem and client-cert.pem are unnecessary. We can connect without those files:
As an aside, in the typical SSL process, we use a validated certificate authority to generate the certificate, whereas for the above process, we created our own certificate authority, ca.pem and ca-key.pem. If we wanted to do the same thing here, we wouldn’t need those files and instead use server-key.pem to generate a Certificate Signing Request, server-req.pem. The Certificate Authority would then send us the certificate, server-cert.pem. At this point, we can connect with --ssl-verify-server-cert.
Where does client-cert.pem and client-key.pem come into play? In the web case, we’re validating the server only, but we can also validate the client! Going back to our original connection, if the certificate supplied to ssl-ca, ca.pem, was not used to sign server-cert.pem, then the connection would be rejected. The analogous case on the client end is that if the certificate supplied to ssl-ca in the MySQL server configuration file was not used to sign client-cert.pem, then the connection would also be rejected. Also of note is that the certificate, ca.pem, does not need to be the same on the client and server. A different certificate authority can be used to sign the client certificate and the server certificate.
In this case we could connect using:
We do not need to supply --ssl-ca. The above will connect if the server can authenticate the client’s keys, but we can also tell the client to additionally authenticate the server by combining the commands:
From the server’s side, we can require SSL in the connection by adding it to the GRANT statement:
But, if we wanted to force the server to authenticate the client, we can use REQUIRE X509 instead:
We’ve covered a basic overview of SSL on the web, how that’s similar to MySQL SSL, and how we might want to additionally verify the client.