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.

Here's the code:

#!/bin/bash

DEFAULTS="--defaults-file=/etc/mysql/debian.cnf"
OPTS="-aoCm --auto-repair $DEFAULTS"
PART=${1:-$(($(date +%u)-1))}
PERIOD=${2:-7}
me=$(dirname $0`/`basename $0)

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

mysql $DEFAULTS <<<"SHOW DATABASES" |
    gawk -v d=$PART -v mod=$PERIOD -- \
        '(NR > 1 && ((NR - 1) % mod) == d) { print }' |
    xargs -r mysqloptimize $OPTS --databases

# End of file.

Debian makes a MySQL account just for this sort of use, and keeps it in the option file /etc/mysql/debian.cnf. Using the --defaults-file option, we can avoid keeping the credentials in our script. They're in just one central location which is easier to keep out of prying eyes.

If you don't use Debian, you should make your own option file and update the DEFAULTS variable in this script to point to it. Then secure the option file according to your security policy.

How it Works

It simply prints out all available MySQL databases, picks one seventh of them, and feeds their names to mysqloptimize. If you have a lot of databases, the command line may get too big. Using xargs fixes that by running mysqloptimize as many times as needed.

Security Concerns

No passwords are kept in this script, so any security concerns arise from incorrect use. I shouldn't have to say that this script comes with absolutely no guarantees, but THIS SCRIPT COMES WITH ABSOLUTELY NO GUARANTEES.

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]}
PERIOD=${2:-28}

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.

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:

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.