Learning MySQL

Scheduling Backups

We all forget to do backups, and as Murphy’s Law would have it: “The hard drive on your computer will crash only when it contains vital information that has not been backed up” (for this and other interesting variations on Murphy’s Law, see http://www.murphys-laws.com). In this section, we’ll describe how you can configure automatic, regular backups using mysqldump; you can also use mysqlhotcopy if you wish.

Linux and Mac OS X

Under Linux and Mac OS X, you can list the commands you want to be executed in a crontab file; commands in the crontab file are run at the times you specify. First, you have to edit a crontab file:

$ crontab -e

This opens the crontab file for the current user for editing; the default editor on most systems is vi. If you’re not comfortable with this editor, you can specify your preferred editor by setting the EDITOR variable to the name of your favorite editor. For example, many novice users find the pico editor somewhat easier to use:

$ export EDITOR=pico
$ crontab -e

The general format of a crontab entry is:

MINUTE   HOUR   DAY   MONTH   DAYOFTHEWEEK   COMMAND

If you want a dump to be created from a particular database using the mysqldump command at 4:45 A.M. every Sunday, you can add the line:

45 4  *  *  sun /usr/local/mysql/bin/mysqldump \
 --user=root \
 --password=the_mysql_root_password \
 --result-file=path_to_backup_file \
 database_to_dump

Note that each entry must be on one line, and you must specify full paths to executables; the cron program might ...