Innobackupex-Based Incremental backup and recovery

MySQL Hot Backup (physical backup) can be used to add incremental backup for full backup to reduce the database I/O pressure and the occupation of system resources. Incremental backup is mainly based on full backup or Incremental Backup

MySQL Hot Backup (physical backup) can be used with full backup and Incremental backup to reduce the I/O pressure on the database and the occupation of system resources. Incremental backup is mainly based on full backup or Incremental Backup

MySQL Hot Backup (physical backup) can be used with full backup and Incremental backup to reduce the I/O pressure on the database and the occupation of system resources. Incremental backup is based on full backup or Incremental backup to back up the changed pages. The principle of its backup is based on an ever-increasing LSN sequence, which is similar to the Oracle SCN. During the recovery period, we need to roll back committed transactions and uncommitted transactions. This document describes Incremental backup and incremental recovery.

1. Additional knowledge

As not all information changes between each backup, the incremental backup strategy uses this to reduce the storage needs and the duration of making a backup. this can be done because each InnoDB page has a log sequence number, LSN, which acts as a version number of the entire database. every time the database is modied, this number gets incremented. an incremental backup copies all pages since a specic LSN. once the pages have been put together in their respective order, applying the logs will recreate the process that affected the database, yielding the data at the moment of the most recently created backup.

Incremental backup is the page that has changed since the last backup. You can use Incremental backup to reduce storage and system resource overhead. Incremental backup is mainly for InnoDB, because InnoDB adopts the log serial number (LSN) method. The LSN of InnoDB is an increasing sequence, similar to the SCN of Oracle, which records the changes of InnoDB. Incremental Backup changes after the specific LSN is backed up. By reorganizing these lsns in order, you can restore the database to a fault point or any time point.

Innobackupex -- incremental/data/backups -- incremental-lsn = 1291135

Innobackupex -- incremental/data/backups -- incremental-lsn = 1358967

As shown above, we can use the -- incremental-lsn option to implement incremental backup.

Warning: This procedure only affects XtraDB or InnoDB-based tables. Other tables with a different storage engine, e.g. MyISAM, will be copied entirely each time an incremental backup is already med.

For non-XtraDB or InnoDB Storage engines, the hot standby mode will still back up all data files, index files, format files, and so on.

Preparing an Incremental Backup with innobackupex Preparing incremental backups is a bit different than full ones. This is, perhaps, the stage where more attention is needed:

• First, only the committed transactions must be replayed on each backup. This will merge the base full backup with the incremental ones.

• Then, the uncommitted transaction must be rolled back in order to have a ready-to-use backup.

For the Prepare phase of Incremental backup, there are two points to note: one is that the committed transaction requires replayed, and the other is that the other is not committed transactions need rollback.

If you replay the committed transactions and rollback the uncommitted ones on the base backup, you will not be able to add the incremental ones. if you do this on an incremental one, you can't be able to add data from that moment and the remaining increments. having this in mind, the procedure is very straight-forward using the -- redo-only option, starting with the base backup:

If you replay committed transactions and roll back uncommitted transactions in the Prepare phase, subsequent Incremental Backup cannot be added to the current full backup. Therefore, the -- redo-only option should be used for full backup in the Prepare stage.

-- Redo-only shocould be used when merging all incrementals should t the last one. that's why the previous line doesn't contain the -- redo-only option. even if the -- redo-only was used on the last step, backup wocould still be consistent but in that case server wocould perform the rollback phase.

In the case of multiple increments, The -- redo-only option is not required only for the last increment. If yes, rollback will be completed when the server starts.

2. Demo Incremental Backup

A. Create a demo Environment
Robin @ localhost [(none)]> create database tempdb;

Robin @ localhost [(none)]> use tempdb;

Robin @ localhost [tempdb]> create table tb (id smallint, val varchar (20 ));

Robin @ localhost [tempdb]> insert into tb values (1, 'fullbak ');

B. Start a full backup
SHELL> innobackupex -- user = robin-password = xxx -- port = 3606 -- socket =/tmp/mysql3606.sock -- defaults-file =/etc/my3606.cnf \
>/Hotbak/full -- no-timestamp

-- Add another record to distinguish full backup from Incremental backup.
Robin @ localhost [tempdb]> insert into tb values (2, 'incbak ');

C. Start an incremental backup
SHELL> innobackupex -- user = robin-password = xxx -- port = 3606 -- socket =/tmp/mysql3606.sock -- defaults-file =/etc/my3606.cnf \
> -- Incremental/hotbak/inc -- incremental-basedir =/hotbak/full -- no-timestamp
-- ...... Ignore non-important information ........
Innobackupex: Using mysql server version 5.6.12-log-current mysql version
Innobackupex: Created backup directory/hotbak/inc -- create backup directory
Innobackupex: Suspend file '/hotbak/inc/xtrabackup_suspended_2' -- A sushortded directory is created for temporary use.
-- ...... Ignore non-important information ........
Xtrabackup: using the full scan for incremental backup
[01] Copying./ibdata1 to/hotbak/inc/ibdata1.delta -- we can see that the corresponding delta file is produced, that is, the incremental part.
[01]... done
> Log scanned up to (391476794)
Xtrabackup: Creating suspend file '/hotbak/inc/xtrabackup_suspended_2' with pid '123'
-- ...... Ignore non-important information ........
141222 14:55:08 innobackupex: Executing flush tables with read lock... -- This is mainly for non-innodb
141222 14:55:08 innobackupex: All tables locked and flushed to disk