Thursday, February 7, 2013

How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication


Mysql Replication Broken issue

Mysql Server1 is running as MASTER.
Mysql Server2 is running as SLAVE.
Now DB replication is happening from MASTER to SLAVE.
Server2 is removed from network and re-connet it back after 1 day. After this there is mismatch in database in master and slave.
How to re-sync the DB again as after restoring DB taken from Master to Slave also doesn't solve the problem ?

This is the full step-by-step procedure to resync a master-slave replication from scratch:
At the master:
RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
 
And copy the values of the result of the last command somewhere.
Wihtout closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:
mysqldump -uroot -p --all-database > /a/path/mysqldump.sql Now you can release the lock, even if the dump hasn't end. To do it perform the following command in the mysql client:
UNLOCK TABLES; Now copy the dump file to the slave using scp or your preferred tool.
 
At the slave:
Open a connection to mysql and type:
STOP SLAVE; Load master's data dump with this console command:
mysql -uroot -p < mysqldump.sql Sync slave and master logs:
RESET SLAVE; CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98; Where the values of the above fields are the ones you copied before.
Finally type
START SLAVE; And to check that everything is working again, if you type
SHOW SLAVE STATUS; you should see:
Slave_IO_Running: Yes Slave_SQL_Running: Yes

Reference Link
http://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o
 

No comments:

Post a Comment