The principle analysis of xtrabackup in MySQL

The official xtrabackup is

Http://www.percona.com/software/percona-xtrabackup.

Xtrabackup contains two main tools, namely Xtrabackup and Innobackupex, which are distinguished by the following:

1 Xtrabackup can only back up the tables of the InnoDB and xtradb two engines, but not the tables of the MyISAM engine; 2 Innobackupex is a Perl script that encapsulates Xtrabackup, supporting the simultaneous backup of InnoDB and MyISAM, However, you need to add a global read lock to the MyISAM backup. And there's the MyISAM. Incremental backups are not supported.

Schematic diagram of the backup process for the Innobackupex tool

, when the backup started

1 First will start a xtrabackup_log background detection process, real-time detection of the changes in MySQL redo, once the discovery Redo has a new log write, immediately writes the log to the log file Xtrabackup_log 2 Copy the InnoDB data file and the system Tablespace file idbdata1 to the corresponding place with the default timestamp as the backup directory 3 after the replication finishes, execute the Flush table with the read lock action 4 copy. frm. myd. myi file 5 And at this moment get binary log position 6 to unlock the table unlock Tables7 stop Xtrabackup_log process

The process of full-Library recovery

This phase starts the Xtrabackup embedded InnoDB instance, plays back the Xtrabackup log Xtrabackup_log, applies the committed transaction information changes to the INNODB data or tablespace, and rolls out the uncommitted transactions

Incremental backup

An incremental backup is primarily done by copying pages with changes in the InnoDB (that is, LSN is greater than the LSN number in xtrabackup_checkpoints). Incremental backups are based on a fully-prepared, first-time incremental backup is based on the last full-time, after which each increment is based on the last increment, culminating in the increase in consistency, doubling the process, and the full preparation is similar, the difference lies in the second step

Recovery of incremental backups

Similar to a full-library restore, it also takes two steps

1 Recovery of data files divided into 3 parts full backup incremental and Xtrabackup_log

2 Rollback of uncommitted transactions

=================================================================================

Use cases of Innobackupex

RPM-UHV http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpmyum-y Install Percona-xtrabackup

1 Creating a backup user

MySQL>Grant reload,lock tables,replicationon*. *  to ' Dbbak '@'localhost'by'bk2016' ; MySQL > Privileges

Make the database fully prepared

- /Data//data/Dbbak

Use the following parameters for a full-library backup

[Email protected]_03 dbbak]#  innobackupex--defaults-file=/etc/my.cnf--user=dbbak--password=bk2016--socket=/ Data/3306/tmp/mysql.sock  /data/dbbak/

xtrabackup:stopping log copying thread.
.160204 00:36:20 >> Log scanned up to (1095197210)

160204 00:36:20 executing UNLOCK TABLES
160204 00:36:20 All Tables Unlocked
160204 00:36:20 Backup created in directory '/data/dbbak//2016-02-04_00-35-36 '
MySQL binlog position:filename ' mysql-bin.000011 ', Position ' 1338619 '
160204 00:36:20 [xx] Writing backup-my.cnf
160204 00:36:20 [xx] ... done
160204 00:36:20 [xx] Writing Xtrabackup_info
160204 00:36:20 [xx] ... done
Xtrabackup:transaction Log of LSN (1095197210) to (1095197210) was copied.
160204 00:36:20 completed ok! indicates a successful backup

View the corresponding generated files

[[email protected]_03 dbbak]# ll 2016-02-04_00-35-36/total 1048648-rw-r-----. 1 root root 387 Feb 4 00:36 backup-my.cnf-rw-r-----. 1 root root 1073741824 Feb 4 00:35 ibdata1drwx------. 2 root root 4096 Feb 4 00:36 iotdrwx------. 2 root root 12288 Feb 4 00:36 iot2drwx------. 2 root root 4096 Feb 4 00:36 iot3drwx------. 2 root root 4096 Feb 4 00:36 lsndrwx------. 2 root root 4096 Feb 4 00:36 mysqldrwx------. 2 root root 4096 Feb 4 00:36 performance_schemadrwx------. 2 root root 4096 Feb 4 00:36 sakiladrwx------. 2 root root 4096 Feb 4 00:36 sbtestdrwx------. 2 root root 4096 Feb 4 00:36 testdrwx------. 2 root root 4096 Feb 4 00:36 xtrabackup0219-rw-r-----. 1 root root 4 00:36 xtrabackup_binlog_info-rw-r-----. 1 root root 119 Feb 4 00:36 xtrabackup_checkpoints-rw-r-----. 1 root root 539 Feb 4 00:36 xtrabackup_info-rw-r-----. 1 root root 2560 Feb 4 00:36 XTRABACKUP_LOGFIle 

