首页 > > 网络编程 > Mysql >

利用mysqlbinlog_flashback闪回丢失数据

2018-06-18 01:41:34来源：未知阅读 ()

today,i'll have a test with the open source tool mysqlbinlog_flashback which is released by 58daojia,here's the github address:https://github.com/58daojia-dba/mysqlbinlog_flashback this tool is similar with the binlog2sql which is also coded by python.let's download and unzip the tool:

 1 [root@zlm1 15:42:35 /vagrant]
 2 #cd mysqlbinlog_flashback-master
 3 
 4 [root@zlm1 15:42:46 /vagrant/mysqlbinlog_flashback-master]
 5 #ls -l
 6 total 70
 7 -rwxrwxrwx 1 vagrant vagrant  4766 Dec 19  2016 binlogstream.py.diff
 8 -rwxrwxrwx 1 vagrant vagrant   524 Dec 19  2016 CHANGELOG.txt
 9 -rwxrwxrwx 1 vagrant vagrant  1365 Dec 19  2016 constant.py
10 -rwxrwxrwx 1 vagrant vagrant 13730 Dec 19  2016 flashback.py
11 -rwxrwxrwx 1 vagrant vagrant  1800 Dec 19  2016 func.py
12 drwxrwxrwx 1 vagrant vagrant     0 Jun  8 15:41 internal
13 -rwxrwxrwx 1 vagrant vagrant 10015 Dec 19  2016 joint_sql.py
14 -rwxrwxrwx 1 vagrant vagrant 11357 Dec 19  2016 LICENSE
15 drwxrwxrwx 1 vagrant vagrant     0 Jun  8 15:41 log
16 -rwxrwxrwx 1 vagrant vagrant  9192 Dec 19  2016 mysqlbinlog_back.py
17 -rwxrwxrwx 1 vagrant vagrant  2782 Dec 19  2016 mysql_table.py
18 drwxrwxrwx 1 vagrant vagrant  4096 Jun  8 15:41 pymysqlreplication
19 -rwxrwxrwx 1 vagrant vagrant  5033 Dec 19  2016 README.md
20 drwxrwxrwx 1 vagrant vagrant  4096 Jun  8 15:41 test
21 
22 [root@zlm1 15:42:47 /vagrant/mysqlbinlog_flashback-master]
23 #python mysqlbinlog_back.py --help
24 Traceback (most recent call last):
25   File "mysqlbinlog_back.py", line 12, in <module>
26     from flashback import Parameter,deal_all_event,generate_create_table,convert_datetime_to_timestamp
27   File "/vagrant/mysqlbinlog_flashback-master/flashback.py", line 13, in <module>
28     from pymysqlreplication import BinLogStreamReader
29   File "/vagrant/mysqlbinlog_flashback-master/pymysqlreplication/__init__.py", line 23, in <module>
30     from .binlogstream import BinLogStreamReader
31   File "/vagrant/mysqlbinlog_flashback-master/pymysqlreplication/binlogstream.py", line 3, in <module>
32     import pymysql
33 ImportError: No module named pymysql
34 
35 [root@zlm1 15:43:06 /vagrant/mysqlbinlog_flashback-master]
36 #python --version
37 Python 2.7.5
38 
39 [root@zlm1 16:02:51 /vagrant/mysqlbinlog_flashback-master]
40 #

the tool need python 2.6,mine is 2.7.5,i'll go to the python official website to download it,Python 2.6.9 is the final version of 2.6.x since Oct.29 2013.here's the address:https://www.python.org/download/releases/2.6.9/,below is the directory structure in Python 2.6.9:

 1 [root@zlm1 16:29:12 ~/Python-2.6.9]
 2 #ls -l
 3 total 788
 4 -rwxr-xr-x  1 root root 380641 Oct 29  2013 configure
 5 -rw-r--r--  1 root root 112213 Oct 29  2013 configure.in
 6 drwxrwxr-x 23 root root   4096 Oct 29  2013 Demo
 7 drwxrwxr-x 17 root root   4096 Oct 29  2013 Doc
 8 drwxrwxr-x  2 root root     20 Oct 29  2013 Grammar
 9 drwxrwxr-x  2 root root   4096 Oct 29  2013 Include
