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 between full backup and Incremental Backup ro Bin @ 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/hotba K/inc -- create the Backup Directory innobackupex: Suspend file '/hotbak/inc/xtrabackup_suspended_2' -- at the same time, the corresponding suincluded directory will be created for temporary use --........ ignore non-important information ........ xtrabackup: using the full scan for incremental backup [01] Copying. /ibdata1 to/hotbak/inc/ibdata1.delta -- the corresponding delta file is generated, that is, the incremental part [01]... done> log scanned up to (391476794) xtrabackup: Creating suspend file '/hotbak/inc/xtrabackup_suspended_2' with pid '100 '--. ....... Ignore non-important information ........ 141222 14:55:08 innobackupex: Executing flush tables with read lock... -- This is mainly for non-innodb141222 14:55:08 innobackupex: All tables locked and flushed to disk141222 14:55:08 innobackupex: Starting to backup non-InnoDB tables and files -- start backup non-innodbinnobackupex: in subdirectories of '/data/inst3606/data3606' -- all non-innodb will be backed up again once innobackupex: Backing up files '/data/inst3606/data36 06/mysql /*. {frm, isl, MYD ,..., CSV, opt, par} '(77 files) 141222 14:55:09 innobackupex: Executing flush engine logs... -- log switch 141222 14:55:09 innobackupex: Waiting for log copying to finishxtrabackup: The latest check point (for incremental): '123' -- checkpoint location 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 '000000' xtrabackup: Transaction log of lsn (25001) 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. Check the related file SHELL> ls-hltr/hotbak/inc/* delta *-rw-r ----- 1 root 96 K 2014/12/22 55/hotbak/inc/ibdata1.deltaSHELL> more/hotbak/inc/xtrabackup_info | grep ^ incremental = Y -- the file xtrabackup_checkpoints contains the backup checkpoint information. SHELL> more/hotbak/inc/checkpoints = incrementalfrom_lsn = begin 391476794 compact = 0 -- file xtrabackup_binlog_info contains the binlog location SHELL> more xtrabackup_binlog_infoinst3606bin.000010 874 -- add a record again, check whether the Incremental Backup can restore robin @ localhost [tempdb]> insert into tb values (3, 'inbinlog'); Query OK, 1 row affected (0.00 sec)

3. Restore Incremental Backup

A. apply based on full backup first. Note, -- redo-onlySHELL> innobackupex -- apply-log -- redo-only -- user = robin-password = xxx -- port = 3606 \> -- defaults-file =/etc/my3606.cnf /hotbak/fullb, 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-onlySHELL> innobackupex -- apply-log -- user = robin-password = xxx -- port = 3606 -- defaults-file =/etc/my3606.cnf \> /hotbak/full -- incremental-dir =/hotbak/inc --........ ignore non-important information ........ x Trabackup: page size for/hotbak/inc/tempdb/tb. ibd. delta is 16384 bytesApplying/hotbak/inc/tempdb/tb. ibd. delta. /tempdb/tb. ibd... -- we can see that the table tb in the database tempdb is applied to the full backup directory xtrabackup: page size for/hotbak/inc/sakila/t. ibd. delta is 16384 bytesApplying/hotbak/inc/sakila/t. ibd. delta. /sakila/t. ibd... --........ ignore non-important information ........ xtrabackup: Starting InnoDB instance for recovery. -- start InnoDB instance to restore 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! -- Prompt log sequence inconsistency InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. -- start crash recoveryInnoDB: Reading tablespace information from. 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. -- Some rollback segments are active InnoDB: Waiting for purge to startInnoDB: 5.6.21 started; lo G sequence number 391476794 --........ ignore non-important information ........ author: Leshami Blog: http://blog.csdn.net/leshamiinnobackupex: Starting to copy non-InnoDB files in '/hotbak/inc' innobackupex: to the full backup directory '/hotbak/full' -- Copy non-InnoDB tables to the full backup directory, and overwrite innobackupex: copying '/hotbak/inc/xtrabackup_binlog_info' to '/hotbak/full/xtrabackup_binlog_info '........ ignore non-important information ........ 141222 16:19:52 innobackupex: Completed OK! -- Merge incremental c and copy backSHELL> mv/data/inst3606/data3606/data/inst3606/data3606bk -- correct @ 2014122251018. We recommend that you disable the instance before the mv, execute mysqldownSHELL> mkdir-p/data/inst3606/data3606SHELL> mysqldown-P3606 -- disable the instance SHELL> netstat-nltp | grep mysql | grep 3606 SHELL> innobackupex -- user = robin-password = xxx -- port = 3606 -- copy-back/hotbak/full -- defaults-file =/etc/my3606.cnfSHELL> chown-R mysql: mysql/data/inst3606/data3606 -- start the recovered instance SHELL> mysqld_safe -- defaults-file =/etc/my3606.cnf & -- Verification Result [mysql @ app inst3606] $ SQL-P3606 -- query as follows, because we have not used binlog for full recovery, we cannot query the record robin @ localhost [(none)]> select * from tempdb. tb; + ------ + --------- + | id | val | + ------ + --------- + | 1 | fullbak | 2 | Incbak | + ------ + --------- +

4. Summary
A. Incremental backup is based on incremental or full backup.
B. The basis of Incremental backup is that the InnoDB engine uses the LSN mechanism. Non-InnoDB engines do not provide incremental backup, and each time they are full backups.
C. roll back the committed transactions and uncommitted transactions during Incremental Backup recovery.
D. Incremental Backup recovery should be replicated one by one based on the backup. You must use the -- apply-log -- redo-only option.
E. The -- redo-only option is not required for the last Incremental backup.
F. If you want to perform full recovery or point-in-time recovery, binlog should be used.