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.

I'm running MySQL 5.5.31 and I have a master/slave setup. I accidentally ran a mysql UPDATE on the slave, a simple one-row update like:

UPDATE thetable SET somefield = 'asdf' WHERE id = 1234

I realized my mistake quickly and then ran the same exact statement on master. I probably should have looked into possible issues after the update to slave before the update to master, but I wasn't thinking clearly, so I don't know what the state was like between these operations.

Anyway, after that, worried that I may have caused a sync problem, I did a SHOW SLAVE STATUS and the replication seemed to be working fine, fully caught up with master, and with no error messages. I also did a CHECKSUM TABLE thetable on both master and slave, and the value returned was identical.

So everything seems to be running fine, but I want to avoid running into any potential problems. Given what I described, should everything be okay here on out? Is something about this resulting state going to pop up and bite me? In general, if you make an update to slave followed immediately by an identical update to master, will sync problems be avoided?

share|improve this question

migrated from serverfault.com Jul 3 '13 at 5:26

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

3 Answers 3

up vote 1 down vote accepted

Running SHOW SLAVE STATUS is not the way to check if you have a sync problem. It only tells you if you ran into the sync problem in a certain way. Your data can be way out of sync with replication still running happily. Checking the checksum is the more proper way indeed, and can be done automatically for you with percona table checksum (http://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html), formerly called maatkit.

share|improve this answer
    
Thanks, I didn't know that about SHOW SLAVE STATUS, that's good to know. So if the checksums come out equivalent is it safe to assume everything's good? –  Ben Lee Jul 3 '13 at 15:16
    
Yeah, entire tables or even databases could be missing, but if there are no writes replicated to them, SHOW SLAVE STATUS would be none the wiser and happily continue. –  arjarj Jul 4 '13 at 5:49

Since the end result on both master and slave was identical, I doubt you'll have any real issues. Though it's definitely best to avoid this whenever possible, since your next query might not be so benign...

share|improve this answer

Assuming that before the UPDATE statements replication has been working correctly and the data was the same on both servers, the UPDATE you ran on master has replicated to the salve and overwritten the UPDATE you ran earlier. Therefore the data should be the same on both servers. The best way to see if that is so is to use pt-table-checksum from percona-toolkit as stated earlier.

share|improve this answer

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.