Automating Backups

If you need to automate backups, you might wonder about the different techniques available to you.

With regards to scheduling backups using built-in features of MySQL, you have two main options:

• Either run mysqldump (or mysqlbackup if you have an Enterprise licence) from an operating system scheduler, for example in
  Linux using "cron" or in Windows using the "Task Scheduler". This is the most commonly used option.
• Alternatively, use the Event Scheduler to perform a series of  SELECT ... INTO OUTFILE ... commands, one for each
  table you need to back up. This is a less commonly used option, but you might still find it useful.

Scheduling mysqlbackup with cron

mysqldump is a client program, so when you run it, you run it from a shell script, or at a terminal, rather than inside a MySQL statement. The following statement backs up the sakila database using settings stored in the file /home/bk/bk.cnf:

mysqldump --defaults-file=/home/bk/bk.cnf sakila > /home/bk/backup.sql

Typically, you would place login information into an options file to avoid specifying a password on the command line. Bear in mind that the configuration file stores the password in plain text, so you should ensure it is secured against unauthorised access. An option file such as that used in the preceding statement could look like the following, assuming that the user bk exists with the password as shown:

[mysqldump]
user=bk
password=bkpwd123

To schedule a backup using mysqldump, use an operating system scheduler such as cron in Linux. Without getting into too much detail, cron uses a configuration file (called a crontab) that allows you to specify a recurring schedule based on minutes, hours, day (of the month or of the week), and month. The following line (placed in the system's crontab)  runs a backup every Sunday at 3:15am:

#m h dom mon dow user    command
15 3 *   *   7   bk      mysqldump --defaults-file=/home/bk/bk.cnf sakila > /home/bk/backup.sql

For a complete discussion of cron and the crontab, your operating system's manpages should have an entry visible with the man crontab command. Wikipedia has a good overview too. 

The Event Scheduler

The Event Scheduler runs from within MySQL, so you can use it to schedule any valid MySQL statement that you can use within a query or stored routine. In particular, you can use the Event Scheduler to schedule a statement such as SELECT ... INTO OUTFILE ... , but you cannot invoke a command-line program such as mysqldump.

For example, you could schedule a regular dump of the sakila.payment table with the following sequence: 

DELIMITER //
CREATE EVENT payment_backup
ON SCHEDULE EVERY 1 WEEK
STARTS '2013-01-20 03:15:00'
DO
BEGIN
  SELECT * INTO OUTFILE '/home/bkuser/payment.tsv'
   FROM sakila.payment;
END//
DELIMITER ;

The preceding statement creates a scheduled event that runs weekly starting at 3:15am on 20th January 2013, a time that is still in the future as I write this. If you create an event starting some time in the past, it is immediately discarded with a warning, so bear that in mind if you're copying and pasting from this post.

The Event Scheduler is enabled with the global event_scheduler variable. This variable is set to OFF by default, so to enable it you must set it to ON.

Comparing the Techniques 

Backups created with mysqldump are files containing SQL
statements to recreate the database and its data; one backup typically consists of a single SQL file that can be executed as a whole to recreate the database(s) along with all data. On the other hand, the files created by SELECT ...
INTO OUTFILE ... statements are tab-separated data values, by default, and are usually one file per table; although you have the flexibility of dumping data from multiple tables in a single query, the resulting file can not be used as a backup, because restoring from such a file would be impractical.

The files are of very different formats, so you would choose your automated backup method based not only on how the schedule and syntax works, but also how best to restore files created using the two techniques and whether you wish to use the files in some other way. For example, many common spreadsheet programs can read TSV files, so you could use files created with SELECT ... INTO OUTFILE ... for analysis as well as for backups.

Also bear in mind the need to build a recovery strategy around the file formats you have; there's no point going to all the effort of taking backups if you can't recover from them.

There are several other alternatives you can use to back up your databases, including MySQL Enterprise Backup (with similar conditions to those of mysqldump mentioned earlier in this post). You can also use file-system snapshots or raw file copies, neither of which makes use of mysqldump or the Event scheduler, and each of which has its own interesting features and limitations.