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