A random weblog but it’s mine

Don’t need to say much, everything is in the title of this post .
Here is my mysql database backup script:

DIR="/var/backups/db/"
MAIL="your@mail.address"
LOGFILE=$DIR/backupdb.log

function backup_db {
HOST="$1"
USER="$2"
PASS="$3"
DB="$4"
MINSIZE="$5"

BACKUPFILESUFFIX="`date +%m%d`.bz2"
DBLIST=`echo "show databases" | mysql -u backup -p$PASS -h $HOST`
NUMDB=`echo $DBLIST |wc -w`

if [ ! -d ${dir} ]; then
  mkdir ${dir}
fi
if [ ! -e $DIR/count.$HOST ]; then
  echo $NUMDB > $DIR/count.$HOST
fi

COUNT=`cat $DIR/count.$HOST`

if [ "$COUNT" -lt "$NUMDB" ]; then
  echo -e "Databases list: $DBLIST" | mail -s "New database, maybe new backups needed!" $MAIL
  echo $NUMDB > $DIR/count.$HOST
fi

/usr/bin/mysqldump -u $USER -p$PASS -h $HOST --routines $DB|bzip2 > $DIR/$DB.$BACKUPFILESUFFIX

if [ $? != 0 ] ; then
  echo -e "Return code is : $? and log file contains:\n `cat $LOGFILE`" | mail -s "Backup MySQL $HOST: $DB Error" $MAIL
fi

SIZE=`du  -sk $DIR/$DB.$BACKUPFILESUFFIX| cut -f1`
if [ "$SIZE" -lt $MINSIZE ]; then
  echo -e "File is smaller than $MINSIZE k, printing an ls output:\n `ls -l $DIR`" | mail -s "Backup MySQL $HOST potential error" $MAIL
fi
}

# Cleaning up old files, or disks won't fill
DIR="/var/backups/db/"
find $DIR -ctime +7 -name "*bz2" -exec rm {} \; -print

backup_db "192.168.0.1" "user" "password" "database" "9999"

To backup your databases add at the end of script, one line per database, based on this format:
backup_db "host" "user" "password" "database name" "min_size"

You also need to create a backup user on your database, I use this script:

CREATE USER 'backup'@ 'backup-host' IDENTIFIED BY 'a-password';

GRANT SHOW DATABASES ON * . * TO 'backup'@ 'backup-host' IDENTIFIED BY 'a-password ;

GRANT SELECT ,
LOCK TABLES ,
SHOW VIEW ON `a_database_to_backup` . * TO 'backup'@ 'backup-host';

And also add a crontab entry:

cat <<EOF >/etc/cron.d/dbbackup
MAILTO=root
0 5 * * * root /var/backups/db/backupdb.sh 2>&1 >/var/backups/db/backupdb.log
EOF
chmod +x /etc/cron.d/dbbackup

This script have some features I have implemented that I find usefull:

• It mails you when a new databases is created (devs sometimes create a database but don’t inform me, they need backup of it, in case)
• It’s easy to add new databases to backup
• It checks for a minimum size, you know that some databases won’t be less than a fixed size, if it happens, there is probably a problem with the backup script or within the database
• It also backups stored procedures (we use the --routines option of mysqldump)
• It has a 7 days rotation mechanism, so the disk don’t fill
• The databases are compressed

Some improvement that can be done to this script:

• Better error handling, I’m not really sure how it works, I made this script pretty much fast for my daily needs
• Use mk-parallel-dump from the maatkit
• Use .my.cnf, and don’t display password in the script, is it better ?
• please comment to give me some ideas