Single Blog

How To Set Up Master Slave Replication in MySQL

February 23, 2015, Written by 0 comment

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
Binlog_Ignore_DB:
1 row in set (0.00 sec)

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

server-id=2
 master-connect-retry=60
 relay-log=slave-relay-bin
 relay-log-index=slave-relay-bin.index
 replicate-do-db=newdatabase

save and service mysqld restart

Now enter MySQL admin with root to insert this

change master to
 master_host='DNS_MASTER_IP',
 master_user='mysqlslave',
 master_password='NEWPASSOWRD',
 master_log_file='mysql-bin.000001',
 master_log_pos=199;
 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.

martin