Setting up SSL replication in MySQL

Introduction

Ensuring the security of your MySQL database is crucial for the protection of sensitive data. One of the most effective ways to achieve this is by setting up SSL (Secure Socket Layer) replication. This process encrypts the data transferred between the master and slave servers, preventing potential attacks like man-in-the-middle and eavesdropping. In this blog post, we’ll guide you through the process of setting up SSL replication in MySQL, complete with examples and step-by-step instructions.

Prerequisites

Before you begin setting up SSL replication, ensure that you have the following prerequisites in place:

  • A MySQL server (master) with a configured replication user.
  • A MySQL server (slave) that can connect to the master server.
  • OpenSSL installed on both master and slave servers.

Generating SSL Certificates and Keys

The first step in setting up SSL replication is generating the SSL certificates and keys. Execute the following commands on the master server to create the necessary files:

$ mkdir /etc/mysql/ssl
$ cd /etc/mysql/ssl
$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem
$ openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem
$ openssl rsa -in server-key.pem -out server-key.pem
$ openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Now, copy the ca-cert.pem, server-key.pem, and server-cert.pem files to the slave server:

$ scp ca-cert.pem server-key.pem server-cert.pem [slave_user]@[slave_host]:/etc/mysql/ssl/

Configuring the Master Server

On the master server, open the MySQL configuration file (typically located at /etc/mysql/my.cnf or /etc/my.cnf) and add the following lines under the [mysqld] section:

[mysqld]
...
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

Restart the MySQL server to apply the changes:

$ sudo service mysql restart

Configuring the Slave Server

On the slave server, open the MySQL configuration file and add the following lines under the [mysqld] section:

[mysqld]
...
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

Restart the MySQL server to apply the changes:

$ sudo service mysql restart

Setting up SSL Replication

On the slave server, execute the following SQL commands to configure the replication user and set up the SSL connection:

STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='[master_host]',
  MASTER_USER='[replication_user]',
  MASTER_PASSWORD='[replication_password]',
  MASTER_SSL=1,
  MASTER_SSL_CA='/etc/mysql/ssl/ca-cert.pem',
  MASTER_SSL_CERT='/etc/mysql/ssl/server-cert.pem',
  MASTER_SSL_KEY='/etc/mysql/ssl/server-key.pem';
START SLAVE;

Replace [master_host], [replication_user], and [replication_password] with the appropriate values for your setup.

Verifying SSL Replication

To verify that SSL replication is working correctly, execute the following command on the slave server:

SHOW SLAVE STATUS\G;

Look for the following output:

...
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/mysql/ssl/ca-cert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/mysql/ssl/server-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /etc/mysql/ssl/server-key.pem
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
...

If the Master_SSL_Allowed value is ‘Yes’ and the Seconds_Behind_Master value is 0 or a low number, this indicates that SSL replication is set up correctly and running smoothly.

Conclusion

SSL replication is an essential component of securing your MySQL database, as it helps protect your data from potential attacks during the replication process. By following the steps outlined in this guide, you’ll be able to set up SSL replication in MySQL and ensure that your data remains safe and secure. Remember to keep your SSL certificates and keys up-to-date to maintain the highest level of security for your database.

Related Articles