MySQL uses binary logs to recover misused data
When performing manual database write operations, such as data correction, especially when dealing with uncontrolled batch updates or deletions, it's generally recommended to perform a backup before making any changes. However, being prepared is always better than being afraid of the unknown. If an accidental operation occurs in a production or test environment, leading to data deletion or modification, there are typically two common methods for data recovery.
Method 1: Use the most recent full backup combined with incremental binary log backups to restore the system to its state before the incident. However, as the amount of data and the size of the binary logs increase, this process can become time-consuming and inefficient.
Method 2: If the binary log format is set to 'row', you can parse the binary logs to generate reverse SQL statements that undo the original operations. This method is particularly useful for recovering from unintended DML (Data Manipulation Language) actions like inserts, updates, and deletes.
The following Python script, binlog_rollback.py, implements Method 2 by parsing the binary logs and generating rollback SQL commands. It is designed to be used with row-based binary logs and requires a read-only user to fetch table structures from MySQL.
Description:
0. The binary log must be in row format for this script to work correctly.
1. The table structure should remain unchanged before and after the operation; otherwise, the script may fail to parse the logs accurately.
2. The script only generates rollback statements for DML operations (INSERT, UPDATE, DELETE).
3. The generated SQL is in reverse order, with the latest DML operation appearing at the top of the file. Each entry includes a timestamp and offset, making it easier to locate specific changes.
4. A read-only user account is required to connect to MySQL and retrieve the necessary table metadata.
5. For large binary logs, it's advisable to specify a time range or limit the scope to a particular database to improve performance.
6. After generating the SQL, it's strongly recommended 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: Parse command-line options 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 not 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
elif name == "-o" or name == "--output":
output = value
elif name == "-h" or name == "--host":
host = value
elif name == "-u" or name == "--user":
user = value
elif name == "-p" or name == "--password":
password = value
elif name == "-P" or name == "--port":
port = value
elif name == "--start-datetime":
start_datetime = value
elif name == "--stop-datetime":
stop_datetime = value
elif name == "--start-position":
start_position = value
elif name == "--stop-position":
stop_position = value
elif name == "-d" or name == "--database":
database = value
elif name == "--only-primary":
only_primary = value
if not binlog:
print("Error: Please specify the binlog file name!")
usage()
if not user:
print("Error: Please specify a username!")
usage()
if not password:
print("Error: Please specify a password!")
usage()
condition_database = "--database='%s'" % database if database else ''
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()
Switching Power,Embedded Switching Power,Intelligent Battery Management,Power Supply System
Guangdong Yuqiu Intelligent Technology Co.,Ltd , https://www.cntcetltd.com