微信文章中看到了介紹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)

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;

+----+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+----------------------------+---------------------+---------------------+----------+----------+------------+

+----+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+----------------------------+---------------------+---------------------+----------+----------+------------+

| 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;

+----+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+----------------------------+---------------------+---------------------+----------+----------+------------+

+----+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+--------------+----------------------------+---------------------+---------------------+----------+----------+------------+

| 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> 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;

+------------------+----------+--------------+------------------+-------------------------------------------+

+------------------+----------+--------------+------------------+-------------------------------------------+

+------------------+----------+--------------+------------------+-------------------------------------------+

1 row in set (0.00 sec)

mysql> delete from account limit 300000;

Query OK, 300000 rows affected (0.63 sec)

mysql>

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------------------------------+

+------------------+----------+--------------+------------------+-------------------------------------------+

+------------------+----------+--------------+------------------+-------------------------------------------+

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)

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)

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]+------------------+----------+--------------+------------------+--------------------------------------------+

[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]+------------------+----------+--------------+------------------+--------------------------------------------+

[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則是逐條處理的語句，這應該是速度差異的一直較為直觀的原因了。

參考文件：

MyFlash工具的使用實踐及其餘binlog2sql的速度比較

相關文章