2

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?

Improve this question

3 Answers 3

Reset to default
1

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.

Improve this answer
2
  • 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
    Commented Jul 3, 2013 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
    Commented Jul 4, 2013 at 5:49
2

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

Improve this answer
0
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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