How to Set Up MySQL Master-Slave Replication

MySQL replication is a process where a master server replicates its data to one or more slave servers. This can provide several benefits such as increased data availability, improved performance, and easier backups. One important aspect of replication is security, and in this article, we will explore how to configure MySQL master-slave replication with authentication using SSL certificates.

By using SSL certificates, we can ensure that the data being replicated is secure and can only be accessed by authorized parties. Additionally, SSL certificates can also be used to authenticate the master and slave servers, providing an additional layer of security.

In this article, we will go through the process of setting up a master-slave replication configuration with SSL certificates, including creating and installing the certificates, configuring the master and slave servers, and troubleshooting common issues.

MySQL Replication Benefits

The main benefits of MySQL Master-Slave Replication are:

• Performance: One of the main benefits is that it allows for the offloading of read-only queries from the master database to the slaves, improving the performance and scalability of the database system. This is because the master database is only responsible for handling written transactions, while the slaves can handle read transactions.
• Redundancy and fault tolerance: Another benefit of MySQL Master-Slave Replication is that it provides a level of redundancy and fault tolerance. If the master database goes down, the slaves can continue to serve read requests, minimizing downtime. Additionally, if a slave database goes down, it can be replaced with a new slave, and the data can be re-synced from the master.
• Backup and Recovery: MySQL Master-Slave Replication can also be used for backups and data recovery. By regularly taking backups of the slaves, you can have a copy of the data that can be used in case of a disaster on the master database.

This article will help you to set up master-slave replication between two MySQL servers. This tutorial is tested on Ubuntu 22.04 Linux instance with MySQL 8.0 database server.

Setup Details:

For testing purposes, I’ve set up two systems within my local network, each assigned a unique IP address from the same network range. These systems are configured as follows:

• Master Server IP: 192.168.1.100
• Slave Server IP: 192.168.1.200

If you are using different – different versions of MySQL on Master and Slave use this link to check compatibility.

Step 1: Create SSL Certificates [Master]

First, you need to generate a CA certificate. A Certificate Authority (CA) is a trusted third party organization that issues digital certificates used to verify the identity and authenticity of an entity. Then we can generate digital certificate for the master and client system signed by the CA certificate.

1. Create a directory to store the new SSL certificates.

   mkdir /etc/mysql/certs 
   cd /etc/mysql/certs 
   

2. Now, generate the certificate to use as Certificate Authority.

   openssl genrsa 4096 > ca-key.pem 
   openssl req -new -x509 -nodes -days 9999 -key ca-key.pem > ca-cert.pem  
   

3. Generate the certificate for the MySQL master server:

   openssl req -newkey rsa:4096 -days 9999 -nodes -keyout server-key.pem > server-req.pem 
   openssl x509 -req -in server-req.pem -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem  
   

4. Then generate the certificate for the client (slave) system.

   openssl req -newkey rsa:4096 -days 9999 -nodes -keyout client-key.pem > client-req.pem 
   openssl x509 -req -in client-req.pem -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem  
   

Important Note: Make sure to use a different "Common Name (FQDN)" for the CA files than the master and client certificate files.

Step 2: Update MySQL Configuraion File [Master]

Next the MySQL configuration file (eg: /etc/mysql/mysql.conf.d/mysqld.cnf) and add/update the following entries:

[mysqld]
server-id = 1
binlog-format = mixed
log-bin = mysql-bin
innodb_flush_log_at_trx_commit = 1
sync_binlog=1

ssl
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

Step 3: Create Replication User [Master]

In this step, we will create a new user in the MySQL server that will be used to authenticate clients with the master service. So, log in to the MySQL server on the master database server with the root privileged user. Then create a new user, that will be used for the replication process.

Connect to MySQL server then execute and execute the following queries to create a new user and grant them to slave access.

CREATE USER 'replication'@'192.168.1.200' IDENTIFIED BY 'aSecUrEPa$$w0rd' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.200';
FLUSH PRIVILEGES;

Make sure to replace "192.168.1.200" with the IP address of slave instance.

Make sure to use a strong password for the user account.

Step 4: Export Databases [Master]

Use the mysqldump utility to export all databases from the master server:

mysqldump -uroot -p --skip-lock-tables --single-transaction \
--flush-logs --hex-blob --master-data=2 -A  | gzip -c > dump.sql.gz 