10 -rwxr-xr-x  1 root root   7122 Oct 29  2013 install-sh
11 drwxrwxr-x 43 root root   8192 Oct 29  2013 Lib
12 -rw-r--r--  1 root root  14411 Oct 29  2013 LICENSE
13 drwxrwxr-x 11 root root   4096 Oct 29  2013 Mac
14 -rw-r--r--  1 root root  39341 Oct 29  2013 Makefile.pre.in
15 drwxrwxr-x  5 root root   4096 Oct 29  2013 Misc
16 drwxrwxr-x  8 root root   4096 Oct 29  2013 Modules
17 drwxrwxr-x  3 root root   4096 Oct 29  2013 Objects
18 drwxrwxr-x  2 root root   4096 Oct 29  2013 Parser
19 drwxrwxr-x 10 root root   4096 Oct 29  2013 PC
20 drwxrwxr-x  2 root root   4096 Oct 29  2013 PCbuild
21 -rw-r--r--  1 root root  29620 Oct 29  2013 pyconfig.h.in
22 drwxrwxr-x  2 root root   4096 Oct 29  2013 Python
23 -rw-r--r--  1 root root  55033 Oct 29  2013 README
24 drwxrwxr-x  5 root root    142 Oct 29  2013 RISCOS
25 -rw-r--r--  1 root root  87576 Oct 29  2013 setup.py
26 drwxrwxr-x 20 root root   4096 Oct 29  2013 Tools
27 
28 [root@zlm1 16:29:13 ~/Python-2.6.9]
29 #./configure && make && make install
30 ... -- Omitted
31  [root@zlm1 16:38:41 ~/Python-2.6.9]
32 #python --version
33 Python 2.6.9
34 
35 [root@zlm1 16:38:49 ~/Python-2.6.9]
36 #[root@zlm1 16:35:40 ~/mysqlbinlog_flashback-master]
37 #pwd
38 /root/mysqlbinlog_flashback-master
39 
40 [root@zlm1 16:39:21 ~/mysqlbinlog_flashback-master]
41 #python mysqlbinlog_back.py --help
42 Traceback (most recent call last):
43   File "mysqlbinlog_back.py", line 12, in <module>
44     from flashback import Parameter,deal_all_event,generate_create_table,convert_datetime_to_timestamp
45   File "/root/mysqlbinlog_flashback-master/flashback.py", line 13, in <module>
46     from pymysqlreplication import BinLogStreamReader
47   File "/root/mysqlbinlog_flashback-master/pymysqlreplication/__init__.py", line 23, in <module>
48     from .binlogstream import BinLogStreamReader
49   File "/root/mysqlbinlog_flashback-master/pymysqlreplication/binlogstream.py", line 3, in <module>
50     import pymysql
51 ImportError: No module named pymysql
52 
53 [root@zlm1 16:39:38 ~/mysqlbinlog_flashback-master]
54 #

it still appear the identical issue,what's wrong?once more i check the readme.md it says besides the python version,"pymysql" module is also necessary.

