Configure MySQL Master-Slave Replication Cluster based on Binary Log

 This section describes replication between MySQL servers based on the binary log file position method. Replica (slave) keeps a record of the binary log coordinates: the file name and position within the file that it has read and processed from the source. The source and each replica must be configured with a unique ID (using the server_id system variable). In addition, each replica must be configured with information about the source's host name, log file name, and position within that file.

MySQL Replication Advantages:

• Offload few queries from Master to Slave(s).
• Utilize master for all writes and Use slave(s) for all reads.
• Slave server also can be used to take backup from it.
• For disaster recovery, you can set up a replication slave in a different region which asynchronously gets the master data.

Server / Environment Details:

Master:

Operating System Name / Version	Oracle Linux 7.4 (64 bit)
MySQL Database Version	8.0
Hostname	masternode
IP Address	192.168.0.100

Slave:

Operating System Name / Version	Oracle Linux 7.4 (64 bit)
MySQL Database Version	8.0
Hostname	slavenode
IP Address	192.168.0.101

Note:- 

Install MySQL Server on all the nodes ( two nodes, in this example). Refer to this link for My SQL Installation on both the nodes.

Each server's firewall should be disabled or servers should allow traffic on ports 22 and 3306.

Master Node Details

                Slave Node Details

Configure Master Node:

Open /etc/my.cnf file and add the following:

bind-address=192.168.0.100

server-id=1

log_bin=mysql-bin

[root@masternode ~]# vim /etc/my.cnf

Restart the MySQL Server for the configurations changes to take place and Check the MySQL status:

[root@masternode ~]# systemctl restart mysqld

[root@masternode ~]# systemctl status mysqld

Create a user named replicator. This user will be used by the slaves to replicate the data from the master to slave and grant privileges to the slave user for slave replication.

mysql> CREATE USER 'replicator'@'192.168.0.101' IDENTIFIED BY 'Rep1Us#rPass';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'192.168.0.101';

mysql> SHOW GRANTS FOR 'replicator'@'192.168.0.101';

Check the Master Status. Note down the file [mysql-bin.000001] and Position[2017] parameters from the output. It is required for the slave replication configuration.

mysql> SHOW MASTER STATUS\G

Configure Slave Node:

Open /etc/my.cnf file and add the following:

bind-address=192.168.0.101

server-id=2

log_bin=mysql-bin

If you have more than one slave node, make sure you to replace the respective slave IP and add a unique server-id per slave.

Restart the MySQL Server for the configurations changes to take place and Check the MySQL status:

[root@slavenode~]# systemctl restart mysqld

[root@slavenode~]# systemctl status mysqld

Now run the change master to master command and start the slave like below:

[root@slavenode ~]# mysql -u root -p

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.100',MASTER_USER='replicator', MASTER_PASSWORD='Rep1Us#rPass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=3153;

mysql> START SLAVE;

Give the Master log file detail and position as pulled out from the Master Node.

Check Slave Status:

mysql> SHOW SLAVE STATUS\G

If Slave_IO_Running and Slave_SQL_Running is showing yes means slave is running ok or if you see Seconds_Behind_Master: 0 means no delay in replication.

Test MySQL Master-Slave Replication:

In this section, we will test master-slave replication.

On Master Server::

mysql> CREATE DATABASE demo;

mysql> show databases;

On Slave:

mysql> show databases;

As expected,You should see the demo database created from the master server on the slave.

Lets create a table under demo database and insert few records to test replication.

On Master Server::

mysql> USE demo;

mysql> create table demo_tbl(

   demo_id INT NOT NULL AUTO_INCREMENT,

   demo_title VARCHAR(100) NOT NULL,

   PRIMARY KEY ( demo_id )

);

mysql> INSERT INTO demo_tbl (demo_id,demo_title) VALUES(100, 'Tom');

mysql> INSERT INTO demo_tbl (demo_id,demo_title) VALUES(101, 'Scott');

mysql> INSERT INTO demo_tbl (demo_id,demo_title) VALUES(102, 'Mustafa');

mysql> select * from demo_tbl;

As expected,You should see the table created from the master server on the slave.

On Slave Server::

mysql> USE demo;

mysql> select * from demo_tbl;

This concludes our article on My SQL Master-Slave Replication Cluster based on Binary Log.