binlog_dml_rollback_py

评论

利用Python解析MySQL BINLOG从而回滚UPDATE和DELETE误操作

MySQL没有Oracle的闪回(flashback)功能，如果不小心执行了UPDATE或者DELETE误操作，想要回滚相比Oracle还是挺麻烦的可以利用mysqlbinlog工具解析binlog，从而拼接出UPDATE和DELETE的回滚语句，人工拼接比较麻烦，所以利用Python来拼接虽然也可以用c或者python直接解析binlog文件，但是太耗费经历和时间了，没空去写
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
import io
import pymysql
 
def processUPDATE(db_name,table_name):
    conn = pymysql.connect("192.168.56.10", "scott", "tiger", "db")
    cur = conn.cursor()
    cur.arraysize = 100
    sql = "select column_name from information_schema.COLUMNS " \
          "where table_schema='" + db_name + "' and table_name='" + table_name + "' order by ordinal_position"
    cur.execute(sql)
    rows = cur.fetchall()
    num_column = len(rows)
    binlogfile = open('binlog.txt', 'r', newline='', encoding='utf8')
    updatefile = open('redo_update.sql', 'w', newline='', encoding='utf8')
    rollbackfile = open('undo_update.sql', 'w', newline='', encoding='utf8')
    while True:
        row = binlogfile.readline()
        update = []
        where = []
        rollback_set = []
        rollback_where = []
        set = []
        i_total = 1
        i_where = 1
        i_set = 1
        update.append(row.replace('#', '').replace('`', '').replace('\n', ''))
        if ('UPDATE' in row) and (db_name in row) and (table_name in row):
        while i_total < num_column * 2 + 3:
            row = binlogfile.readline()
            if i_where < num_column + 2:
            where.append(row.replace('#', '').replace('`', '').replace('\n', '').replace('=NULL', ' IS NULL').replace('@' + str(i_where - 1), rows[i_where - 2][0]))rollback_set.append(row.replace('#', '').replace('`', '').replace('\n', '').replace('@' + str(i_where - 1),rows[i_where - 2][0].replace('WHERE', 'SET'))
            if num_column > 1 and i_where > 1 and i_where < num_column + 1:
              where.append(' AND ')
              rollback_set.append(',')
            if i_where == num_column + 1:
              where.append(';')
            if i_set >= num_column + 2:
            set.append(row.replace('#', '').replace('`', '').replace('\n', '').replace('@' + str(i_set - 2 - num_column), rows[i_set - 3 - num_column][0]))
            rollback_where.append(
              row.replace('#', '').replace('`', '').replace('\n', '').replace('@' + str(i_set - 2 - num_column),
                                                                              rows[i_set - 3 - num_column][0]).replace('SET','WHERE').replace('=NULL', ' IS NULL'))
            if num_column > 1 and i_set > num_column + 2 and i_set < num_column * 2 + 2:
              set.append(',')
              rollback_where.append(' AND ')
            if i_total == num_column * 2 + 2:
              rollback_where.append(';')
            i_total = i_total + 1
            i_where = i_where + 1
            i_set = i_set + 1
        statement = ''.join(update) + ''.join(set) + ''.join(where)
        rollback = ''.join(update) + ''.join(rollback_set) + ''.join(rollback_where)
        updatefile.writelines(
          statement.replace('  ', ' ').replace(',  ', ',').replace('SET  ', 'SET ').replace('WHERE  ', 'WHERE ').replace(
          'AND  ', 'AND ') + '\n')
        rollbackfile.writelines(
          rollback.replace('  ', ' ').replace(',  ', ',').replace('SET  ', 'SET ').replace('WHERE  ', 'WHERE ').replace(
          'AND  ', 'AND ') + '\n')
        if not row:
      break
    cur.close()
    conn.close()
    updatefile.close()
    binlogfile.close()
    rollbackfile.close()
    print('redo_update.sql是原始SQL')
    print('undo_update.sql是回滚SQL')
 return
 
def processDELETE(db_name,table_name):
    conn = pymysql.connect("192.168.56.10", "scott", "tiger", "db")
    cur = conn.cursor()
    cur.arraysize = 100
    sql = "select column_name from information_schema.COLUMNS " \
          "where table_schema='" + db_name + "' and table_name='" + table_name + "' order by ordinal_position"
    cur.execute(sql)
    rows = cur.fetchall()
    num_column = len(rows)
    binlogfile = open('binlog.txt', 'r', newline='', encoding='utf8')
    deletefile = open('delete.sql','w',newline='',encoding='utf8')
    insertfile = open('insert.sql','w',newline='',encoding='utf8')
    while True:
        row = binlogfile.readline()
        i = 0
        delete = []
        insert =[]
        where = []
        values =[]
        column = []
        value = []
        delete.append(row.replace('#', '').replace('`', '').replace('\n', ''))
        insert.append(row.replace('#', '').replace('`', '').replace('\n', '').replace('DELETE FROM','INSERT INTO'))
        if 'DELETE' in row and db_name in row and table_name in row:
        while i < num_column + 1:
            row = binlogfile.readline()
            if i == 0:
            where.append(row.replace('#', '').replace('\n', ''))
            values.append(row.replace('#', '').replace('\n', '').replace('WHERE','VALUES('))
            if i > 0:
            column.append(row.replace('#', '').replace('@' + str(i), rows[i - 1][0]).replace('\n', '').replace('=NULL', ' IS NULL'))
            value.append(row.replace('#', '').replace('@' + str(i)+'=','').replace('\n',''))
            if num_column > 1 and i >= 1 and i < num_column:
            column.append(' AND  ')
            value.append(',')
            if i == num_column:
            column.append(';')
            value.append(' );')
            i = i + 1
          delete_sql = ''.join(delete) + ''.join(where) + ''.join(column).replace('  ', '').replace(', ', ',')
          deletefile.writelines(delete_sql+'\n')
          insert_sql =''.join(insert)+''.join(values)+''.join(value).replace('  ','').replace(', ',',')
          insertfile.writelines(insert_sql+'\n')
        if not row:
      break
    cur.close()
    conn.close()
    binlogfile.close()
    deletefile.close()
    insertfile.close()
    print('delete.sql是原始SQL')
    print('insert.sql是回滚SQL')
 return
processUPDATE('db','emp_bak')
processDELETE('db','emp_bak')

UPDATE误操作恢复示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
mysql> select * from emp;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | 沙雕   | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
 
mysql> update emp set ename='沙雕';
Query OK, 13 rows affected (0.01 sec)
Rows matched: 14  Changed: 13  Warnings: 0
 
mysql> show binlog events in 'binlog.000002'  limit 55,10;
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| binlog.000002 | 4111 | Write_rows     |         1 |        4151 | table_id: 105 flags: STMT_END_F      |
| binlog.000002 | 4151 | Xid            |         1 |        4182 | COMMIT /* xid=352 */                 |
| binlog.000002 | 4182 | Anonymous_Gtid |         1 |        4261 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 4261 | Query          |         1 |        4343 | BEGIN                                |
| binlog.000002 | 4343 | Table_map      |         1 |        4411 | table_id: 102 (db.emp)               |
| binlog.000002 | 4411 | Update_rows    |         1 |        5420 | table_id: 102 flags: STMT_END_F      |
| binlog.000002 | 5420 | Xid            |         1 |        5451 | COMMIT /* xid=355 */                 |
+---------------+------+----------------+-----------+-------------+--------------------------------------+
7 rows in set (0.01 sec)
 
mysqlbinlog binlog.000002 -v -d db --start-position=4343 --stop-position=5420 > binlog.txt
 
将binlog.txt传输到C:\Python\project\目录
 
C:\Python\project>python processbinlog.py
redo_update.sql是原始SQL
undo_update.sql是回滚SQL
 
执行undo_update.sql
 
mysql>  UPDATE db.emp SET empno=7499,ename='ALLEN',job='SALESMAN',mgr=7698,hiredate='1981-02-20 00:00:00',sal=1600.00,comm=300.00,deptno=30 WHERE empno=7499 AND ename='沙雕' AND job='SALESMAN' AND mgr=7698 AND hiredate='1981-02-20 00:00:00' AND sal=1600.00 AND comm=300.00 AND deptno=30;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>  UPDATE db.emp SET empno=7521,ename='WARD',job='SALESMAN',mgr=7698,hiredate='1981-02-22 00:00:00',sal=1250.00,comm=500.00,deptno=30 WHERE empno=7521 AND ename='沙雕' AND job='SALESMAN' AND mgr=7698 AND hiredate='1981-02-22 00:00:00' AND sal=1250.00 AND comm=500.00 AND deptno=30;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>  UPDATE db.emp SET empno=7566,ename='JONES',job='MANAGER',mgr=7839,hiredate='1981-04-02 00:00:00',sal=2975.00,comm=NULL,deptno=20 WHERE empno=7566 AND ename='沙雕' AND job='MANAGER' AND mgr=7839 AND hiredate='1981-04-02 00:00:00' AND sal=2975.00 AND comm IS NULL AND deptno=20;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>  UPDATE db.emp SET empno=7654,ename='MARTIN',job='SALESMAN',mgr=7698,hiredate='1981-09-28 00:00:00',sal=1250.00,comm=1400.00,deptno=30 WHERE empno=7654 AND ename='沙雕' AND job='SALESMAN' AND mgr=7698 AND hiredate='1981-09-28 00:00:00' AND sal=1250.00 AND comm=1400.00 AND deptno=30;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>  UPDATE db.emp SET empno=7698,ename='BLAKE',job='MANAGER',mgr=7839,hiredate='1981-05-01 00:00:00',sal=2850.00,comm=NULL,deptno=30 WHERE empno=7698 AND ename='沙雕' AND job='MANAGER' AND mgr=7839 AND hiredate='1981-05-01 00:00:00' AND sal=2850.00 AND comm IS NULL AND deptno=30;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>  UPDATE db.emp SET empno=7782,ename='CLARK',job='MANAGER',mgr=7839,hiredate='1981-06-09 00:00:00',sal=2450.00,comm=NULL,deptno=10 WHERE empno=7782 AND ename='沙雕' AND job='MANAGER' AND mgr=7839 AND hiredate='1981-06-09 00:00:00' AND sal=2450.00 AND comm IS NULL AND deptno=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>  UPDATE db.emp SET empno=7788,ename='SCOTT',job='ANALYST',mgr=7566,hiredate='1987-04-19 00:00:00',sal=3000.00,comm=NULL,deptno=20 WHERE empno=7788 AND ename='沙雕' AND job='ANALYST' AND mgr=7566 AND hiredate='1987-04-19 00:00:00' AND sal=3000.00 AND comm IS NULL AND deptno=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>  UPDATE db.emp SET empno=7839,ename='KING',job='PRESIDENT',mgr=NULL,hiredate='1981-11-17 00:00:00',sal=5000.00,comm=NULL,deptno=10 WHERE empno=7839 AND ename='沙雕' AND job='PRESIDENT' AND mgr IS NULL AND hiredate='1981-11-17 00:00:00' AND sal=5000.00 AND comm IS NULL AND deptno=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>  UPDATE db.emp SET empno=7844,ename='TURNER',job='SALESMAN',mgr=7698,hiredate='1981-09-08 00:00:00',sal=1500.00,comm=0.00,deptno=30 WHERE empno=7844 AND ename='沙雕' AND job='SALESMAN' AND mgr=7698 AND hiredate='1981-09-08 00:00:00' AND sal=1500.00 AND comm=0.00 AND deptno=30;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>  UPDATE db.emp SET empno=7876,ename='ADAMS',job='CLERK',mgr=7788,hiredate='1987-05-23 00:00:00',sal=1100.00,comm=NULL,deptno=20 WHERE empno=7876 AND ename='沙雕' AND job='CLERK' AND mgr=7788 AND hiredate='1987-05-23 00:00:00' AND sal=1100.00 AND comm IS NULL AND deptno=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>  UPDATE db.emp SET empno=7900,ename='JAMES',job='CLERK',mgr=7698,hiredate='1981-12-03 00:00:00',sal=950.00,comm=NULL,deptno=30 WHERE empno=7900 AND ename='沙雕' AND job='CLERK' AND mgr=7698 AND hiredate='1981-12-03 00:00:00' AND sal=950.00 AND comm IS NULL AND deptno=30;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>  UPDATE db.emp SET empno=7902,ename='FORD',job='ANALYST',mgr=7566,hiredate='1981-12-03 00:00:00',sal=3000.00,comm=NULL,deptno=20 WHERE empno=7902 AND ename='沙雕' AND job='ANALYST' AND mgr=7566 AND hiredate='1981-12-03 00:00:00' AND sal=3000.00 AND comm IS NULL AND deptno=20;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql>  UPDATE db.emp SET empno=7934,ename='MILLER',job='CLERK',mgr=7782,hiredate='1982-01-23 00:00:00',sal=1300.00,comm=NULL,deptno=10 WHERE empno=7934 AND ename='沙雕' AND job='CLERK' AND mgr=7782 AND hiredate='1982-01-23 00:00:00' AND sal=1300.00 AND comm IS NULL AND deptno=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from emp;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | 沙雕   | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

gladlyDBA

个人简介。