How To Set Up MySQL Master-Slave Replication - Free Websites, Share News And Posts Publicly

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.

Table of Contents

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 the testing purpose, I have created two system in my local network. Both system have different IP of same network. Assuming that the master and slave database servers have the below IPs as below:

Master Server: 11.11.11.11
Slave Server: 22.22.22.22

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.

Create a directory to store the new SSL certificates.
mkdir /etc/mysql/certs
cd /etc/mysql/certs
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

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

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.

23	mysql> CREATE USER ‘replication’@’22.22.22.22’ IDENTIFIED BY ‘aSecUrEPa$$w0rd’ REQUIRE SSL; mysql> GRANT REPLICATION SLAVE ON . TO ‘replication’@’22.22.22.22’; mysql> FLUSH PRIVILEGES;

Make sure to replace ‘22.22.22.22’ with the IP address of slave instance.

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

Step 4: Dump Databases [Master]

Now, export all the databases on the master instance using the `mysqldump` utility.

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

Copy the dump file to the slave server.

scp dump.sql.gz root@22.22.22.22:

The command will prompt for the remote user password. Once the user is authentication, the file will be copied to remote server under the home directory of ‘root’ account.

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@22.22.22.22:/etc/mysql/certs/ 
$ scp /etc/mysql/certs/client-cert.pem root@22.22.22.22:/etc/mysql/certs/ 
$ scp /etc/mysql/certs/client-key.pem root@22.22.22.22:/etc/mysql/certs/

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

Step 6: Restore Database Backup [Slave]

Login to the MySQL replication client server. Extract the dump file using gunzip utility. After that, restore the database.

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

Then check for the Log file position in backup file.

$ head dump.sql -n80 | grep "MASTER_LOG_POS"

The output will look like this:

Output
-- 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=’11.11.11.11′,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:

1	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

Output
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 11.11.11.11
                  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.
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.

source : https://tecadmin.net/how-to-set-up-mysql-master-slave-replication/

By RahulMarch 13, 2023 7 Mins Read

Chat read-only to anonymous users. Chat with Anyone and Anywhere. Only registered users are allowed to send messages.

Loading the chat ...

99550 Register Login