Shulou(Shulou.com)06/01 Report--

Integrated some data on the Internet, combined with my own understanding, and carried out experimental verification.

Understanding one:

1What is the last Xtrabackup?

Xtrabackup is a data backup tool for InnoDB, supports online hot backup (backup does not affect data read and write), and is a good substitute for commercial backup tool InnoDB Hotbackup.

Xtrabackup has two main tools: xtrabackup and innobackupex

(1) xtrabackup can only back up InnoDB and XtraDB data tables, but not MyISAM data tables

(2) innobackupex is modified with reference to InnoDB Hotbackup's innoback script. Innobackupex is a perl script package that encapsulates xtrabackup. It is mainly for the convenience of backing up the tables of both the InnoDB and MyISAM engines, but

You need to add a read lock when dealing with myisam. And added some options to use. For example, slave-info can record backup recovery, as some information needed by slave, according to this information, you can easily use backup to redo slave.

2What can Magi Xtrabackup do:

Online (hot) backup of the InnoDB and XtraDB tables of the entire library

Make an incremental backup (innodb only) based on the last full library backup of xtrabackup

Generate a backup as a stream, which can be saved directly to a remote machine (useful when the local hard disk is out of space)

The tools provided by the MySQL database itself do not support true incremental backups, and binary log recovery is a point-in-time (point-in-time) recovery rather than an incremental backup.

The Xtrabackup tool supports incremental backups of the InnoDB storage engine, which works as follows:

(1) first complete a full backup and record the LSN (Log Sequence Number) of the checkpoint at this time.

(2) during the process incremental backup, compare whether the LSN of each page in the tablespace is greater than the LSN of the last backup, and if so, back up the page and record the LSN of the current checkpoint.

First, find and record the last checkpoint ("last checkpoint LSN") in xtrabackup_checkpoints, and then start copying InnoDB's xtrabackup_checkpoints to xtrabackup_logfile from the location of LSN.

Next, start copying all the data text .ibd; do not stop copying the logfile until you have finished copying all the data files.

Because all data modifications are recorded in logfile, even if the data file is modified during backup, you can still maintain data consistency by parsing xtrabackup_logfile during recovery.

Understanding 2:

When backing up, the backup tool performs two main tasks to complete the backup:

① starts a log copy thread in the background. This thread monitors the InnoDB log file, and when the log file changes, the thread copies the changed data blocks to a file called xtrabackup_logfile under the backup directory. This

The operation is necessary because the backup may last for a long time, and all these log files are required from the beginning to the end of the backup when the database is restored.

② copies the InnoDB data file to the specified backup directory. This is not a simple copy. The backup tool opens and reads the file in a similar way to InnoDB by reading the file directory and copying it on a page basis.

Understanding 3:

The recovery process of backup includes two parts: recovery and restore.

Let's first take a look at the recovery of the full backup set.

During the backup of the InnoDB table or, more directly, the replication of the ibd data file, the database is in an inconsistent state, so roll back the transactions that have not yet been committed in the xtraback_logfile and roll forward the transactions that have been committed so that each number

According to the fact that the file is in a consistent state, this process is called "prepare".

If you are performing a backup from the library, you have nothing to roll back, just a simple apply redo log. In addition, in the prepare process, we can use the parameter-use-memory to increase the amount of memory used in the system so as to improve the recovery speed.

The recovery process for an incremental backup is similar to the restore of a full backup set, with a slight difference:

1) the recovery process requires the use of the full backup set and each incremental backup set, and the recovery of each backup set is the same as previously mentioned (rollforward and rollback), and then the redo log of each incremental backup set will be applied to the full backup set

2) for the new table generated after the complete standby set, there should be a special treatment so that the table will not be lost after recovery.

3), based on the full backup set, and then apply each incremental backup set sequentially.

Understanding 4:

The principle of full backup:

Make a backup of InnoDB,XtraBackup 's InnoDB-based crash-recovery feature.

