Mysql Generate SSL/TLS and RSA certificates and keys
Colleagues hello to all.
In the last article, we talked about how to set up encrypted connections to Mysql database using an SSL/TLS certificate. Today we will talk about how to create SSL / TLS certificates and how to apply them on the database without reloading the database itself.
Previous article: Configuring SSL/TLS on MySQL. Using an encrypted connection.
The DBMS version I use: 8.0.29
Article content:
- SSL/TLS certificate validity period.
- Create an SSL/TLS certificate.
- Applying a new certificate.
- Check.
- Old sessions.
- Summary
1. SSL/TLS certificate validity period.
All SSL/TLS certificates have an expiration date. After the expiration date, the next time you connect to the database, there will be access problems or the database will not let you in at all. There are two ways to see when your certificate expires.
The first way to view it is through the performance_schema system database and access the tls_channel_status table.
mysql> select * from performance_schema.tls_channel_status WHERE CHANNEL = 'mysql_main';
We are interested in two fields:
- Ssl_server_not_after - Certificate expiration time.
- Ssl_server_not_before - The time the certificate was created.
The second option is to access the Ssl_server_not system variable.
mysql> SHOW STATUS LIKE 'Ssl_server_not%';
The result will be the same. You can use any of the options that you like.
2. Create an SSL/TLS certificate.
The mysql_ssl_rsa_setup utility is responsible for creating a new certificate in Mysql. But you need to create a certificate in a separate directory, otherwise if you create a certificate in the same directory where you already have old certificates, then mysql_ssl_rsa_setup will not create anything for you, but simply completes its work and does not even show an error.
2.1. Create a directory for the certificate.
$. mkdir -p /app/mysql/safety/09_06_2022
2.2. Create a new certificate.
$. mysql_ssl_rsa_setup --datadir=/app/mysql/safety/09_06_2022 --verbose
After executing the command, certificates should appear in our new directory.
2.3. Let's assign the user directory owner and the mysql group.
$. chown -R mysql:mysql /app/mysql/safety/09_06_2022
3. Applying a new certificate.
The remaining problem is how how do we now apply our created certificate.
There is, of course, an easy option to simply write a new path to the certificates in the configuration file and restart the database, but if we have a constantly loaded database and a lot of clients, then somehow we don’t really want to restart the database.
There are solutions!
After you have created your certificate, you need to do a few things:
3.1. Change the path to the CA certificate file.
mysql> SET GLOBAL ssl_ca = '/app/mysql/safety/09_06_2022/ca.pem';
3.2. Change the path to the public key certificate file.
mysql> SET GLOBAL ssl_cert = '/app/mysql/safety/09_06_2022/server-cert.pem';
3.3. Change the path to the private key file.
mysql> SET GLOBAL ssl_key = '/app/mysql/safety/09_06_2022/server-key.pem';
3.4. Run a command that will reread the new keys and apply them.
mysql> ALTER INSTANCE RELOAD TLS;
3.5. But of course, write new paths to certificates in the configuration file.
4. Check.
After all that we have done, you need to revisit the database and check.
mysql> SHOW STATUS LIKE 'Ssl_server_not%';
As you can see, the date of creation and the expiration date of the certificate have changed.
5. Old sessions.
The last nuance remains. Old sessions that are left in the database will still use the old certificate. In order for them to use a new certificate, they need to re-login to the database or reset sessions using commands.
5.1. Find sessions.
mysql> SHOW PROCESSLIST;
5.2. Reset session.
mysql> KILL session_id;
6. Results.
At the end of these two articles, our colleagues learned how to set up secure connections to the database, and create new certificates and apply them without restarting the database.
Thank you all, I hope that my article helped you in some way.
