Innobackupex-Based Incremental backup and recovery

Innobackupex-Based Incremental backup and recovery

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 modi already ed, this number gets incremented. an incremental backup copies all pages since a speci sans c 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

141222 14:55:08 innobackupex: Starting to backup non-InnoDB tables and files -- start backup of non-innodb
Innobackupex: in subdirectories of '/data/inst3606/data3606' -- all non-innodb will be backed up again
Innobackupex: Backing up files '/data/inst3606/data3606/mysql/*. {frm, isl, MYD,..., CSV, opt, par}' (77 files)

141222 14:55:09 innobackupex: Executing flush engine logs... -- log Switching
141222 14:55:09 innobackupex: Waiting for log copying to finish

Xtrabackup: The latest check point (for incremental): '000000' -- checkpoint position
Xtrabackup: Stopping log copying thread.
.> Log scanned up to (391476794)
-- ...... Ignore non-important information ........
Xtrabackup: Creating suspend file '/hotbak/inc/xtrabackup_log_copied' with pid '123'
Xtrabackup: Transaction log of lsn (391476794) to (391476794) was copied. -- copy the Transaction log
141222 14:55:10 innobackupex: All tables unlocked -- table unlock
-- ...... Ignore non-important information ........
141222 14:55:10 innobackupex: completed OK!

D. View related files after preparation
SHELL> ls-hltr/hotbak/inc/* delta *
-Rw-r ----- 1 root 96 K 2014/12/22/hotbak/inc/ibdata1.delta

SHELL> more/hotbak/inc/xtrabackup_info | grep ^ incremental
Incremental = Y

-- The xtrabackup_checkpoints file contains the backup checkpoint information.
SHELL> more/hotbak/inc/xtrabackup_checkpoints
Backup_type = incremental
From_lsn = 391476482
To_lsns = 391476794
Last_lsns = 391476794
Compact = 0

-- The xtrabackup_binlog_info file contains the binlog location.
SHELL> more xtrabackup_binlog_info
Inst3606bin. 000010 874

-- Add a record again to check whether the Incremental Backup can be restored.
Robin @ localhost [tempdb]> insert into tb values (3, 'inbinlog ');
Query OK, 1 row affected (0.00 sec)

3. Restore Incremental Backup

A. First apply based on full backup. Note that -- redo-only is used.
SHELL> innobackupex -- apply-log -- redo-only -- user = robin-password = xxx -- port = 3606 \
> -- Defaults-file =/etc/my3606.cnf/hotbak/full

B. apply Based on Incremental Backup,
-- There is no -- redo-only at this time. If there are multiple incremental backups, you do not need to specify -- redo-only for the last Incremental backup.
SHELL> innobackupex -- apply-log -- user = robin-password = xxx -- port = 3606 -- defaults-file =/etc/my3606.cnf \
>/Hotbak/full -- incremental-dir =/hotbak/inc
-- ...... Ignore non-important information ........
Xtrabackup: page size for/hotbak/inc/tempdb/tb. ibd. delta is 16384 bytes
Applying/hotbak/inc/tempdb/tb. ibd. delta to./tempdb/tb. ibd... -- The following table tb of the database tempdb is applied to the full backup directory.
Xtrabackup: page size for/hotbak/inc/sakila/t. ibd. delta is 16384 bytes
Applying/hotbak/inc/sakila/t. ibd. delta to./sakila/t. ibd...
-- ...... Ignore non-important information ........
Xtrabackup: Starting InnoDB instance for recovery. -- start InnoDB instance recovery
Xtrabackup: Using 104857600 bytes for buffer pool (set by -- use-memory parameter)
-- ...... Ignore non-important information ........
InnoDB: Highest supported file format is Barracuda.
InnoDB: The log sequence numbers 391476482 and 391476482 in ibdata files do not match
The log sequence number 391476794 in the ib_logfiles! -- The log sequence is inconsistent.
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery. -- start crash recovery
InnoDB: Reading tablespace information from the. ibd files...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 874, file name inst3606bin. 000010
InnoDB: 128 rollback segment (s) are active. -- The system prompts that some rollback segments are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.21 started; log sequence number 391476794
--... Ignore non-important information... Author: Leshami Blog: <A href ="