1 [root@zlm1 16:59:16 ~/pip-10.0.1]
2 #pip install pymysql
3 Traceback (most recent call last):
4   File "/usr/bin/pip", line 5, in <module>
5     from pkg_resources import load_entry_point
6 ImportError: No module named pkg_resources
7 
8 [root@zlm1 16:59:33 ~/pip-10.0.1]
9 #

  1 [root@zlm1 17:10:16 ~/PyMySQL-0.8.1]
  2 #ls -l
  3 total 52
  4 -rw-r--r-- 1 501 games 8006 May  7 12:22 CHANGELOG
  5 -rw-r--r-- 1 501 games  320 May 18  2016 example.py
  6 -rw-r--r-- 1 501 games 1070 Nov 27  2013 LICENSE
  7 -rw-r--r-- 1 501 games   84 Jan 14  2015 MANIFEST.in
  8 -rw-r--r-- 1 501 games 6051 May  7 12:27 PKG-INFO
  9 drwxr-xr-x 4 501 games 4096 May  7 12:27 pymysql
 10 drwxr-xr-x 2 501 games  101 May  7 12:27 PyMySQL.egg-info
 11 -rw-r--r-- 1 501 games 3879 May  7 12:10 README.rst
 12 -rwxr-xr-x 1 501 games  704 Aug 29  2016 runtests.py
 13 -rw-r--r-- 1 501 games  145 May  7 12:27 setup.cfg
 14 -rwxr-xr-x 1 501 games 1491 May  7 12:26 setup.py
 15 -rw-r--r-- 1 501 games  184 Jan 13  2017 tox.ini
 16 
 17 [root@zlm1 17:10:17 ~/PyMySQL-0.8.1]
 18 #python setup.py install
 19 Traceback (most recent call last):
 20   File "setup.py", line 3, in <module>
 21     from setuptools import setup, find_packages
 22 ImportError: No module named setuptools
 23 
 24 [root@zlm1 17:10:32 ~/PyMySQL-0.8.1]
 25 #chown root.root -R *
 26 
 27 [root@zlm1 17:11:23 ~/PyMySQL-0.8.1]
 28 #ls -l
 29 total 52
 30 -rw-r--r-- 1 root root 8006 May  7 12:22 CHANGELOG
 31 -rw-r--r-- 1 root root  320 May 18  2016 example.py
 32 -rw-r--r-- 1 root root 1070 Nov 27  2013 LICENSE
 33 -rw-r--r-- 1 root root   84 Jan 14  2015 MANIFEST.in
 34 -rw-r--r-- 1 root root 6051 May  7 12:27 PKG-INFO
 35 drwxr-xr-x 4 root root 4096 May  7 12:27 pymysql
 36 drwxr-xr-x 2 root root  101 May  7 12:27 PyMySQL.egg-info
 37 -rw-r--r-- 1 root root 3879 May  7 12:10 README.rst
 38 -rwxr-xr-x 1 root root  704 Aug 29  2016 runtests.py
 39 -rw-r--r-- 1 root root  145 May  7 12:27 setup.cfg
 40 -rwxr-xr-x 1 root root 1491 May  7 12:26 setup.py
 41 -rw-r--r-- 1 root root  184 Jan 13  2017 tox.ini
 42 
 43 [root@zlm1 17:11:25 ~/PyMySQL-0.8.1]
 44 #python setup.py install
 45 Traceback (most recent call last):
 46   File "setup.py", line 3, in <module>
 47     from setuptools import setup, find_packages
 48 ImportError: No module named setuptools  -- It seems another module name "setuptools" is needed.
 49 
 50 [root@zlm1 17:22:25 ~/setuptools-39.2.0]
 51 #ls -l
 52 total 192
 53 -rwxrwxrwx 1 root root   1669 May 19 13:18 bootstrap.py
 54 -rwxrwxrwx 1 root root 113612 May 19 13:18 CHANGES.rst
 55 -rwxrwxrwx 1 root root    223 May 19 13:18 conftest.py
 56 drwxrwxrwx 4 root root   4096 Jun  8 17:18 docs
 57 -rwxrwxrwx 1 root root    126 May 19 13:18 easy_install.py
 58 -rwxrwxrwx 1 root root  10317 May 19 13:18 launcher.c
 59 -rwxrwxrwx 1 root root   1078 May 19 13:18 LICENSE
 60 -rwxrwxrwx 1 root root    430 May 19 13:18 MANIFEST.in
 61 -rwxrwxrwx 1 root root   1640 May 19 13:18 msvc-build-launcher.cmd
 62 -rwxrwxrwx 1 root root   1438 May 19 13:18 pavement.py
 63 -rwxrwxrwx 1 root root   3160 May 19 13:19 PKG-INFO
 64 drwxrwxrwx 5 root root    103 Jun  8 17:18 pkg_resources
 65 -rwxrwxrwx 1 root root    493 May 19 13:18 pytest.ini
 66 -rwxrwxrwx 1 root root   1545 May 19 13:18 README.rst
 67 -rwxrwxrwx 1 root root    434 May 19 13:19 setup.cfg
 68 -rwxrwxrwx 1 root root   7365 May 19 13:18 setup.py
 69 drwxrwxrwx 6 root root   4096 Jun  8 17:18 setuptools
 70 drwxrwxrwx 2 root root    143 Jun  8 17:18 setuptools.egg-info
 71 drwxrwxrwx 2 root root     46 Jun  8 17:18 tests
 72 -rwxrwxrwx 1 root root    662 May 19 13:18 towncrier_template.rst
 73 -rwxrwxrwx 1 root root   1391 May 19 13:18 tox.ini
 74 
 75 [root@zlm1 17:22:26 ~/setuptools-39.2.0]
 76 #python setup.py install
 77 running install
 78 running bdist_egg
 79 running egg_info
 80 writing requirements to setuptools.egg-info/requires.txt
 81 writing setuptools.egg-info/PKG-INFO
 82 writing top-level names to setuptools.egg-info/top_level.txt
 83 writing dependency_links to setuptools.egg-info/dependency_links.txt
 84 writing entry points to setuptools.egg-info/entry_points.txt
 85 reading manifest file 'setuptools.egg-info/SOURCES.txt'
 86 reading manifest template 'MANIFEST.in'
 87 writing manifest file 'setuptools.egg-info/SOURCES.txt'
 88 installing library code to build/bdist.linux-x86_64/egg
 89 running install_lib
 90 running build_py
 91 creating build
 92 creating build/lib
 93 ... -- Omitted.
 94  
 95 root@zlm1 17:25:21 ~/PyMySQL-0.8.1]
 96 #python setup.py install
 97 /usr/lib64/python2.7/distutils/dist.py:267: UserWarning: Unknown distribution option: 'project_urls'
 98   warnings.warn(msg)
 99 running install
