Automatized backup of MySQL databases

I was given the task to write a script that makes daily backups of our company's MySQL databases and keeps only the most recent <n> of them (where <n> is a positive integer Wink ). This is not a big deal for anyone with some knowledge of shell scripting, but I publish it nonetheless, because I think the implementation is quite nice and might spare some minutes/hours for beginners.

The script uses zsh (the shell of my choice), but most of it would work in Bash, too. You should run it from crontab on a daily basis, which means you have to:
  1. place the attached mysql_backup.zsh in a directory of your choice (eg. ${HOME}/bin)
  2. open the script in a text editor (eg. vi) and add the necessary "do_backup" commands with the necessary parameters for backing up your databases
  3. extract your current crontab with something like crontab -l > .crontab
  4. add a new line to the end of ".crontab" with this: 0 3 * * * ${HOME}/bin/mysql_backup.zsh
  5. overwrite the crontab settings with crontab .crontab
That's all. Smile

mysql_backup-1.0.0.zsh1.42 KB
mysql_backup-1.1.0.zsh1.7 KB
mysql_backup-1.2.0.zsh2.33 KB
mysql_backup-1.3.0.zsh2.38 KB
mysql_backup-1.4.0.zsh2.38 KB
mysql_backup-1.5.0.zsh2.51 KB
mysql_backup-1.6.0.zsh2.66 KB
mysql_backup-1.7.0.zsh2.66 KB


Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

MySQL backup v1.3.0

This version adds the option to specify the date format used in the logfile. By default it uses the RFC2822 format.

MySQL backup v1.4.0

I just realized that the last uploaded version did not contain the fix for the cleanup of old backups. The -ctime parameter of find did not contain the + prefix before the number of days, thus it was only deleting backups that were exactly of the specified age and skipped older ones.

MySQL backup v1.5.0

  • use of nice (with "-n +19") to limit the CPU resources consumed by the bzip2 compression
  • separated the mysqldump command from the bzip2 compression so the former is not affected in any way by the latter
  • use of quotes around all path related shell variables

MySQL backup v1.6.0

  • if the hostname parameter for a do_backup call is "localhost" or "", then the generated filename will contain the real hostname (fetched from the output of the hostname command) instead

MySQL backup v1.7.0

  • Bugfix: the "days" variable was not used properly in the cleanup code, find ... -mtime +${days} returned ${days}+2 items. Replaced it with an ls -1t + tail -n +$((days+1)) combo.
  • Bugfix: in case the directory specified by the "backupdir" variable was not writable, the script still tried to log an error message in that directory.