Crash-recovery goes like this: InnoDB maintains a redo log, also known as transaction log, or transaction log, which contains all changes to InnoDB data. When InnoDB starts, it checks datafile and transaction log, then applies all committed transactions and rolls back all uncommitted transactions.

XtraBackup does not lock the table when backing up, but copies InnoDB data page by page. At the same time, XtraBackup has another thread monitoring transactions log. Once the log changes, it copies the changed log pages away (because the transactions log file size is limited, after it is full, it will start all over again, and the new data may overwrite the old data, so as soon as the change is made, it will be copied away immediately). Stop copying logfile after all data files have been copied.

XtraBackup uses its built-in InnoDB library to open InnoDB data files in read-write mode, and then reads and writes 1MB (1MB/16KB=64page) data every time, page by page, while using InnoDB's buf_page_is_corrupted () function to check whether the data on this page is normal, copy if normal, reread if abnormal, re-read up to 10 times, if it still fails, the backup fails to exit. The principle of copying transactions log is the same, except that every time you read and write 512KB (512KB/16KB=32page) data.

Because XtraBackup's built-in InnoDB library opens the file with rw, the user running XtraBackup must have read and write access to InnoDB's data file.

Because XtraBackup replicates large amounts of data from the file system, it uses posix_fadvise () as much as possible to tell OS not to cache read data (because it won't be reused) to improve performance. If you want to cache, big

A large amount of data will put a lot of pressure on the virtual memory of OS, and other processes (such as mysqld) are likely to be swap, which will cause problems. At the same time, XtraBackup pre-reads as much as possible when reading the data.

Because the table is not locked, the copied data is inconsistent, and the consistency of the data is achieved using crash-recovery at the time of recovery.

For MyISAM,XtraBackup, all tables are locked first, and then all files are copied.

Understanding 5:

The principle of incremental backup:

There is a file, xtrabackup_checkpoints, in both the full and incremental backup files that records the LSN of the checkpoint when the backup completes. When making a new incremental backup, XtraBackup compares whether the LSN of each page in the tablespace is greater than that of the last backup

LSN, if yes, back up the page and record the LSN of the current checkpoint.

Innobackupex-- apply-log: same as the-prepare parameter of xtrabackup. In general, data cannot be used for restore operations after the backup is completed, because the backed-up data may contain transactions that have not yet been committed or have been committed but have not been synchronized.

To the transaction in the data file. Therefore, the data file still handles the inconsistent state at this time. The role of apply-log is to keep data files in a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to data files.

1. Complete:

Innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=leyou / home/data/backup/full

Innobackupex-- defaults-file=/etc/mysql/my.cnf-- user=root-- password=leyou-- host=172.30.1.110 / home/data/backup/full (optional, backup remote host)

Root@debian:/home/data/backup/full# innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=leyou / home/data/backup/full

InnoDB Backup Utility v1.5.1 InnoDB Backup Utility xtrabackup; Copyright 2003, 2009 Innobase Oy

And Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under

The GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:

Http://www.percona.com/xb/p

170306 16:16:49 innobackupex: Executing a version check against the server...

170306 16:16:49 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/mysql/my.cnf;mysql_read_default_group=xtrabackup' as' root'

(using password: YES)

170306 16:16:49 innobackupex: Connected to MySQL server

170306 16:16:49 innobackupex: Done.

170306 16:16:49 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/mysql/my.cnf;mysql_read_default_group=xtrabackup' as' root'

(using password: YES)

170306 16:16:49 innobackupex: Connected to MySQL server

170306 16:16:49 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.

At the end of a successful backup run innobackupex

Prints "completed OK!".

Innobackupex: Using server version 5.5.47-0+deb7u1-log

Innobackupex: Created backup directory / home/data/backup/full/2017-03-06 16-16-49

170306 16:16:49 innobackupex: Starting ibbackup with command: xtrabackup-defaults-file= "/ etc/mysql/my.cnf"-defaults-group= "mysqld"-backup-suspend-at-end--

Target-dir=/home/data/backup/full/2017-03-06 16-16-49-innodb_log_file_size= "5242880"-innodb_data_file_path= "ibdata1:10M:autoextend"-- tmpdir=/tmp-- extra-

Lsndir='/tmp'

Innobackupex: Waiting for ibbackup (pid=10845) to suspend

Innobackupex: Suspend file'/ home/data/backup/full/2017-03-06, 16-16-49

Xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86 / 64) (revision id: 70f4be3)

