Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Today, the replication between our master mysql db server and the two replication servers dropped. I have a procedure here which was written a long time ago and i'm not sure it's the fastest method to recover for this issue. I'd like to share with you the procedure and I'd appreciate if you could give your thoughts about it and maybe even tell me how it can be done quicker.

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 mysq
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
That's it!

At the moment i'm in the stage of copying the db from the master to the other two replication servers and it takes more than 6 hours to that point, isn't it too slow? The servers are connected through a 1gb switch.

share|improve this question

migrated from serverfault.com Jul 1 '13 at 0:25

This question came from our site for system and network administrators.

1  
1  
Thanks for your response, but this article is talking about how to create replication and not about how to quickly recover from a replication fail. –  Itai Ganot Jun 30 '13 at 16:04
    
Your question is pretty much not answerable. You ask what is the best way to recover from a failed replication (without any further details!). Answer: Depends. It depends on what failed, your general setup, the workload, ... If you want to improve your existing procedure then check out the link @quanta posted. Your procedure also doesn't 'recover' anything, it just sets up replication from scratch. –  faker Jun 30 '13 at 19:30

3 Answers 3

We can start replication from the position from where it stopped. You should check the Mysql log on slave servers log must have the last mysql bin log postion where the replication stopped.

 STOP SLAVE;

 RESET Slave;

 FLUSH TABLES WITH READ LOCK;

 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

 Where the values of the above fields are the ones where replication stopped.  

Finally type

 START SLAVE;
share|improve this answer
    
It would be an extremely rare situation where this advice would be helpful. In most cases, following these steps is almost guaranteed to mask the real problem and/or cause the slave to further diverge from comsistency. –  Michael - sqlbot Jul 1 '13 at 4:22

I know this is an old thread, but I will post for the benefit of others. I do not have an answer to this question myself, but I am currently in the process of planning for a scenario such as this and while doing so ran into this article that I think might be helpful:

http://www.barryodonovan.com/index.php/2013/03/23/recovering-mysql-master-master-replication

It is very well written, easy to follow along, practical, and I think it is in line with what the OP was looking for.

share|improve this answer

Really depends on what failed and why. If it's only connection problem and no data has been damaged on the master, I usually just check 'show slave status' and see what statement has failed. In my case, usually I just remove a row if it's trying to insert the same row and 'start slave' again.

If it's the master that failed (e.g. power failure), I'd promote the slave to be the master while I synchronise the old master with the new master. This way you keep your down time minimal and avoid transferring log file.

Only copy log file when you really need to, like after a disk crash.

share|improve this answer
    
It failed with error "max_allowed_packet...", after some investigation it seems like someone ran an update on the whole database and that was the cause for the drop, we finished the process 8 hours after we started it and the company lost lots of money during that offline time. –  Itai Ganot Jul 1 '13 at 7:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.