Setting Up MySQL Replication (Master-Slave) – PART 2

My Environment :

MySQL Server 01 – Master : 192.168.10.10
MySQL Server 02 – Slave : 192.168.10.11
DB To Replicate – hdada

Things to Remember :

1. MySQL version should be the same on Master and Slave
2. During configuration of Replication once the DBDump is taken, no new changes should be made till the configuration is complete (If new data is added in between there will be a difference of data in Master and Slave and the replication may fail after sometime)
3. This article assumes, MySQL is installed on both the server’s (Same Version)
4. Most of the Replication process is same in different flavours of Linux, however this we are performing this on a Redhat/CentOS server.
5. We will represent the password in this article with less secure password for easy understanding, make sure you replace this with a strong password in case you are configuring a replication on a LIVE server

Starting off with replication – Setting Up the Slave server:

– Restore the Database backup in the Slave server

– Edit Slave’s MySQL configuration (my.cnf) and add the following lines

server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = hdada

– Restart the MySQL service using the following command

service mysqld restart

In case you are using mariadb on CentOS 7 server run the below command

systemctl restart mariadb

– Enable replication by running the following command in MySQL shell

mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.10',MASTER_USER='hdadauser', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 107;

Note : Database name, Replication User and Password with the ones you configured during PART – 1

– Start SLAVE by issuing the following command in MySQL shell

mysql> START SLAVE;

– Check the SLAVE status using the following command

mysql> SHOW SLAVE STATUS\G

The result should be something like below which means the replication is configured successfully

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.10
Master_User: hdadauser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 502428513
Relay_Log_File: mysql-relay-bin.000010
Relay_Log_Pos: 502428676
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

In case you run into issue, get back with the error message in the comments so that I can help you resolve the same