Xtrabackup: uses posix_fadvise ().

Xtrabackup: cd to / var/lib/mysql

Xtrabackup: open files limit requested 0, set to 1024

Xtrabackup: using the following InnoDB configuration:

Xtrabackup: innodb_data_home_dir =. /

Xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend

Xtrabackup: innodb_log_group_home_dir =. /

Xtrabackup: innodb_log_files_in_group = 2

Xtrabackup: innodb_log_file_size = 5242880

> > log scanned up to (31407794483)

Xtrabackup: Generating a list of tablespaces

[01] Copying. / ibdata1 to / home/data/backup/full/2017-03-06 16-16-49/ibdata1

[01]... done

[01] Copying. / ZLECUBE/PO_Reverse_Box_Product_Relation.ibd to / home/data/backup/full/2017-03-06 16-16-49/ZLECUBE/PO_Reverse_Box_Product_Relation.ibd

[01]... done

[01] Copying. / ZLECUBE/PO_Box_RecvSend_Action_Item.ibd to / home/data/backup/full/2017-03-06 16-16-49/ZLECUBE/PO_Box_RecvSend_Action_Item.ibd

...

...

...

Xtrabackup: Creating suspend file'/ home/data/backup/full/2017-03-06 16-16-49 Xtrabackupply suspended2 'with pid' 10846'

170306 16:18:13 innobackupex: Continuing after ibbackup has suspended

170306 16:18:13 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG TABLES...

170306 16:18:13 innobackupex: Executing FLUSH TABLES WITH READ LOCK...

170306 16:18:13 innobackupex: All tables locked and flushed to disk

170306 16:18:13 innobackupex: Starting to backup non-InnoDB tables and files

Innobackupex: in subdirectories of'/ var/lib/mysql/'

Innobackupex: Backing up files'/ var/lib/mysql//ZLECUBE/*. {frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(234 files)

> > log scanned up to (31407794483)

Innobackupex: Backing up files'/ var/lib/mysql//mysql/*. {frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(72 files)

> > log scanned up to (31407794483)

Innobackupex: Backing up file'/ var/lib/mysql//test/bb.frm'

Innobackupex: Backing up file'/ var/lib/mysql//test/db.opt'

Innobackupex: Backing up file'/ var/lib/mysql//test/aa.frm'

Innobackupex: Backing up files'/ var/lib/mysql//performance_schema/*. {frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'(18 files)

170306 16:18:14 innobackupex: Finished backing up non-InnoDB tables and files

170306 16:18:14 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

170306 16:18:14 innobackupex: Waiting for log copying to finish

Xtrabackup: The latest check point (for incremental): '31407794483'

Xtrabackup: Stopping log copying thread.

Log scanned up to (31407794483)

Xtrabackup: Creating suspend file'/ home/data/backup/full/2017-03-06 16-16-49 Xtrabackupposed logically copided 'with pid' 10846'

Xtrabackup: Transaction log of lsn (31407794483) to (31407794483) was copied.

170306 16:18:15 innobackupex: All tables unlocked

Innobackupex: Backup created in directory'/ home/data/backup/full/2017-03-06 16-16-49'

170306 16:18:15 innobackupex: Connection to database server closed

170306 16:18:15 innobackupex: completed OK!

You can see that after backing up innodb's tables, non-innodb tables also start to be backed up and lock table at the same time.

Root@debian:/home/data/backup/full/2017-03-06-16-16-4 ls-l

Total 18508

-rw-r--r-- 1 root root 188 Mar 6 16:16 backup-my.cnf

-rw-r- 1 root root 18874368 Mar 6 16:16 ibdata1

Drwxr-xr-x 2 root root 4096 Mar 6 16:18 log

