I wrote a MySQL database backup script a while back. I known they are more than enough of them already floating around the internet, but hey, I figured I'd share it here anyway.

The script

For the script to work, you'll need to edit a few variable to match your configuration.

BACKUPDIR is the path of the directory where you want your backups to be stored.
BACKUPUSR is the user that will connect to MySQL to dump the databases. It should have access to all you databases without needing a password.
EXCLUDELIST is a list of databases that should not be backed-up. Leaving it as is is probably fine.

#!/bin/bash

BACKUPDIR="/home/user/backup"
BACKUPUSR="user"
EXCLUDELIST="^Databases$|^information_schema$|^mysql$|^performance_schema$"

sqlbk() {
  for each in $(mysqlshow | awk '/[[:alnum:]]/{print $2}'); do
    if [[ $each =~ $EXCLUDELIST ]]; then
      true
    else
      mysqldump $each | bzip2 > ${BACKUPDIR}/${each}.sql.bz2
      chown ${BACKUPUSR}: ${BACKUPDIR}/${each}.sql.bz2 && chmod 600 ${BACKUPDIR}/${each}.sql.bz2
    fi
  done
}

[[ -e /etc/init.d/mysql ]] && sqlbk

I personnaly have this script running once a week, in my user's personnal crontab (editable using the crontab -e command) :

## WEEKLY DATABASE BACKUP
@weekly /home/user/bin/backupdb

Conclusion

You've probably noticed that the script erases the previous backup when a new one is made.

I don't need to keep multiple versions of the same database backup on my servers because they are all saved remotely on a daily basis using Rsnapshot. I'll probably write an article on the subject in the future.

As usual, feedback is always appreciated !

MySQL backup script

The script

Conclusion