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:
mysqlbackupif you have an Enterprise licence) from an operating system scheduler, for example in
SELECT ... INTO OUTFILE ...commands, one for each
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
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 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
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
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 ... 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.
INTO OUTFILE ...
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.