Drwxr-xr-x 2 root root 4096 Mar 6 16:18 mysql

Drwxr-xr-x 2 root root 4096 Mar 6 16:18 performance_schema

Drwx- 2 root root 4096 Mar 6 16:18 test

-rw-r- 1 root root 97 Mar 6 16:18 xtrabackup_checkpoints

-rw-r--r-- 1 root root 579 Mar 6 16:18 xtrabackup_info

-rw-r- 1 root root 2560 Mar 6 16:18 xtrabackup_logfile

Drwx- 2 root root 40960 Mar 6 16:18 ZLECUBE

(1) xtrabackup_checkpoints-backup type (such as full or incremental), backup status (such as prepared status) and LSN (log serial number) scope information

Each InnoDB page (usually 16k in size) contains a log sequence number, LSN. LSN is the system version number of the entire database system, and the LSN associated with each page can indicate how the page has changed recently.

(2) xtrabackup_binlog_info-the location of the binary log files currently in use by the mysql server and the binary log events up to the moment of backup.

(this file will not be available if the system does not open binlog)

(3) xtrabackup_binlog_pos_innodb-the current position of the binary log file and the binary log file used for the InnoDB or XtraDB table.

(4) xtrabackup_binary-the executable file of xtrabackup used in backup

(5) backup-my.cnf-configuration option information used for backup commands

When using innobackupex for backup, you can also use the-- no-timestamp option to prevent the command from automatically creating a directory named by time; in this way, the innobackupex command will create a BACKUP-DIR directory to store the backup data.

two。 Full recovery: prepare (prepare) a full backup

After that, we can copy the data files back to the corresponding directory according to the configuration in backup-my.cnf, or you can copy them yourself, but innobackupex will do it for us. In this case, it is a "post-preparation" for the InnoDB table.

Action, we call it "recovery", but for MyISAM table, because the backup is replicated by locking table, it is simply copied back at this time, and there is no need for apply log, which we call "restore".

Note: the use of restore and restore in this document also looks the same as other databases such as Oracle.

If you need to stop the library, you can back up the previously damaged database.

Cd / var/lib

Mv mysql mysql.old

Mkdir mysql

Chown mysql.mysql mysql

In general, after the backup is complete, the data cannot be used for restore operations because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file still handles the inconsistent state at this time.

No, no, no. The main function of "preparation" is to make the data file consistent by rolling back the uncommitted transaction and synchronizing the committed transaction to the data file.

The-- apply-log option of the innobakupex command can be used to achieve the above functionality. Such as the following command:

Innobackupex-defaults-file=/etc/mysql/my.cnf-apply-log / home/data/backup/full/2017-03-06 16-16-49

In the process of implementing preparation, innobackupex can also use the-- use-memory option to specify the amount of memory it can use, which is usually 100m by default. If enough memory is available, you can allocate more memory to the prepare process to improve its completion speed.

Innobackupex-defaults-file=/etc/mysql/my.cnf-apply-log-use-memory=5G / home/data/backup/full/2017-03-06 16-16-49

Innobackupex-defaults-file=/etc/mysql/my.cnf-apply-log-use-memory=5G-host=172.30.1.110 / home/data/backup/full/2017-03-06 16-16-49 (optional)

170213 17:14:06 innobackupex: Starting ibbackup with command: xtrabackup-- defaults-file= "/ home/data/backup/2017-02-13-17-11-22/backup-my.cnf"-- defaults-

Group= "mysqld"-- prepare-- target-dir=/home/data/backup/2017-02-13-17-11-22-- apply-log-only-- use-memory=5G

Xtrabackup: Starting InnoDB instance for recovery.

Xtrabackup: Using 5368709120 bytes for buffer pool (set by-use-memory parameter)

InnoDB: Using atomics to ref count buffer pool pages

InnoDB: The InnoDB memory heap is disabled

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Memory barrier is not used

InnoDB: Compressed tables use zlib 1.2.7

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, size = 5.0G

InnoDB: Completed initialization of buffer pool

InnoDB: Setting logfile. / ib_logfile101 size to 5 MB

