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

可以通过闪回的语句进行数据恢复。

当然，实际的情况可能比较这更加的复杂，比如我们有了误删除，但是也有新的正确的数据插入进来，故就不能一概的全部都用闪回，或者全都不使用闪回，那么得准确的找到误删除的点。

亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>