Automatic MySQL Backup Script

[canela]

I modified crouse’s mysql backup script with some modifications and have been using it for a few weeks. It saved my tush yesterday, so I thought I’d share it.

It stores backup files in the directory /var/backups/mysql that it automatically creates. It also only backs up if MySQL is running by checking for the lock file.

The nice thing about it is that you don’t have to specify the names of the databases. It figures it out by itself.

Stick it in cron, and make sure you have enough space in /var !

You run it like this:

# mysql-backup <root-username> <root-password>

The code looks like this:

#!/bin/bash 
LOCK_FILE="/var/lock/subsys/mysqld"
# Create the backup directory if it doesn't already exist
if [ ! -d /var/backups/mysql ]; then
     mkdir -p /var/backups/mysql
fi
# Do backups
if [ -f "$LOCK_FILE" ]; then
    # Do MySQL backups
    for i in `/usr/bin/mysql -B -u $1 -e 'show databases' -p$2 | /bin/grep -v Database`; do
        /usr/bin/mysqldump --opt -u $1 -p$2 $i | gzip > /var/backups/mysql/$i.`date --iso-8601`.sql.gz
    done
fi

[trickster]

Thanks!

Perhaps we should add this to the Wiki?

[canela]

OK, it’s done! You can find it here.