MySQL database backup script

I wrote one of these for Mailman last fall, and finally got around to putting together a quick script to back up your MySQL databases. It optimizes and repairs all of your tables, then creates a directory for today, dumps each database into a SQL file and zips it up, leaving you with a directory of .tgz files.

The script requires the Python MySQL module. If you’re using DarwinPorts, port install py-mysql; on Debian, apt-get install python-mysqldb. With fink, install mysql-python-py24 (or mysql-python-py23, or mysql-python-py25, etc.).

The script is called mysqlBackup.py, and its syntax is pretty straightforward:
Usage: mysqlBackup.py <backuppath> <password> [<user>] [<hostname>]

If the username isn’t specified, it defaults to root. Likewise, the host defaults to localhost.

Typical use would be to place a helper script like this /etc/cron.weekly:

#!/bin/sh
/usr/local/bin/mysqlBackup.py /home/backups/mysql/ mypassword


mysqlBackup.py is available under a CreativeCommons-Attribution license.