Whatsapp/ Telegram: +65 9776 5889 Live Chat Submit Ticket   Login

How To Set Up Master Slave Replication in MySQL

It’s pretty straight forward to setup Master and Slave replication that might sound complicated. It’s obvious that you 1st need two MySQL running instances and identify which is your master and which is your slave.

On your Master MySQL configuration file, vi /etc.my.cnf
 server-id = 1
 log-bin = mysql-bin
 log-bin-index = mysql-bin.index
 expire-logs-days = 10
 max-binlog-size = 100M
 binlog-do-db = newdatabase

Restart MySQL:service mysqld restart

A new SQL user needs to be created on the master:

create user mysqslave;
 create user 'mysqlslave'@'*';
 grant replication slave on *.* to mysqlslave identified by 'NEWPASSOWRD';
 flush privileges;

Next we need to extract some information on the Master MySQL with this command
show master status G

You will probably see something similar
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 199
Binlog_Do_DB: newdatabase
1 row in set (0.00 sec)

Go to Slave MySQL server
Open up my.cnf and insert these


save and service mysqld restart

Now enter MySQL admin with root to insert this

change master to
 start slave;

Now, check status with
 show slave status G

Look for error if any. Common mistake is usually on the master_log_pos is wrong if you have cut and pasted my command from this tutorial 😛

Congratulation, you have completed the Master and Slave MySQL replication.