Full and incremental backups of data are two separate types of backups – one is more comprehensive than the other and each of them has their own distinct advantages and disadvantages. In this post, we will try to cover their usage in MySQL.

What is a Full Backup?

A full backup is a basic backup type – it’s a type of backup that backs up data in its entirety. In other words, a full backup is a total copy of the data assets in question. Such type of backup is the most comprehensive which means that you can have a complete copy of your data, but it can also occupy large amounts of space.

What is an Incremental Backup?

An incremental backup only backs up data that has changed since the last backup occurred. Such backups are often completed quickly and the backed up data occupies less resources than the full backup, but bear in mind that not all data is backed up – only data that has changed since the last backup occurred will be backed up.

Full Backups in MySQL

A full backup in MySQL includes all tables in a MySQL database and all databases in a MySQL instance. Full backups take the most time to complete, but they also require the least administration overhead.

To make a full backup of MySQL without external tools, you can make use of mysqlbackup. Run the following query:
$ mysqlbackup --user=root --password --host=127.0.0.1 --backup-image=/home/user/backups/backup.mbi --backup-dir=/home/user/backup-dir backup-to-image

Here user is your MySQL user, password tells the MySQL instance to prompt for a MySQL password for that user, host is the name of your MySQL host (usually 127.0.0.1), backup-image specifies the destination file, backup-dir is the location of a folder where the backup data and metadata will be saved, and backup-to-image creates a single file backup. The mysqlbackup command invokes MySQL Enterprise Backup – the tool is covered in-depth in this blog.

Also, note that the command-line options that are used with the mysqlbackup command can also be supplied to [mysqlbackup] in my.cnf. The tool also picks up options from the [mysqld] section if they are there. mysqlbackup can also compress InnoDB backup data files if the --compress option is present.

Incremental Backups in MySQL

An incremental backup only captures data that has changed since the previous backup. When a backup is needed to be restored, the restoration process would need all of the unchanged data from the last full backup and the changed data derived from incremental backups which were created after the full backup.

Incremental backups can also be made using mysqlbackup: there are two ways to do this, one using an --incremental-base option, the other using the --start-lsn option.

When the --incremental-base option is in use, mysqlbackup retrieves information that is required to perform incremental backups from the metadata inside of the backup directory. The --start-lsn option specifies the highest Log Sequence Number value from a previous backup (LSN represents a point in time corresponding to the operations in the redo log) – the value can be obtained by running the DESC backup_history query. Each row in the table records the details of one completed backup produced by mysqlbackup (backups also have a backup_id row which can help identify the ID of the backup).

Here’s an example of an incremental backup using the –incremental-base option:

$ mysqlbackup --defaults-file=/home/my.cnf --incremental --incremental-base=history:last_backup --backup-dir=/home/dir --backup-image=incremental_img.bi backup-to-image

In this case, a LSN value does not have to be specified because the –incremental-base option is used. Incremental backups can also be created using the --start-lsn option:

$ mysqlbackup --defaults-file=/home/my.cnf --incremental --start-lsn=XXXXXXXXXX --with-timestamp --backup-dir=/incremental-dir --backup-image=/incremental-backup/incremental_image.bi backup-to-image

Here the --start-lsn option specifies the Log Sequence Number and the –backup-dir parameter specifies the backup directory. Do note that when the --with-timestamp option is used, the directory specified in the --backup-dir parameter must be empty, otherwise the backup will fail.

Restoring Backups

To restore an incremental backup, shut down the database server and delete all files from its data directory. Also delete all files inside of the directories specified in the --innodb_data_home_dir, --innodb_log_group_home_dir and --innodb_undo_recovery options. Then, run the query below:

$ mysqlbackup --defaults-file=/home/my.cnf -uroot --backup-image=incremental_img.bi --backup-dir=/incremental-dir --datadir=/restore-dir --incremental copy-back-and-apply-log

To restore the data to a desired point in time, repeat the step for all of the incremental backup images that you have: see the MySQL’s Restore Operations documentation for all of the available options.

Restoring a full backup is a bit easier and it can be done by running this query:

$ mysqlbackup --datadir=/home/datadir --backup-image=/home/mysql_backups/my.mbi --backup-dir=/home/backupdir copy-back-and-apply-log

Here’s what the options do:

  • The --defaults-file option specifies the location of the my.cnf file;
  • The --backup-image option provides the path to a single-file backup;
  • The --backup-dir option provides the location to an empty folder where some temporary files will be stored;
  • The --datadir option defines the location of the data directory for restoring the data;
  • The --incremental option specifies an incremental backup;
  • The --copy-back-and-apply-log option makes the backup state consistent (prepares the backup), then copies all of the data onto the server.

Summary

A full backup backs up all tables in a MySQL database and all tables in a MySQL instance. Full backups usually take time to complete, but they also usually require the least follow up work. An incremental backup, on the other hand, only captures data that has changed since the previous backup, but the incremental backup data must be merged with the contents of the previous backup before it can be restored. When using an incremental backup, it also may be impossible to recover data if one of the incremental backups is missing or damaged.

If you’re deciding whether to use a full or an incremental backup solution, do some research and know their advantages and disadvantages – knowing those should set you on the right track.

Contributor
Comments to: MySQL Incremental vs Full Backups

Your email address will not be published. Required fields are marked *

Attach images - Only PNG, JPG, JPEG and GIF are supported.