Several documents to be aware of

[Email protected]_03 dbbak]# cat 2016-02-04_00-35-36/xtrabackup_checkpoints backup_type = full-backuped # # #全备from_lsn = 0TO_LSN = 1095197210last_lsn = 1095197210  # # # # #LSN号compact = 0recover_binlog_info = 0[[email protected]_03 dbbak]# C At 2016-02-04_00-35-36/xtrabackup_binlog_info mysql-bin.000011    1338619

Delete a database for full-Library recovery

MySQL>dropdatabase affected rows (7.93 sec)

Close the database

[Email protected]_03 dbbak]#/etc/init.d/mysqld stopshutting down MySQL ..... success! [Email protected]_03 dbbak]# Mv/data/3306/data/data/3306/data_bak
[Email protected]_03 dbbak]# Mkdir/data/3306/data

Recovery

[[email protected]_03 dbbak]# Innobackupex--apply-log/data/dbbak/2016-02-04_00-35-36/160204 00:56:47           Innobackupex:starting the Apply-log operationimportant:please check that the Apply-log run completes successfully. At the end of a successful apply-log run Innobackupex prints "completed ok!". Innobackupex version 2.3.3 based on MySQL server 5.6.24 Linux (x86_64) (revision id:525ca7d) XTRABACKUP:CD To/data/dbbak /2016-02-04_00-35-36/xtrabackup:this target seems to is not prepared yet.xtrabackup:xtrabackup_logfile detected:size=2 097152, start_lsn= (1095197210) xtrabackup:using The following InnoDB configuration for Recovery:xtrabackup:innodb_data _home_dir =./xtrabackup:innodb_data_file_path = Ibdata1:1g:autoextendxtrabackup:innodb_log_group_home_dir =./xtrab Ackup:innodb_log_files_in_group = 1xtrabackup:innodb_log_file_size = 2097152xtrabackup:using The following InnoDB C Onfiguration for recovery:xtrabackup:innodb_data_home_dir =./xtrabAckup:innodb_data_file_path = Ibdata1:1g:autoextendxtrabackup:innodb_log_group_home_dir =./xtrabackup:innodb_log _files_in_group = 1xtrabackup:innodb_log_file_size = 2097152xtrabackup:starting InnoDB instance for Recovery.xtrabacku P:using 104857600 bytes for buffer pool (set by--use-memory parameter) Innodb:using Atomics to ref count buffer pool Pag Esinnodb:the InnoDB Memory Heap is disabledinnodb:mutexes and rw_locks use GCC atomic builtinsinnodb:memory barrier are  Not usedinnodb:compressed tables use zlib 1.2.3innodb:using CPU crc32 instructionsinnodb:initializing buffer pool, size  = 100.0minnodb:completed initialization of buffer poolinnodb:highest supported file format is Barracuda.InnoDB:The log Sequence numbers 532847032 and 532847032 in Ibdata files does not match the log sequence number 1095197210 in the Ib_logfil Es! Innodb:database is not shutdown normally! Innodb:starting crash recovery. innodb:reading tablespace information from the. ibd files ... InnoDB: Restoring possible Half-written data pages innodb:from the Doublewrite buffer ... innodb:128 rollback segment (s) is active. Innodb:waiting for purge to startinnodb:5.6.24 started; Log sequence number 1095197210xtrabackup:last MySQL binlog file position 1337268, file name mysql-bin.000011xtrabackup:s tarting shutdown with innodb_fast_shutdown = 1innodb:fts optimize thread exiting. Innodb:starting shutdown ... Innodb:shutdown completed; Log sequence number 1095198530xtrabackup:using The following InnoDB configuration for Recovery:xtrabackup:innodb_data_ Home_dir =./xtrabackup:innodb_data_file_path = Ibdata1:1g:autoextendxtrabackup:innodb_log_group_home_dir =./xtraba Ckup:innodb_log_files_in_group = 3xtrabackup:innodb_log_file_size = 1073741824innodb:using Atomics to ref count BUF  Fer pool pagesinnodb:the InnoDB memory heap is disabledinnodb:mutexes and rw_locks use GCC atomic builtinsinnodb:memory Barrier isn't usedinnodb:compressed tables use zlib 1.2.3innodb:uSing CPU crc32 instructionsinnodb:initializing buffer pool, size = 100.0minnodb:completed initialization of buffer Pooli nnodb:setting log file./ib_logfile101 size to 1024x768 mbinnodb:progress in mb:100 1000Inn  odb:setting log file./ib_logfile1 size to 1024x768 mbinnodb:progress in mb:100 1000InnoDB: Setting log file./ib_logfile2 size to 1024x768 mbinnodb:progress in mb:100.  aming log file/ib_logfile101 to./ib_logfile0innodb:new log files created, Lsn=1095198530innodb:highest supported file Format is barracuda.innodb:128 rollback segment (s) is active. Innodb:waiting for purge to startinnodb:5.6.24 started; Log sequence number 1095198732xtrabackup:starting shutdown with innodb_fast_shutdown = 1innodb:fts optimize thread Exiti Ng. Innodb:starting shutdown ... Innodb:shutdown completed; Log sequence number 1095202631160204 00:57:31 completed ok!

