MyFlash工具的使用实践及其余binlog2sql的速度比较

来源：这里教程网时间：2026-03-01 16:09:17 作者：

微信文章中看到了介绍MyFlash工具的文章，今天来验证一把。效果不错，以后闪回工具优先用这个了~ 一、安装 [root@centos84 ~]# unzip MyFlash-master.zip Archive: MyFlash-master.zip b128c0faaf1a657d09238b7cda1c2a80ad599909 creating: MyFlash-master/ extracting: MyFlash-master/.gitignore inflating: MyFlash-master/License.md inflating: MyFlash-master/README.md creating: MyFlash-master/binary/ inflating: MyFlash-master/binary/flashback inflating: MyFlash-master/binary/mysqlbinlog20160408 inflating: MyFlash-master/binlog_output_base.flashback inflating: MyFlash-master/build.sh creating: MyFlash-master/doc/ extracting: MyFlash-master/doc/FAQ.md extracting: MyFlash-master/doc/Function.md inflating: MyFlash-master/doc/INSTALL.md inflating: MyFlash-master/doc/TestCase.md inflating: MyFlash-master/doc/how_to_use.md creating: MyFlash-master/source/ inflating: MyFlash-master/source/binlogParseGlib.c creating: MyFlash-master/source/mysqlHelper/ extracting: MyFlash-master/source/mysqlHelper/mysqlHelper.c creating: MyFlash-master/source/network/ inflating: MyFlash-master/source/network/network.c creating: MyFlash-master/testbinlog/ inflating: MyFlash-master/testbinlog/haha.000005 inflating: MyFlash-master/testbinlog/haha.000007 inflating: MyFlash-master/testbinlog/haha.000008 inflating: MyFlash-master/testbinlog/haha.000009 inflating: MyFlash-master/testbinlog/haha.000041 [root@centos84 ~]# cd MyFlash-master/ [root@centos84 MyFlash-master]# gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback Package glib-2.0 was not found in the pkg-config search path. Perhaps you should add the directory containing `glib-2.0.pc' to the PKG_CONFIG_PATH environment variable Package 'glib-2.0', required by 'virtual:world', not found source/binlogParseGlib.c:6:10: fatal error: glib.h: No such file or directory #include <glib.h> ^~~~~~~~ compilation terminated. 按照提示，安装glib包： [root@centos84 MyFlash-master]# yum install glib2-devel -y Last metadata expiration check: 0:06:53 ago on Fri 26 Nov 2021 11:53:18 PM CST. Dependencies resolved. =========================================================================================================================================================================== Package Architecture Version Repository Size =========================================================================================================================================================================== Installing: glib2-devel x86_64 2.56.4-156.el8 baseos 424 k Upgrading: glib2 x86_64 2.56.4-156.el8 baseos 2.5 M pcre x86_64 8.42-6.el8 baseos 211 k Installing dependencies: pcre-cpp x86_64 8.42-6.el8 baseos 47 k pcre-devel x86_64 8.42-6.el8 baseos 551 k pcre-utf16 x86_64 8.42-6.el8 baseos 195 k pcre-utf32 x86_64 8.42-6.el8 baseos 186 k Transaction Summary =========================================================================================================================================================================== Install 5 Packages Upgrade 2 Packages Total download size: 4.1 M Downloading Packages: (1/7): pcre-cpp-8.42-6.el8.x86_64.rpm 444 kB/s | 47 kB 00:00 (2/7): pcre-utf16-8.42-6.el8.x86_64.rpm 635 kB/s | 195 kB 00:00 (3/7): pcre-utf32-8.42-6.el8.x86_64.rpm 1.8 MB/s | 186 kB 00:00 (4/7): glib2-devel-2.56.4-156.el8.x86_64.rpm 692 kB/s | 424 kB 00:00 (5/7): glib2-2.56.4-156.el8.x86_64.rpm 20 MB/s | 2.5 MB 00:00 (6/7): pcre-devel-8.42-6.el8.x86_64.rpm 856 kB/s | 551 kB 00:00 (7/7): pcre-8.42-6.el8.x86_64.rpm 2.0 MB/s | 211 kB 00:00 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 5.7 MB/s | 4.1 MB 00:00 Running transaction check Transaction check succeeded. Running transaction test Transaction test succeeded. Running transaction Preparing : 1/1 Upgrading : pcre-8.42-6.el8.x86_64 1/9 Running scriptlet: pcre-8.42-6.el8.x86_64 1/9 Installing : pcre-cpp-8.42-6.el8.x86_64 2/9 Upgrading : glib2-2.56.4-156.el8.x86_64 3/9 Installing : pcre-utf32-8.42-6.el8.x86_64 4/9 Installing : pcre-utf16-8.42-6.el8.x86_64 5/9 Installing : pcre-devel-8.42-6.el8.x86_64 6/9 Installing : glib2-devel-2.56.4-156.el8.x86_64 7/9 Cleanup : glib2-2.56.4-10.el8_4.x86_64 8/9 Cleanup : pcre-8.42-4.el8.x86_64 9/9 Running scriptlet: pcre-8.42-4.el8.x86_64 9/9 Running scriptlet: glib2-2.56.4-156.el8.x86_64 9/9 Verifying : glib2-devel-2.56.4-156.el8.x86_64 1/9 Verifying : pcre-cpp-8.42-6.el8.x86_64 2/9 Verifying : pcre-devel-8.42-6.el8.x86_64 3/9 Verifying : pcre-utf16-8.42-6.el8.x86_64 4/9 Verifying : pcre-utf32-8.42-6.el8.x86_64 5/9 Verifying : glib2-2.56.4-156.el8.x86_64 6/9 Verifying : glib2-2.56.4-10.el8_4.x86_64 7/9 Verifying : pcre-8.42-6.el8.x86_64 8/9 Verifying : pcre-8.42-4.el8.x86_64 9/9 Upgraded: glib2-2.56.4-156.el8.x86_64 pcre-8.42-6.el8.x86_64 Installed: glib2-devel-2.56.4-156.el8.x86_64 pcre-cpp-8.42-6.el8.x86_64 pcre-devel-8.42-6.el8.x86_64 pcre-utf16-8.42-6.el8.x86_64 pcre-utf32-8.42-6.el8.x86_64 Complete! [root@centos84 MyFlash-master]# gcc -g -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback [root@centos84 MyFlash-master]# cd binary/ [root@centos84 binary]# ./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(,) --tableNames-file 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. if multiple, seperate by comma(,) --include-gtids-file gtids to process. if multiple, seperate by comma(,) --exclude-gtids gtids to skip. if multiple, seperate by comma(,) --exclude-gtids-file gtids to skip. if multiple, seperate by comma(,) [root@centos84 binary]# [root@centos84 binary]# /app/mysql/app/mysql57/bin/mysql -uroot -P3306 -h127.0.0.1 -p'root123' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.33-log MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A mysql> show create table testFlashback2; +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | testFlashback2 | CREATE TABLE `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 AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 | +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from testFlashback2; +----+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+----------------------------+---------------------+---------------------+----------+----------+------------+ | id | nameShort | nameLong | amount | amountFloat | amountDouble | createDatetime6 | createDatetime | createTimestamp | nameText | nameBlob | nameMedium | +----+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+----------------------------+---------------------+---------------------+----------+----------+------------+ | 1 | aaa | bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb | 10.500000000 | 10.6 | 10.7 | 2017-10-26 10:00:00.000000 | 2017-10-26 10:00:00 | 2017-10-26 10:00:00 | cccc | dddd | eee | | 2 | aaa | bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb | 10.500000000 | 10.6 | 10.7 | 2017-10-26 10:00:00.000000 | 2017-10-26 10:00:00 | 2017-10-26 10:00:00 | cccc | dddd | eee | +----+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+----------------------------+---------------------+---------------------+----------+----------+------------+ 2 rows in set (0.00 sec) mysql> delete from testFlashback2; Query OK, 2 rows affected (0.00 sec) mysql> 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'); Query OK, 1 row affected (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> delete from testFlashback2; Query OK, 1 row affected (0.00 sec) mysql> mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000013 | 914 | | mysql-bin.000014 | 1329 | | mysql-bin.000015 | 233 | | mysql-bin.000016 | 209 | | mysql-bin.000017 | 1594 | | mysql-bin.000018 | 730 | +------------------+-----------+ 6 rows in set (0.00 sec) mysql> show variables like '%bin%'; +--------------------------------------------+-------------------------------------+ | Variable_name | Value | +--------------------------------------------+-------------------------------------+ …… | log_bin_basename | /app/mysql/log/3306/mysql-bin | …… +--------------------------------------------+-------------------------------------+ 29 rows in set (0.00 sec) mysql> mysql> show variables like '%sock%'; +-----------------------------------------+--------------------------------+ | Variable_name | Value | +-----------------------------------------+--------------------------------+ | performance_schema_max_socket_classes | 10 | | performance_schema_max_socket_instances | -1 | | socket | /app/mysql/data/mysql3306.sock | +-----------------------------------------+--------------------------------+ 3 rows in set (0.00 sec) [root@centos84 MyFlash-master]# ./binary/flashback --binlogFileNames=/app/mysql/log/3306/mysql-bin.000018 [root@centos84 MyFlash-master]# ls binary binlog_output_base.flashback build.sh doc License.md README.md source testbinlog [root@centos84 MyFlash-master]# more binlog_output_base.flashback bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb]0;root@centos84:~/MyFlash-master[root@centos84 MyFlash-master]# [root@centos84 MyFlash-master]# 执行闪回操作： [root@centos84 MyFlash-master]# mysqlbinlog --skip-gtids binlog_output_base.flashback | mysql --socket=/app/mysql/data/mysql3306.sock -uroot -proot123 test mysql: [Warning] Using a password on the command line interface can be insecure. 验证闪回结果： mysql> select * from testFlashback2; +----+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+----------------------------+---------------------+---------------------+----------+----------+------------+ | id | nameShort | nameLong | amount | amountFloat | amountDouble | createDatetime6 | createDatetime | createTimestamp | nameText | nameBlob | nameMedium | +----+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+----------------------------+---------------------+---------------------+----------+----------+------------+ | 3 | aaa | bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb | 10.500000000 | 10.6 | 10.7 | 2017-10-26 10:00:00.000000 | 2017-10-26 10:00:00 | 2017-10-26 10:00:00 | cccc | dddd | eee | +----+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+----------------------------+---------------------+---------------------+----------+----------+------------+ 1 row in set (0.00 sec) 用MyFlash恢复数据需要执行两个步骤： 1.flashback命令，将需要恢复的数据放到当前目录下的binlog_output_base.flashback文件中 2.通过mysqlbinlog命令执行上一步文件中的命令 ################################################ 二、MyFlash与binlog2sql进行速度对比： mysql> CREATE TABLE sample( -> id INT(11) NOT NULL AUTO_INCREMENT, -> value INT(5) NOT NULL DEFAULT 0, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO sample(value) -> VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> mysql> CREATE TABLE account( -> id INT(11) NOT NULL AUTO_INCREMENT, -> name VARCHAR(50) NOT NULL, -> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.01 sec) mysql> mysql> mysql> INSERT INTO account( NAME ) -> SELECT concat( 'name', @nownum := @nownum + 1 ) -> FROM -> sample AS a, -> sample AS b, -> sample AS c, -> sample AS d, -> sample AS e, -> sample AS f, -> ( SELECT @nownum := 0 ) AS h; Query OK, 1000000 rows affected (2.88 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> mysql> show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------+ | mysql-bin.000018 | 23982109 | | | 14a16fba-4ba4-11ec-9027-00163e012cf2:1-62 | +------------------+----------+--------------+------------------+-------------------------------------------+ 1 row in set (0.00 sec) mysql> delete from account limit 300000; Query OK, 300000 rows affected (0.63 sec) mysql> mysql> mysql> show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------+ | mysql-bin.000018 | 31098152 | | | 14a16fba-4ba4-11ec-9027-00163e012cf2:1-63 | +------------------+----------+--------------+------------------+-------------------------------------------+ 1 row in set (0.00 sec) mysql> exit Bye [root@centos84 ~]# cd /root/MyFlash-master/ [root@centos84 MyFlash-master]# [root@centos84 MyFlash-master]# ./binary/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(,) --tableNames-file 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. if multiple, seperate by comma(,) --include-gtids-file gtids to process. if multiple, seperate by comma(,) --exclude-gtids gtids to skip. if multiple, seperate by comma(,) --exclude-gtids-file gtids to skip. if multiple, seperate by comma(,) [root@centos84 MyFlash-master]# ./binary/flashback --binlogFileNames=/app/mysql/log/3306/mysql-bin.000018 --start-position=23982109 --stop-position=31098152 ** (flashback:1432): ERROR **: 14:36:44.116: the output binlog_output_base.* may overwrite the existing file binlog_output_base.flashback, please choose a newFileName specified by --outBinlogFileNameBase or remove the existing file Trace/breakpoint trap (core dumped) [root@centos84 MyFlash-master]# rm binlog_output_base.flashback rm: remove regular file 'binlog_output_base.flashback'? yes [root@centos84 MyFlash-master]# ./binary/flashback --binlogFileNames=/app/mysql/log/3306/mysql-bin.000018 --start-position=23982109 --stop-position=31098152 [root@centos84 MyFlash-master]# [root@centos84 MyFlash-master]# ls -l binlog_output* -rw-r--r-- 1 root root 7185477 Nov 27 14:38 binlog_output_base.flashback [root@centos84 MyFlash-master]# /app/mysql/app/mysql57/bin/mysql -uroot -P3306 -h127.0.0.1 -p'root123' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.33-log MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select count(1) from test.account; +----------+ | count(1) | +----------+ | 700000 | +----------+ 1 row in set (0.07 sec) mysql> exit Bye [root@centos84 MyFlash-master]# mysqlbinlog --skip-gtids binlog_output_base.flashback | mysql --socket=/app/mysql/data/mysql3306.sock -uroot -proot123 test mysql: [Warning] Using a password on the command line interface can be insecure. [root@centos84 MyFlash-master]# /app/mysql/app/mysql57/bin/mysql -uroot -P3306 -h127.0.0.1 -p'root123' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.33-log MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select count(1) from test.account; +----------+ | count(1) | +----------+ | 1000000 | +----------+ 1 row in set (0.11 sec) mysql> 使用binlog2sql: [14:42:14]mysql> select count(1) from account; [14:42:14]+----------+ [14:42:14]| count(1) | [14:42:14]+----------+ [14:42:14]| 1000000 | [14:42:14]+----------+ [14:42:14]1 row in set (0.11 sec) [14:42:14] [14:42:18]mysql> show master status; [14:42:18]+------------------+----------+--------------+------------------+--------------------------------------------+ [14:42:18]| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | [14:42:18]+------------------+----------+--------------+------------------+--------------------------------------------+ [14:42:18]| mysql-bin.000018 | 69492098 | | | 14a16fba-4ba4-11ec-9027-00163e012cf2:1-936 | [14:42:18]+------------------+----------+--------------+------------------+--------------------------------------------+ [14:42:18]1 row in set (0.00 sec) [14:42:18] [14:42:27]mysql> delete from account limit 300000; [14:42:27]Query OK, 300000 rows affected (0.64 sec) [14:42:27] [14:42:33]mysql> show master status; [14:42:33]+------------------+----------+--------------+------------------+--------------------------------------------+ [14:42:33]| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | [14:42:33]+------------------+----------+--------------+------------------+--------------------------------------------+ [14:42:33]| mysql-bin.000018 | 76608141 | | | 14a16fba-4ba4-11ec-9027-00163e012cf2:1-937 | [14:42:33]+------------------+----------+--------------+------------------+--------------------------------------------+ [14:42:33]1 row in set (0.00 sec) [14:42:33] [14:45:33][root@centos84 binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root123' -dtest -taccount --start-file='mysql-bin.000018' --start-position=69492098 --stop-position=76608141 --flashback >recover.sql [14:46:13][root@centos84 binlog2sql]# more recover.sql [14:46:13]INSERT INTO `test`.`account`(`updated_at`, `created_at`, `id`, `name`) VALUES ('2021-11-27 14:42:06', '2021-11-27 14:42:06', 300000, 'name300000'); #start 69492159 end 766 [14:46:13]08114 time 2021-11-27 14:42:26 [14:46:13]INSERT INTO `test`.`account`(`updated_at`, `created_at`, `id`, `name`) VALUES ('2021-11-27 14:42:06', '2021-11-27 14:42:06', 299999, 'name299999'); #start 69492159 end 766 [14:46:13]08114 time 2021-11-27 14:42:26 [14:46:13]INSERT INTO `test`.`account`(`updated_at`, `created_at`, `id`, `name`) VALUES ('2021-11-27 14:42:06', '2021-11-27 14:42:06', 299998, 'name299998'); #start 69492159 end 766 [14:46:13]08114 time 2021-11-27 14:42:26 …… [14:46:43][root@centos84 binlog2sql]# mysql -h127.0.0.1 -P3306 -uroot -p'root123' <recover.sql [14:46:43]mysql: [Warning] Using a password on the command line interface can be insecure. [14:46:45]SLEEP(1.0) [14:46:45]0 [14:46:48]SLEEP(1.0) [14:46:48]0 [14:46:51]SLEEP(1.0) [14:46:51]0 [14:46:53]SLEEP(1.0) [14:46:53]0 [14:46:56]SLEEP(1.0) [14:46:56]0 [14:46:59]SLEEP(1.0) [14:46:59]0 [14:47:02]SLEEP(1.0) [14:47:21]mysql> select count(1) from account; [14:47:21]+----------+ [14:47:21]| count(1) | [14:47:21]+----------+ [14:47:21]| 714014 | [14:47:21]+----------+ [14:47:21]1 row in set (0.08 sec) …… [14:52:44]mysql> select count(1) from account; [14:52:44]+----------+ [14:52:44]| count(1) | [14:52:44]+----------+ [14:52:44]| 827462 | [14:52:44]+----------+ [14:52:44]1 row in set (0.09 sec) [14:52:44] 6分钟恢复了22万数据，速度确实比myflash慢了太多。 MyFlash记录的是压缩格式的日志，而binlog2sql则是逐条处理的语句，这应该是速度差异的一直较为直观的原因了。参考文档： https://mp.weixin.qq.com/s?__biz=MjM5NjQ5MTI5OA==&mid=2651747096&idx=1&sn=e561ce9254f69c3fcd04c73b44e56e69&chksm=bd12aa558a65234332298e1d611da408ec45afb2d08ecd22078a600379525e75b2fa13149fb0&mpshare=1&scene=23&srcid=1116o1gBWoNf47Rv1E1ngYir#rd https://github.com/Meituan-Dianping/MyFlash