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

MySQL没有Oracle的闪回(flashback)功能,如果不小心执行了UPDATE或者DELETE误操作,想要回滚相比Oracle还是挺麻烦的

可以利用mysqlbinlog工具解析binlog,从而拼接出UPDATE和DELETE的回滚语句,人工拼接比较麻烦,所以利用Python来拼接

虽然也可以用c或者python直接解析binlog文件,但是太耗费经历和时间了,没空去写

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误操作恢复示例:

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)

 

 

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页