开源工具-MyFlash工具

生产中使用binlog2sql较多,但也有缺点例如速度不够快;不支持blob的列,因此使用美团开源的myflash试试。

开源工具-MyFlash工具

MyFlash是由美团点评公司技术工程部开发维护的一个回滚DML操作的工具。该工具通过解析v4版本的binlog,完成回滚操作。相对已有的回滚工具,其增加了更多的过滤选项,让回滚更加容易。 该工具已经在美团点评内部使用

详细说明

  1. 安装

  2. 使用

  3. 测试用例

    限制

  4. binlog格式必须为row,且binlog_row_image=full

  5. 仅支持5.6与5.7

  6. 只能回滚DML(增、删、改)

    FAQ

  7. 实现的原理是什么?

  1. 支持gtid吗?
  • 答:支持。请参考 使用
  1. 在开启gtid的MySQL server上,应用flashback报错,错误为:ERROR 1782 (HY000) at line 16: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON. ?
  • 答:在导入时加入–skip-gtids
    mysqlbinlog –skip-gtids | mysql -uxxx -pxxx
  1. 如果回滚后的binlog日志尺寸超过20M,在导入时,很耗时。如何处理?
  • 答:参考 使用 ,搜索maxSplitSize。使用该参数可以对文件进行切片

使用实践

安装MyFlash

git clone https://github.com/Meituan-Dianping/MyFlash.git
cd MyFlash
yum install glib2* -y
gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
cp binary/flashback /usr/local/bin/
which flashback

查看帮助

执行flashback --help查看帮助

[root@node1 MyFlash]# flashback --help
Usage:
flashback [OPTION?]

Help Options:
-h, --help Show help options

Application Options:
--databaseNames databaseName to apply. if multiple, seperate by comma(,)
--tableNames tableName to apply. if multiple, seperate by comma(,)
--start-position start position
--stop-position stop position
--start-datetime start time (format %Y-%m-%d %H:%M:%S)
--stop-datetime stop time (format %Y-%m-%d %H:%M:%S)
--sqlTypes sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,)
--maxSplitSize max file size after split, the uint is M
--binlogFileNames binlog files to process. if multiple, seperate by comma(,)
--outBinlogFileNameBase output binlog file name base
--logLevel log level, available option is debug,warning,error
--include-gtids gtids to process
--exclude-gtids gtids to skip

下面的这些参数是可以任意组合的。

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

测试使用

检查mysql日志参数

show variables like 'binlog_format';
show variables like 'binlog_row_image';

创建测试表