100 running bdist_egg
101 running egg_info
102 writing PyMySQL.egg-info/PKG-INFO
103 writing top-level names to PyMySQL.egg-info/top_level.txt
104 writing dependency_links to PyMySQL.egg-info/dependency_links.txt
105 reading manifest file 'PyMySQL.egg-info/SOURCES.txt'
106 reading manifest template 'MANIFEST.in'
107 writing manifest file 'PyMySQL.egg-info/SOURCES.txt'
108 installing library code to build/bdist.linux-x86_64/egg
109 running install_lib
110 running build_py
111 creating build
112 creating build/lib
113 creating build/lib/pymysql
114 ... -- Omitted.
115  
116 --Finally the PyMySQL was installed successfully.

 1 [root@zlm1 17:31:37 ~/mysqlbinlog_flashback-master]
 2 #python mysqlbinlog_back.py --help
 3 ===log will also  write to .//mysqlbinlog_flashback.log===
 4 Usage: python mysqlbinlog_back.py [options]
 5 sample1:python  mysqlbinlog_back.py --host="127.0.0.1" --username="root" --port=43306 --password="" --schema=test --table="test5"
 6 sample2:python mysqlbinlog_back.py --host="127.0.0.1" --username="root" --port=43306 --password="" --schema=test --table="test5,test6" --binlog_end_time="2016-11-05 11:27:13" --binlog_start_file_name="mysql-bin.000024"  --binlog_start_file_position=4 --binlog_start_time="2016-11-04 11:27:13"  --skip_delete  --skip_insert --add_schema_name
 7 sample3:python mysqlbinlog_back.py  --host="127.0.0.1" --username="root" --port=43306 --password="" --schema=test --table="test5,test6" --binlog_start_file_name="mysql-bin.000022"
 8 
 9 Options:
10   -h, --help            show this help message and exit
11   -H HOST, --host=HOST  mandatory,mysql hostname
12   -P PORT, --port=PORT  mysql port,default 3306
13   -u USERNAME, --username=USERNAME
14                         mandatory,username
15   -p PASSWORD, --password=PASSWORD
16                         password
17   -s SCHEMA, --schema=SCHEMA
18                         mandatory,mysql schema
19   -t TABLES, --tables=TABLES
20                         mandatory,mysql tables,suport multiple tables,use
21                         comma as separator
22   -N BINLOG_END_TIME, --binlog_end_time=BINLOG_END_TIME
23                         binlog end time,format yyyy-mm-dd hh24:mi:ss,default
24                         is current time
25   -S BINLOG_START_FILE_NAME, --binlog_start_file_name=BINLOG_START_FILE_NAME
26                         binlog start file name,default is current logfile of
27                         db
28   -L BINLOG_START_FILE_POSITION, --binlog_start_file_position=BINLOG_START_FILE_POSITION
29                         binlog start file name
30   -E BINLOG_START_TIME, --binlog_start_time=BINLOG_START_TIME
31                         binlog start time,format yyyy-mm-dd hh24:mi:ss
32   -l OUTPUT_FILE_PATH, --output_file_path=OUTPUT_FILE_PATH
33                         file path that sql generated,,default ./log
34   -I, --skip_insert     skip insert(WriteRowsEvent) event
35   -U, --skip_update     skip update(UpdateRowsEvent) event
36   -D, --skip_delete     skip delete(DeleteRowsEvent) event
37   -a, --add_schema_name
38                         add schema name for flashback sql
39   -v, --version         version info

View Code

