How to set up MySQL Master-Slave Replication

Table of Contents

What is MySQL Master-Slave Replication?

MySQL Master-Slave replication is a process in which we have 2 servers one master and other slave, all the processing/data modifications are done on master and same are replicated on slave. So simply Slave is a copy of Master which helps in case failure of master or we require downtime from master to some OS level changes etc. Hence, Master-Slave replication is very useful for a robust and always running database.

For this guide let us suppose we have 2 servers with MySQL installed and running:

1- Master (i.p. 192.168.1.1)

2- Slave (i.p. 192.168.1.2)

Steps to Set up MySQL Master-Slave replication:

1. Set up Configuration file:

The MySQL configuration file (my.cnf) is generally located at /etc/my.cnf . Let’s configure it:

On Master open the file and in ‘mysqld’ section add the following:

server-id = 1

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

Restart MySQL server after updating the file:

/etc/init.d/mysql restart

or

systemctl restart mysql

Now similarly on Slave server open my.cnf file and add the following:

server-id = 2

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

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

Now again restart the MySQL similarly on slave server.

2. Take backup from Master and copy to slave:

So the first step will be to take backup from the master you can simply ‘Mysqldump’ utility to take the backup, just run the below command to take the backup.

mysqldump -u root -p password --all-databases > masterbackup.sql

This will create a masterbackup.sql file, copy this file to Slave server. You can use ssh/filezilla to copy the file to slave server.

Now login to slave server and restore the backup, simply execute the below command:

mysql -u root -p password < masterbackup.sql

Now you have same data on slave as master.

3. Create Replication user on both the servers:

Simple execute below commands on both master and slave servers:

mysql -u root -p password

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slavepass';

flush privileges;

Execute these on both the servers.

4. Final step to set up replication:

mysql -u root -p

SHOW MASTER STATUS;

Output should look like this:

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

Now copy the File and Position from here and login to MySQL prompt on Slave server:

mysql -u root -p

mysql> CHANGE MASTER TO
                MASTER_HOST=192.168.1.1,
                MASTER_USER='repl',
                MASTER_PASSWORD='slavepass',
                MASTER_LOG_FILE='TheMaster-bin.000001',
                MASTER_LOG_POS=107;

Here MASTER_LOG_FILE and MASTER_LOG_POS is what we have copied from master

Done. Now on slave you can check the replication status using following command:

show slave status\G;