The corresponding directory is the Innobackupex full backup to create their own directory

[Email protected]_03 dbbak]# Innobackupex--defaults-file=/etc/my.cnf--copy-back--rsync/data/dbbak/2016-02-04_ 00-35-36/

160204 01:08:39 [... done]
160204 01:08:39 [] Copying./IOT2/T_HASH1#P#P3.IBD to/data/3306/data/iot2/t_hash1#p#p3.ibd
160204 01:08:39 [... done]
160204 01:08:39 [] Copying./iot2/db.opt to/data/3306/data/iot2/db.opt
160204 01:08:39 [... done]
160204 01:08:39 [] Copying./xtrabackup0219/db.opt to/data/3306/data/xtrabackup0219/db.opt
160204 01:08:39 [... done]
160204 01:08:39 completed ok!

Change permissions

[Email protected]_03 tmp]# chown-r mysql.mysql/data/3306/data/

Start mysqld

[[Email protected]_03 tmp]#/etc/init.d/mysqld Start

[[email protected]_03 tmp]# MySQL-Uroot-penter password:welcome toThe MySQL Monitor. CommandsEnd  with;or\g.your MySQL Connection ID is 1Server Version:5.6. --LogMySQL Community Server (GPL) Copyright (c) -, -, Oracleand/orits affiliates. Allrights reserved. Oracle isA registered trademark ofOracle Corporationand/oritsaffiliates. Names may trademarks oftheir respectiveowners. Type'Help ;' or '\h'  forHelp. Type'\c'  toClear the Currentinput Statement.mysql>show databases;+--------------------+| Database           |+--------------------+|Information_schema||IoT||Iot2| # # #被删除的库|Iot3||Lsn||Mysql||Performance_schema||Sakila||Sbtest||Test||xtrabackup0219|+--------------------+ OneRowsinch Set(0.00Sec

Discovery data is already successfully restored

Fully prepared first

mysql> use xtrabackup0219;mysql> CREATE TABLE t1 (id int (5) primary key auto_increment,name varchar);

Innobackupex--defaults-file=/etc/my.cnf--user=dbbak--password=bk2016--socket=/data/3306/tmp/mysql.sock  / data/dbbak/

Incremental backup

# # # #往表里插入数据

mysql> INSERT INTO T1 Select 1, ' Love SQL ';
Query OK, 1 row affected (0.01 sec)
Records:1 duplicates:0 warnings:0