okay,now the tool mysqlbinlog_flashback can te used normally,let's begin our data-loss flashback test:

  1 root@localhost:mysql3306.sock [zlm]05:39:39>create table test(
  2     -> id bigint not null auto_increment,
  3     -> name varchar(20) not null default '',
  4     -> primary key(id)
  5     -> ) engine=innodb default charset=utf8mb4;
  6 Query OK, 0 rows affected (0.05 sec)
  7 
  8 root@localhost:mysql3306.sock [zlm]05:39:45>delimiter //
  9 root@localhost:mysql3306.sock [zlm]05:39:51>create procedure pro_insert()
 10     -> begin
 11     -> declare id int;
 12     -> set id = 100000;
 13     -> while id>0 do
 14     -> insert into test(name) values ('aaron8219');  
 15     -> set id=id-1;               
 16     -> end while;
 17     -> end //
 18 Query OK, 0 rows affected (0.05 sec)
 19 
 20 root@localhost:mysql3306.sock [zlm]05:39:51>delimiter ;
 21 root@localhost:mysql3306.sock [zlm]05:39:53>call pro_insert();
 22 Query OK, 1 row affected (8.87 sec)
 23 
 24 root@localhost:mysql3306.sock [zlm]05:40:40>select count(*) from test;
 25 +----------+
 26 | count(*) |
 27 +----------+
 28 |   100000 |
 29 +----------+
 30 1 row in set (0.03 sec)
 31 
 32 root@localhost:mysql3306.sock [zlm]05:41:02>flush logs;
 33 Query OK, 0 rows affected (0.12 sec)
 34 
 35 root@localhost:mysql3306.sock [zlm]05:42:03>show binary log;
 36 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'log' at line 1
 37 root@localhost:mysql3306.sock [zlm]05:42:10>show binary logs;
 38 +------------------+-----------+
 39 | Log_name         | File_size |
 40 +------------------+-----------+
 41 | mysql-bin.000016 |       680 |
 42 | mysql-bin.000017 | 268435617 |
 43 | mysql-bin.000018 |  72724171 |
 44 | mysql-bin.000019 |       241 |
 45 | mysql-bin.000020 |  11019314 |
 46 | mysql-bin.000021 |       241 |
 47 | mysql-bin.000022 |   1305983 |
 48 | mysql-bin.000023 | 268435590 |
 49 | mysql-bin.000024 |   9566499 |
 50 | mysql-bin.000025 |  19081875 |
 51 | mysql-bin.000026 |  27801367 |
 52 | mysql-bin.000027 |   1908620 |
 53 | mysql-bin.000028 |   1908617 |
 54 | mysql-bin.000029 |   8337364 |
 55 | mysql-bin.000030 |  83100241 |
 56 | mysql-bin.000031 |   3214613 |
 57 | mysql-bin.000032 |       241 |
 58 | mysql-bin.000033 |       217 |
 59 | mysql-bin.000034 |       217 |
 60 | mysql-bin.000035 |       217 |
 61 | mysql-bin.000036 |  27000879 |
 62 | mysql-bin.000037 |       194 |
 63 +------------------+-----------+
 64 22 rows in set (0.00 sec)
 65 
 66 root@localhost:mysql3306.sock [zlm]05:42:14>update test set name='zlm';
 67 Query OK, 100000 rows affected (1.83 sec)
 68 Rows matched: 100000  Changed: 100000  Warnings: 0
 69 
 70 root@localhost:mysql3306.sock [zlm]05:42:38>select * from test limit 1,5;
 71 +----+------+
 72 | id | name |
 73 +----+------+
 74 |  2 | zlm  |
 75 |  3 | zlm  |
 76 |  4 | zlm  |
 77 |  5 | zlm  |
 78 |  6 | zlm  |
 79 +----+------+
 80 5 rows in set (0.00 sec)
 81 
 82 root@localhost:mysql3306.sock [zlm]05:43:02>show binary logs;
 83 +------------------+-----------+
 84 | Log_name         | File_size |
 85 +------------------+-----------+
 86 | mysql-bin.000016 |       680 |
 87 | mysql-bin.000017 | 268435617 |
 88 | mysql-bin.000018 |  72724171 |
 89 | mysql-bin.000019 |       241 |
 90 | mysql-bin.000020 |  11019314 |
 91 | mysql-bin.000021 |       241 |
 92 | mysql-bin.000022 |   1305983 |
 93 | mysql-bin.000023 | 268435590 |
 94 | mysql-bin.000024 |   9566499 |
 95 | mysql-bin.000025 |  19081875 |
 96 | mysql-bin.000026 |  27801367 |
 97 | mysql-bin.000027 |   1908620 |
 98 | mysql-bin.000028 |   1908617 |
 99 | mysql-bin.000029 |   8337364 |
100 | mysql-bin.000030 |  83100241 |
101 | mysql-bin.000031 |   3214613 |
102 | mysql-bin.000032 |       241 |
103 | mysql-bin.000033 |       217 |
104 | mysql-bin.000034 |       217 |
105 | mysql-bin.000035 |       217 |
106 | mysql-bin.000036 |  27000879 |
107 | mysql-bin.000037 |   3214558 |
108 +------------------+-----------+
109 22 rows in set (0.00 sec)
110 
111 root@localhost:mysql3306.sock [zlm]05:43:22>

obviously,the lost data should be contained in "mysql-bin.000037",let's have a try by using mysqlbinlog_flash to get back the data,first of all,let's see what's in the binary log "mysql-bin.000037"：

 1 [root@zlm1 17:52:12 /data/mysql/mysql3306/logs]
 2 #mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000037 | sed -n '1,50p'
 3 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
 4 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 5 DELIMITER /*!*/;
 6 # at 4
 7 #180608 17:42:03 server id 1003306  end_log_pos 123 CRC32 0xf0e90436     Start: binlog v 4, server v 5.7.21-log created 180608 17:42:03
 8 # Warning: this binlog is either in use or was not closed properly.
 9 # at 123