InnoDB: Setting logfile. / ib_logfile1 size to 5 MB

InnoDB: Renaming logfile. / ib_logfile101 to. / ib_logfile0

InnoDB: New log files created, LSN=31405539763

InnoDB: Highest supported file format is Barracuda.

[notice (again)]

If you use binary log and don't use any hack of group commit

The binary log position seems to be:

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 31405539852

170213 17:14:07 innobackupex: completed OK!

Use copy-back to recover the data file.

Innobackupex-- defaults-file=/etc/mysql/my.cnf-- copy-back / home/data/backup/full/2017-03-06 / 15-31-05 /

You can see that innobackupex is copying the backed up files to the data directory of the database

The 12g file will be finished soon, about 1 minute.

Innobackupex: Copying'/ home/data/backup/full/2017-03-06, 15-31-05, ZLECUBE, BTC, orderground Baseball, info. Ibd'to'/ var/lib/mysql/ZLECUBE/BTC_Order_Base_Info.ibd'

Innobackupex: Copying'/ home/data/backup/full/2017-03-06 quarter 15-31-05 var/lib/mysql/ZLECUBE/Rbac_base_role.ibd' ZLECUBEG Rbacking baseband role.ibd'to'/

Innobackupex: Copying'/ home/data/backup/full/2017-03-06, 15-31-05, ZLECUBE, Rbacket, packs, powerhouses, area, provincetrees, group.frm'to

'/ var/lib/mysql/ZLECUBE/Rbac_p_power_area_province_group.frm'

Innobackupex: Starting to copy InnoDB log files

Innobackupex: in'/ home/data/backup/full/2017-03-06 / 15-31-05'

Innobackupex: back to original InnoDB log directory'/ var/lib/mysql'

Innobackupex: Copying'/ home/data/backup/full/2017-03-06 var/lib/mysql/ib_logfile0' 15-31-05 Universe ibaths logfile0' to'/

Innobackupex: Copying'/ home/data/backup/full/2017-03-06 to 15-31-05 Universe ibaths logfile1'to'/ var/lib/mysql/ib_logfile1'

Innobackupex: Finished copying back files.

170306 16:07:10 innobackupex: completed OK!

Finally, modify the permissions:

Chown-R mysql.mysql / var/lib/mysql

After starting the database, it will be fine.

3 perform a binary log incremental backup of the database changes after a full backup:

3.1 View complete:

Cat xtrabackup_checkpoints

Backup_type = full-prepared

From_lsn = 0

To_lsn = 31407798454

Last_lsn = 31407798454

Compact = 0

3.2 simulate database modification:

Mysql-uroot-pleyou test

Mysql > drop table aa

Query OK, 0 rows affected (0.00 sec)

Mysql > drop table bb

Query OK, 0 rows affected (0.00 sec)

3.3 incremental backup database:

Innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=leyou-incremental / home/data/backup/incr/-incremental-basedir=/home/data/backup/full/2017-

03-06 16-44-09 /-- parallel=2

Root@debian:~# innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=leyou-incremental / home/data/backup/incr/-incremental-

Basedir=/home/data/backup/full/2017-03-06 16-44-09 /-- parallel=2

170306 17:04:08 innobackupex: Executing a version check against the server...

170306 17:04:08 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/mysql/my.cnf;mysql_read_default_group=xtrabackup' as' root'

(using password: YES)

170306 17:04:08 innobackupex: Connected to MySQL server

170306 17:04:08 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.

At the end of a successful backup run innobackupex

Prints "completed OK!".

Innobackupex: Using server version 5.5.47-0+deb7u1-log

Innobackupex: Created backup directory / home/data/backup/incr/2017-03-06 / 17-04-08

170306 17:04:08 innobackupex: Starting ibbackup with command: xtrabackup-defaults-file= "/ etc/mysql/my.cnf"-defaults-group= "mysqld"-backup-suspend-at-end--

Target-dir=/home/data/backup/incr/2017-03-06-17-04-08-innodb_log_file_size= "5242880"-innodb_data_file_path= "ibdata1:10M:autoextend"-- tmpdir=/tmp-- extra-

