Tilde~A: Alexios' Homepage

Incremental MySQL Maintenance Script

Table of Contents


You run a medium-to-large size MySQL server with lots of databases, possibly for hosted websites. You've experienced performance degradation. You've fine-tuned your database engines, and no joy. Then you finally realise you need to run mysqloptimize, and the day is saved! Good on you. Of course, running mysqloptimize on a large server still takes half an ice age. Maybe you don't really want to use that as an excuse to middle management. ‘My code is compiling’ is better than ‘mysqloptimize is still running’. So let's perform our optimisations in batch. This script optimises one seventh of your databases every day. Run it once a day when it's quiet, and at the end of every week every database will have had its maintenance run. If you want to run it on a different schedule, just pass it a couple of parameters. The first parameter is which run number this is (0–6 for weekly runs, depending on the day of the week). The second parameter is the total number of runs in a cycle (7 for weeklies). The date invocation shows you how to do this (note how we're subtracting one to make sure the first run is run 0). The OPTS variable controls options to mysqloptimize itself. Change these all you want. And, of course, you'll want to set your HOST, USER and PASSWORD for a user who has the privileges to run mysqloptimize on all tables of all databases. Naturally, you'll want to protect the contents of this file from random (or intentional) perusal using chmod 700 mysql-maintenance-batch or something like that. Then, just stick it into a crontab. (you know how to do this, right?) Beware: by default, the output from mysqloptimize is sent to stdout, and crond will probably email you every night. If you don't like that, you know what to do (>/dev/null or uncommend the exec >/dev/null line). Here's the code:


OPTS="-aoCm --auto-repair"
PART=${1:-$[`date +%u`-1]}
me=`dirname $0`/`basename $0`

# Uncomment to silence the script
#exec >/dev/null

mysqloptimize $OPTS --user=$USER \
    --password=$(grep "^ *PASSWORD=" $me | head -1 | cut -d= -f2-) \
    --databases mysql `mysqlshow \
            --user=$USER \
            --password=$(grep "^ *PASSWORD=" $me | head -1 | cut -d= -f2-) |
            tail -n +4 |
            grep '^| [^ ]' |
            gawk -v d=$PART -v mod=$PERIOD -- \
                '{ if (((NR - 1) % mod) == d) print $2; }'`
# End of file.

How it Works

It simply prints out all MySQL databases, picks one seventh of them, and feeds their names to mysqloptimize. The mysql database is always optimised, no matter what day it is. Most of the weirdness is for security reasons.

Security Concerns

Obviously, your MySQL password is stored in this file in plaintext. 'Nuff said, really.

The weird lines pertaining to passwords are so that the actual password is never put on the command line (even by expansion, by saying --password=$PASSWORD. MySQL tries to hide the passwords on some operating systems, but not all of them allow it. Solaris 10 (on SPARC) and Debian GNU/Linux (on AMD64) seem to not modify the command line, and you can clearly see the passwords.

Customising It

If you have a very big server and wish to run a monthly cycle, you could change PART and PERIOD as follows:

PART=${1:-$[`date +%e`-1]}

Note the period, 28. If we don't do that, some databases will not be optimised on some months. With a period of 28 days, every database gets its chance. Only the mysql database will be optimised on the 29th, 30th and 31st day of every month that has them.

Missing mysqloptimize?

If mysqloptimize appears to be missing on your system, fear not! This is simply an alias for mysqlcheck. In fact, you can get away by symlinking it with this magic:

[ -z $(which mysqlcheck) ] ||
    ln -s $(which mysqlcheck) $(which mysqlcheck | sed s/check/optimize)

If you can't be bothered, just substitute mysqlcheck -o (that's the lower-case letter) for mysqloptimize.

This is obviously for *nix systems (including MacOS X). On Windows, VMS, CP/M and RT/11, you're on your own.