MySQL Uses Binlog to Recover Misused Data
When performing manual database write operations, such as data correction, especially when dealing with uncontrolled batch updates or deletions, it is generally recommended to perform a backup before making any changes. However, being prepared is always better than being afraid of the worst. There are typically two common methods for restoring data after an accidental operation in a production or test environment that has caused data deletion or modification.
Method 1: Using the most recent full backup combined with incremental binlog backups, you can restore the database to its state before the incident. However, as the amount of data increases, so does the size of the binlog, which can make recovery time-consuming and inefficient.
Method 2: If the binlog format is set to "row," it's possible to parse the binlog and generate the reverse SQL statements for rollback purposes. This method is particularly useful when you need to undo specific operations without restoring the entire database.
The following Python script, binlog_rollback.py, is designed based on Method 2. It helps generate reverse DML (Data Manipulation Language) SQL statements for rollback operations.
Description:
0. The premise is that the binlog format must be set to "row".
1. The table structure before and after the operation should remain unchanged; otherwise, the script will not work properly.
2. Only DML statements (INSERT, UPDATE, DELETE) are supported for rollback generation.
3. The generated SQL is in reverse order, with the latest DML statement appearing at the top of the file. Each entry includes a timestamp and offset, making it easier to locate the exact point of interest.
4. A read-only user must be provided to connect to MySQL, mainly to retrieve the table structure needed for parsing.
5. If the binlog is large, it’s recommended to specify a time range or restrict the operation to a single database.
6. After generating the SQL, it is crucial to test it in a non-production environment before applying it to a live system.
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 parameters and generate the corresponding binlog parsing file.
# --------------------------------------------------
def getopts_parse_binlog():
global host, user, password, port, fileContent, output, binlog, start_datetime, stop_datetime, start_position, stop_position, database, 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()
Outdoor Cabinet Lock,Cabinet Material,Outdoor Telecom,Electrical Cabinet
Guangdong Yuqiu Intelligent Technology Co.,Ltd , https://www.cntcetltd.com