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
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.
