xtrabackup分析
innobackupex工作流程：
start xtrabackup_log流程：
首先向数据库注册线程拷贝redo log，另一个线程拷贝datafile。拷贝的redo log和datafile放到xtrabackup_log，这个过程数据库不锁定可写入，实现了热备份。因为xtrabackup不拷贝表结构定义信息，所以需要innobackupex来完成锁、拷贝表结构定义信息（这个过程非常快）。最后拿redo log和undo log协作在datafile把数据恢复。
1.使用yum安装xtrabackup:
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum install epel-release –y
yum install libev –y
yum install percona-xtrabackup
2.全量备份并将屏幕输出重定向到/tmp/xtrabackup.log方便以后分析：
[root@bogon ~]# innobackupex --defaults-file=/usr/local/mysql/my3317.cnf /tmp/ 2>/tmp/xtrabackup.log
开始拷贝共享表空间ibdata1文件：
xtrabackup: Generating a list of tablespaces
160723 16:05:25 [01] Copying ./ibdata1 to /tmp//2016-07-23_16-05-24/ibdata1
160723 16:05:25 >> log scanned up to (1662885)
160723 16:05:26 >> log scanned up to (1662885)
160723 16:05:27 >> log scanned up to (1662885)
160723 16:05:28 >> log scanned up to (1662885)
160723 16:05:29 >> log scanned up to (1662885)
160723 16:05:30 >> log scanned up to (1662885)
160723 16:05:31 >> log scanned up to (1662885)
160723 16:05:32 >> log scanned up to (1662885)
160723 16:05:33 >> log scanned up to (1662885)
开始拷贝独立表空间文件（按照每个page16k）：
160723 16:06:00 >> log scanned up to (1662885) 160723 16:06:00 [01] Copying ./mysql/innodb_index_stats.ibd to /tmp//2016-07-23_16-05-24/mysql/innodb_index_stats.ibd 160723 16:06:00 [01] ...done 160723 16:06:00 [01] Copying ./mysql/slave_relay_log_info.ibd to /tmp//2016-07-23_16-05-24/mysql/slave_relay_log_info.ibd 160723 16:06:00 [01] ...done 160723 16:06:00 [01] Copying ./mysql/slave_worker_info.ibd to /tmp//2016-07-23_16-05-24/mysql/slave_worker_info.ibd 160723 16:06:01 [01] ...done 160723 16:06:01 [01] Copying ./mysql/innodb_table_stats.ibd to /tmp//2016-07-23_16-05-24/mysql/innodb_table_stats.ibd 160723 16:06:01 [01] ...done 160723 16:06:01 [01] Copying ./mysql/slave_master_info.ibd to /tmp//2016-07-23_16-05-24/mysql/slave_master_info.ibd 160723 16:06:01 [01] ...done 160723 16:06:01 [01] Copying ./zhangshuo/zhangshuo.ibd to /tmp//2016-07-23_16-05-24/zhangshuo/zhangshuo.ibd 160723 16:06:01 [01] ...done
执行停止写操作并下发flush table with read lock，开始拷贝非innodb表。
160723 16:06:02 Executing FLUSH NO_WRITE_TO_BINLOG TABLES... 160723 16:06:02 Executing FLUSH TABLES WITH READ LOCK... 160723 16:06:02 Starting to backup non-InnoDB tables and files
接着拷贝表结构定义信息（注意这时候已经锁表）：
160723 16:06:04 [01] Copying ./performance_schema/events_waits_history_long.frm to /tmp//2016-07-23_16-05-24/performance_schema/events_waits_history_long.frm 160723 16:06:04 [01] ...done 160723 16:06:04 [01] Copying ./zhangshuo/zhangshuo.frm to /tmp//2016-07-23_16-05-24/zhangshuo/zhangshuo.frm 160723 16:06:04 [01] ...done 160723 16:06:04 [01] Copying ./zhangshuo/db.opt to /tmp//2016-07-23_16-05-24/zhangshuo/db.opt 160723 16:06:04 [01] ...done 160723 16:06:04 Finished backing up non-InnoDB tables and files
最后执行解锁并做show master status操作，注意检查是否completed OK!
160723 16:06:04 [00] Writing xtrabackup_binlog_info 160723 16:06:04 [00] ...done 160723 16:06:04 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '1662885' xtrabackup: Stopping log copying thread. .160723 16:06:04 >> log scanned up to (1662885) 160723 16:06:04 Executing UNLOCK TABLES 160723 16:06:04 All tables unlocked 160723 16:06:04 Backup created in directory '/tmp//2016-07-23_16-05-24' MySQL binlog position: filename 'mysql-bin.000006', position '1234' 160723 16:06:04 [00] Writing backup-my.cnf 160723 16:06:04 [00] ...done 160723 16:06:04 [00] Writing xtrabackup_info 160723 16:06:04 [00] ...done xtrabackup: Transaction log of lsn (1662885) to (1662885) was copied. 160723 16:06:05 completed OK!
3.进入备份目录查看：
xtrabackup默认备份目录名字使用系统时间戳，可以使用--no-timestamp参数跳过。
[root@bogon tmp]# cd 2016-07-23_16-05-24/ [root@bogon 2016-07-23_16-05-24]# ls -lh 总用量 1.1G -rw-r-----. 1 root root 387 7月 23 16:06 backup-my.cnf -rw-r-----. 1 root root 1.0G 7月 23 16:05 ibdata1 drwx------. 2 root root 4.0K 7月 23 16:06 mysql drwx------. 2 root root 4.0K 7月 23 16:06 performance_schema drwx------. 2 root root 4.0K 7月 23 16:06 test -rw-r-----. 1 root root 22 7月 23 16:06 xtrabackup_binlog_info -rw-r-----. 1 root root 113 7月 23 16:06 xtrabackup_checkpoints -rw-r-----. 1 root root 479 7月 23 16:06 xtrabackup_info -rw-r-----. 1 root root 2.5K 7月 23 16:06 xtrabackup_logfile drwx------. 2 root root 4.0K 7月 23 16:06 zhangshuo
backup-my.cnf是对数据库配置信息的核心定义：
# This MySQL options file was generated by innobackupex. # The MySQL server [mysqld] innodb_checksum_algorithm=innodb innodb_log_checksum_algorithm=innodb innodb_data_file_path=ibdata1:1G:autoextend innodb_log_files_in_group=3 innodb_log_file_size=524288000 innodb_fast_checksum=false innodb_page_size=16384 innodb_log_block_size=512 innodb_undo_directory=. innodb_undo_tablespaces=0
xtrabackup_info是对整个备份的清楚描述：
uuid = 4db914fa-50ac-11e6-95ec-000c29111e54 name = tool_name = innobackupex tool_command = --defaults-file=/usr/local/mysql/my3317.cnf /tmp/ tool_version = 2.3.5 ibbackup_version = 2.3.5 server_version = 5.6.30-log start_time = 2016-07-23 16:05:24 end_time = 2016-07-23 16:06:04 lock_time = 0 binlog_pos = filename 'mysql-bin.000006', position '1234' innodb_from_lsn = 0 innodb_to_lsn = 1662885 partial = N incremental = N format = file compact = N compressed = N encrypted = N ~
xtrabackup_checkpoints记录了LSN号和备份类型：
backup_type = full-backuped from_lsn = 0 to_lsn = 1662885 last_lsn = 1662885 compact = 0 recover_binlog_info = 0
xtrabackup_binlog_info记录了binlog位置方便做主从：
mysql-bin.000006 1234
xtrabackup_logfile是redo log结构信息。
4.将备份恢复出来：
[root@bogon 2016-07-23_16-05-24]# innobackupex --apply-log /tmp/2016-07-23_16-05-24/
查看恢复后的内容：
[root@bogon 2016-07-23_16-05-24]# ls -lh 总用量 2.5G -rw-r-----. 1 root root 387 7月 23 16:06 backup-my.cnf -rw-r-----. 1 root root 1.0G 7月 23 17:18 ibdata1 -rw-r--r--. 1 root root 500M 7月 23 17:18 ib_logfile0 -rw-r--r--. 1 root root 500M 7月 23 17:18 ib_logfile1 -rw-r--r--. 1 root root 500M 7月 23 17:18 ib_logfile2 drwx------. 2 root root 4.0K 7月 23 16:06 mysql drwx------. 2 root root 4.0K 7月 23 16:06 performance_schema drwx------. 2 root root 4.0K 7月 23 16:06 test -rw-r-----. 1 root root 22 7月 23 16:06 xtrabackup_binlog_info -rw-r--r--. 1 root root 22 7月 23 17:18 xtrabackup_binlog_pos_innodb -rw-r-----. 1 root root 113 7月 23 17:18 xtrabackup_checkpoints -rw-r-----. 1 root root 479 7月 23 16:06 xtrabackup_info -rw-r-----. 1 root root 2.0M 7月 23 17:07 xtrabackup_logfile drwx------. 2 root root 4.0K 7月 23 16:06 zhangshuo
比恢复之前多了4个文件：ib_logfile0、ib_logfile1、ib_logfile2、xtrabackup_binlog_pos_innodb。
[root@bogon 2016-07-23_16-05-24]# more xtrabackup_binlog_pos_innodb mysql-bin.000006 1234
知道ib_logfile内有redo log，为什么xtrabackup_binlog_pos_innodb和xtrabackup_binlog_info一样？因为xtrabackup_binlog_pos_innodb来自redo log。
思考：
如果这俩参数位置不同应该以哪个为准呢？
xtrabackup_binlog_pos_innodb小于xtrabackup_binlog_info：这个区间是没有数据写入的。
xtrabackup_binlog_pos_innodb大于xtrabackup_binlog_info：是因为在备份完成时做完show master status、Executing UNLOCK TABLES后，线程不一致导致数据又写进去。这时候可选择位置小的，然后解决1062、1032错误。GTID环境可以直接changge。
数据恢复：(执行copy-back前，确保mysql服务器处于可启动状态）
[root@bogon data]# innobackupex --defaults-file=/usr/local/mysql/my3317.cnf --copy-back /tmp/2016-07-23_16-05-24/
160723 17:58:48 innobackupex: Starting the copy-back operation
160723 18:01:24 [01] Copying ./zhangshuo/zhangshuo.ibd to /data/mysql/mysql3317/data/zhangshuo/zhangshuo.ibd
160723 18:01:24 [01] ...done
160723 18:01:24 [01] Copying ./zhangshuo/zhangshuo.frm to /data/mysql/mysql3317/data/zhangshuo/zhangshuo.frm
160723 18:01:24 [01] ...done
160723 18:01:24 [01] Copying ./zhangshuo/db.opt to /data/mysql/mysql3317/data/zhangshuo/db.opt
160723 18:01:24 [01] ...done
160723 18:01:24 completed OK!
看到completedOK，进入实例检查数据成功恢复：
root@localhost:mysql3317.sock 18:06:36 [(none)]>show databases; +------------------------------+ | Database | +------------------------------+ | information_schema | | #mysql50#2016-07-23_17-49-02 | | mysql | | performance_schema | | test | | zhangshuo | +------------------------------+ 6 rows in set (0.00 sec) root@localhost:mysql3317.sock 18:06:40 [(none)]>use zhangshuo Database changed root@localhost:mysql3317.sock 18:08:02 [zhangshuo]>select * from zhangshuo; +------+-----------+ | id | name | +------+-----------+ | 1 | zhangshuo | +------+-----------+ 1 row in set (0.04 sec)
5.使用xtrabackup完成增量备份，增量备份流程：
在增备之前向数据库写入第二条数据：
root@localhost:mysql3317.sock 12:08:07 [zhangshuo]>select * from zhangshuo; +------+-----------+ | id | name | +------+-----------+ | 1 | zhangshuo | | 2 | zhangjie | +------+-----------+
基于全备目录的第1次增量备份：
[root@bogon tmp]# innobackupex --defaults-file=/usr/local/mysql/my3317.cnf --no-timestamp --incremental --incremental-basedir=/tmp/fullback/2016-07-24_16-10-38/ /tmp/incremental/incr1 2>/tmp/incrback1.log
对所有innodb表有变化的进行拷贝：
160724 16:18:12 >> log scanned up to (1673871)
160724 16:18:13 [01] ...done
160724 16:18:13 [01] Copying ./mysql/innodb_index_stats.ibd to /tmp/incremental/incr1/mysql/innodb_index_stats.ibd.delta
160724 16:18:13 [01] ...done
160724 16:18:13 [01] Copying ./mysql/slave_relay_log_info.ibd to /tmp/incremental/incr1/mysql/slave_relay_log_info.ibd.delta
160724 16:18:13 [01] ...done
160724 16:18:13 [01] Copying ./mysql/slave_worker_info.ibd to /tmp/incremental/incr1/mysql/slave_worker_info.ibd.delta
160724 16:18:13 [01] ...done
160724 16:18:13 [01] Copying ./mysql/innodb_table_stats.ibd to /tmp/incremental/incr1/mysql/innodb_table_stats.ibd.delta
160724 16:18:13 [01] ...done
160724 16:18:13 [01] Copying ./mysql/slave_master_info.ibd to /tmp/incremental/incr1/mysql/slave_master_info.ibd.delta
160724 16:18:13 [01] ...done
160724 16:18:13 [01] Copying ./zhangshuo/zhangshuo.ibd to /tmp/incremental/incr1/zhangshuo/zhangshuo.ibd.delta
160724 16:18:13 [01] ...done
160724 16:18:13 >> log scanned up to (1673871)
160724 16:18:15 [01] Copying ./zhangshuo/zhangshuo.frm to /tmp/incremental/incr1/zhangshuo/zhangshuo.frm
160724 16:18:15 [01] ...done
160724 16:18:15 [01] Copying ./zhangshuo/db.opt to /tmp/incremental/incr1/zhangshuo/db.opt
160724 16:18:15 [01] ...done
160724 16:18:15 Finished backing up non-InnoDB tables and files
再去查看xtrabackup_checkpoiints信息，已经变为incremental备份，from_lsn已经变为上次全备LSN号：
[root@bogon tmp]# cat incremental/incr1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1672825
to_lsn = 1673871
last_lsn = 1673871
compact = 0
recover_binlog_info = 0
因为增备只备份了修改过的数据：
[root@bogon tmp]# du -sh incremental/incr1
2.2M incremental/incr1
现在向数据库插入第三条数据：
root@localhost:mysql3317.sock 12:35:35 [zhangshuo]>select * from zhangshuo; +------+--------------+ | id | name | +------+--------------+ | 1 | zhangshuo | | 2 | zhangjie | | 3 | zhangsanfeng | +------+--------------+
基于第一次增备的第2次增量备份：
[root@bogon tmp]# innobackupex --defaults-file=/usr/local/mysql/my3317.cnf --no-timestamp --incremental --incremental-basedir=/tmp/incremental/incr1/ /tmp/incremental/incr2 2>/tmp/incrback2.log
160724 16:28:11 Backup created in directory '/tmp/incremental/incr2'
MySQL binlog position: filename 'mysql-bin.000010', position '1440'
xtrabackup: Transaction log of lsn (1674922) to (1674922) was copied.
160724 16:28:11 completed OK!
6.增量备份恢复：
（1）首先将全备恢复出来：（memory参数可以不加，默认是1G）
[root@bogon tmp]# innobackupex --defaults-file=/usr/local/mysql/my3317.cnf --apply-log --redo-only --use-memory=2G /tmp/fullback/2016-07-24_16-10-38/ 2>/tmp/apply_log_full.log
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1672825
160724 16:32:52 completed OK!
（2）将第1次增量备份应用到全备：
[root@bogon tmp]# innobackupex --defaults-file=/usr/local/mysql/my3317.cnf --apply-log --redo-only --use-memory=2G /tmp/fullback/2016-07-24_16-10-38/ --incremental-dir=/tmp/incremental/incr1/ 2>/tmp/apply_log_incr1.log
可看到使用apply_log将增备恢复到全备只是LSN位置的变化：
xtrabackup: page size for /tmp/incremental/incr1//zhangshuo/zhangshuo.ibd.delta is 16384 bytes
Applying /tmp/incremental/incr1//zhangshuo/zhangshuo.ibd.delta to ./zhangshuo/zhangshuo.ibd...
160724 16:39:36 [01] Copying /tmp/incremental/incr1/zhangshuo/zhangshuo.frm to ./zhangshuo/zhangshuo.frm
160724 16:39:36 [01] ...done
160724 16:39:36 [01] Copying /tmp/incremental/incr1/zhangshuo/db.opt to ./zhangshuo/db.opt
160724 16:39:36 [01] ...done
160724 16:39:36 [00] Copying /tmp/incremental/incr1//xtrabackup_binlog_info to ./xtrabackup_binlog_info
160724 16:39:36 [00] ...done
160724 16:39:36 [00] Copying /tmp/incremental/incr1//xtrabackup_info to ./xtrabackup_info
160724 16:39:36 [00] ...done
160724 16:39:36 completed OK!
将第2次增量备份应用到全备（最后一次恢复增备是去掉redo-only）：
[root@bogon tmp]# innobackupex --defaults-file=/usr/local/mysql/my3317.cnf --apply-log --use-memory=2G /tmp/fullback/2016-07-24_16-10-38/ --incremental-dir=/tmp/incremental/incr2/ 2>/tmp/apply_log_incr2.log
(3)最后把所有合并好的数据进行apply-log：
[root@bogon 2016-07-24_16-55-36]# innobackupex --apply-log /tmp/fullback/2016-07-24_16-10-38
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
160724 17:18:58 completed OK!
将备份恢复到数据库：
[root@bogon data]# innobackupex --defaults-file=/usr/local/mysql/my3317.cnf --copy-back /tmp/fullback/2016-07-24_16-10-38/
7.检查数据是否恢复：
root@localhost:mysql3317.sock 17:29:05 [zhangshuo]>select * from zhangshuo; +------+--------------+ | id | name | +------+--------------+ | 1 | zhangshuo | | 2 | zhangjie | | 3 | zhangsanfeng | +------+--------------+
160724 17:06:10 [01] Copying /tmp/incremental/incr2/performance_schema/setup_consumers.frm to ./performance_schema/setup_consumers.frm 160724 17:06:10 [01] ...done 160724 17:06:10 [01] Copying /tmp/incremental/incr2/performance_schema/events_statements_current.frm to ./performance_schema/events_statements_current.frm 160724 17:06:10 [01] ...done 160724 17:06:10 [01] Copying /tmp/incremental/incr2/performance_schema/events_waits_history_long.frm to ./performance_schema/events_waits_history_long.frm 160724 17:06:10 [01] ...done 160724 17:06:10 [01] Copying /tmp/incremental/incr2/zhangshuo/zhangshuo.frm to ./zhangshuo/zhangshuo.frm 160724 17:06:10 [01] ...done
[root@bogon 2016-08-12_16-07-27]# innobackupex --apply-log /backup/pit/2016-08-12_16-07-27
[root@bogon mysql3306]# innobackupex --defaults-file=/usr/local/mysql/my3316.cnf --copy-back /backup/pit/2016-08-12_16-07-27
启动数据库，恢复全备之后的数据。
[root@bogon 2016-08-12_17-21-05]# cat xtrabackup_binlog_info mysql-bin.000001 120
[root@bogon logs]# /usr/local/mysql/bin/mysqlbinlog --start-position=120 mysql-bin.000001 |/usr/local/mysql/bin/mysql -S /tmp/mysql3316.sock
10.误执行drop table后，数据恢复。
[root@bogon mysql3316]# innobackupex --defaults-file=/usr/local/mysql/my3316.cnf --copy-back /backup/pit/2016-08-12_17-21-05/
启动数据库，找到drop table位置，跳过并恢复drop table前后事物。
--stop-position #是drop table 位置，这个位置不会执行，只会执行之前事物。
--start-position #是start开始位置，包含这个事物。
[root@bogon logs]# /usr/local/mysql/bin/mysqlbinlog --stop-position=672 mysql-bin.000002 |/usr/local/mysql/bin/mysql -S /tmp/mysql3316.sock
[root@bogon logs]# /usr/local/mysql/bin/mysqlbinlog --start-position=927 mysql-bin.000002 |/usr/local/mysql/bin/mysql -S /tmp/mysql3316.sock
11.使用正则备份(从库备份--slave-info，主库备份--no-lock）
[root@bogon mysql]# innobackupex --defaults-file="/usr/local/mysql/my3316.cnf" --host="127.0.0.1" --user="root" --password="" --port=3316 --no-lock --include='^test[.]zhangshuo' /backup/re/
12.以文件形式指出备份目标（文件形式以数据库名.数据表名罗列）
[root@bogon ~]# innobackupex --defaults-file="/usr/local/mysql/my3316.cnf" --host="127.0.0.1" --user="root" --password="" --port=3316 --no-lock --table-files=/backup/back_test.list /backup/list/
13.以数据库为单位进行备份
[root@bogon ~]# innobackupex --defaults-file="/usr/local/mysql/my3316.cnf" --host="127.0.0.1" --user="root" --password="" --port=3316 --no-lock --databases='test.zhangshuo employees' /backup/database/
像11、12、13这种部分备份，进行恢复时与完全、增备不同，必须使用以数据表为单位的恢复方式，执行innobackupex脚本时必须一起使用--apply-log与--export选项。
[root@bogon 2016-08-13_09-47-05]# innobackupex --apply-log --export re/2016-08-13_09-18-25 [root@bogon 2016-08-13_09-47-05]# innobackupex --apply-log --export /backup/list/2016-08-13_09-42-10/ [root@bogon 2016-08-13_09-47-05]# innobackupex --apply-log --export /backup/database/2016-08-13_09-47-05/
以备份的数据表为单位生成.exp与.cfg和.ibd文件，最后以mysql5.6的 表空间传输方式，将备份的数据表应用到mysql服务器。
