Skip to content

Commit 61f558b

Browse files
danfengcaodanfengcao
committed
fix README
1 parent 3b18564 commit 61f558b

3 files changed

Lines changed: 131 additions & 106 deletions

File tree

‎README.md‎

Lines changed: 77 additions & 51 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
binlog2sql
22
========================
33

4-
从MySQL binlog得到你要的SQL。根据不同设置，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。
4+
从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。
55

66
用途
77
===========
@@ -11,6 +11,15 @@ binlog2sql
1111
* 从binlog生成标准SQL，带来的衍生功能
1212

1313

14+
项目状态
15+
===
16+
正常维护
17+
18+
* 已测试环境
19+
* Python 2.6, 2.7
20+
* MySQL 5.6
21+
22+
1423
安装
1524
==============
1625

@@ -83,71 +92,87 @@ DELETE FROM `test`.`test4` WHERE `data`='World' AND `id`=1 AND `data2`='Hello' L
8392

8493
###应用案例
8594

86-
#### **案例一 跑错SQL，需要紧急回滚**
95+
#### **误删整张表数据，需要紧急回滚**
8796

8897
详细描述可参见[example/mysql-rollback-your-data.md](./example/mysql-rollback-your-data.md)
8998

90-
**背景**：误删test库c表的数据，需要紧急回滚。
91-
92-
**步骤**：
93-
94-
1. 定位误操作的SQL位置
99+
```bash
100+
test库tbl表原有数据
101+
mysql> select * from tbl;
102+
+----+--------+---------------------+
103+
| id | name | addtime |
104+
+----+--------+---------------------+
105+
| 1 | 小赵 | 2016-12-10 00:04:33 |
106+
| 2 | 小钱 | 2016-12-10 00:04:48 |
107+
| 3 | 小孙 | 2016-12-10 00:04:51 |
108+
| 4 | 小李 | 2016-12-10 00:04:56 |
109+
+----+--------+---------------------+
110+
4 rows in set (0.00 sec)
111+
112+
mysql> delete from tbl;
113+
Query OK, 4 rows affected (0.00 sec)
114+
115+
tbl表被清空
116+
mysql> select * from tbl;
117+
Empty set (0.00 sec)
118+
```
95119
96-
```bash
97-
$ python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t c --start-file='mysql-bin.000002'
120+
**恢复数据步骤**：
98121
99-
输出：
100-
INSERT INTO `test`.`c`(`id`, `name`) VALUES (0, 'b'); #start 310 end 459
101-
DELETE FROM `test`.`c` WHERE `id`=0 AND `name`='b' LIMIT 1; #start 682 end 831
102-
UPDATE `test`.`c` SET `id`=3, `name`='b' WHERE `id`=3 AND `name`='a' LIMIT 1; #start 858 end 1015
103-
```
104-
2. 查看回滚sql是否正确
122+
1. 登录mysql，查看目前的binlog文件
105123
106124
```bash
107-
$ python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t c --start-file='mysql-bin.000002' --start-pos=682 --end-pos=831 -B
125+
mysql> show master logs;
126+
+------------------+-----------+
127+
| Log_name | File_size |
128+
+------------------+-----------+
129+
| mysql-bin.000046 | 12262268 |
130+
| mysql-bin.000047 | 3583 |
131+
+------------------+-----------+
132+
```
108133
109-
输出：
110-
INSERT INTO `test`.`c`(`id`, `name`) VALUES (0, 'b'); #start 682 end 831
111-
```
112-
3. 执行回滚语句
134+
2. 最新的binlog文件是mysql-bin.000047，我们再定位误操作SQL的binlog位置
113135
114136
```bash
115-
$ python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t c --start-file='mysql-bin.000002' --start-pos=682 --end-pos=831 -B | mysql -h127.0.0.1 -P3306 -uadmin -p'admin'
116-
117-
回滚成功
118-
```
119-
120-
121-
**案例二 主从切换后数据不一致的修复**
122-
123-
详细描述可参见[example/FixOldMasterExtraData.md](./example/FixOldMasterExtraData.md)
124-
125-
1. 提取old master未同步的数据，并对其中的insert语句去除主键（为了防止步骤3中出现主键冲突）
126-
127-
```
128-
$ python binlog2sql.py --popPk -h10.1.1.1 -P3306 -uadmin -p'admin' --start-file='mysql-bin.000040' --start-pos=125466 --end-file='mysql-bin.000041' > oldMaster.sql
129-
```
130-
131-
2. 将old master回滚，开启同步。同步正常；
132-
133-
```
134-
$ python binlog2sql.py --flashback -h10.1.1.1 -P3306 -uadmin -p'admin' --start-file='mysql-bin.mysql-bin.000040' --start-pos=125466 --end-file='mysql-bin.000041' | mysql -h10.1.1.1 -P3306 -uadmin -p'admin'
135-
```
137+
$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000047'
138+
输出：
139+
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='小赵' LIMIT 1; #start 3346 end 3556
140+
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='小钱' LIMIT 1; #start 3346 end 3556
141+
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:51' AND `id`=3 AND `name`='小孙' LIMIT 1; #start 3346 end 3556
142+
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:56' AND `id`=4 AND `name`='小李' LIMIT 1; #start 3346 end 3556
143+
```
144+
145+
3. 生成回滚sql，并检查回滚sql是否正确
136146
137-
3. 在new master重新导入改造后的sql；
147+
```bash
148+
$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000047' --start-pos=3346 --end-pos=3556 -B
149+
输出：
150+
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:56', 4, '小李'); #start 3346 end 3556
151+
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:51', 3, '小孙'); #start 3346 end 3556
152+
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 3346 end 3556
153+
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, '小赵'); #start 3346 end 3556
154+
```
155+
156+
3. 确认回滚sql正确，执行回滚语句。登录mysql确认，数据回滚成功。
138157
139-
```
140-
$ mysql -h10.1.1.2 -P3306 -uadmin -p'admin' < oldMaster.sql
141-
```
158+
```bash
159+
$ python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000047' --start-pos=3346 --end-pos=3556 -B | mysql -h127.0.0.1 -P3306 -uadmin -p'admin'
160+
161+
mysql> select * from tbl;
162+
+----+--------+---------------------+
163+
| id | name | addtime |
164+
+----+--------+---------------------+
165+
| 1 | 小赵 | 2016-12-10 00:04:33 |
166+
| 2 | 小钱 | 2016-12-10 00:04:48 |
167+
| 3 | 小孙 | 2016-12-10 00:04:51 |
168+
| 4 | 小李 | 2016-12-10 00:04:56 |
169+
+----+--------+---------------------+
170+
```
142171
143172
###限制
144-
* mysql server必须开启，离线模式下不能解析binlog
145-
* binlog格式必须是行模式
146-
* flashback模式只支持DML，DDL将不做输出
173+
* mysql server必须开启，离线模式下不能解析
147174
* flashback模式，一次性处理的binlog不宜过大，不能超过内存大小(有待优化)
148-
* 目前已测试环境
149-
* Python 2.7
150-
* MySQL 5.6
175+
151176
152177
###优点（对比mysqlbinlog）
153178
@@ -157,6 +182,7 @@ DELETE FROM `test`.`test4` WHERE `data`='World' AND `id`=1 AND `data2`='Hello' L
157182
* 代码容易改造，可以支持更多个性化解析
158183
159184
185+
160186
###联系我
161187
有任何问题，请与我联系 [danfengcao.info@gmail.com](danfengcao.info@gmail.com)
162188

