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:,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>
如对本文有疑问, 点击进行留言回复!!
SSD原理及Pytorch代码解读——网络架构(二):特征提取网络及总体计算过程
集合类 ArrayList 和 LinkedList 直接用 Innodb(MySQL 5.0
网友评论