MySQL uses binlog to recover misuse data
When manually performing some database write operations (such as data correction), especially some uncontrollable batch updates or deletions, it is usually recommended to perform post-backup operations. However, if you are not afraid of it, you will be afraid of 10,000. It is always good to be prepared. There are generally two ways to restore data after the online or test environment misoperation causes the data to be deleted or updated.
Method 1: Using the most recent full backup + incremental binlog backup, the state before the misoperation is restored, but as the amount of data increases, the binlog increases, which is time consuming to recover.
Method 2, if the format of the binlog is row, then the binlog can be parsed to generate the reverse original SQL.
The following is a python script, binlog_rollback.py, written in Method 2, which can be used to generate reverse raw SQL.
Description:
0, the premise is that the format of binlog is row
1. The table structure before and after the table operation to be restored has not changed, otherwise the script cannot be parsed.
2, only generate DML (insert / update / delete) rollback statement
3, the final generated SQL is reverse order, so the latest DML will be generated at the top of the input file, and with a timestamp and offset point, easy to find the target
4, need to provide a read-only user to connect to MySQL, mainly to obtain the table structure
5, if the binlog is too large, it is recommended to bring the time range, you can also specify only restore a SQL of a library
6, after SQL generation, please be sure to test and restore in the test environment before applying to the online
Script code
#! /bin/env python
# -*- coding:utf-8 -*-
Import os,sys,re,getopt
Import MySQLdb
Host = '127.0.0.1'
User = ''
Password = ''
Port = 3306
start_dateTIme = '1971-01-01 00:00:00'
stop_dateTIme = '2037-01-01 00:00:00'
start_posiTIon = '4'
stop_posiTIon = '18446744073709551615'
Database = ''
Mysqlbinlog_bin = 'mysqlbinlog -v'
Binlog = ''
fileContent = ''
Output='rollback.sql'
Only_primary = 0
# ------------------------------------------------- ---------------------------------------
# Function: Get the parameters and generate the corresponding binlog parsing file.
# ------------------------------------------------- ---------------------------------------
Def getopts_parse_binlog():
Global host
Global user
Global password
Global port
Global fileContent
Global output
Global binlog
Global start_datetime
Global stop_datetime
Global start_position
Global stop_position
Global database
Global only_primary
Try:
Options, args = getopt.getopt(sys.argv[1:], "f:o:h:u:p:P:d:",["help","binlog=","output=","host =","user=","password=","port=","start-datetime=", \
"stop-datetime=", "start-position=", "stop-position=", "database=", "only-primary="])
Except getopt.GetoptError:
Print "The parameter input is incorrect!!!!!"
Options = []
If options == [] or options[0][0] in ("--help"):
Usage()
Sys.exit()
Print "Getting parameters...."
For name, value in options:
If name == "-f" or name == "--binlog":
Binlog = value
If name == "-o" or name == "--output":
Output = value
If name == "-h" or name == "--host":
Host = value
If name == "-u" or name == "--user":
User = value
If name == "-p" or name == "--password":
Password = value
If name == "-P" or name == "--port":
Port = value
If name == "--start-datetime":
Start_datetime = value
If name == "--stop-datetime":
Stop_datetime = value
If name == "--start-position":
Start_position = value
If name == "--stop-position":
Stop_position = value
If name == "-d" or name == "--database":
Database = value
If name == "--only-primary" :
Only_primary = value
If binlog == '' :
Print "Error: Please specify the binlog file name!"
Usage()
If user == '' :
Print "Error: Please specify a username!"
Usage()
If password == '' :
Print "Error: Please specify a password!"
Usage()
If database "" '' :
Condition_database = "--database=" + "'" + database + "'"
Else:
Condition_database = ''
Print "Parsing binlog...."
fileContent=os.popen("%s %s --base64-output=DECODE-ROWS --start-datetime='%s' --stop-datetime='%s' --start-position='%s' --stop-position='%s' %s\
|grep '###' -B 2|sed -e 's/### //g' -e 's/^INSERT/##INSERT/g' -e 's/^UPDATE/##UPDATE/ g' -e 's/^DELETE/##DELETE/g' †\
%(mysqlbinlog_bin,binlog,start_datetime,stop_datetime,start_position,stop_position,condition_database)).read()
#print fileContent
Ningbo Bwinners Optical Tech Co., Ltd , https://www.sijee-optical.com