Lsndir='/tmp'-- incremental-basedir='/home/data/backup/full/2017-03-06. 16-44-09. Parallel=2.

Innobackupex: Waiting for ibbackup (pid=21857) to suspend

Innobackupex: Suspend file'/ home/data/backup/incr/2017-03-06 December 17-04-08Universe xtrabackupply suspendedcards 2'

Xtrabackup version 2.2.13 based on MySQL server 5.6.24 Linux (x86 / 64) (revision id: 70f4be3)

Incremental backup from 31407798454 is enabled.

Xtrabackup: uses posix_fadvise ().

Xtrabackup: cd to / var/lib/mysql

Xtrabackup: open files limit requested 0, set to 1024

Xtrabackup: using the following InnoDB configuration:

Xtrabackup: innodb_data_home_dir =. /

Xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend

Xtrabackup: innodb_log_group_home_dir =. /

Xtrabackup: innodb_log_files_in_group = 2

Xtrabackup: innodb_log_file_size = 5242880

> log scanned up to (31407804447)

Xtrabackup: Generating a list of tablespaces

Xtrabackup: using the full scan for incremental backup

Xtrabackup: Starting 2 threads for parallel data files transfer

[01] Copying. / ibdata1 to / home/data/backup/incr/2017-03-06 / 17-04-08/ibdata1.delta

[02] Copying. / ZLECUBE/GA_GoodsArea_Info.ibd to / home/data/backup/incr/2017-03-06 / 17-04-08/ZLECUBE/GA_GoodsArea_Info.ibd.delta

[02]... done

[01]... done

[02] Copying. / ZLECUBE/SYS_Access_Authority_Info.ibd to / home/data/backup/incr/2017-03-06 / 17-04-08/ZLECUBE/SYS_Access_Authority_Info.ibd.delta

[02]... done

Xtrabackup: Creating suspend file'/ home/data/backup/incr/2017-03-06 December 17-04-08 Universe xtrabackupposed logically copided 'with pid' 21858'

Xtrabackup: Transaction log of lsn (31407804447) to (31407804447) was copied.

170306 17:04:33 innobackupex: All tables unlocked

...

...

...

Innobackupex: Backup created in directory'/ home/data/backup/incr/2017-03-06 / 17-04-08'

170306 17:04:33 innobackupex: Connection to database server closed

170306 17:04:33 innobackupex: completed OK!

Root@debian:~#

Du-m-s *

1 backup-my.cnf

4 ibdata1.delta

1 ibdata1.meta

1 log

2 mysql

1 performance_schema

1 test

1 xtrabackup_checkpoints

1 xtrabackup_info

1 xtrabackup_logfile

8 ZLECUBE

As you can see, the amount of data in incremental backups is very small.

Root@debian:/home/data/backup/incr/2017-03-06 17-04-0 cat xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 31407798454

To_lsn = 31407804447

Last_lsn = 31407804447

Compact = 0

3.4 modify the database and then create incremental backup 2 (this time based on the last incremental backup)

Mysql > create database test2

Query OK, 1 row affected (0.00 sec)

Innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=leyou-incremental / home/data/backup/incr/-incremental-basedir=/home/data/backup/incr/2017-

03-06 17-04-08 /-- parallel=2

Xtrabackup: Creating suspend file'/ home/data/backup/incr/2017-03-06 December 17-14-16 xtrabackupply logically copided 'with pid' 23367'

Xtrabackup: Transaction log of lsn (31407804447) to (31407804447) was copied.

170306 17:14:25 innobackupex: All tables unlocked

Innobackupex: Backup created in directory'/ home/data/backup/incr/2017-03-06 / 17-14-16'

170306 17:14:25 innobackupex: Connection to database server closed

170306 17:14:25 innobackupex: completed OK!

You can also delete the second backup based on / home/data/backup/incr/2017-03-06 backup 17-04-08 / re-backup

Root@debian:/home/data/backup/incr# ls-lrt

Total 8

