Mysql Backup Script

[Note: This script was not written originally by me. I am just pasting it for others to consume.]

Here is a quick and nice mysql backup script that one can add to their crontab to produce nightly mysql backups. It is not completely turnkey as you need to edit some of the variables (or write some code and pass some arguments such that they can be specified on the command line) and also have a dblist.txt file ready with the names of the databases you wish to backup.

#!/bin/bash

# Backup mysql databases

# Set a value that we can use for a datestamp
DATE=date +%Y-%m-%d

# Our Base backup directory
BASEBACKUP="/folder/to/store/mysql/backups"

for DATABASE in cat dblist.txt
do
       # This is where we throw our backups.
       FILEDIR="$BASEBACKUP/$DATABASE"

       # Test to see if our backup directory exists.
       # If not, create it.
       if [ ! -d $FILEDIR ]
       then
               mkdir -p $FILEDIR
       fi

       echo -n "Exporting database:  $DATABASE"
       mysqldump --host=localhost --user='YourMySQLUser' --password='YourMySQLPW' --opt $DATABASE | gzip -c -9 > $FILEDIR/$DATABASE-$DATE.sql.gz
       echo "      ......[ Done ] "
done

# AutoPrune our backups.  This will find all files
# that are "MaxFileAge" days old and delete them.
MaxFileAge=30
find $BASEBACKUP -name '*.gz' -type f -mtime +$MaxFileAge -exec rm -f {} \;