MySQL Backups

From Devpit
Jump to: navigation, search

An easy way to back up MySQL databases is to simply dump the entire thing soon before your backup job for the host machine runs. Store the dump on the host machine's filesystem, and it'll get slurped onto tape or wherever with everything else.

  • Make a user named backup.
    • Assign Select_priv and Lock_tables_priv in mysql.user so mysqldump will always be able to read all the databases.
    • Give the user a password if you like. On a machine where only administrators may log in, this isn't necessary. If you do, add it to the mysqldump line in the script below. If you don't, take out the --pass option below.
  • Create the script below, and add it to root's crontab. Set it to run with enough time before the host machine's backups start to be sure it will finish before the host machine's backups start.
  • Add "-mtime +7d" (or however many days you want to keep your old backups) to the find command if you like. It should be at least twice the interval of the crontab so that there is always an old version available if the backup fails once. I didn't include this because normally your normal backup routine will keep the old copy for awhile.
#!/usr/local/bin/zsh

date="`date '+%Y.%m.%d.%H.%M.%S'`"
destdir="/var/backups/mysql"
destfile="${destdir}/mysqldump-${date}"

chmod 700 $0
mkdir $destdir 2>/dev/null
chown root:wheel $destdir
chmod 700 $destdir

find $destdir -type f -name "mysqldump-*" -print0 | xargs -t -0 rm -f

/usr/local/bin/mysqldump --host=localhost --user=backup --pass=XYZ \
        --add-drop-table --add-locks --all --complete-insert --extended-insert=0 --quick --lock-tables \
        --all-databases | gzip -9 > ${destfile}.gz