10 #180608 17:42:03 server id 1003306  end_log_pos 194 CRC32 0xb6bac207     Previous-GTIDs
11 # 2a4b3562-2ab6-11e8-be7a-080027de0e0e:5-2600051
12 # at 194
13 #180608 17:42:36 server id 1003306  end_log_pos 259 CRC32 0x67866ae9     GTID    last_committed=0    sequence_number=1    rbr_only=yes
14 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
15 SET @@SESSION.GTID_NEXT= '2a4b3562-2ab6-11e8-be7a-080027de0e0e:2600052'/*!*/;
16 # at 259
17 #180608 17:42:36 server id 1003306  end_log_pos 330 CRC32 0xd92b1815     Query    thread_id=3    exec_time=0    error_code=0
18 SET TIMESTAMP=1528472556/*!*/;
19 SET @@session.pseudo_thread_id=3/*!*/;
20 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
21 SET @@session.sql_mode=1436549152/*!*/;
22 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
23 /*!\C utf8 *//*!*/;
24 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
25 SET @@session.lc_time_names=0/*!*/;
26 SET @@session.collation_database=DEFAULT/*!*/;
27 BEGIN
28 /*!*/;
29 # at 330
30 #180608 17:42:36 server id 1003306  end_log_pos 379 CRC32 0xcfa8815d     Table_map: `zlm`.`test` mapped to number 113  -- The Table_map event from position 330.
31 # at 379
32 #180608 17:42:36 server id 1003306  end_log_pos 8575 CRC32 0xadcde7b6     Update_rows: table id 113  -- The Update_rows event from postion 379.
33 # at 8575
34 #180608 17:42:36 server id 1003306  end_log_pos 16771 CRC32 0xee29017d     Update_rows: table id 113
35 # at 16771
36 #180608 17:42:36 server id 1003306  end_log_pos 24967 CRC32 0x6306a3c2     Update_rows: table id 113
37 # at 24967
38 #180608 17:42:36 server id 1003306  end_log_pos 33163 CRC32 0x69ca971f     Update_rows: table id 113
39 # at 33163
40 #180608 17:42:36 server id 1003306  end_log_pos 41359 CRC32 0xb99e0b43     Update_rows: table id 113
41 # at 41359
42 #180608 17:42:36 server id 1003306  end_log_pos 49555 CRC32 0xe8438e0a     Update_rows: table id 113
43 # at 49555
44 #180608 17:42:36 server id 1003306  end_log_pos 57751 CRC32 0x2a83a31d     Update_rows: table id 113
45 # at 57751
46 #180608 17:42:36 server id 1003306  end_log_pos 65947 CRC32 0xbcb85d9c     Update_rows: table id 113
47 # at 65947
48 #180608 17:42:36 server id 1003306  end_log_pos 74143 CRC32 0xfd385e6b     Update_rows: table id 113
49 # at 74143
50 #180608 17:42:36 server id 1003306  end_log_pos 82339 CRC32 0x5dd0ec46     Update_rows: table id 113
51 # at 82339
52 #180608 17:42:36 server id 1003306  end_log_pos 90535 CRC32 0x14c028cb     Update_rows: table id 113
53 -- Omitted

 1 [root@zlm1 18:02:40 ~/mysqlbinlog_flashback-master]
 2 #pwd
 3 /root/mysqlbinlog_flashback-master
 4 
 5 [root@zlm1 18:02:41 ~/mysqlbinlog_flashback-master]
 6 #python mysqlbinlog_back.py --host="127.0.0.1" --username="root" --port=3306 --password="Passw0rd" --schema=zlm --table="test" --add_schema_name
 7 ===log will also  write to .//mysqlbinlog_flashback.log===
 8 parameter={'start_binlog_file': u'mysql-bin.000037', 'stream': None, 'keep_data': True, 'file': {'data_create': None, 'flashback': None, 'data': None}, 'add_schema_name': True, 'start_time': None, 'keep_current_data': False, 'start_to_timestamp': None, 'mysql_setting': {'passwd': 'Passw0rd', 'host': '127.0.0.1', 'charset': 'utf8', 'port': 3306, 'user': 'root'}, 'table_name': 'test', 'skip_delete': False, 'schema': 'zlm', 'stat': {'flash_sql': {}}, 'table_name_array': ['test'], 'one_binlog_file': False, 'output_file_path': './log', 'start_position': 4, 'skip_update': False, 'dump_event': False, 'end_to_timestamp': 1528473771.0, 'skip_insert': False, 'schema_array': ['zlm']}
 9 ===statistics===