Drwxr-xr-x 7 root root 4096 Mar 6 17:04 2017-03-06 October 17-04-08

Drwxr-xr-x 8 root root 4096 Mar 6 17:14 2017-03-06 October 17-14-16

Root@debian:/home/data/backup/incr#

Root@debian:/home/data/backup/incr#

Root@debian:/home/data/backup/incr# rm-rf 2017-03-06 17-14-16

Root@debian:/home/data/backup/incr#

Mysql > drop database test2

Query OK, 0 rows affected (0.00 sec)

Mysql > drop database test

Query OK, 0 rows affected (0.00 sec)

Mysql >\ Q

Root@debian:/home/data/backup/incr/2017-03-06, 17-17-5 cat xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 31407804447

To_lsn = 31407804447

Last_lsn = 31407804447

Compact = 0

3.5 incremental backup recovery

The recovery of incremental backup consists of three steps.

* restore a full backup

* restore an incremental backup to a full backup (add the-redo-only parameter for the incremental backup at the beginning, and remove the-redo-only parameter for the last incremental backup)

* restore the overall full backup and roll back the uncommitted data

Restore a full backup (be sure to add the-redo-only parameter here, which means that only committed transaction data in the xtrabackup log will be applied, and uncommitted data will not be rolled back)

Innobackupex-defaults-file=/etc/mysql/my.cnf-apply-log-redo-only / home/data/backup/full/2017-03-06 16-44-09 /

[notice (again)]

If you use binary log and don't use any hack of group commit

The binary log position seems to be:

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 31407802010

170306 17:30:37 innobackupex: completed OK!

Apply incremental backup 1 to full backup

Innobackupex-- defaults-file=/etc/mysql/my.cnf-- apply-log-- redo-only / home/data/backup/full/2017-03-06pm 16-44-09 /-incremental-dir=/home/data/backup/incr/2017-03-06pm 17-04-08It marywise bygone eventual name. FRM'

Innobackupex: Copying'/ home/data/backup/incr/2017-03-06 performances 17-04-08 dynamic performanceschemaAccording to eventsperformances waitsprints. Frm'to'/ home/data/backup/full/2017-03-06 performances 16-44-09 Universe performance schemaabouts.

Innobackupex: Copying'/ home/data/backup/incr/2017-03-06 performances 17-04-08 substitute performance schemaPlacement fileholders instances.frm'to'/ home/data/backup/full/2017-03-06 performances 16-44-09According to performance instances.frm'

Innobackupex: Copying'/ home/data/backup/incr/2017-03-06 transactions 17-04-08 swap performance schemaAccording to mutextual instances.frm'to'/ home/data/backup/full/2017-03-06 performances 16-44-09Cances.frm'

170306 17:33:13 innobackupex: completed OK!

Apply incremental backup 2 to a full backup (note that you need to remove the-redo-only parameter when restoring the last incremental backup and roll back uncommitted data in the xtrabackup log)

Innobackupex-- defaults-file=/etc/mysql/my.cnf-- apply-log / home/data/backup/full/2017-03-06 / 16-44-09 /-incremental-dir=/home/data/backup/incr/2017-03-06 / 17-54 /

Innobackupex: Copying'/ home/data/backup/incr/2017-03-06, 17-17-54; performanceschemaBash, fileholders, pictures, bysides, eventual performances, name.frm'to'/ home/data/backup/full/2017-03-06, 16-44-

09Uniformance schemaCompact fileworthy performances ypast bylaws eventual performancename.frm'

Innobackupex: Copying'/ home/data/backup/incr/2017-03-06 December 17-17-54 performance schemaAccording to eventsperformancewaitswriting .frm'to'/ home/data/backup/full/2017-03-06 December 16-44-

09Uniformance performance schemaAccording to eventsperformancewaitsplans.

Innobackupex: Copying'/ home/data/backup/incr/2017-03-06 stories 17-17-54 performancecharts schemaCharpy fileholders instances.frm'to'/ home/data/backup/full/2017-03-06 cycles 16-44-

