背景:
源于美团点评。
增强版binlog2sql,拥有更快的恢复速度以及更灵活便捷的过滤方式。
基于binlog中记录的前后镜像实现,故依赖于以下两个参数:
binlog_format=ROW
binlog_row_image=FULL
一、安装
yum install gcc
yum install glib2-devel
cd /usr/local/
git clone https://github.com/Meituan-Dianping/MyFlash.git
cd MyFlash
动态编译:
gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
cd /usr/local/sbin/
ln -s /usr/local/MyFlash/binary/flashback flashback
二、恢复原理
1、基于binlog前后镜像的恢复过程
基础概念:
type_code:每条write_row_event、update_row_even、delete_row_event都会有type_code,分别对应30、31、32;
最小执行单元:一个table map event及随后可能出现的write_row_event、update_row_even、delete_row_event,直至下一个table map event之前。
实现过程如下:
(1)根据过滤条件对binlog event进行筛选
(2)反转最小执行单元中的row event,根据不同的DML操作实现方式区分如下:
误删除恢复:
将binlog event中的 type_code 从32(删除)变更为30(insert)
误插入恢复:
将binlog event中的 type_code 从30(insert)变更为32(删除)
误更新恢复:
将before image和after image互换,由于前后镜像可能存在长度不同的情况,需要做到准确计算镜像长度。
(3)逆序最小执行单元队列
2、恢复区间的选取
(1)按时间段选取
--start-datetime、--stop-datetime。(format %Y-%m-%d %H:%M:%S)
注意,按时间端选取,依然会从文件开始处开始读取,直到遇到起始时间点位置,开始进行binlog event的解析操作。
(2)按位置点选取
--start-position、--stop-position
直接跳到起始位置点。当binlog较大、需要恢复的binlog较少、需要恢复的binlog在文件中靠后的情况下,会更快速。
三、参数解析
1.databaseNames
指定需要回滚的数据库名。多个数据库可以用“,”隔开。如果不指定该参数,相当于指定了所有数据库。
2.tableNames
指定需要回滚的表名。多个表可以用“,”隔开。如果不指定该参数,相当于指定了所有表。
3.start-position
指定回滚开始的位置。如不指定,从文件的开始处回滚。请指定正确的有效的位置,否则无法回滚
4.stop-position
指定回滚结束的位置。如不指定,回滚到文件结尾。请指定正确的有效的位置,否则无法回滚
5.start-datetime
指定回滚的开始时间。注意格式必须是 %Y-%m-%d %H:%M:%S。 如不指定,则不限定时间
6.stop-datetime
指定回滚的结束时间。注意格式必须是 %Y-%m-%d %H:%M:%S。 如不指定,则不限定时间
7.sqlTypes
指定需要回滚的sql类型。目前支持的过滤类型是INSERT, UPDATE ,DELETE。多个类型可以用“,”隔开。
8.maxSplitSize
一旦指定该参数,对文件进行固定尺寸的分割(单位为M),过滤条件有效,但不进行回滚操作。该参数主要用来将大的binlog文件切割,防止单次应用的binlog尺寸过大,对线上造成压力
9.binlogFileNames
指定需要回滚的binlog文件,目前只支持单个文件,后续会增加多个文件支持
10.outBinlogFileNameBase
指定输出的binlog文件前缀,如不指定,则默认为binlog_output_base.flashback
11.logLevel
仅供开发者使用,默认级别为error级别。在生产环境中不要修改这个级别,否则输出过多
12.include-gtids
指定需要回滚的gtid,支持gtid的单个和范围两种形式。
13.exclude-gtids
指定不需要回滚的gtid,用法同include-gtids
四、恢复案例
利用sysbench造数,表结构如下:
mysql> show create table sbtest1 \G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
1 row in set (0.00 sec)
共1000000行。
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.30 sec)
1、误删整表的恢复
(1)drop table方式
DDL操作,MyFlash无能为力
(2)delete操作不带where条件
mysql> delete from sbtest1;
Query OK, 1000000 rows affected (15.39 sec)
生成恢复数据(顺便统计命令耗时):
[root@test16 tmp]# time flashback --binlogFileNames=/home/mysql/bin-log.000012 --tableNames=sbtest1 --outBinlogFileNameBase=/tmp/binlog_output_base.flashback
real 0m13.911s
user 0m8.235s
sys 0m5.403s
从近1个G的binlog文件中,生成反转语句仅需13s
上述恢复语句基于binlog文件中之前无sbtest1表相关操作,否则,需要按时间或位置点区间选取:
时间选取法:
flashback --binlogFileNames=/home/mysql/bin-log.000012 --start-datetime="2020-01-08 17:17:00" --stop-datetime="2020-01-08 17:19:00" --tableNames=sbtest1 --outBinlogFileNameBase=/tmp/binlog_output_base.flashback
更推荐利用位置点方法,参考第2节
附:有兴趣可以执行以下命令查看转换后的binlog信息
mysqlbinlog /tmp/binlog_output_base.flashback.flashback --base64-output=decode-rows -v
将反转语句应用到数据库中:
mysqlbinlog /tmp/binlog_output_base.flashback.flashback |mysql -uroot -p
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.32 sec)
2、部分数据的恢复
(1)误删sbtest表500行记录为例:
mysql> select count(*) from sbtest2 limit 500;
+----------+
| count(*) |
+----------+
| 1009314 |
+----------+
1 row in set (0.30 sec)
mysql> delete from sbtest2 limit 500;
Query OK, 500 rows affected (0.02 sec)
mysql> select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
| 1008814 |
+----------+
1 row in set (0.31 sec)
首先定位误操作所在binlog文件。方法:观察binlog最后修改时间、结合误删操作大致时间信息,从而定位。
若所在binlog文件过大,通过如下方式对binlog做初步过滤。
mysqlbinlog --start-datetime="2020-01-09 17:21:00" --stop-datetime="2020-01-09 17:59:00" bin-log.000016 --base64-output=decode-rows -v > /tmp/binlogcontent.txt
再从该文件中定位开始、结束位置点
以此案例来讲,首先找到binlog中误删操作开始位置点,可选搜索条件:
a、库名、表名。
例如表名为:sbtest.sbtest2,就可以按如下条件搜索:
Table_map: `sbtest`.`sbtest2`
b、被误操作a的数据。
例如知道第二个字段为517916,或第三个字段为'11341497448-94884019052',就可以按如下条件搜索:
@2=517916
@3='11341497448-94884019052'
上述方法找到位置后,向前定位到最近一个BEGIN
结果如下:
# at 154
#200109 18:11:30 server id 1 end_log_pos 219 CRC32 0x84f0cf79 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200109 18:11:30 server id 1 end_log_pos 298 CRC32 0xae6b8aa2 Query thread_id=424 exec_time=0 error_code=0
SET TIMESTAMP=1578564690/*!*/;
SET @@session.pseudo_thread_id=424/*!*/;
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=3, @@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/*!*/;
BEGIN
/*!*/;
# at 298
#200109 18:11:30 server id 1 end_log_pos 357 CRC32 0xcb6225e4 Table_map: `sbtest`.`sbtest2` mapped to number 147
# at 357
#200109 18:11:30 server id 1 end_log_pos 8562 CRC32 0x28da1824 Delete_rows: table id 147
# at 8562
#200109 18:11:30 server id 1 end_log_pos 16767 CRC32 0xffc40b01 Delete_rows: table id 147
# at 16767
#200109 18:11:30 server id 1 end_log_pos 24972 CRC32 0xf529aba7 Delete_rows: table id 147
# at 24972
#200109 18:11:30 server id 1 end_log_pos 33177 CRC32 0x19d0962a Delete_rows: table id 147
# at 33177
随后在当前位置往后搜索COMMIT,从而定位到最近一次事务提交操作,从而确定binlog中误删操作结束位置点:
略
### DELETE FROM `sbtest`.`sbtest2`
### WHERE
### @1=1495
### @2=517916
### @3='11341497448-75781161833-94884019052-94577658432-12712378883-35926064749-18467726185-67618919043-07212977021-62158832584'
### @4='17734181665-90426399251-01635303017-08064406161-94955663606'
### DELETE FROM `sbtest`.`sbtest2`
### WHERE
### @1=1498
### @2=499869
### @3='39104648814-09547584582-30255754786-74589531519-91886895635-04267005066-37562766100-90970532433-74799956628-22023646262'
### @4='05708688591-07646545120-72309934228-15444393431-23368620588'
# at 95777
#200109 18:11:30 server id 1 end_log_pos 95808 CRC32 0xe3f33d5b Xid = 1786763
COMMIT/*!*/;
# at 95808
略
获取到:--start-position=154 --stop-positon=95808
flashback --binlogFileNames=/home/mysql/bin-log.000016 --start-position=154 --stop-position=95808 --tableNames=sbtest2 --outBinlogFileNameBase=/tmp/binlog_output_base.flashback
回滚文件开头:
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200109 18:10:49 server id 1 end_log_pos 123 CRC32 0x8bacc736 Start: binlog v 4, server v 5.7.27-30-57-log created 200109 18:10:49
# Warning: this binlog is either in use or was not closed properly.
# at 123
#200109 18:11:30 server id 1 end_log_pos 182 CRC32 0xcb6225e4 Table_map: `sbtest`.`sbtest2` mapped to number 147
# at 182
#200109 18:11:30 server id 1 end_log_pos 5347 CRC32 0x39e1b318 Write_rows: table id 147 flags: STMT_END_F
### INSERT INTO `sbtest`.`sbtest2`
### SET
### @1=1420
### @2=503570
### @3='29903943947-33882778963-44278033068-72440306107-18098636489-10991531270-22605890154-66792820167-99053317518-52327278132'
### @4='26858467823-61471885493-01388986011-99791610535-50210873439'
### INSERT INTO `sbtest`.`sbtest2`
### SET
### @1=1423
### @2=500840
### @3='17531886600-94360788237-62463645324-29421632602-27664088937-65009680565-46688416406-47927275163-74509433009-51353006227'
### @4='70137369026-87942946987-36419484982-60617385894-85274245530'
### INSERT INTO `sbtest`.`sbtest2`
回滚文件末尾:
### INSERT INTO `sbtest`.`sbtest2`
### SET
### @1=127
### @2=497754
### @3='12129620806-37146297090-08308922701-42581848454-44731846327-11106759041-33257365784-60391916000-44820639318-69774301737'
### @4='49343091886-13665445238-86975912558-61882976881-05609050641'
# at 96592
#200109 18:11:30 server id 1 end_log_pos 96623 CRC32 0xe3f33d5b Xid = 1786763
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*/;
导入到数据库,并复测数据已恢复。
mysqlbinlog /tmp/binlog_output_base.flashback.flashback | mysql -uroot -p
mysql> select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
| 1009314 |
+----------+
1 row in set (0.31 sec)
附:误删操作之后提交的事务的binlog日志如下:
# at 95808
#200109 18:11:30 server id 1 end_log_pos 95873 CRC32 0x1293a3a3 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 95873
#200109 18:11:30 server id 1 end_log_pos 95946 CRC32 0x4bfc4abf Query thread_id=427 exec_time=720 error_code=0
SET TIMESTAMP=1578564690/*!*/;
SET @@session.sql_mode=524288/*!*/;
BEGIN
/*!*/;
# at 95946
#200109 18:11:30 server id 1 end_log_pos 96005 CRC32 0x4cd707cb Table_map: `sbtest`.`sbtest2` mapped to number 147
# at 96005
#200109 18:11:30 server id 1 end_log_pos 101170 CRC32 0x411dfc4c Write_rows: table id 147 flags: STMT_END_F
### INSERT INTO `sbtest`.`sbtest2`
### SET
### @1=1420
### @2=503570
### @3='29903943947-33882778963-44278033068-72440306107-18098636489-10991531270-22605890154-66792820167-99053317518-52327278132'
### @4='26858467823-61471885493-01388986011-99791610535-50210873439'
### INSERT INTO `sbtest`.`sbtest2`
### SET
### @1=1423
### @2=500840
### @3='17531886600-94360788237-62463645324-29421632602-27664088937-65009680565-46688416406-47927275163-74509433009-51353006227'
### @4='70137369026-87942946987-36419484982-60617385894-85274245530'
参考文档:
Meituan-Dianping/MyFlash项目 Github地址