How to configure Master-Slave replication for MySQL database on CentOS 7

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

To configure replication you should have two servers.

You need to install MySQL in 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 setup these steps to configure Master.

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

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

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

# systemctl restart mysqld

Step 3: Now, login to the 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: Above binary position details are important to configure in Slave Server.

SLAVE SERVER CONFIGURATION:

We need to setup these steps to configure Master.

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 configuration file, now restart MySQL service.

# systemctl restart mysqld

Step 3: Now, configure slave server parameters which will use 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 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 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.