09Uniformance performance schemaCompact fileworthy instances.frm'

Innobackupex: Copying'/ home/data/backup/incr/2017-03-06 examples 17-17-54 performance schemaUniverse instances. FRM'to'/ home/data/backup/full/2017-03-06 cycles 16-44-

09Universe performancedriven schemaCompact mutexcoded instances.frm'

170306 17:37:42 innobackupex: completed OK!

At this time, the two incremental backups are actually merged into full backup, and you only need to use full backup to restore.

Simulate data failure [delete the data directory of the database, perform the following command restore]

Innobackupex-- defaults-file=/etc/mysql/my.cnf-- copy-back / home/data/backup/full/2017-03-06 / 16-44-09 /

Innobackupex: Starting to copy files in'/ home/data/backup/full/2017-03-06 / 16-44-09'

Innobackupex: back to original data directory'/ var/lib/mysql'

Innobackupex: Copying'/ home/data/backup/full/2017-03-06 inch 16-44-09 Universe xtrabackupply info'to'/ var/lib/mysql/xtrabackup_info'

Innobackupex: Creating directory'/ var/lib/mysql/ZLECUBE'

Innobackupex: Copying'/ home/data/backup/full/2017-03-06 to 16-44-09 Universe ZLECUBEGR backing baseband roleball privileged Backupship 20160625.frm' Backupship

'/ var/lib/mysql/ZLECUBE/Rbac_base_role_privilege_Backup_20160625

...

...

...

Ce.frm'

Innobackupex: Copying'/ home/data/backup/full/2017-03-06 December 16-44-09

'/ var/lib/mysql/performance_schema/file_summary_by_event_name.frm'

Innobackupex: Copying'/ home/data/backup/full/2017-03-06 million 16-44-09

'/ var/lib/mysql/performance_schema/events_waits_history.frm'

Innobackupex: Copying'/ home/data/backup/full/2017-03-06 to 16-44-09 Universe performanceworthy schemaPlacement fileholders instances.frm'to'/ var/lib/mysql/performance_schema/file_instances.frm'

Innobackupex: Copying'/ home/data/backup/full/2017-03-06 to 16-44-09 Universe performancecoded schemaAccording to mutextual instances.frm'to'/ var/lib/mysql/performance_schema/mutex_instances.frm'

Innobackupex: Starting to copy InnoDB system tablespace

Innobackupex: in'/ home/data/backup/full/2017-03-06 / 16-44-09'

Innobackupex: back to original InnoDB data directory'/ var/lib/mysql'

Innobackupex: Copying'/ home/data/backup/full/2017-03-06 to 16-44-09 Universe ibdata 1'to'/ var/lib/mysql/ibdata1'

Innobackupex: Starting to copy InnoDB undo tablespaces

Innobackupex: in'/ home/data/backup/full/2017-03-06 / 16-44-09'

Innobackupex: back to'/ var/lib/mysql'

Innobackupex: Starting to copy InnoDB log files

Innobackupex: in'/ home/data/backup/full/2017-03-06 / 16-44-09'

Innobackupex: back to original InnoDB log directory'/ var/lib/mysql'

Innobackupex: Copying'/ home/data/backup/full/2017-03-06 to 16-44-09 Universe ibaths logfile0' to'/ var/lib/mysql/ib_logfile0'

Innobackupex: Copying'/ home/data/backup/full/2017-03-06 to 16-44-09 Universe ibacclogfile1'to'/ var/lib/mysql/ib_logfile1'

Innobackupex: Finished copying back files.

170307 15:11:51 innobackupex: completed OK!

Root@debian:/var/lib#

Chown-R mysql:mysql / var/lib/mysql/

Root@debian:/var/lib# / etc/init.d/mysql restart

Root@debian:/var/lib# mysql-uroot-pleyou

Mysql >

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | ZLECUBE |

| | log |

| | mysql |

| | performance_schema |

| | test |

+-+

6 rows in set (0.00 sec)

Mysql > use test

Database changed

Mysql > show tables

Empty set (0.00 sec)

Mysql >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.