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

Prerequisites:

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

Configure the networking between two nodes:

Step 1: Change the hostname of the master node to master-node and make entries to the /etc/hosts file.

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

Step 2: Change the hostname of the slave node to slave-node-01 and make entries to the /etc/hosts file.

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

Step 3: Check the network is reachable between the nodes, and execute the below on each node.

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

 

Configuring the master node:

Step 1: Binary logging must be enabled on the master, must have a 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 the [mysqld] section.

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

Step 2: After making the changes, restart the server.

$ sudo systemctl restart mysqld

Step 3: Each slave connects to the master using a MySQL username 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'@'%.kaysariqbal.com' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicat'@'%.kaysariqbal.com';

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:

mysql> FLUSH TABLES WITH READ LOCK;

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:

mysql > SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+-------------------+
| 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.

Step 5: On the master, release the read lock:

mysql> UNLOCK TABLES; 

 

Configuring the replica node:

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

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

After making the changes, restart the server.

Step 2: 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 

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

Step 3: Start the slave.

mysql> START SLAVE;

Step 4: 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

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

Step 6: Optionally load data from other databases if required, and import the backup file on the master node which will automatically replicate to the slave node.

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