Prerequisites:

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

Workaround:

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 '192.168.56.103 master-node' >> /etc/hosts
# echo '192.168.56.104 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 '192.168.56.103 master-node' >> /etc/hosts
# echo '192.168.56.104 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'@'%.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.

Step5: On the master, release the read lock:

mysql> UNLOCK TABLES; 

 

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 

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

Step3: Start the slave.

mysql> START 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