Next, transfer the export file to the slave server:

scp dump.sql.gz root@192.168.1.200: 

This command will request the password for the remote user. After successful authentication, the file will be transferred to the remote server, specifically into the root account's home directory.

Step 5: Copy SSL Certificates Files [Master]

Copy the client key and certificate and ca certificate files to the slave instance. Make sure that the "/etc/mysql/certs" directory exists remote system.

scp /etc/mysql/certs/ca-cert.pem root@192.168.1.200:/etc/mysql/certs/ 
scp /etc/mysql/certs/client-cert.pem root@192.168.1.200:/etc/mysql/certs/ 
scp /etc/mysql/certs/client-key.pem root@192.168.1.200:/etc/mysql/certs/ 

Note: Configure SSH keys for auto login or use passwords to connect to remote.

Step 6: Restore Database Backup [Slave]

Log in to the MySQL replication client server. Use the gunzip utility to decompress the dump file. Following that, proceed to restore the database.

gunzip dump.sql.gz 
mysql -uroot -p -f 

Then check for the Log file position in backup file.

head dump.sql -n80 | grep "MASTER_LOG_POS" 

The output will look like this:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=154;

Note the MASTER_LOG_FILE and MASTER_LOG_POS values, that will be used later in this article.

Step 7: Update Slave MySQL Configuration [Slave]:

Next the MySQL configuration file (eg: /etc/mysql/mysql.conf.d/mysqld.cnf) and add/update the folloiwng entries:

[mysqld]
server-id  = 2
binlog-format = mixed
log-bin = mysql-bin
relay-log = mysql-relay-bin
log-slave-updates = 1
read-only = 1

Step 8: Start Slave Replication [Slave]

Configure the slave instance with the IP address of Master instance along with replication user, their password and the certificate copied from master instance. Make sure to update `mysql-bin.######` and `MASTER_LOG_POS=###` same as we get in step 6. Include all zeros.

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.100',MASTER_USER='replication', 
      MASTER_PASSWORD='aSecUrEPa$$w0rd', 
      MASTER_SSL_CA='/etc/mysql/certs/ca-cert.pem', 
      MASTER_SSL_CERT='/etc/mysql/certs/client-cert.pem', 
      MASTER_SSL_KEY='/etc/mysql/certs/client-key.pem', 
      MASTER_SSL=1, 
      MASTER_LOG_FILE='mysql-bin.######', 
      MASTER_LOG_POS=###;

Finally start the replication process:

mysql> START SLAVE;

This will enable and start the replication process.

Step 9: Verify Replication

You can run the `SHOW SLAVE STATUS` query to check for the current status of the replication:

mysql> SHOW SLAVE STATUS \G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.1.100
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000177
          Read_Master_Log_Pos: 100443919
               Relay_Log_File: mysql-relay-bin.000498
                Relay_Log_Pos: 104189538
        Relay_Master_Log_File: mysql-bin.000169
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 104189322
              Relay_Log_Space: 972921462
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/certs/ca-cert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /etc/mysql/certs/client-cert.pem
            Master_SSL_Cipher:
               Master_SSL_Key: /etc/mysql/certs/client-key.pem
        Seconds_Behind_Master: 13803
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: a548db65-a007-11ec-9f5e-b626277913b3
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Waiting for dependent transaction to commit
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

Check for the value of "Seconds_Behind_Master". The value zero (0) means both systems are synced completely.

Congratulations, MySQL Master-slave Replication has been configured successfully on your system and is in working mode. Thanks for using this tutorial, Kindly do not forget to share it with your friends.

Conclusion

In this article, we have covered the process of configuring MySQL master-slave replication with authentication using SSL certificates. We have gone through the steps of creating and installing the certificates, configuring the master and slave servers, and troubleshooting common issues. By using SSL certificates, we have ensured that the data being replicated is secure and can only be accessed by authorized parties. Additionally, SSL certificates have been used to authenticate the master and slave servers, providing an additional layer of security. This approach can help organizations to improve the security of their replication environment and to ensure the integrity and confidentiality of their data.

Overall, MySQL Master-Slave Replication is a useful tool for improving the performance, scalability, and fault tolerance of a MySQL database system. It allows for the distribution of data across multiple servers and can be used for backups and data recovery.