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

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 flavors 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 :

– Add the following lines in my.cnf

server-id = 1
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

service mariadb restart

– Create a user in MySQL for replication and Allow full access to this user from Slave server for replication

CREATE USER hdadauser IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

– Check Master Status

mysql> SHOW MASTER STATUS;

Take a Backup of MySQL DB from the master server and copy it to the Slave server.

Once it is done, continue to the second part of our article, setting up the slave server for replication.