mysql> INSERT INTO T1 select 2, ' Love SQL ';
Query OK, 1 row Affected (0.00 sec)
Records:1 duplicates:0 warnings:0

mysql> INSERT INTO T1 select 3, ' Love SQL ';
Query OK, 1 row affected (0.01 sec)
Records:1 duplicates:0 warnings:0

Mysql> select * from T1;
+----+----------+
| ID | name |
+----+----------+
| 1 | Love SQL |
| 2 | Love SQL |
| 3 | Love SQL |
+----+----------+
3 Rows in Set (0.00 sec)

[Email protected]_03 dbbak]# innobackupex--defaults-file=/etc/my.cnf--user=dbbak--password=bk2016--socket=/data/ 3306/tmp/mysql.sock--incremental/data/dbbak/--incremental-basedir=/data/dbbak/2016-02-04_01-44-24/--parallel=2

[Email protected]_03 dbbak]# Du-sh *
1.5g2016-02-04_01-44-24
6.0m2016-02-04_01-46-48

[Email protected]_03 dbbak]# Cat 2016-02-04_01-46-48/xtrabackup_checkpoints
Backup_type = Incremental # # #说明是增量的
FROM_LSN = 1095215215
TO_LSN = 1095217565
LAST_LSN = 1095217565
Compact = 0
Recover_binlog_info = 0

Insert data at this time

mysql> INSERT INTO T1 Select 4, ' MySQL DBA ';
Query OK, 1 row affected (0.01 sec)
Records:1 duplicates:0 warnings:0

Incremental Backup 2

[Email protected]_03 dbbak]# innobackupex--defaults-file=/etc/my.cnf--user=dbbak--password=bk2016--socket=/data/ 3306/tmp/mysql.sock--incremental/data/dbbak/--incremental-basedir=/data/dbbak/2016-02-04_01-46-48/--parallel=2

[Email protected]_03 dbbak]# Du-sh *
1.5g2016-02-04_01-44-24
6.0m2016-02-04_01-46-48
5.9m2016-02-04_01-49-21

Recovery of incremental backups

Recovery of incremental backups requires 3 steps

1 Recovering a full backup

2 Restore an incremental backup to a full backup (incremental backup to start recovery to add the--redo-only parameter to the last incremental backup to remove--redo-only)

3 Restore the entire full backup, roll back uncommitted data

[Email protected]_03 dbbak]# Innobackupex--apply-log--redo-only/data/dbbak/2016-02-04_01-44-24/

xtrabackup:starting shutdown with Innodb_fast_shutdown = 1
Innodb:starting shutdown ...
Innodb:shutdown completed; Log sequence Number 1095209828
160204 01:27:09 completed ok!

Apply Delta 1 to a full backup

[Email protected]_03 dbbak]# Innobackupex--apply-log--redo-only/data/dbbak/2016-02-04_01-44-24/--incremental-dir =/data/dbbak/2016-02-04_01-46-48/

[Email protected]_03 dbbak]# Innobackupex--apply-log/data/dbbak/2016-02-04_01-44-24/--incremental-dir=/data/ dbbak/2016-02-04_01-49-21/

Take all of the full backups together and roll back the uncommitted data in one apply operation

[Email protected]_03 dbbak]# Innobackupex--apply-log/data/dbbak/2016-02-04_01-44-24/

Simulation test

mysql> drop table T1; Query OK, 0 rows affected (0.05 sec)

[Email protected]_03 dbbak]# rm-rf/data/3306/data/
[Email protected]_03 dbbak]# Mkdir/data/3306/data
[Email protected]_03 dbbak]# Innobackupex--defaults-file=/etc/my.cnf--copy-back--rsync/data/dbbak/2016-02-04_ 01-44-24/
[Email protected]_03 dbbak]# chown-r mysql.mysql/data/3306/data/
Log in to view

Mysql> select * from T1;
+----+-----------+
| ID | name |
+----+-----------+
| 1 | Love SQL |
| 2 | Love SQL |
| 3 | Love SQL |
| 4 | MySQL DBA |
+----+-----------+
4 rows in Set (0.05 sec)

Discovery data is already correct

After testing, recovery is still relatively simple.

The principle analysis of xtrabackup in MySQL