How to Setup/Configure MySQL (Master-Slave) Replication on Linux

MySQL master-slave replication is a process that automatically copies data from one MySQL database server (master) to one or more MySQL database servers (slaves).

MySQL supports various replication methods, but Master-Slave is the best of them.

  • Master-slave replication
  • Master-master replication
  • Multi-source replication

The master-slave replication can be made using the following two methods:

  • Binary Log File Position Based Replication Configuration
  • Replication with Global Transaction Identifiers (GTIDs)

The traditional method is based on replicating events from the master’s binary log, and the log files and positions in them must be synchronized between master and slave.

The newer method based on global transaction identifiers (GTIDs), which does not relay on the log file. When using GTIDs, each transaction can be identified and tracked as it is committed on the originating server and applied by any slaves.

Replication in MySQL supports different types of synchronization. The original type of synchronization is one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves.

You can replicate all databases, selected databases, or even selected tables within a database based on your needs.

In this tutorial, we will explain how to configure a MySQL Master/Slave replication with one master and one slave server on CentOS 7.

The same procedure works for other distributions.

  • Configure the Master Server
  • Configure the Slave Server
  • Finally Test this Set up

The server details are as follows:

Master IP: 192.168.1.113
Slave IP:  192.168.1.114

Why Do You Need to Implement This?

This setup provides redundancy and fault tolerance for databases, which helps you retrieve them based on your needs.

Prerequisites for MySQL Master/Slave Replication

Make sure that the master and slave should use the same version of MySQL, because replication between different versions may cause problems.

If you haven’t already installed MySQL on your system, use the below yum command to install it.

# yum update && yum install MariaDB-server MariaDB-client

For CentOS/RHEL 8 use the dnf command to install it.

# dnf -y install @mysql

Allow the database service through the firewall using the below commands.

# firewall-cmd --add-service=mysql --permanent
# firewall-cmd --reload

1) How to Configure the Master Server for MySQL Master/Slave Replication

To do so, edit the “my.cnf” or “my.ini” file and configure the binary log and server ID options. Also, change the “loopback” address to match the IP address of the Master node.

# vi /etc/my.cnf

[mysqld]
server-id = 1
bind-address = 192.168.1.113
log-bin = mysql-bin

Make a note: The server ID must be a unique number and it will not be used again.

Once you make changes, restart the MySQL service for the changes to take effect.

$ sudo systemctl restart mysqld

Next, log in to MySQL and create a replica user. This user will be used by Slave Server to request binary logs from the master.

# mysql -u root -p
Enter Password : *****

Run the commands below to create a user and grant the replication slave privilege. For demonstration purposes, we have added a username called “replica_user” and you can change it according to your needs.

Make sure you have given the user a strong password.

mysql> CREATE USER 'replica_user'@'192.168.1.114' IDENTIFIED BY 'your_password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'192.168.1.114';
Query OK, 0 rows affected (0.00 sec)

Flush the privileges for changes take effect.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Run the command below to determine the current binary log file name and their position. These details are required in the slave configuration.

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000006
         Position: 336
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

mysql> quit;

Now you have successfully configured the Master server.

2) How to Configure the Slave Server for MySQL Master/Slave Replication

Add the necessary changes to the slave like the master, but the values must be changed accordingly.

# vi /etc/my.cnf

[mysqld]
server-id = 2
bind-address = 192.168.1.114
log-bin = mysql-bin

Once you made the changes, restart the MySQL service for changes to take effect.

$ sudo systemctl restart mysqld

To configure the slave to communicate with the master for replication, configure the slave with the necessary connection information as follows:

Login to MySQL shell.

# mysql -uroot -p

First, stop the slave threads:

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.1.113,
    ->     MASTER_USER='replica_user',
    ->     MASTER_PASSWORD='your_password',
    ->     MASTER_LOG_FILE='mysql-bin.000006',
    ->     MASTER_LOG_POS=336;

Once done, start the slave threads.

mysql> START SLAVE;

Now, check the slave server status.

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.113
                  Master_User: replica_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 336
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 1269
        Relay_Master_Log_File: mysql-bin.000006
             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: 1354
              Relay_Log_Space: 1424
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

3) How to Test the Configuration

To validate this, we are going to create a test database on the Master Node.

# mysql -uroot -p

mysql> CREATE DATABASE replica_test;

Now, check this on the slave by executing the following command.

# mysql -uroot -p

mysql> SHOW DATABASE;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replica_test       |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Conclusion

In this tutorial, you learned how to configure MySQL master-slave replication in CentOS 7. This process works seamlessly on the new system setup, but you need to make additional configuration changes if you want to replicate an existing database.

We will cover this in an upcoming article.

About Magesh Maruthamuthu

Love to play with all Linux distribution

View all posts by Magesh Maruthamuthu

Leave a Reply

Your email address will not be published. Required fields are marked *