MySql Master Slave Replication Setup

In this article, I will let you know how to set up Master-Slave Replication in MySql.
For this you should have two IPs, one for Master server and another one for Slave server.
Consider we have the below two IPs for Master & Slave server.
10.10.19.20 – Master Server
10.10.19.22 – Slave Server
1. Install MySql in both the Master & Slave Server. You can visit the link given below and download the MySql as per your OS.
http://dev.mysql.com/doc/refman/5.7/en/installing.html

Step 1.
Configure the Master Server :-
Open the file vim /etc/mysql/my.cnf
Change the bind address as

bind-address = 10.10.19.20

Now change the Server-id, It should be unique and does not match with any server-id within the Replication group. I am giving the server-id 1, just for ease.

server-id=1

Now enable the logging of log_bin, our slave servers will copy the data from this bin file. Uncomment this line in /etc/mysql/my.cnf as given below.

log_bin = /var/log/mysql/mysql-bin.log 

Finally, we have to decide which database we want to replicate to slave server, mention the database name.

binlog_do_db = db_name 

Restart the MySql.

sudo service mysql restart

Now, we will log-in to mysql shell to give access to slave-user to replicate data from Master server.

mysql -uroot -p 

To give access to the slave-user, we will grant permission.

GRANT REPLICATION SLAVE ON *.* TO 'slave-user'@'%' IDENTIFIED BY 'password'; 

FLUSH PRIVILEGES; 

Switch to database which you want to replicate.

USE db_name 

FLUSH TABLES WITH READ LOCK; 

SHOW MASTER STATUS; 

Result will look this.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      110 | db_name      |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 

Above given is the position from where the Slave server start replication from Master.
Take the database backup using mysqldump

mysqldump -uroot -p db_name --master-data --single-transaction > db_name.sql 

Now Unlock the database and exit.

UNLOCK TABLES; 

QUIT; 

We are done with Master-Server configuration.

Step 2.
Import the database, which we have previously taken at Master-Server to Slave-Server.

mysql -u root -p db_name < /path/to/db_name.sql 

Configure the Slave-Server( Ip- 10.10.19.22) :-
Open the file /etc/mysql/my.cnf

vi /etc/mysql/my.cnf 

Change the server-id, log_bin, relay-log, binlog_do_db as given below.

server-id = 2 

relay-log = /var/log/mysql/mysql-relay-bin.log 

log_bin = /var/log/mysql/mysql-bin.log 

bin_log_do_db = db_name 

Restart the mysql.

sudo service mysql restart

Log-in to MySql shell in Slave-server and enable replication.

mysql -u root -p 

CHANGE MASTER TO MASTER_HOST='10.10.19.20',MASTER_USER='slave-user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  117; 

Above statement do following things.
1. It update the current server as slave of Master-Server.
2. It gives to server the correct login credentials.
3. It inform the Slave-Server replication should start from where; the master log file and log position come from the numbers we note down previously.
Start the slave server from the MySql shell.

START SLAVE; 

You can see the Slave-Server status from MySql shell.

SHOW SLAVE STATUS; 

If getting any issue while connecting start the slave with a command to skip over it.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START; 

Your MySql Master-Slave Replication is ready to use.