create database booboo;
CREATE TABLE `booboo`.`testFlashback2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nameShort` varchar(20) DEFAULT NULL,
`nameLong` varchar(260) DEFAULT NULL,
`amount` decimal(19,9) DEFAULT NULL,
`amountFloat` float DEFAULT NULL,
`amountDouble` double DEFAULT NULL,
`createDatetime6` datetime(6) DEFAULT NULL,
`createDatetime` datetime DEFAULT NULL,
`createTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`nameText` text,
`nameBlob` blob,
`nameMedium` mediumtext,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

插入测试数据

flush logs
insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.5,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');
insert into testFlashback2(nameShort,nameLong,amount,amountFloat,amountDouble,createDatetime6,createDatetime,createTimestamp,nameText,nameBlob,nameMedium) values('aaa','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',10.5,10.6,10.7,'2017-10-26 10:00:00','2017-10-26 10:00:00','2017-10-26 10:00:00','cccc','dddd','eee');
flush logs;

查看插入的数据

root@localhost [booboo]>select * from testFlashback2\G;
*************************** 1. row ***************************
id: 1
nameShort: aaa
nameLong: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
amount: 10.500000000
amountFloat: 10.6
amountDouble: 10.7
createDatetime6: 2017-10-26 10:00:00.000000
createDatetime: 2017-10-26 10:00:00
createTimestamp: 2017-10-26 10:00:00
nameText: cccc
nameBlob: dddd
nameMedium: eee
*************************** 2. row ***************************
id: 2
nameShort: aaa
nameLong: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
amount: 10.500000000
amountFloat: 10.6
amountDouble: 10.7
createDatetime6: 2017-10-26 10:00:00.000000
createDatetime: 2017-10-26 10:00:00
createTimestamp: 2017-10-26 10:00:00
nameText: cccc
nameBlob: dddd
nameMedium: eee
2 rows in set (0.00 sec)

ERROR:
No query specified

回滚

  1. 找到对应的binlog日志 /alidata/mysql/log/mysql-bin.000078
root@localhost [booboo]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000077 | 5991 |
| mysql-bin.000078 | 1348 |
| mysql-bin.000079 | 191 |
+------------------+-----------+
3 rows in set (0.00 sec)

root@localhost [booboo]>exit;
Bye
[root@node1 MyFlash]# cd /alidata/mysql/
bin/ data/ include/ log/ my.cnf README share/ support-files/
COPYING docs/ lib/ man/ mysql-test/ scripts/ sql-bench/ tmp/
[root@node1 MyFlash]# cd /alidata/mysql/log/
mysql-bin.000077 mysql-bin.000078 mysql-bin.000079 mysql-bin.index test.sql
[root@node1 MyFlash]# cd /alidata/mysql/log/
[root@node1 log]# ll
总用量 24
-rw-rw----. 1 mysql mysql 5991 5月 8 17:08 mysql-bin.000077
-rw-rw----. 1 mysql mysql 1348 5月 8 17:09 mysql-bin.000078
-rw-rw----. 1 mysql mysql 191 5月 8 17:09 mysql-bin.000079
-rw-rw----. 1 mysql mysql 108 5月 8 17:09 mysql-bin.index
[root@node1 log]# mysqlbinlog -v -v mysql-bin.000078
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200508 17:08:40 server id 1003306 end_log_pos 120 CRC32 0x2b689980 Start: binlog v 4, server v 5.6.45-log created 200508 17:08:40
BINLOG '
mCG1Xg8qTw8AdAAAAHgAAAAAAAQANS42LjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYCZ
aCs=
'/*!*/;
# at 120
#200508 17:08:40 server id 1003306 end_log_pos 191 CRC32 0x51646940 Previous-GTIDs
# 8ae1b527-bd2c-11e9-b46d-000c296330ff:1-9
# at 191
#200508 17:09:12 server id 1003306 end_log_pos 239 CRC32 0x285c9356 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8ae1b527-bd2c-11e9-b46d-000c296330ff:10'/*!*/;
# at 239
#200508 17:09:12 server id 1003306 end_log_pos 321 CRC32 0x59cb1159 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1588928952/*!*/;
SET @@session.pseudo_thread_id=31/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
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=45/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 321
#200508 17:09:12 server id 1003306 end_log_pos 406 CRC32 0x541898f4 Table_map: `booboo`.`testflashback2` mapped to number 74
# at 406
#200508 17:09:12 server id 1003306 end_log_pos 715 CRC32 0x9df50e18 Write_rows: table id 74 flags: STMT_END_F

BINLOG '
uCG1XhMqTw8AVQAAAJYBAAAAAEoAAAAAAAEABmJvb2JvbwAOdGVzdGZsYXNoYmFjazIADAMPD/YE
BRISEfz8/A5QABAEEwkECAYAAAICA/4O9JgYVA==
uCG1Xh4qTw8ANQEAAMsCAAAAAEoAAAAAAAEAAgAM//8A8AEAAAADYWFhzQBiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJigAAAAAodzWUAmpkpQWZmZmZmZiVAmZ30oAAAAACZnfSgAFnx
QaAEAGNjY2MEAGRkZGQDAABlZWUYDvWd
'/*!*/;
### INSERT INTO `booboo`.`testflashback2`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='aaa' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### @3='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' /* VARSTRING(1040) meta=1040 nullable=1 is_null=0 */
### @4=10.500000000 /* DECIMAL(19,9) meta=4873 nullable=1 is_null=0 */
### @5=10.6 /* FLOAT meta=4 nullable=1 is_null=0 */
### @6=10.699999999999999289 /* DOUBLE meta=8 nullable=1 is_null=0 */
### @7='2017-10-26 10:00:00.000000' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
### @8='2017-10-26 10:00:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @9=1508983200 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @10='cccc' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @11='dddd' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @12='eee' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=1 is_null=0 */
# at 715
#200508 17:09:12 server id 1003306 end_log_pos 746 CRC32 0x637a4759 Xid = 153
COMMIT/*!*/;
# at 746
#200508 17:09:12 server id 1003306 end_log_pos 794 CRC32 0x6df52572 GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8ae1b527-bd2c-11e9-b46d-000c296330ff:11'/*!*/;
# at 794
#200508 17:09:12 server id 1003306 end_log_pos 876 CRC32 0x6944db63 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1588928952/*!*/;
BEGIN
/*!*/;
# at 876
#200508 17:09:12 server id 1003306 end_log_pos 961 CRC32 0xb3177cd9 Table_map: `booboo`.`testflashback2` mapped to number 74
# at 961
#200508 17:09:12 server id 1003306 end_log_pos 1270 CRC32 0x47f9804d Write_rows: table id 74 flags: STMT_END_F

BINLOG '
uCG1XhMqTw8AVQAAAMEDAAAAAEoAAAAAAAEABmJvb2JvbwAOdGVzdGZsYXNoYmFjazIADAMPD/YE
BRISEfz8/A5QABAEEwkECAYAAAICA/4O2XwXsw==
uCG1Xh4qTw8ANQEAAPYEAAAAAEoAAAAAAAEAAgAM//8A8AIAAAADYWFhzQBiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJigAAAAAodzWUAmpkpQWZmZmZmZiVAmZ30oAAAAACZnfSgAFnx
QaAEAGNjY2MEAGRkZGQDAABlZWVNgPlH
'/*!*/;
### INSERT INTO `booboo`.`testflashback2`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='aaa' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### @3='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' /* VARSTRING(1040) meta=1040 nullable=1 is_null=0 */
### @4=10.500000000 /* DECIMAL(19,9) meta=4873 nullable=1 is_null=0 */
### @5=10.6 /* FLOAT meta=4 nullable=1 is_null=0 */
### @6=10.699999999999999289 /* DOUBLE meta=8 nullable=1 is_null=0 */
### @7='2017-10-26 10:00:00.000000' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
### @8='2017-10-26 10:00:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @9=1508983200 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @10='cccc' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @11='dddd' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @12='eee' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=1 is_null=0 */
# at 1270
#200508 17:09:12 server id 1003306 end_log_pos 1301 CRC32 0xaf0eaeb3 Xid = 154
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
# at 1301
#200508 17:09:13 server id 1003306 end_log_pos 1348 CRC32 0x53ecb2a7 Rotate to mysql-bin.000079 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  1. 执行生产flashback文件 flashback --binlogFileNames=/alidata/mysql/log/mysql-bin.000078
[root@node1 log]# flashback --binlogFileNames=/alidata/mysql/log/mysql-bin.000078
[root@node1 log]# ll
总用量 28
-rw-r--r--. 1 root root 970 5月 8 17:19 binlog_output_base.flashback
-rw-rw----. 1 mysql mysql 5991 5月 8 17:08 mysql-bin.000077
-rw-rw----. 1 mysql mysql 1348 5月 8 17:09 mysql-bin.000078
-rw-rw----. 1 mysql mysql 191 5月 8 17:09 mysql-bin.000079
-rw-rw----. 1 mysql mysql 108 5月 8 17:09 mysql-bin.index
-rw-r--r--. 1 root root 795 4月 14 16:23 test.sql

3.

[root@node1 log]# mysqlbinlog binlog_output_base.flashback
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200508 17:08:40 server id 1003306 end_log_pos 120 CRC32 0x2b689980 Start: binlog v 4, server v 5.6.45-log created 200508 17:08:40
BINLOG '
mCG1Xg8qTw8AdAAAAHgAAAAAAAQANS42LjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYCZ
aCs=
'/*!*/;
# at 120
#200508 17:09:12 server id 1003306 end_log_pos 205 CRC32 0xb3177cd9 Table_map: `booboo`.`testflashback2` mapped to number 74
# at 205
#200508 17:09:12 server id 1003306 end_log_pos 514 CRC32 0x47f9804d Delete_rows: table id 74 flags: STMT_END_F

BINLOG '
uCG1XhMqTw8AVQAAAM0AAAAAAEoAAAAAAAEABmJvb2JvbwAOdGVzdGZsYXNoYmFjazIADAMPD/YE
BRISEfz8/A5QABAEEwkECAYAAAICA/4O2XwXsw==
uCG1XiAqTw8ANQEAAAICAAAAAEoAAAAAAAEAAgAM//8A8AIAAAADYWFhzQBiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJigAAAAAodzWUAmpkpQWZmZmZmZiVAmZ30oAAAAACZnfSgAFnx
QaAEAGNjY2MEAGRkZGQDAABlZWVNgPlH
'/*!*/;
# at 514
#200508 17:09:12 server id 1003306 end_log_pos 545 CRC32 0x637a4759 Xid = 153
COMMIT/*!*/;
# at 545
#200508 17:09:12 server id 1003306 end_log_pos 630 CRC32 0x541898f4 Table_map: `booboo`.`testflashback2` mapped to number 74
# at 630
#200508 17:09:12 server id 1003306 end_log_pos 939 CRC32 0x9df50e18 Delete_rows: table id 74 flags: STMT_END_F

BINLOG '
uCG1XhMqTw8AVQAAAHYCAAAAAEoAAAAAAAEABmJvb2JvbwAOdGVzdGZsYXNoYmFjazIADAMPD/YE
BRISEfz8/A5QABAEEwkECAYAAAICA/4O9JgYVA==
uCG1XiAqTw8ANQEAAKsDAAAAAEoAAAAAAAEAAgAM//8A8AEAAAADYWFhzQBiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJigAAAAAodzWUAmpkpQWZmZmZmZiVAmZ30oAAAAACZnfSgAFnx
QaAEAGNjY2MEAGRkZGQDAABlZWUYDvWd
'/*!*/;
# at 939
#200508 17:09:12 server id 1003306 end_log_pos 970 CRC32 0x637a4759 Xid = 153
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@node1 log]# mysqlbinlog -v -v binlog_output_base.flashback
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200508 17:08:40 server id 1003306 end_log_pos 120 CRC32 0x2b689980 Start: binlog v 4, server v 5.6.45-log created 200508 17:08:40
BINLOG '
mCG1Xg8qTw8AdAAAAHgAAAAAAAQANS42LjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYCZ
aCs=
'/*!*/;
# at 120
#200508 17:09:12 server id 1003306 end_log_pos 205 CRC32 0xb3177cd9 Table_map: `booboo`.`testflashback2` mapped to number 74
# at 205
#200508 17:09:12 server id 1003306 end_log_pos 514 CRC32 0x47f9804d Delete_rows: table id 74 flags: STMT_END_F

BINLOG '
uCG1XhMqTw8AVQAAAM0AAAAAAEoAAAAAAAEABmJvb2JvbwAOdGVzdGZsYXNoYmFjazIADAMPD/YE
BRISEfz8/A5QABAEEwkECAYAAAICA/4O2XwXsw==
uCG1XiAqTw8ANQEAAAICAAAAAEoAAAAAAAEAAgAM//8A8AIAAAADYWFhzQBiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJigAAAAAodzWUAmpkpQWZmZmZmZiVAmZ30oAAAAACZnfSgAFnx
QaAEAGNjY2MEAGRkZGQDAABlZWVNgPlH
'/*!*/;
### DELETE FROM `booboo`.`testflashback2`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='aaa' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### @3='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' /* VARSTRING(1040) meta=1040 nullable=1 is_null=0 */
### @4=10.500000000 /* DECIMAL(19,9) meta=4873 nullable=1 is_null=0 */
### @5=10.6 /* FLOAT meta=4 nullable=1 is_null=0 */
### @6=10.699999999999999289 /* DOUBLE meta=8 nullable=1 is_null=0 */
### @7='2017-10-26 10:00:00.000000' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
### @8='2017-10-26 10:00:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @9=1508983200 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @10='cccc' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @11='dddd' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @12='eee' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=1 is_null=0 */
# at 514
#200508 17:09:12 server id 1003306 end_log_pos 545 CRC32 0x637a4759 Xid = 153
COMMIT/*!*/;
# at 545
#200508 17:09:12 server id 1003306 end_log_pos 630 CRC32 0x541898f4 Table_map: `booboo`.`testflashback2` mapped to number 74
# at 630
#200508 17:09:12 server id 1003306 end_log_pos 939 CRC32 0x9df50e18 Delete_rows: table id 74 flags: STMT_END_F

BINLOG '
uCG1XhMqTw8AVQAAAHYCAAAAAEoAAAAAAAEABmJvb2JvbwAOdGVzdGZsYXNoYmFjazIADAMPD/YE
BRISEfz8/A5QABAEEwkECAYAAAICA/4O9JgYVA==
uCG1XiAqTw8ANQEAAKsDAAAAAEoAAAAAAAEAAgAM//8A8AEAAAADYWFhzQBiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJiYmJi
YmJiYmJiYmJiYmJiYmJiYmJiYmJigAAAAAodzWUAmpkpQWZmZmZmZiVAmZ30oAAAAACZnfSgAFnx
QaAEAGNjY2MEAGRkZGQDAABlZWUYDvWd
'/*!*/;
### DELETE FROM `booboo`.`testflashback2`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='aaa' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### @3='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' /* VARSTRING(1040) meta=1040 nullable=1 is_null=0 */
### @4=10.500000000 /* DECIMAL(19,9) meta=4873 nullable=1 is_null=0 */
### @5=10.6 /* FLOAT meta=4 nullable=1 is_null=0 */
### @6=10.699999999999999289 /* DOUBLE meta=8 nullable=1 is_null=0 */
### @7='2017-10-26 10:00:00.000000' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
### @8='2017-10-26 10:00:00' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @9=1508983200 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### @10='cccc' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @11='dddd' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### @12='eee' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=1 is_null=0 */
# at 939
#200508 17:09:12 server id 1003306 end_log_pos 970 CRC32 0x637a4759 Xid = 153
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  1. 导入数据库mysqlbinlog -v -v binlog_output_base.flashback | mysql -uroot -proot
[root@node1 log]# mysqlbinlog -v -v binlog_output_base.flashback | mysql -uroot -proot
Warning: Using a password on the command line interface can be insecure.
  1. 检查执行结果
[root@node1 log]# mysql -uroot -proot -e "select count(*) from booboo.testflashback2"
Warning: Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
| 0 |
+----------+

如何对阿里云RDS进行闪回

难点:

  1. 阿里云的binlog获取方法
  2. 阿里云账号没有Super权限因此需要处理binlog日志删除备注信息。

阿里云的binlog获取方法

  1. 若binlog日志已上传至oss,则需要在控制台进行下载,下载时需要注意找到主的日志(阿里云RDS日志包含主和被的日志)
  2. 若binlog日志还在实例本地,则需要在控制台先点击一键上传,待日志上传到oss后,从控制台下载

处理binlog日志的备注信息

# 5.6版本
mysqlbinlog -v -v --skip-gtids binlog_output_base.flashback | sed 's@\/\*.*\*\/@@' |mysql -uroot -proot

其他方法

对于阿里云RDS实例,若待回滚的表中,没有blob字段建议使用binlog2sql。

# 远程在线获取binlog日志
mysqlbinlog -vv -P 3306 -uxxx -pxxxx -hxxxxx.mysql.rds.aliyuncs.com --read-from-remote-server --start-datetime='2017-09-12 06:29:00' --stop-datetime='2017-09-12 06:30:01' --base64-output=DECODE-ROWS mysql-bin.000634 > taiping0912.binlog
# binlog2sql远程连接数据库直接生成回滚sql
python binlog2sql.py -P 3306 -uxxx -pxxxx -hxxxxx.mysql.rds.aliyuncs.com -t tablenamexxx --start-file='mysql-bin.000001' --start-position=4946 --stop-position=5921 -B