Thursday Jan 17, 2013

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:


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: 

CREATE EVENT payment_backup
STARTS '2013-01-20 03:15:00'
   SELECT * INTO OUTFILE '/home/bkuser/payment.tsv'
   FROM sakila.payment;

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.

    Friday Jan 11, 2013

    Excluding a Table From Backup

    Let's say you have a database that stores not only current transactional data, but also historic data that's unchanging. In particular, you have a large table containing hundreds of gigabytes worth of last year's data, and it won't change. Having backed it up already, you don't need to back it up every time. Is there any way to exclude this table from a backup?

    For InnoDB tables with innodb-file-per-table enabled (the default as of MySQL 5.6), MySQL Enterprise Backup supports this feature in inverse. Specifically, you can choose to include specific innodb-file-per-table tables in addition to those stored in the system tablespace.

    In order to exclude a specific table, you need to provide a regular expression to the --include option that includes every table except the one you want to exclude. For example, in my sakila database, I know that the payment table is the only one beginning with "p", so if I want to exclude it, I can do this:

    # mysqlbackup --datadir=/var/lib/mysql --backup_dir=/backups \
    >        --include 'sakila\.[^p].*' backup-and-apply-log

    The following link contains detailed information on the --include option:

    You can also specify filters for non-InnoDB tables using options such as --databases and --databases-list-file. The following link covers a variety of different partial backup techniques: 


    Jeremy Smyth writes MySQL training courses, and likes exploring interesting questions that come up from novices and experts alike.



    « July 2016