‎example/mysql-rollback-your-data.md‎

Lines changed: 52 additions & 53 deletions
Original file line numberDiff line numberDiff line change
@@ -23,80 +23,79 @@ pip install -r requirements.txt
2323

2424
然后，我们就可以生成回滚SQL了。
2525

26-
**背景**：误删了test库f表整张表的数据，需要紧急回滚。
26+
**背景**：误删了test库tbl表整张表的数据，需要紧急回滚。
2727

2828
```bash
29-
原有数据
30-
mysql> select * from f;
31-
+-----+-----+---------------------+
32-
| uid | did | updateTime |
33-
+-----+-----+---------------------+
34-
| 1 | 18 | 2016-12-06 12:28:18 |
35-
| 2 | 19 | 2016-12-06 12:55:56 |
36-
| 3 | 20 | 2016-12-07 14:00:58 |
37-
| 4 | 21 | 2016-12-07 14:01:00 |
38-
+-----+-----+---------------------+
39-
40-
误操作
41-
mysql> delete from f;
29+
test库tbl表原有数据
30+
mysql> select * from tbl;
31+
+----+--------+---------------------+
32+
| id | name | addtime |
33+
+----+--------+---------------------+
34+
| 1 | 小赵 | 2016-12-10 00:04:33 |
35+
| 2 | 小钱 | 2016-12-10 00:04:48 |
36+
| 3 | 小孙 | 2016-12-10 00:04:51 |
37+
| 4 | 小李 | 2016-12-10 00:04:56 |
38+
+----+--------+---------------------+
39+
4 rows in set (0.00 sec)
40+
41+
mysql> delete from tbl;
4242
Query OK, 4 rows affected (0.00 sec)
4343

44-
f表被清空
45-
mysql> select * from f;
44+
tbl表被清空
45+
mysql> select * from tbl;
4646
Empty set (0.00 sec)
4747
```
4848
49-
**回滚步骤**：
49+
**恢复数据步骤**：
5050
5151
1. 登录mysql，查看目前的binlog文件
5252
5353
```bash
54-
mysql> show master logs;
55-
+------------------+-----------+
56-
| Log_name | File_size |
57-
+------------------+-----------+
58-
| mysql-bin.000001 | 12262268 |
59-
| mysql-bin.000002 | 132776 |
60-
+------------------+-----------+
54+
mysql> show master logs;
55+
+------------------+-----------+
56+
| Log_name | File_size |
57+
+------------------+-----------+
58+
| mysql-bin.000046 | 12262268 |
59+
| mysql-bin.000047 | 3583 |
60+
+------------------+-----------+
6161
```
6262
63-
2. 最新的binlog文件是mysql-bin.000002，我们再定位误操作SQL的binlog位置
63+
2. 最新的binlog文件是mysql-bin.000047，我们再定位误操作SQL的binlog位置
6464
6565
```bash
66-
$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f --start-file='mysql-bin.000002'
67-
68-
输出：
69-
DELETE FROM `test`.`f` WHERE `did`=18 AND `updateTime`='2016-12-06 12:28:18' AND `uid`=1 LIMIT 1; #start 4 end 314
70-
DELETE FROM `test`.`f` WHERE `did`=19 AND `updateTime`='2016-12-06 12:55:56' AND `uid`=2 LIMIT 1; #start 4 end 314
71-
DELETE FROM `test`.`f` WHERE `did`=20 AND `updateTime`='2016-12-07 14:00:58' AND `uid`=3 LIMIT 1; #start 4 end 314
72-
DELETE FROM `test`.`f` WHERE `did`=21 AND `updateTime`='2016-12-07 14:01:00' AND `uid`=4 LIMIT 1; #start 4 end 314
66+
$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000047'
67+
输出：
68+
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='小赵' LIMIT 1; #start 3346 end 3556
69+
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='小钱' LIMIT 1; #start 3346 end 3556
70+
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:51' AND `id`=3 AND `name`='小孙' LIMIT 1; #start 3346 end 3556
71+
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:56' AND `id`=4 AND `name`='小李' LIMIT 1; #start 3346 end 3556
7372
```
74-
2. 生成回滚sql，并检查回滚sql是否正确
73+
74+
3. 生成回滚sql，并检查回滚sql是否正确
7575
7676
```bash
77-
$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f --start-file='mysql-bin.000002' --start-pos=4 --end-pos=314 -B
78-
79-
输出：
80-
INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (21, '2016-12-07 14:01:00', 4); #start 4 end 314
81-
INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (20, '2016-12-07 14:00:58', 3); #start 4 end 314
82-
INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (19, '2016-12-06 12:55:56', 2); #start 4 end 314
83-
INSERT INTO `test`.`f`(`did`, `updateTime`, `uid`) VALUES (18, '2016-12-06 12:28:18', 1); #start 4 end 314
77+
$ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000047' --start-pos=3346 --end-pos=3556 -B
78+
输出：
79+
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:56', 4, '小李'); #start 3346 end 3556
80+
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:51', 3, '小孙'); #start 3346 end 3556
81+
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 3346 end 3556
82+
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, '小赵'); #start 3346 end 3556
8483
```
85-
3. 确认回滚sql正确，执行回滚语句。登录mysql，数据回滚成功。
84+
85+
4. 确认回滚sql正确，执行回滚语句。登录mysql确认，数据回滚成功。
8686
8787
```bash
88-
$ python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t f --start-file='mysql-bin.000002' --start-pos=4 --end-pos=314 -B | mysql -h127.0.0.1 -P3306 -uadmin -p'admin'
89-
90-
mysql> select * from f;
91-
+-----+-----+---------------------+
92-
| uid | did | updateTime |
93-
+-----+-----+---------------------+
94-
| 1 | 18 | 2016-12-06 12:28:18 |
95-
| 2 | 19 | 2016-12-06 12:55:56 |
96-
| 3 | 20 | 2016-12-07 14:00:58 |
97-
| 4 | 21 | 2016-12-07 14:01:00 |
98-
+-----+-----+---------------------+
99-
88+
$ python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000047' --start-pos=3346 --end-pos=3556 -B | mysql -h127.0.0.1 -P3306 -uadmin -p'admin'
89+
90+
mysql> select * from tbl;
91+
+----+--------+---------------------+
92+
| id | name | addtime |
93+
+----+--------+---------------------+
94+
| 1 | 小赵 | 2016-12-10 00:04:33 |
95+
| 2 | 小钱 | 2016-12-10 00:04:48 |
96+
| 3 | 小孙 | 2016-12-10 00:04:51 |
97+
| 4 | 小李 | 2016-12-10 00:04:56 |
98+
+----+--------+---------------------+
10099
```
101100
102101
至此，不用再担心被炒鱿鱼了。

‎requirments.txt‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,3 @@
1-
PyMySQL==0.7.9
1+
PyMySQL==0.7.8
22
wheel==0.24.0
3-
mysql-replication==0.10
3+
mysql-replication==0.9

0 commit comments

Comments
 (0)