MYSQL8-CentOS环境-利用binlog2sql恢复表（表结构、数据、主外键、索引、触发器等...）

MYSQL8-CentOS环境-利用binlog2sql恢复表（表结构、数据、主外键、索引、触发器等...）

一、创建测试用的数据库（recovery_test_db）和表（customers、orders、order_audit_log），并插入测试数据

-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS recovery_test_db;
USE recovery_test_db;

-- 创建主表
CREATE TABLE customers (
	customer_id INT AUTO_INCREMENT,
	first_name VARCHAR(50) NOT NULL,
	last_name VARCHAR(50) NOT NULL,
	email VARCHAR(100) UNIQUE,
	phone VARCHAR(20),
	registration_date DATETIME DEFAULT CURRENT_TIMESTAMP,
	credit_limit DECIMAL(10,2) CHECK (credit_limit >= 0),
	status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
	PRIMARY KEY (customer_id),
	INDEX idx_name (last_name, first_name),
	INDEX idx_email (email),
	FULLTEXT INDEX idx_fulltext_name (first_name, last_name)
) ENGINE=InnoDB;

-- 创建从表（有外键关联）
CREATE TABLE orders (
	order_id INT AUTO_INCREMENT,
	customer_id INT NOT NULL,
	order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
	total_amount DECIMAL(12,2) NOT NULL,
	status VARCHAR(20) DEFAULT 'processing',
	payment_method ENUM('credit_card', 'paypal', 'bank_transfer'),
	PRIMARY KEY (order_id),
	FOREIGN KEY (customer_id) 
		REFERENCES customers(customer_id)
		ON DELETE RESTRICT
		ON UPDATE CASCADE,
	INDEX idx_customer (customer_id),
	INDEX idx_date_status (order_date, status),
	CONSTRAINT chk_amount CHECK (total_amount > 0)
) ENGINE=InnoDB;

-- 创建日志表（用于触发器测试）
CREATE TABLE order_audit_log (
	log_id INT AUTO_INCREMENT PRIMARY KEY,
	order_id INT NOT NULL,
	action VARCHAR(20) NOT NULL,
	change_date DATETIME DEFAULT CURRENT_TIMESTAMP,
	changed_by VARCHAR(50),
	notes TEXT
) ENGINE=InnoDB;

-- 创建BEFORE INSERT触发器
DELIMITER //
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
	IF NEW.total_amount > 10000 THEN
		SET NEW.status = 'pending_approval';
	END IF;
END//
DELIMITER ;

-- 创建AFTER INSERT触发器
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
	INSERT INTO order_audit_log (order_id, action, changed_by, notes)
	VALUES (NEW.order_id, 'create', CURRENT_USER(), 'New order created');
END//
DELIMITER ;

-- 创建AFTER UPDATE触发器
DELIMITER //
CREATE TRIGGER after_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO order_audit_log (order_id, action, changed_by, notes)
	VALUES (NEW.order_id, 'update', CURRENT_USER(), 
			CONCAT('Status changed from ', OLD.status, ' to ', NEW.status));
END//
DELIMITER ;

-- 插入测试数据
INSERT INTO customers (first_name, last_name, email, phone, credit_limit)
VALUES 
('张', '三', 'zhangsan@example.com', '13800138001', 5000.00),
('李', '四', 'lisi@example.com', '13900139001', 10000.00),
('王', '五', 'wangwu@example.com', '13700137001', 15000.00);

INSERT INTO orders (customer_id, total_amount, payment_method)
VALUES 
(1, 1200.50, 'credit_card'),
(2, 550.75, 'paypal'),
(3, 12000.00, 'bank_transfer');

二、查看创建的表

2.1、 表 customers

2.2、表 order

2.3、表 order_audit_log

三、执行语句删除 这三张表

-- Drop order: Start with tables that have foreign keys referencing others
DROP TABLE IF EXISTS order_audit_log;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

四、从二进制日志恢复（需启用binlog）

4.1、确认binlog是否启用

SHOW VARIABLES LIKE 'log_bin';

4.2、查看所有可用的binlog文件

SHOW BINARY LOGS;

4.3、安装binlog2sql工具

4.3.1、下载安装包

官网下载地址

或者

通过网盘分享的文件：binlog2sql-master.zip
链接: https://pan.baidu.com/s/137lLIQsKODSMwi3ve0e6mQ?pwd=sky1 提取码: sky1

4.3.2、安装相关依赖

pip --version
python --version

4.3.3、安装依赖包

cd /home/binlog2sql-master
pip install -r requirements.txt

4.3.4、查询drop table操作位置

cd /home/binlog2sql-master
python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p<密码> -d<数据库名称> \
--start-file='binlog.000003' \
--start-datetime='2025-06-23 00:00:00' \
--stop-datetime='2025-06-24 23:59:59' \
| grep -i 'DROP TABLE'

4.3.5、根据上面操作，我们已经在binlog.000003中有三张表被drop了：order_audit_log、orders、customers

4.3.6、确定精确的POSITION位置

python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p<密码> -d<数据库名称> \
--start-file='binlog.000003' \
--start-datetime='2025-06-23 00:00:00' \
--stop-datetime='2025-06-24 23:59:59' \
> /tmp/full_binlog_output.txt

grep -A 10 -B 10 'DROP TABLE' /tmp/full_binlog_output.txt

五、查看 /tmp/full_binglog_output.txt文件

部分数据展示如下：

此时我们就可以根据binlog_output.txt文件进行数据恢复了