利用binlog2sql闪回丢失数据
程序员文章站
2022-06-05 08:19:44
today,i'll using the open source tool named "binlog2sql" which is release by danfengchao to do some flashback test. here's the github address:https:// ......
today,i'll using the open source tool named "binlog2sql" which is release by danfengchao to do some flashback test.
here's the github address:
according to the readme.md doc,using "git" command to download it at the beginning:
1 ns-notepc399+jujun@ns-notepc399 MINGW64 /d/vagrant 2 $ git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql 3 Cloning into 'binlog2sql'... 4 remote: Counting objects: 294, done. 5 remote: Total 294 (delta 0), reused 0 (delta 0), pack-reused 294 6 Receiving objects: 100% (294/294), 142.05 KiB | 108.00 KiB/s, done. 7 Resolving deltas: 100% (152/152), done. 8 ns-notepc399+jujun@ns-notepc399 MINGW64 /d/vagrant/binlog2sql (master)
secondly,"pip" command need to be installed,which can be downloaded on the official
website:
the latest version is 10.0.1 now,i choose the tar package to download.
1 [root@zlm3 07:46:15 /vagrant/pip-10.0.1] 2 #pwd 3 /vagrant/pip-10.0.1 4 5 [root@zlm3 07:46:24 /vagrant/pip-10.0.1] 6 #ls -l 7 total 104 8 -rwxrwxrwx 1 vagrant vagrant 14750 Jun 6 07:46 AUTHORS.txt 9 drwxrwxrwx 1 vagrant vagrant 0 Jun 6 07:47 build 10 drwxrwxrwx 1 vagrant vagrant 0 Jun 6 07:47 dist 11 drwxrwxrwx 1 vagrant vagrant 4096 Apr 19 20:52 docs 12 -rwxrwxrwx 1 vagrant vagrant 1110 Jun 6 07:46 LICENSE.txt 13 -rwxrwxrwx 1 vagrant vagrant 663 Jun 6 07:46 MANIFEST.in 14 -rwxrwxrwx 1 vagrant vagrant 75033 Jun 6 07:46 NEWS.rst 15 -rwxrwxrwx 1 vagrant vagrant 2972 Jun 6 07:46 PKG-INFO 16 -rwxrwxrwx 1 vagrant vagrant 886 Jun 6 07:46 pyproject.toml 17 -rwxrwxrwx 1 vagrant vagrant 1472 Jun 6 07:46 README.rst 18 -rwxrwxrwx 1 vagrant vagrant 627 Jun 6 07:46 setup.cfg 19 -rwxrwxrwx 1 vagrant vagrant 2879 Jun 6 07:46 setup.py 20 drwxrwxrwx 1 vagrant vagrant 0 Apr 19 20:52 src 21 22 [root@zlm3 07:46:37 /vagrant/pip-10.0.1] 23 #python install setup.py 24 python: can't open file 'install': [Errno 2] No such file or directory 25 26 [root@zlm3 07:46:51 /vagrant/pip-10.0.1] 27 #python setup.py install 28 /usr/lib64/python2.7/distutils/dist.py:267: UserWarning: Unknown distribution option: 'python_requires' 29 warnings.warn(msg) 30 running install 31 running bdist_egg 32 running egg_info 33 writing requirements to src/pip.egg-info/requires.txt 34 writing src/pip.egg-info/PKG-INFO 35 writing top-level names to src/pip.egg-info/top_level.txt 36 writing dependency_links to src/pip.egg-info/dependency_links.txt 37 writing entry points to src/pip.egg-info/entry_points.txt 38 reading manifest file 'src/pip.egg-info/SOURCES.txt' 39 reading manifest template 'MANIFEST.in' 40 ... -- Here,i omitted the output 'cause too many rows.
then,use the "pip" command to install necessary python package below:
1 [root@zlm3 08:54:58 /root/binlog2sql] 2 #pwd 3 /vagrant/binlog2sql 4 5 [root@zlm3 08:56:00 /root/binlog2sql] 6 #ls -l 7 total 52 8 drwxrwxrwx 2 root root 69 Jun 6 07:21 binlog2sql 9 drwxrwxrwx 2 root root 53 Jun 6 07:21 example 10 -rwxrwxrwx 1 root root 35815 Jun 6 07:21 LICENSE 11 -rwxrwxrwx 1 root root 9749 Jun 6 07:21 README.md 12 -rwxrwxrwx 1 root root 57 Jun 6 07:21 requirements.txt 13 drwxrwxrwx 2 root root 36 Jun 6 07:21 tests 14 15 [root@zlm3 08:56:20 /root/binlog2sql] 16 #cat requirements.txt 17 PyMySQL==0.7.11 18 wheel==0.29.0 19 mysql-replication==0.13 20 21 [root@zlm3 08:57:05 /root/binlog2sql] 22 #pip install -r requirements.txt 23 Collecting PyMySQL==0.7.11 (from -r requirements.txt (line 1)) 24 Downloading https://files.pythonhosted.org/packages/c6/42/c54c280d8418039bd2f61284f99cb6d9e0eae80383fc72ceb6eac67855fe/PyMySQL-0.7.11-py2.py3-none-any.whl (78kB) 25 100% |?..?..?..?..?..?..?..?..?..?..?..?..?..?..?..?..| 81kB 145kB/s 26 Collecting wheel==0.29.0 (from -r requirements.txt (line 2)) 27 Downloading https://files.pythonhosted.org/packages/8a/e9/8468cd68b582b06ef554be0b96b59f59779627131aad48f8a5bce4b13450/wheel-0.29.0-py2.py3-none-any.whl (66kB) 28 100% |?..?..?..?..?..?..?..?..?..?..?..?..?..?..?..?..| 71kB 356kB/s 29 Collecting mysql-replication==0.13 (from -r requirements.txt (line 3)) 30 Downloading https://files.pythonhosted.org/packages/dd/23/384047702e694139e9fe75a8ba7ad007e8942fd119ebadabc32ce19f70f2/mysql-replication-0.13.tar.gz 31 Installing collected packages: PyMySQL, wheel, mysql-replication 32 Running setup.py install for mysql-replication ... done 33 Successfully installed PyMySQL-0.7.11 mysql-replication-0.13 wheel-0.29.0 34 35 [root@zlm3 08:56:20 /root/binlog2sql] 36 #
the binlog2sql tool has been installed,let's begin the test now:
1 (root@localhost mysql3306.sock)[zlm]09:27:16>create table flash_test( 2 -> id int unsigned not null auto_increment, 3 -> code bigint unsigned not null default '0', 4 -> primary key(id) 5 -> ) engine=innodb charset utf8mb4; 6 Query OK, 0 rows affected (0.02 sec) 7 8 (root@localhost mysql3306.sock)[zlm]09:27:26>delimiter $$ 9 (root@localhost mysql3306.sock)[zlm]09:27:41>create procedure pro_insert (count int) 10 -> begin 11 -> declare i int unsigned default 0; 12 -> start transaction; 13 -> while i < count do 14 -> insert into flash_test(code) values(i); 15 -> set i=i+1; 16 -> end while; 17 -> commit; 18 -> end; 19 -> $$ 20 Query OK, 0 rows affected (0.00 sec) 21 22 (root@localhost mysql3306.sock)[zlm]09:27:41>delimiter ; 23 (root@localhost mysql3306.sock)[zlm]09:27:42>call pro_insert(10000); 24 Query OK, 0 rows affected (0.33 sec) 25 26 (root@localhost mysql3306.sock)[zlm]09:27:55>select count(*) from flash_test; 27 +----------+ 28 | count(*) | 29 +----------+ 30 | 10000 | 31 +----------+ 32 1 row in set (0.01 sec) 33 34 (root@localhost mysql3306.sock)[zlm]09:28:43>select * from flash_test limit 5; 35 +----+------+ 36 | id | code | 37 +----+------+ 38 | 1 | 0 | 39 | 2 | 1 | 40 | 3 | 2 | 41 | 4 | 3 | 42 | 5 | 4 | 43 +----+------+ 44 5 rows in set (0.00 sec) 45 46 (root@localhost mysql3306.sock)[zlm]09:28:50>
then,pretend to delete all the records in the table "flash_table" by accidentally:
1 (root@localhost mysql3306.sock)[zlm]09:28:50>show binary logs; 2 +------------------+-----------+ 3 | Log_name | File_size | 4 +------------------+-----------+ 5 | mysql-bin.000001 | 177 | 6 | mysql-bin.000002 | 177 | 7 | mysql-bin.000003 | 1012934 | 8 +------------------+-----------+ 9 3 rows in set (0.00 sec) 10 11 (root@localhost mysql3306.sock)[zlm]09:30:07>flush logs; 12 Query OK, 0 rows affected (0.02 sec) 13 14 (root@localhost mysql3306.sock)[zlm]09:30:21>delete from flash_test; 15 Query OK, 10000 rows affected (0.05 sec) 16 17 (root@localhost mysql3306.sock)[zlm]09:30:39>select count(*) from flash_test; 18 +----------+ 19 | count(*) | 20 +----------+ 21 | 0 | 22 +----------+ 23 1 row in set (0.00 sec) 24 25 (root@localhost mysql3306.sock)[zlm]09:30:47>show binary logs; 26 +------------------+-----------+ 27 | Log_name | File_size | 28 +------------------+-----------+ 29 | mysql-bin.000001 | 177 | 30 | mysql-bin.000002 | 177 | 31 | mysql-bin.000003 | 1012981 | 32 | mysql-bin.000004 | 130974 | 33 +------------------+-----------+ 34 4 rows in set (0.00 sec) 35 36 (root@localhost mysql3306.sock)[zlm]09:31:33>
it's the right time using the binlog2sql to flashback the missing data:
1 [root@zlm3 09:36:57 ~/binlog2sql/binlog2sql]
2 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000004'
3 Traceback (most recent call last):
4 File "binlog2sql.py", line 149, in <module>
5 back_interval=args.back_interval, only_dml=args.only_dml, sql_type=args.sql_type)
6 File "binlog2sql.py", line 46, in __init__
7 self.connection = pymysql.connect(**self.conn_setting)
8 File "/usr/lib/python2.7/site-packages/pymysql/__init__.py", line 90, in Connect
9 return Connection(*args, **kwargs)
10 File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 706, in __init__
11 self.connect()
12 File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 932, in connect
13 self._request_authentication()
14 File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1152, in _request_authentication
15 auth_packet = self._read_packet()
16 File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1014, in _read_packet
17 packet.check_error()
18 File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 393, in check_error
19 err.raise_mysql_exception(self._data)
20 File "/usr/lib/python2.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
21 raise errorclass(errno, errval)
22 pymysql.err.OperationalError: (1045, u"Access denied for user 'root'@'zlm3' (using password: YES)") --It seems the connection failure.
23
24 [root@zlm3 09:37:32 ~/binlog2sql/binlog2sql]
25 #mysql
26 Welcome to the MySQL monitor. Commands end with ; or \g.
27 Your MySQL connection id is 15
28 Server version: 5.7.21-log MySQL Community Server (GPL)
29
30 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
31
32 Oracle is a registered trademark of Oracle Corporation and/or its
33 affiliates. Other names may be trademarks of their respective
34 owners.
35
36 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
37
38 (root@localhost mysql3306.sock)[(none)]09:43:33>alter user 'root'@'192.168.1.102' identified by 'Passw0rd';
39 ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'192.168.1.102'
40 (root@localhost mysql3306.sock)[(none)]09:44:10>create user 'root'@'192.168.1.102' identified by 'Passw0rd';
41 Query OK, 0 rows affected (0.00 sec)
42
43 (root@localhost mysql3306.sock)[(none)]09:47:37>exit
44 Bye
45
46 [root@zlm3 09:47:40 ~/binlog2sql/binlog2sql]
47 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000004'
48 Traceback (most recent call last):
49 File "binlog2sql.py", line 149, in <module>
50 back_interval=args.back_interval, only_dml=args.only_dml, sql_type=args.sql_type)
51 File "binlog2sql.py", line 48, in __init__
52 cursor.execute("SHOW MASTER STATUS")
53 File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 166, in execute
54 result = self._query(query)
55 File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 322, in _query
56 conn.query(q)
57 File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 856, in query
58 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
59 File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1057, in _read_query_result
60 result.read()
61 File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1340, in read
62 first_packet = self.connection._read_packet()
63 File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1014, in _read_packet
64 packet.check_error()
65 File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 393, in check_error
66 err.raise_mysql_exception(self._data)
67 File "/usr/lib/python2.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception
68 raise errorclass(errno, errval)
69 pymysql.err.InternalError: (1227, u'Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation') --The user 'root'@'192.168.1.102' is short of imperative privileges.
70
71 [root@zlm3 09:47:43 ~/binlog2sql/binlog2sql]
72 #mysql
73 Welcome to the MySQL monitor. Commands end with ; or \g.
74 Your MySQL connection id is 17
75 Server version: 5.7.21-log MySQL Community Server (GPL)
76
77 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
78
79 Oracle is a registered trademark of Oracle Corporation and/or its
80 affiliates. Other names may be trademarks of their respective
81 owners.
82
83 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
84
85 (root@localhost mysql3306.sock)[(none)]09:49:43>grant select,replication slave,replication client on *.* to 'root'@'192.168.1.102';
86 Query OK, 0 rows affected (0.00 sec)
87
88 (root@localhost mysql3306.sock)[(none)]09:50:24>exit
89 Bye
90
91 [root@zlm3 09:50:29 ~/binlog2sql/binlog2sql]
92 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000004'
93 CREATE USER 'root'@'192.168.1.102' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F';
94 GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'192.168.1.102';
95
96 [root@zlm3 09:50:32 ~/binlog2sql/binlog2sql]
97 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000003'
98 CREATE USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E';
99 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
100 USE zlm;
101 create table flash_test(
102 id int unsigned not null auto_increment,
103 code bigint unsigned not null default '0',
104 primary key(id)
105 ) engine=innodb charset utf8mb4;
106 USE zlm;
107 CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_insert`(count int)
108 begin
109 declare i int unsigned default 0;
110 start transaction;
111 while i < count do
112 insert into flash_test(code) values(i);
113 set i=i+1;
114 end while;
115 commit;
116 end;
117
118 [root@zlm3 09:51:06 ~/binlog2sql/binlog2sql]
119 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000004' --only-dml --sql-typedelete --stop-never > flashback.sql
120 usage: binlog2sql.py [-h HOST] [-u USER] [-p PASSWORD] [-P PORT]
121 [--start-file START_FILE] [--start-position START_POS]
122 [--stop-file END_FILE] [--stop-position END_POS]
123 [--start-datetime START_TIME] [--stop-datetime STOP_TIME]
124 [--stop-never] [--help] [-d [DATABASES [DATABASES ...]]]
125 [-t [TABLES [TABLES ...]]] [--only-dml]
126 [--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B]
127 [--back-interval BACK_INTERVAL]
128 binlog2sql.py: error: unrecognized arguments: --sql-typedelete
129
130 [root@zlm3 10:00:12 ~/binlog2sql/binlog2sql]
131 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000004' --only-dml --sql-type delete --stop-never > flashback.sql
132
133 [root@zlm3 10:00:35 ~/binlog2sql/binlog2sql]
134 #cat flashback.sql
135
136 [root@zlm3 10:00:47 ~/binlog2sql/binlog2sql]
137 #ls -l
138 total 36
139 -rwxrwxrwx 1 root root 7882 Jun 6 07:21 binlog2sql.py
140 -rwxrwxrwx 1 root root 11696 Jun 6 07:21 binlog2sql_util.py
141 -rw-r--r-- 1 root root 10344 Jun 6 09:35 binlog2sql_util.pyc
142 -rw-r--r-- 1 root root 0 Jun 6 10:00 flashback.sql
143 -rwxrwxrwx 1 root root 98 Jun 6 07:21 __init__.py
144
145 [root@zlm3 10:00:52 ~/binlog2sql/binlog2sql]
146 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='mysql-bin.000003' --only-dml --sql-type delete --stop-never > flashback.sql
147
148 [root@zlm3 10:01:26 ~/binlog2sql/binlog2sql]
149 #cat flashback.sql
150
151 [root@zlm3 10:01:32 ~/binlog2sql/binlog2sql]
152 #
what's amazing is that there're no delete operation found in the existent binlog files at all,how can that happen?let's use the original tool of MySQL itself's to see the details:
1 [root@zlm3 10:12:51 ~/binlog2sql/binlog2sql] 2 #mysqlbinlog -vv --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.000004 > flashback1.sql 3 4 [root@zlm3 10:13:32 ~/binlog2sql/binlog2sql] 5 #ls -l 6 total 1536 7 -rwxrwxrwx 1 root root 7882 Jun 6 07:21 binlog2sql.py 8 -rwxrwxrwx 1 root root 11696 Jun 6 07:21 binlog2sql_util.py 9 -rw-r--r-- 1 root root 10344 Jun 6 09:35 binlog2sql_util.pyc 10 -rw-r--r-- 1 root root 1532363 Jun 6 10:13 flashback1.sql 11 -rw-r--r-- 1 root root 0 Jun 6 10:01 flashback.sql 12 -rwxrwxrwx 1 root root 98 Jun 6 07:21 __init__.py 13 14 [root@zlm3 10:20:49 ~/binlog2sql/binlog2sql] 15 #cat flashback1.sql | sed -n '1,100p' 16 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; 17 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 18 DELIMITER /*!*/; 19 # at 4 20 #180606 9:30:21 server id 1023306 end_log_pos 123 CRC32 0xea71a6c0 Start: binlog v 4, server v 5.7.21-log created 180606 9:30:21 21 # Warning: this binlog is either in use or was not closed properly. 22 # at 123 23 #180606 9:30:21 server id 1023306 end_log_pos 194 CRC32 0x7fe5f98f Previous-GTIDs 24 # 5c77c31b-4add-11e8-81e2-080027de0e0e:1-12 25 # at 194 26 #180606 9:30:38 server id 1023306 end_log_pos 259 CRC32 0x7792ab23 GTID last_committed=0 sequence_number=1 rbr_only=yes 27 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; 28 SET @@SESSION.GTID_NEXT= '5c77c31b-4add-11e8-81e2-080027de0e0e:13'/*!*/; 29 # at 259 30 #180606 9:30:38 server id 1023306 end_log_pos 330 CRC32 0x3eb43079 Query thread_id=10 exec_time=0 error_code=0 31 SET TIMESTAMP=1528270238/*!*/; 32 SET @@session.pseudo_thread_id=10/*!*/; 33 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; 34 SET @@session.sql_mode=1436549152/*!*/; 35 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; 36 /*!\C utf8 *//*!*/; 37 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; 38 SET @@session.lc_time_names=0/*!*/; 39 SET @@session.collation_database=DEFAULT/*!*/; 40 BEGIN 41 /*!*/; 42 # at 330 43 #180606 9:30:38 server id 1023306 end_log_pos 383 CRC32 0x31c37710 Table_map: `zlm`.`flash_test` mapped to number 177 44 # at 383 45 #180606 9:30:38 server id 1023306 end_log_pos 8595 CRC32 0xd8a33ccb Delete_rows: table id 177 46 # at 8595 47 #180606 9:30:38 server id 1023306 end_log_pos 16807 CRC32 0x1105fcdc Delete_rows: table id 177 48 # at 16807 49 #180606 9:30:38 server id 1023306 end_log_pos 25019 CRC32 0x4702cdba Delete_rows: table id 177 50 # at 25019 51 #180606 9:30:38 server id 1023306 end_log_pos 33231 CRC32 0xf49afbfa Delete_rows: table id 177 52 # at 33231 53 #180606 9:30:38 server id 1023306 end_log_pos 41443 CRC32 0x8fc2a48e Delete_rows: table id 177 54 # at 41443 55 #180606 9:30:38 server id 1023306 end_log_pos 49655 CRC32 0x947c8ca2 Delete_rows: table id 177 56 # at 49655 57 #180606 9:30:38 server id 1023306 end_log_pos 57867 CRC32 0x4d1b4f53 Delete_rows: table id 177 58 # at 57867 59 #180606 9:30:38 server id 1023306 end_log_pos 66079 CRC32 0x76275b27 Delete_rows: table id 177 60 # at 66079 61 #180606 9:30:38 server id 1023306 end_log_pos 74291 CRC32 0x99c0b33c Delete_rows: table id 177 62 # at 74291 63 #180606 9:30:38 server id 1023306 end_log_pos 82503 CRC32 0x132f757d Delete_rows: table id 177 64 # at 82503 65 #180606 9:30:38 server id 1023306 end_log_pos 90715 CRC32 0x047a5f63 Delete_rows: table id 177 66 # at 90715 67 #180606 9:30:38 server id 1023306 end_log_pos 98927 CRC32 0xf5026c08 Delete_rows: table id 177 68 # at 98927 69 #180606 9:30:38 server id 1023306 end_log_pos 107139 CRC32 0xd58c93f3 Delete_rows: table id 177 70 # at 107139 71 #180606 9:30:38 server id 1023306 end_log_pos 115351 CRC32 0x8a7ea487 Delete_rows: table id 177 72 # at 115351 73 #180606 9:30:38 server id 1023306 end_log_pos 123563 CRC32 0xe0450750 Delete_rows: table id 177 74 # at 123563 75 #180606 9:30:38 server id 1023306 end_log_pos 130943 CRC32 0x336c7539 Delete_rows: table id 177 flags: STMT_END_F 76 ### DELETE FROM `zlm`.`flash_test` 77 ### WHERE 78 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ 79 ### @2=0 /* LONGINT meta=0 nullable=0 is_null=0 */ 80 ### DELETE FROM `zlm`.`flash_test` 81 ### WHERE 82 ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ 83 ### @2=1 /* LONGINT meta=0 nullable=0 is_null=0 */ 84 ### DELETE FROM `zlm`.`flash_test` 85 ### WHERE 86 ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ 87 ### @2=2 /* LONGINT meta=0 nullable=0 is_null=0 */ 88 ### DELETE FROM `zlm`.`flash_test` 89 ### WHERE 90 ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ 91 ### @2=3 /* LONGINT meta=0 nullable=0 is_null=0 */ 92 ### DELETE FROM `zlm`.`flash_test` 93 ### WHERE 94 ### @1=5 /* INT meta=0 nullable=0 is_null=0 */ 95 ### @2=4 /* LONGINT meta=0 nullable=0 is_null=0 */ 96 ### DELETE FROM `zlm`.`flash_test` 97 ### WHERE 98 ### @1=6 /* INT meta=0 nullable=0 is_null=0 */ 99 ### @2=5 /* LONGINT meta=0 nullable=0 is_null=0 */ 100 ### DELETE FROM `zlm`.`flash_test` 101 ### WHERE 102 ### @1=7 /* INT meta=0 nullable=0 is_null=0 */ 103 ### @2=6 /* LONGINT meta=0 nullable=0 is_null=0 */ 104 ### DELETE FROM `zlm`.`flash_test` 105 ### WHERE 106 ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ 107 ### @2=7 /* LONGINT meta=0 nullable=0 is_null=0 */ 108 ### DELETE FROM `zlm`.`flash_test` 109 ### WHERE 110 ### @1=9 /* INT meta=0 nullable=0 is_null=0 */ 111 ### @2=8 /* LONGINT meta=0 nullable=0 is_null=0 */ 112 ### DELETE FROM `zlm`.`flash_test` 113 ### WHERE 114 ### @1=10 /* INT meta=0 nullable=0 is_null=0 */ 115 ### @2=9 /* LONGINT meta=0 nullable=0 is_null=0 */ -- This is the deletion of record no.10 in the original table "flash_table". 116 117 [root@zlm3 10:22:32 ~/binlog2sql/binlog2sql] 118 #
throught the evidence above,i'm afaid the binlog2sql tool didn't recognize the right path of the binlog files and cast an exception at all.Am i right?
1 [root@zlm3 10:32:40 ~/binlog2sql/binlog2sql]
2 #python binlog2sql.py -h192.168.1.102 -P3306 -uroot -pPassw0rd -dzlm -tflash_table --start-file='/data/mysql/mysql3306/logs/mysql-bin.000004' --only-dml --sql-type delete --stop-never > flashback3.sql
3 Traceback (most recent call last):
4 File "binlog2sql.py", line 149, in <module>
5 back_interval=args.back_interval, only_dml=args.only_dml, sql_type=args.sql_type)
6 File "binlog2sql.py", line 53, in __init__
7 raise ValueError('parameter error: start_file %s not in mysql server' % self.start_file)
8 ValueError: parameter error: start_file /data/mysql/mysql3306/logs/mysql-bin.000004 not in mysql server
9
10 [root@zlm3 10:32:54 ~/binlog2sql/binlog2sql]
11 #ls -l /data/mysql/mysql3306/logs/mysql-bin.000004
12 -rw-r----- 1 mysql mysql 131467 Jun 6 09:50 /data/mysql/mysql3306/logs/mysql-bin.000004 --The binlog file is right there,it shows that it's no need to specify the absolute path in parameter "--start-file"
13
14 [root@zlm3 10:34:07 ~/binlog2sql/binlog2sql]
15 #
unfortunately it still doesn't work,i'm confused and have no idea now,maybe i'll test it again text time.Let's see then.
Summury:
- binlog2sql is coded by python,in order to use it you need to install python and relevant packages.
- some parameter such as "binlog_format=row","binlog_row_image=full" etc. in MySQL Server should be satisfied with.
- some privileges such as "super"/"replication client","replication slave" etc. in MySQL Server should be satisfied with too.
- it seems more inconvenient rather than the MyFlash tool does.