I need help from my fellow mysql users. I know some of the people who read this are alot better then me with mysql so hopefully you can help 🙂
So today we decided that we are going to migrate one of our master database servers to new hardware. Since we got the hardware this morning and we wanted to move on to it asap, we decided that we will take our slave down, copy data from it, and bring it up on future master server. At that point, we will let it run as slave to the current master server until its time for us to take it down. Reason we did that instead of mysqldump/import was to avoid the lag mysqldump creates on our server.
After we did all this and put up the new master server, we started to notice odd issues. After looking around and comparing old db with new, we found out that new db was missing data. How it happened is beyond me and is the reason why I am writing this. We never had issues with the slave which would cause data to be lost; so what happened to those missing rows? Is this something which is common? Can we not trust our slave enough to use it as master if master died? Can we not run backups off the slave with confident that our data is protected and up to date so to keep load down on our master? All these questions which keep me awake and wondering…
Alas, no you cannot trust slaves. You must check them. Hence Maatkit: mk-table-checksum and mk-table-sync.
You MUST monitor mysql slaves all the time using something such as mk-table-checksum – otherwise there is no guarantee they will stay in sync even if initially there.
There are many possible reasons for this, the mysql manual explains some of them. Most of them are to do with the application’s design, others are shortcomings in mysql itself.
Using row-based replication on 5.1 + might be better, but it still won’t be guaranteed – you’ll want to continue checking.
Mark
This is why you can never trust slaves. Maatkit is a very valuable tool but only suitable for small-medium sized databases.
Frank: what do you use for large databases if that tool is not suitable for it?
Xaprb/Mark: Thanks for you comments. Good to know it is not something I mis configured and it is a known issue.
Well… define small. That means different things to different people. The mk-table-checksum tools are suitable for 100GB or so of data, depending on your performance needs.
From all the people I have talked to (mostly mysql consultants), they have never had issues with mk-table-checksum running on any of the dbs they used it on. I really don’t think I need to be concerned with the size at this time. Since Baron mentioned 100gb of data and none of my dbs are there (biggest one is close to 40 gigs), I will use this tool until I start to see performance issues.
Thanks for all of the hard work on this blog. Kate really loves managing investigations and it’s really easy to see why. Many of us notice all concerning the lively mode you make very helpful tips through this web site and in addition attract response from some others on the area plus our favorite girl is without question starting to learn so much. Enjoy the remaining portion of the new year. You have been doing a fantastic job.
I not to mention my buddies have already been analyzing the nice tricks located on the blog then the sudden I got a horrible suspicion I had not expressed respect to the site owner for those strategies. These people were definitely totally excited to study them and have in effect really been having fun with these things. Appreciation for turning out to be very accommodating and also for using some impressive issues millions of individuals are really eager to learn about. My sincere regret for not expressing appreciation to sooner.
I have to show my respect for your generosity supporting people that really want help with this issue. Your real commitment to passing the message around became pretty functional and has continuously helped those just like me to realize their pursuits. Your new important recommendations entails much to me and a whole lot more to my mates. Thanks a ton; from everyone of us.