The Beauty of mysqlcheck

I manage a ton of mysql databases, from single wordpress instances, to clusters, to systems that have a core data structure that selectively replicates out to as many as 50 systems.   Fortunately, or maybe not, they seldom have issues and after setup, other than disk space issues, I seldom have to touch them.

Recently, as I was migrating a set of DBs from an aquired company onto new hardware in a new location, I ran across a DB that seemed to consistently become corrupt.  It was usually the larger tables and typically on the slave system.  At first, it was bad enough that I blamed the hardware.  So the box was replaced and things went well for a while.  Recently the head developer for this system came to me with corrupted tables.  He was seeing the output in his code and was feeding me the tables so that I could repair them.  We did them one by one and it became clear that I really needed to check the whole DB.

It would have been relatively simple to throw together a script to do this, but I was apparenlty feeling lazy and did a google search instead.  Low an behold, I ran across a utility that I have read about countless times in the past, and I’m sure at some point I had actually run.   mysqlcheck.  I believe this nice little script has been packaged with mysql since the late 3.x strains.

At any rate, I think I’m documenting it here so that I won’t forget 6 months from now when I need the same functionality again.

So, on the system in question, I had about 20 instances and only one of them needed to be checked.  This is a big deal as the check script, or the repair command, or the optimize command will lock up your tables when it does it’s work and there were 19 web/data instances running on this box that I didn’t want to bother.  The check script is smart enough to handle this situation with the –databases flag.  Simply add the names of the instances that you want the check after that flag and you’re off.

Obviously, you want to do your best to stop traffic to the instances you intend to repair.  Can you get away with not doing this?  Well sure, remember however that the check, repair and optimize functions will lock up the tables in question when they do their work.  What does this mean?  Well, it means that none of your inbound querys will be run, they will queue up behind the repair functions until those functions finish.  In a really bad scenario, it’s possible that each of your querys will be bound to an apache instance and your web server will start eating up memory and potentially crash the whole box.  Thus, it’s in your best interest to stop as much traffic as possible before you start.

In my case, the usage of mysqlcheck looked like this:

mysqlcheck -uroot -pxxxxx –auto-repair –optimize –databases broken_db

The output is fantastic, it tells you where it’s at and what is broken or ok.  If you don’t want to see the output, if you intend to run from cron or whatever, there is a –silent option as well.

At any rate, leave it to mysql to simplify your admin a bit.  Run the mysqlcheck without any options and you’ll see all the options.  Takes 5 minutes to read through them.

That’s it, happy mysql’ing.

Related Posts:

• When to use MySQL BlackHole Tables
• Quick and Dirty DRBD with HA
• Getting Rid Of MySQL Binary Logs