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, and its syntax is pretty straightforward:
Usage: <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:

/usr/local/bin/ /home/backups/mysql/ mypassword is available under a CreativeCommons-Attribution license.

Join the Conversation

1 Comment

Leave a comment

Your email address will not be published. Required fields are marked *