innobackupex+binlog备份测试实验
全备
原始数据：test库
开始全备：
备份目录/backup/full/20191126
innobackupex --socket=/tmp/mysql.sock --user=root --password=123456 /backup/full/20191126 --no-timestamp --no-lock --throttle=100
--no-timestamp 不使用时间戳生成目录，默认备份目录是一个时间戳目录
--throttle 每秒IO
--no-lock 关闭表锁，适用场景见innobackupex 帮助
xtrabackup_binlo_info记录了二进制日志的位置，我们二进制日志恢复需要使用到
开始做数据操作，创建一张表t，并添加2条数据
查看二进制日志：
[root@localhost logs]# mysqlbinlog -vvv mysql-bin.000016 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #191126 10:25:02 server id 1 end_log_pos 123 CRC32 0xfaeb9c84 Start: binlog v 4, server v 5.7.26-log created 191126 10:25:02 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' /ozcXQ8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAD+jNxdEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AYSc6/o= '/*!*/; # at 123 #191126 10:25:02 server id 1 end_log_pos 194 CRC32 0xdfc752d1 Previous-GTIDs # 044c1532-f7c9-11e9-8ac4-00505637b733:1-3112 # at 194 #新的操作开始的位置 #191126 14:17:58 server id 1 end_log_pos 259 CRC32 0x894b1a31 GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= '044c1532-f7c9-11e9-8ac4-00505637b733:3113'/*!*/; # at 259 #191126 14:17:58 server id 1 end_log_pos 376 CRC32 0x4eb46aa2 Query thread_id=22 exec_time=0 error_code=0 use `test`/*!*/; SET TIMESTAMP=1574749078/*!*/; SET @@session.pseudo_thread_id=22/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549120/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create table t(id int) default charset=utf8 /*!*/; # at 376 #191126 14:24:57 server id 1 end_log_pos 441 CRC32 0x0f84381a GTID last_committed=1 sequence_number=2 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '044c1532-f7c9-11e9-8ac4-00505637b733:3114'/*!*/; # at 441 #191126 14:24:57 server id 1 end_log_pos 513 CRC32 0x6de752e9 Query thread_id=23 exec_time=0 error_code=0 SET TIMESTAMP=1574749497/*!*/; BEGIN /*!*/; # at 513 #191126 14:24:57 server id 1 end_log_pos 557 CRC32 0x95e465d0 Table_map: `test`.`t` mapped to number 275 # at 557 #191126 14:24:57 server id 1 end_log_pos 602 CRC32 0x5e3fb325 Write_rows: table id 275 flags: STMT_END_F BINLOG ' OcXcXRMBAAAALAAAAC0CAAAAABMBAAAAAAEABHRlc3QAAXQAAQMAAdBl5JU= OcXcXR4BAAAALQAAAFoCAAAAABMBAAAAAAEAAgAB//4BAAAA/gIAAAAlsz9e '/*!*/; ### INSERT INTO `test`.`t` ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### INSERT INTO `test`.`t` ### SET ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ # at 602 #191126 14:24:57 server id 1 end_log_pos 633 CRC32 0x11f291f2 Xid = 139 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
删库操作：直接删除mysql数据库的data目录下的所有数据，因为还原的时候需要这个目录为空，正好就直接删除这里
恢复操作：
prepare步骤：
innobackupex --default-file=/backup/full/20191126/backup-my.cnf --user=root --password=123456 --apply-log /backup/full/20191126/
copy_back 步骤：
innobackupex --default-file=/backup/full/20191126/backup-my.cnf --user=root --copy-back /backup/full/20191126/
恢复后，需要修改mysql数据目录下的属主属组为mysql（我是mysql用户启动，默认root用户），重启mysql
登陆验证：
恢复到了原始表位置。
想恢复后面做的操作怎么办?使用二进制日志恢复。这里使用binlog2sql工具（安装略）。
通过备份的xtrabackup_binlog_info找到我们需要恢复的二进制日志的起止点。
python binlog2sql/binlog2sql.py -h227.0.0.1 -P3306 -uadmin -padmin --start-file='mysql-bin.000016' --start-position="259" --stop-position="602" > /tmp/tmp.sql
这里需要注意一下，tmp.log里的USE 语句有点错误，需要修改一下，另外可以把tmp.log改成tmp.sql。
恢复：
mysql -p < /tmp/tmp.sql
注意事项： mysql在做恢复的时候需要关闭二进制日志 set sql_log_bin=0，数据恢复后在开启; 在恢复之前需要重启mysql，而且还会报表t已经存在。
增量备份：
为了防止干扰，把/backup/full/20191126清空，并把最新的表数据作为原始数据。
当前原始数据5张表，t表2条数据（主要以t表做测试）
全备：
innobackupex --socket=/tmp/mysql.sock --user=root --password=123456 /backup/full/20191126 --no-timestamp --no-lock --throttle=100
做数据操作：删除了2，新增了3，4，5
第一次增量备份
innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=123456 --no-timestamp --incremental-basedir=/backup/full/20191126 --incremental /backup/incre/20191126
第二次增量备份
数据库再做一些操作
新增数据6，7
innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=123456 --no-timestamp --incremental-basedir=/backup/incre/20191126 --incremental /backup/incre/20191127
差异增备，基于上一次的增备目录
备份恢复：
prepare过程
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --apply-log --redo-only /backup/full/20191126
合并增量备份到初始备份：
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --apply-log --redo-only /backup/full/20191126 --incremental-dir=/backup/incre/20191126
copy-back过程：
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --copy-back /backup/full/20191126/
修改mysqldata目录下数据的属组属主，重启mysql
目前这里合并增量备份只合并了第一次的增量备份，故只有第一次的增量备份数据恢复了。
合并第二次的增量备份
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --apply-log --redo-only /backup/full/20191126 --incremental-dir=/backup/incre/20191127
copy-back：
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --copy-back /backup/full/20191126/
依然修改属主属组，重启mysql。这次数据就恢复到第二次备份了
这里实际生产中可以先合并完增量备份后，再进行copy-back操作。
二进制日志恢复补充：
对于误删除操作的sql语句，可以使用--flashback来闪回sql语句。
例： 我操作删除了7 这条数据，现在需要恢复，一样的还是找到最近备份的二进制起止位置。
不加--flashback时：
python binlog2sql/binlog2sql.py -h227.0.0.1 -P3306 -uroot -p123456 --start-file='mysql-bin.000019' --start-position="4" --stop-position="375" > /tmp/tmp.sql
可以看到你的删除操作的SQL语句
添加--flashback:
python binlog2sql/binlog2sql.py -h227.0.0.1 -P3306 -uroot -p123456 --flashback --start-file='mysql-bin.000019' --start-position="4" --stop-position="375" > /tmp/tmp.sql
可以通过闪回的语句进行数据恢复。
当然，实际的情况可能比较这更加的复杂，比如我们有了误删除，但是也有新的正确的数据插入进来，故就不能一概的全部都用闪回，或者全都不使用闪回，那么得准确的找到误删除的点。