mysql binlog 丢失_利用mysqlbinlog_flashback闪回丢失数据

1 [root@zlm1 18:02:40 ~/mysqlbinlog_flashback-master]2 #pwd

3 /root/mysqlbinlog_flashback-master4

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_name7 ===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 400events11 {'flash_sql': {u'zlm': {u'test': {'insert': 0, 'update': 100000, 'delete': 0}}}}12

13 [root@zlm1 18:03:13 ~/mysqlbinlog_flashback-master]14 #ls -l15 total 136

16 -rwxrwxrwx 1 root root 4766 Dec 19 2016 binlogstream.py.diff

17 -rwxrwxrwx 1 root root 524 Dec 19 2016CHANGELOG.txt18 -rwxrwxrwx 1 root root 1365 Dec 19 2016constant.py19 -rw-r--r-- 1 root root 1255 Jun 8 17:31constant.pyc20 -rwxrwxrwx 1 root root 13730 Dec 19 2016flashback.py21 -rwxrwxrwx 1 root root 12702 Jun 8 15:57flashback.pyc22 -rwxrwxrwx 1 root root 1800 Dec 19 2016func.py23 -rwxrwxrwx 1 root root 1787 Jun 8 15:57func.pyc24 drwxrwxrwx 2 root root 35 Jun 8 15:41internal25 -rwxrwxrwx 1 root root 10015 Dec 19 2016joint_sql.py26 -rw-r--r-- 1 root root 10250 Jun 8 17:31joint_sql.pyc27 -rwxrwxrwx 1 root root 11357 Dec 19 2016LICENSE28 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 2016mysqlbinlog_back.py30 -rw-r--r-- 1 root root 1407 Jun 8 18:03mysqlbinlog_flashback.log31 -rwxrwxrwx 1 root root 2782 Dec 19 2016mysql_table.py32 -rw-r--r-- 1 root root 3469 Jun 8 17:31mysql_table.pyc33 drwxrwxrwx 4 root root 4096 Jun 8 17:31pymysqlreplication34 -rwxrwxrwx 1 root root 5033 Dec 19 2016README.md35 drwxrwxrwx 3 root root 131 Jun 8 15:41test36

37 [root@zlm1 18:03:22 ~/mysqlbinlog_flashback-master]38 #cd log39

40 [root@zlm1 18:03:24 ~/mysqlbinlog_flashback-master/log]41 #ls -l42 total 22676

43 -rw-r--r-- 1 root root 7105559 Jun 8 18:03 flashback_zlm_20180608_180251.sql -- This filecontains 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 filecontains 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 whichyou want to flashback data)46 -rwxrwxrwx 1 root root 0 Dec 19 2016test.txt47

48 [root@zlm1 18:03:25 ~/mysqlbinlog_flashback-master/log]49 #catsave_data_create_table_zlm_20180608_180251.sql50 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 #