Update TLS Certificate During MySQL Replication

Update TLS Certificate During MySQL Replication

Backup / By

TLS Certificate is important to keep MySQL replication running over SSL connection. We should check and update TLS certificate when it gets any corruption due to any reason.

When TLS Certificate changes for a any reason, MySQL replication will stop when you reboot Master server as the certificate changes take effect after the rebooting process. So, if you would like to renew the current TLS certificate, follow the steps below:

1. [Master] Delete the current certificate in live, archive and renewal folders by running the following commands on PuTTY SSH client or any Linux terminal (manual delete):

 root@mail:~# rm -r /etc/letsencrypt/live/mail.vpsprof.com
 root@mail:~# rm -r /etc/letsencrypt/archive/mail.vpsprof.com
 root@mail:~# rm -r /etc/letsencrypt/renewal/mail.vpsprof.com.conf

You can delete the current certificate automatically by running the command:

root@mail:~# certbot delete --cert-name mail.vpsprof.com

Or run the below command then choose the one you want to delete:

root@mail:~# certbot delete

2. [Master] Generate new certificate by running the following command:

root@mail:~# certbot certonly --webroot --agree-tos --email postmaster@vpsprof.com -d mail.vpsprof.com -w /var/www/html/

3. [Master] Grant MySQL the permission to access the SSL files using the below commands:

root@mail:~# setfacl -R -m "u:mysql:rx" /etc/letsencrypt/archive/
root@mail:~# setfacl -R -m "u:mysql:rx" /etc/letsencrypt/live/

4. [Master] Convert certificate key from PKCS8 to PKCS1 by running the command:

root@mail:~# openssl rsa -in /etc/letsencrypt/live/mail.vpsprof.com/privkey.pem -out /etc/letsencrypt/live/mail.vpsprof.com/privkey.pem

5. [Master] Restart MariaDB server for the changes to take the effect:

root@mail:~# service mariadb restart

6. [Master] You need to make sure that SSL is enabled on Master. So, login to MariaDB Console then run the below command:

MariaDB [(none)]> show global variables like "%ssl%";

Note that SSL should be enabled.

7. [Slave] Check the replication status by running the below command:

MariaDB [(none)]> show slave 'master01' status\G;

Please note that you should check the replication status every time you reboot your Master server. This is because TLS certificate changes take effect after rebooting the server. If you want to add new TLS certificate to Master server, make sure to add its domain in 50-server.cnf file then apply the same steps in this article.