ITPub博客

首页 > 数据库 > MySQL > MyFlash工具的使用实践及其余binlog2sql的速度比较

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

原创 MySQL 作者:to_be_Dba 时间:2021-11-27 15:03:47 0 删除 编辑

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-2844433/,如需转载,请注明出处,否则将追究法律责任。

下一篇: doDBA工具的使用
请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    170
  • 访问量
    440336