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:
mysqldump
(or mysqlbackup
if you have an Enterprise licence) from an operating system scheduler, for example inSELECT ... INTO OUTFILE ...
commands, one for eachmysqlbackup
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 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
.
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.
/home/bkuser/payment.tsv
I used the event schedule to backup my database. But I don't know where the backup file is saved.
Thank you for your helping.
You'll need to change the path '/home/bkuser/payment.tsv' to one that's valid on your system.
If you're using Linux, you can use something like '/tmp/mybackup.tsv' to test it, but note that the /tmp directory is visible to all users on that system so you should plan to use a more secure directory for your actual backups.