How to configure MySQL replication on CentOS 7

MySQL replication enables you to sync data from one database server to another. So If your master database is down for any reason then you can restore your all data from the slave database. 

To configure replication you should have two servers. 

Master Server: 192.168.20.2
Slave Server: 192.168.20.3

You need to install MySQL on both servers first. In my case, I am using MySQL 5.6 version.

Check this page for MySQL 5.6 installation

MASTER SERVER CONFIGURATION:

We need to set up these steps to configure Master.

• Provide server ID 
• Enable log-bin
• Bind with the local IP address (if required)
  

Step 1: Edit MySQL configuration with server id and log-bin details.

# vim /etc/my.cnf
 
server-id = 1 
log-bin = mysql-bin

Step 2: After changes in the configuration file, restart MySQL service.

# systemctl restart mysqld

Step 3: Now, login to MySQL server and create a replication user for a slave. 

# mysql -u root -p 
Enter password: 
mysql>
mysql> CREATE USER 'testreplica'@'192.168.20.3' IDENTIFIED BY 'yourpassword';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'testreplica'@'192.168.20.3';
mysql> exit

Step 4: Check binary file and position details while in MySQL master service. 

mysql > show master status\g
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011 | 1006 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql [(none)]>

Note: The above binary position details are important to configure in Slave Server. 

SLAVE SERVER CONFIGURATION:

We need to set up these steps to configure Master. 

• Provide server ID 
• Enable log-bin
• Bind with the local IP address (if required)
  

Step 1:  Edit MySQL configuration with serverid and log-bin details.

# vim /etc/my.cnf   
server-id = 2  
log-bin = mysql-bin

Step 2: After changes in the configuration file, restart MySQL service. 

# systemctl restart mysqld

Step 3: Now, configure slave server parameters which will be used to connect with the master server. 

# mysql -u root -p
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.20.2', MASTER_USER='testreplica', MASTER_PASSWORD='yourpassword', MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=1006;
mysql> start slave;
mysql> exit

The configuration part is done. If you want to check whether replication is working or not then login to the master server and create some new databases/tables and check in the slave server. It should sync with the master database. 

# mysql -u root -p
mysql > create database testingreplica;

Login to the slave server and list all available databases. It will show you the newly created database in the master. 

# mysql -u root -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testingreplica |
+--------------------+
4 rows in set (0.00 sec)

That’s it. Our Master-Slave database replication is done.