10 scan 400 events 
11 {'flash_sql': {u'zlm': {u'test': {'insert': 0, 'update': 100000, 'delete': 0}}}}
12 
13 [root@zlm1 18:03:13 ~/mysqlbinlog_flashback-master]
14 #ls -l
15 total 136
16 -rwxrwxrwx 1 root root  4766 Dec 19  2016 binlogstream.py.diff
17 -rwxrwxrwx 1 root root   524 Dec 19  2016 CHANGELOG.txt
18 -rwxrwxrwx 1 root root  1365 Dec 19  2016 constant.py
19 -rw-r--r-- 1 root root  1255 Jun  8 17:31 constant.pyc
20 -rwxrwxrwx 1 root root 13730 Dec 19  2016 flashback.py
21 -rwxrwxrwx 1 root root 12702 Jun  8 15:57 flashback.pyc
22 -rwxrwxrwx 1 root root  1800 Dec 19  2016 func.py
23 -rwxrwxrwx 1 root root  1787 Jun  8 15:57 func.pyc
24 drwxrwxrwx 2 root root    35 Jun  8 15:41 internal
25 -rwxrwxrwx 1 root root 10015 Dec 19  2016 joint_sql.py
26 -rw-r--r-- 1 root root 10250 Jun  8 17:31 joint_sql.pyc
27 -rwxrwxrwx 1 root root 11357 Dec 19  2016 LICENSE
28 drwxrwxrwx 2 root root  4096 Jun  8 18:02 log  -- If you don't sepcify the "OUTPUT_FILE_PATH",this directory is default value.
29 -rwxrwxrwx 1 root root  9192 Dec 19  2016 mysqlbinlog_back.py
30 -rw-r--r-- 1 root root  1407 Jun  8 18:03 mysqlbinlog_flashback.log
31 -rwxrwxrwx 1 root root  2782 Dec 19  2016 mysql_table.py
32 -rw-r--r-- 1 root root  3469 Jun  8 17:31 mysql_table.pyc
33 drwxrwxrwx 4 root root  4096 Jun  8 17:31 pymysqlreplication
34 -rwxrwxrwx 1 root root  5033 Dec 19  2016 README.md
35 drwxrwxrwx 3 root root   131 Jun  8 15:41 test
36 
37 [root@zlm1 18:03:22 ~/mysqlbinlog_flashback-master]
38 #cd log
39 
40 [root@zlm1 18:03:24 ~/mysqlbinlog_flashback-master/log]
41 #ls -l
42 total 22676
43 -rw-r--r-- 1 root root  7105559 Jun  8 18:03 flashback_zlm_20180608_180251.sql  -- This file contains the real records to flashback.
44 -rw-r--r-- 1 root root      187 Jun  8 18:03 save_data_create_table_zlm_20180608_180251.sql  -- This file contains the structure of intermediate table.
45 -rw-r--r-- 1 root root 16105559 Jun  8 18:03 save_data_dml_zlm_20180608_180251.sql -- This file contains the records which will be inserted into the intermediate table which called "_test_keep_data_"(here,"test" is the real table name which you want to flashback data)
46 -rwxrwxrwx 1 root root        0 Dec 19  2016 test.txt
47 
48 [root@zlm1 18:03:25 ~/mysqlbinlog_flashback-master/log]
49 #cat save_data_create_table_zlm_20180608_180251.sql
50 CREATE TABLE `_test_keep_data_` (op varchar(64),op_datetime datetime,bfr_id bigint(20),bfr_name varchar(20),aft_id bigint(20),aft_name varchar(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
51 
52 [root@zlm1 18:08:00 ~/mysqlbinlog_flashback-master/log]
53 #cat save_data_dml_zlm_20180608_180251.sql | tail -10
54 insert into `_test_keep_data_`(`op_datetime`,`bfr_name`,`aft_id`,`aft_name`,`bfr_id`,`op`) values('2018-06-08 17:42:36','aaron8219',99991,'zlm',99991,'update');
55 insert into `_test_keep_data_`(`op_datetime`,`bfr_name`,`aft_id`,`aft_name`,`bfr_id`,`op`) values('2018-06-08 17:42:36','aaron8219',99992,'zlm',99992,'update');
56 insert into `_test_keep_data_`(`op_datetime`,`bfr_name`,`aft_id`,`aft_name`,`bfr_id`,`op`) values('2018-06-08 17:42:36','aaron8219',99993,'zlm',99993,'update');
57 insert into `_test_keep_data_`(`op_datetime`,`bfr_name`,`aft_id`,`aft_name`,`bfr_id`,`op`) values('2018-06-08 17:42:36','aaron8219',99994,'zlm',99994,'update');
58 insert into `_test_keep_data_`(`op_datetime`,`bfr_name`,`aft_id`,`aft_name`,`bfr_id`,`op`) values('2018-06-08 17:42:36','aaron8219',99995,'zlm',99995,'update');
59 insert into `_test_keep_data_`(`op_datetime`,`bfr_name`,`aft_id`,`aft_name`,`bfr_id`,`op`) values('2018-06-08 17:42:36','aaron8219',99996,'zlm',99996,'update');
60 insert into `_test_keep_data_`(`op_datetime`,`bfr_name`,`aft_id`,`aft_name`,`bfr_id`,`op`) values('2018-06-08 17:42:36','aaron8219',99997,'zlm',99997,'update');
61 insert into `_test_keep_data_`(`op_datetime`,`bfr_name`,`aft_id`,`aft_name`,`bfr_id`,`op`) values('2018-06-08 17:42:36','aaron8219',99998,'zlm',99998,'update');
62 insert into `_test_keep_data_`(`op_datetime`,`bfr_name`,`aft_id`,`aft_name`,`bfr_id`,`op`) values('2018-06-08 17:42:36','aaron8219',99999,'zlm',99999,'update');
63 insert into `_test_keep_data_`(`op_datetime`,`bfr_name`,`aft_id`,`aft_name`,`bfr_id`,`op`) values('2018-06-08 17:42:36','aaron8219',100000,'zlm',100000,'update');
64 
65 [root@zlm1 18:08:33 ~/mysqlbinlog_flashback-master/log]
66 #cat flashback_zlm_20180608_180251.sql | tail -10
67 update `zlm`.`test` set`id`=99991,`name`='aaron8219' where `id`=99991;
68 update `zlm`.`test` set`id`=99992,`name`='aaron8219' where `id`=99992;
69 update `zlm`.`test` set`id`=99993,`name`='aaron8219' where `id`=99993;
70 update `zlm`.`test` set`id`=99994,`name`='aaron8219' where `id`=99994;
71 update `zlm`.`test` set`id`=99995,`name`='aaron8219' where `id`=99995;
72 update `zlm`.`test` set`id`=99996,`name`='aaron8219' where `id`=99996;
73 update `zlm`.`test` set`id`=99997,`name`='aaron8219' where `id`=99997;
74 update `zlm`.`test` set`id`=99998,`name`='aaron8219' where `id`=99998;
75 update `zlm`.`test` set`id`=99999,`name`='aaron8219' where `id`=99999;
76 update `zlm`.`test` set`id`=100000,`name`='aaron8219' where `id`=100000;
77 
78 [root@zlm1 18:08:50 ~/mysqlbinlog_flashback-master/log]
79 #

since we've got the sql file which can flashback our lost data,let's have a try:

 1 [root@zlm1 18:08:50 ~/mysqlbinlog_flashback-master/log]
 2 #mysql --default-character-set=utf8mb4 < flashback_zlm_20180608_180251.sql
 3 
 4 [root@zlm1 18:19:59 ~/mysqlbinlog_flashback-master/log]
 5 #mysql
 6 Welcome to the MySQL monitor.  Commands end with ; or \g.
 7 Your MySQL connection id is 14
 8 Server version: 5.7.21-log MySQL Community Server (GPL)
 9 
10 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
11 
12 Oracle is a registered trademark of Oracle Corporation and/or its
13 affiliates. Other names may be trademarks of their respective
14 owners.
15 
16 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
17 
18 root@localhost:mysql3306.sock [(none)]06:20:09>use zlm;
19 Reading table information for completion of table and column names
20 You can turn off this feature to get a quicker startup with -A
21 
22 Database changed
23 root@localhost:mysql3306.sock [zlm]06:20:12>select count(*) from test where name='zlm';
24 +----------+
25 | count(*) |
26 +----------+
27 |        0 |
28 +----------+
29 1 row in set (0.03 sec)
30 
31 root@localhost:mysql3306.sock [zlm]06:20:49>select count(*) from test where name='aaron8219';
32 +----------+
33 | count(*) |
34 +----------+
35 |   100000 |
36 +----------+
37 1 row in set (0.03 sec)
38 
39 root@localhost:mysql3306.sock [zlm]06:20:55>

finally,the lost data come back again,all the column of "name" of the 100000 rows have been changed from "zlm" to "aaron8219",the test finished successfully.

Summary:

mysqlbinlog_flashback is similar with the binglog2sql which also depend on python 2.6(maybe above is also feasible),you must install several python module such as PyMySQL,setuptools to ensure the tool can work normally.
mysqlbinlog_flashback is surely non-offline tool when parsing the needed binary logs which is the same as MyFlash,binlog2sql.
mysqlbinlog_flashback works effectively,10w rows of records can be flashback in a rather short time.
what i most favorate point is that the output file is extraordinarily unambiguous,you can clearly see what it will really do by these sql files(check them first then flashback).
contrast the tool with MyFlash & binlog2sql,mysqlbinlog_flashback is the most convenient tool to flashback lost data.the other two tools are difficult to dignose when exception occurs.they seem like black boxes i'm afraid.