MySQL rollback Python script writing tutorial
- 2020-11-30 08:36:46
- OfStack
The operation of the database will inevitably be due to "carelessness" and misoperation, if you need to restore quickly, it is not possible to restore by backup, because it needs to restore and binlog difference to restore, can not wait, very time-consuming. The recovery method of Delete operation is explained here: the recovery method is mainly through binlog, provided that binlog_format must be in Row format, otherwise the data can only be restored by backup.
Methods:
Condition: Binlog is turned on and Format is Row.
Steps:
1. Record of export operation specified by MySQL with its own tool mysqlbinlog:
mysqlbinlog
--no-defaults
--start-datetime='2012-12-25 14:56:00'
--stop-datetime='2012-12-25 14:57:00'
-vv mysql-bin.000001 > /home/zhoujy/restore/binlog.txt
2. After the data is taken out, the data needs to be parsed and reversed. The original data:
### DELETE FROM test.me_info
### WHERE
### @1=2165974 /* INT meta=0 nullable=0 is_null=0 */
### @2='1984:03:17' /* DATE meta=0 nullable=1 is_null=0 */
### @3=NULL /* DATE meta=765 nullable=1 is_null=1 */
### @4=2012-10-25 00:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */
### @5='' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
### @6=0 /* TINYINT meta=0 nullable=1 is_null=0 */
### @7='' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
### @8=-1 (4294967295) /* INT meta=0 nullable=1 is_null=0 */
### @9=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
### @10=NULL /* MEDIUMINT meta=0 nullable=1 is_null=1 */
### @11=2 /* TINYINT meta=0 nullable=1 is_null=0 */
### @12=0 /* TINYINT meta=0 nullable=1 is_null=0 */
### @13='' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
### @14='' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
### @15=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */
### @16=320 /* INT meta=0 nullable=1 is_null=0 */
..........................................
..........................................
..........................................
The format of binlog records in Row format is shown above. What needs to be done is to convert the operation of Delete to the operation of Insert. There is a certain rule for the above hair, and it should be noted that:
1. Field type DATETIME date. Save in the log as @4=2012-10-25 00:00:00, you need to put 2012-10-25 00:00:00 in quotes.
2. Negative Numbers. The format saved in the log is @1=-1 (4294967295), -2(4294967294), -3(4294967293). The data in () should be removed and only @1=-1 should be kept.
3. Escape character set. For example: 's, \, etc.
After the above three points are clear, I can write a script (limited level, not good in the promotion) :
#!/bin/env python
# -*- encoding: utf-8 -*-
#-------------------------------------------------------------------------------
# Name: restore.py
# Purpose: through Binlog restore Delete Misoperated data
# Author: zhoujy
# Created: 2012-12-25
# update: 2012-12-25
# Copyright: (c) Mablevi 2012
# Licence: zjy
#-------------------------------------------------------------------------------
def read_binlog(file,column_num):
f=open(file)
num = '@'+str(column_num)
while True:
lines = f.readline()
if lines.strip()[0:3] == '###':
lines=lines.split(' ',3)
if lines[1] == 'DELETE' and lines[2] =='FROM': # This part replacement Delete for Insert
lines[1] = "INSERT"
lines[2] = 'INTO'
lines[-1] = lines[-1].strip()
if lines[1].strip() == 'WHERE':
lines[1] = 'VALUES ('
if ''.join(lines).find('@') <> -1 and lines[3].split('=',1)[0] <> num: #num Is the number of columns. If the number is less than the maximum number of columns, all the columns are added afterwards ,
lines[3] = lines[3].split('=',1)[-1].strip()
if lines[3].strip('\'').strip().find('\'') <> -1:
lines[3] = lines[3].split('/*')[0].strip('\'').strip().strip('\'').replace('\\','').replace('\'','\\\'') # This filters out escaped strings
lines[3] = '\'' + lines[3] + '\','
elif lines[3].find('INT meta') <> -1: # filter Int The field of type is followed by a negative number (). Positive Numbers are not affected
lines[3] = lines[3].split('/*')[0].strip()
lines[3] = lines[3].split()[0] + ','
elif lines[3].find('NULL') <> -1:
lines[3] = lines[3].split('/*')[0].strip()
lines[3] = lines[3] + ','
else:
lines[3] = lines[3].split('/*')[0].strip('\'').strip().strip('\'').replace('\\','').replace('\'','\\\'') # This filters out escaped strings
lines[3] = '\'' + lines[3].strip('\''' ') + '\','
if ''.join(lines).find('@') <> -1 and lines[3].split('=',1)[0] == num: #num Is the number of columns. If the number is less than the maximum number of columns, all the columns are added afterwards );
lines[3] = lines[3].split('=',1)[-1].strip()
if lines[3].find('\'') <> -1:
lines[3] = lines[3].split('/*')[0].strip('\'').strip().strip('\'').replace('\\','').replace('\'','\\\'') # Same as above
lines[3] = '\'' + lines[3] + '\');'
elif lines[3].find('INT meta') <> -1: # Same as above
lines[3] = lines[3].split('/*')[0].strip()
lines[3] = lines[3].split(' ')[0] + ');'
elif lines[3].find('NULL') <> -1:
lines[3] = lines[3].split('/*')[0].strip()
lines[3] = lines[3] + ');'
else:
lines[3] = lines[3].split('/*')[0].strip('\'').strip().strip('\'').replace('\\','').replace('\'','\\\'') # Same as above
lines[3] = '\'' + lines[3].strip('\''' ') + '\');'
print ' '.join(lines[1:])
if lines == '':
break
if __name__ == '__main__':
import sys
read_binlog(sys.argv[1],sys.argv[2])
python restore.py binlog.txt 36 > binlog.sql
36 in the command line indicates that there are 36 fields in the table to be restored. The effect is as follows:
INSERT INTO test.me_info
VALUES (
2123269,
'1990:11:12',
NULL,
2,
'',
0,
'',
-1,
0,
340800,
1,
0,
'',
...
...
1,
NULL
);
Final reduction:
mysql test < binlog.sql