MySQL Replication on CentOs 7: Configure binary log file position based replication (Fresh server with no data)


Two virtual machine each of 2GB RAM, 10 GB HDD and Two vCPU with CentOS 7 installed on these.


Configure the networking between two nodes:

Step1: Change the host name of the master node to master-node and make entries to the /etc/hosts file.

# hostnamectl set-hostname master-node
# echo ' master-node' >> /etc/hosts
# echo ' slave-node-01' >> /etc/hosts

Step2: Change the host name of the slave node to slave-node-01 and make entries to the /etc/hosts file.

# hostnamectl set-hostname slave-node-01
# echo ' master-node' >> /etc/hosts
# echo ' slave-node-01' >> /etc/hosts

Step3: Check the network is reachable between the nodes, execute the below on each nodes.

# ping master-node
# ping slave-node-01


Configuring the master node:

Step1: Binary logging must be enabled on the master, must have an unique ID, networking enabled and additionally for the durability and consistency of replication enable the log flushing at commit and syncing binary logs. Set the below variables in the options file my.cnf under [mysqld] section.

log-bin = master-bin
server-id = 1 
skip_networking = 0
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

Step2: After making the changes, restart the server.

$ sudo systemctl restart mysqld

Step3: Each slave connects to the master using a MySQL user name and password, so there must be a user account on the master that the slave can use to connect.

$ mysql -uroot -p
mysql> CREATE USER 'replicat'@'' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicat'@'';

Step4: To obtain the master binary log coordinates, follow these steps:

  • Start a session on the master by connecting to it with the command-line client, and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

Note: Leave the client from which you issued the FLUSH TABLES statement running so that the read lock remains in effect. If you exit the client, the lock is released.

  • In a different session on the master, use the SHOW MASTER STATUS statement to determine the current binary log file name and position:
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| master-bin.000001 | 680           |                                 |                                    | 
1 row in set (0.00 sec)


Note: Note the log file name and the position from the above command, which will be needed later on.

Step5: On the master, release the read lock:



Configuring the Slave node:

Step1: Set the below variables in the options file my.cnf under [mysqld] section on the salve.

log-bin = slave01-bin
server-id = 2
skip_networking = 0

After making the changes, restart the server.

Step2: To set up the slave to communicate with the master for replication, configure the slave with the necessary connection information. To do this, execute the following statement on the slave, replacing the option values with the actual values relevant to your system:

$ mysql -uroot -p 

             -> MASTER_HOST='master-node',
             -> MASTER_USER='replicat',
             -> MASTER_PASSWORD='password',
             -> MASTER_LOG_FILE='master-bin.000001',
             -> MASTER_LOG_POS=680;

Step3: Start the slave.


Step4: Show the slave node status.

        mysql> SHOW SLAVE STATUS \G;

Note: Look at the below status to ensure the replication is running successfully.

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Step5: Test the replication is working by creating a database and tables on master node and performing some DML on the created tables. If the tables and rows are reflected at the slave mysql database then replication is functioning properly.

Step6: Optionally load data from other database if required, import the backup file on the master node which will automatically replicate to the slave node.

$ mysql -h master-node -uroot -p < fulldb.dump 

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.