MySQL数据库：容灾测试
基于 test 库和 t1 表的容灾测试
测试目标
- 手动删除
test.t1表。 - 使用主库的 binlog 恢复表结构和数据（50 万条记录）。
- 验证恢复后
test.t1表行数为 50 万，并确保主从复制和 ProxySQL 路由正常。
环境信息
- 主库：127.0.0.1:3306，数据目录
/var/lib/mysql，binlog 配置log_bin = /var/log/mysql/mysql-bin.log。 - 从库：127.0.0.1:3307，数据目录
/var/lib/mysql_slave。 - ProxySQL：127.0.0.1:6033（客户端端口），6032（管理端口）。
- 测试表：
test.t1（已通过存储过程generate_posts插入 50 万条数据），若没有创建test数据库和t1表可在自行根据下文的创建test数据库和t1表栏目进行创建。
请结合自身实际环境和文件还有命令执行的结果进行操作，不要照抄！
创建test数据库和t1表（如果已有跳过）
创建test数据库和test表
PS：主库创建的表插入的数据正常情况下一般会自动同步到从库，所以不需要在从库再执行一次！！！
在主库执行：
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE IF NOT EXISTS t1 (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255));
验证：
在主库执行：
SELECT * FROM test.t1;
写一个储存过程
在主库执行：
DELIMITER //
DROP PROCEDURE IF EXISTS generate_posts//
CREATE PROCEDURE generate_posts(IN num_posts INT)
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE next_id INT;
DECLARE start_time BIGINT;
DECLARE batch_size INT DEFAULT 5000; -- 每批插入5000条
SET start_time = UNIX_TIMESTAMP();
SELECT IFNULL(MAX(id), 0) INTO next_id FROM test.t1;
-- 优化设置
SET SESSION unique_checks = 0;
SET SESSION foreign_key_checks = 0;
SET SESSION autocommit = 0;
-- 禁用索引（加速插入）
ALTER TABLE test.t1 DISABLE KEYS;
WHILE i < num_posts DO
SET @batch_values = '';
SET @batch_count = 0;
-- 构建当前批次数据
WHILE i < num_posts AND @batch_count < batch_size DO
SET next_id = next_id + 1;
-- 适配 test.t1 表的字段 (id, data)
SET @batch_values = CONCAT(@batch_values,
IF(@batch_count > 0, ',', ''),
'(',
next_id, ',',
'''测试内容-', i, REPEAT(' 内容填充', 5), ''')'
);
SET i = i + 1;
SET @batch_count = @batch_count + 1;
END WHILE;
-- 执行当前批次插入
SET @sql = CONCAT('INSERT INTO test.t1 (id, data) VALUES ', @batch_values);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 每批提交一次
COMMIT;
-- 进度报告
IF i % 10000 = 0 THEN
SELECT CONCAT('已插入 ', i, '/', num_posts, ' 条 (',
ROUND(i/num_posts*100,1), '%)') AS progress;
END IF;
END WHILE;
-- 恢复设置
ALTER TABLE test.t1 ENABLE KEYS;
COMMIT;
SET SESSION unique_checks = 1;
SET SESSION foreign_key_checks = 1;
SET SESSION autocommit = 1;
-- 性能报告
SELECT CONCAT('插入完成! 共 ', num_posts, ' 条，耗时: ',
ROUND((UNIX_TIMESTAMP()-start_time)/60,2), ' 分钟') AS result;
END//
DELIMITER ;
插入50万条数据
在主库执行：
CALL generate_posts(500000);
使用LIMIT子句查询测试前100条，看看有没有数据
在主库执行：
SELECT * FROM test.t1 LIMIT 100;
验证数据是否存在
容灾测试
1. 备份表
为防止意外数据丢失，建议先备份 test.t1 表，或者给虚拟机打快照方便回滚。
2. 手动删除 test.t1 表
删除 test.t1 表以模拟误操作。
执行位置：主库 (127.0.0.1:3306)
-- 登录主库
mysql -u root -p -h 127.0.0.1 -P 3306
-- 确认表存在
USE test;
SHOW TABLES LIKE 't1';
-- 删除表
DROP TABLE test.t1;
验证删除：
执行位置：主库 (127.0.0.1:3306)
SHOW TABLES LIKE 't1'; -- 应返回空结果
注意：
- 删除操作会记录在主库 binlog 中，主从复制会自动同步到从库，导致从库的
test.t1表也被删除。 为避免客户端通过 ProxySQL 访问已删除的表，临时禁用 ProxySQL 路由规则： 执行位置：ProxySQL 管理端 (127.0.0.1:6032)
mysql -u admin -padmin -h 127.0.0.1 -P 6032执行SQL语句
执行位置：ProxySQL 管理端 (127.0.0.1:6032)
UPDATE mysql_query_rules SET active=0 WHERE rule_id IN (1,2,3,4,5); LOAD MYSQL QUERY RULES TO RUNTIME;
3. 找到最近 binlog 文件
查找主库的最新 binlog 文件，用于恢复 test.t1 表。
执行位置：主库 (127.0.0.1:3306)
SHOW BINARY LOGS;
示例输出：
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 629 | No |
| mysql-bin.000002 | 49921655 | No |
| mysql-bin.000003 | 361 | No |
+------------------+-----------+-----------+
记录最新 binlog 文件名（例如 mysql-bin.000002）。
查找 binlog 位置：
在主库查找binlog位置
SHOW VARIABLES LIKE 'log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+--------------------------------+
5 rows in set (0.02 sec)
4. 检查表并分析
实用的恢复参考步骤
场景 1：恢复误删的表
mysql-bin.000002：CREATE TABLE t1+INSERT INTO t1mysql-bin.000003：DROP TABLE t1- 恢复方案：只需恢复
000002（跳过000003的删除操作）。
场景 2：恢复部分丢失数据
mysql-bin.000002：CREATE TABLE t1mysql-bin.000004：INSERT INTO t1（关键数据）- 恢复方案：需恢复
000002（结构） +000004（数据）。
恢复思路
按照我们的需求，恢复误删的表，正好对应上文的实用的恢复步骤的场景 1：恢复误删的表
严格按照步骤来
先分析创建和插入语句
分析binlog命令
先检查和分析CREATE TABLE（创建）和INSERT INTO（插入）语句存在于哪些binlog中
文件的位置按上文查找 binlog 位置找到的位置来操作，上文找到的位置为/var/lib/mysql/mysql-bin
在命令行直接执行这些检查命令（不是数据库命令行）
# 检查 mysql-bin.000001
mysqlbinlog --database=test /var/lib/mysql/mysql-bin.000001 | grep -i "CREATE TABLE.*t1" -A 10
mysqlbinlog --database=test /var/lib/mysql/mysql-bin.000001 | grep -i "INSERT INTO.*t1" | head
# 检查 mysql-bin.000002
mysqlbinlog --database=test /var/lib/mysql/mysql-bin.000002 | grep -i "CREATE TABLE.*t1" -A 10
mysqlbinlog --database=test /var/lib/mysql/mysql-bin.000002 | grep -i "INSERT INTO.*t1" | head
# 检查 mysql-bin.000003
mysqlbinlog --database=test /var/lib/mysql/mysql-bin.000003 | grep -i "CREATE TABLE.*t1" -A 10
mysqlbinlog --database=test /var/lib/mysql/mysql-bin.000003 | grep -i "INSERT INTO.*t1" | head
分析结果
分析mysql-bin.000001
上图这个输出结果表示 在 mysql-bin.000001文件中未找到 t1 表的 CREATE TABLE 语句，也未找到 t1 表的 INSERT INTO 语句
root@debian-3:~# mysqlbinlog --database=test /var/lib/mysql/mysql-bin.000001 | grep -i "CREATE TABLE.*t1" -A 10
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
root@debian-3:~#
root@debian-3:~# mysqlbinlog --database=test /var/lib/mysql/mysql-bin.000001 | grep -i "INSERT INTO.*t1" | head
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
分析mysql-bin.000002
输出结果表示 在 mysql-bin.000002文件中未找到 t1 表的 INSERT INTO 语句，仅发现动态生成的插入语句片段（SET @sql = CONCAT('INSERT INTO test.t1 (id, data) VALUES ', @batch_values);），但是找到了 t1表的 CREATE TABLE语句。（这正是我们想知道的）
找到 CREATE TABLE t1记录
→ 说明这个 binlog 文件包含该表的创建事件（表曾经存在过）。
root@debian-3:~# mysqlbinlog --database=test /var/lib/mysql/mysql-bin.000002 | grep -i "CREATE TABLE.*t1" -A 10
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
CREATE TABLE IF NOT EXISTS t1 (id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(255))
/*!*/;
# at 1991
#250722 22:13:09 server id 1 end_log_pos 2068 CRC32 0x54aa7d04 Anonymous_GTID last_committed=8 sequence_number=9 rbr_only=no original_committed_timestamp=1753193589834698 immediate_commit_timestamp=1753193589834698 transaction_length=186
# original_commit_timestamp=1753193589834698 (2025-07-22 22:13:09.834698 CST)
# immediate_commit_timestamp=1753193589834698 (2025-07-22 22:13:09.834698 CST)
/*!80001 SET @@session.original_commit_timestamp=1753193589834698*//*!*/;
/*!80014 SET @@session.original_server_version=80042*//*!*/;
/*!80014 SET @@session.immediate_server_version=80042*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2068
root@debian-3:~#
root@debian-3:~# mysqlbinlog --database=test /var/lib/mysql/mysql-bin.000002 | grep -i "INSERT INTO.*t1" | head
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
SET @sql = CONCAT('INSERT INTO test.t1 (id, data) VALUES ', @batch_values);
root@debian-3:~#
分析mysql-bin.000003
输出结果表示 在 mysql-bin.000003文件中未找到 t1 表的 CREATE TABLE 语句，也未找到 t1 表的 INSERT INTO 语句。
root@debian-3:~# mysqlbinlog --database=test /var/lib/mysql/mysql-bin.000003 | grep -i "CREATE TABLE.*t1" -A 10
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
root@debian-3:~#
root@debian-3:~# mysqlbinlog --database=test /var/lib/mysql/mysql-bin.000003 | grep -i "INSERT INTO.*t1" | head
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
root@debian-3:~#
再次分析删除语句
其次检查和分析DROP TABLE（删除）语句存在于哪些binlog中
在命令行直接执行这些检查命令（不是数据库命令行）
分析binlog命令
# 检查 mysql-bin.000001
mysqlbinlog /var/lib/mysql/mysql-bin.000001 | grep -i "DROP TABLE.*t1"
# 检查 mysql-bin.000002
mysqlbinlog /var/lib/mysql/mysql-bin.000002 | grep -i "DROP TABLE.*t1"
# 检查 mysql-bin.000003
mysqlbinlog /var/lib/mysql/mysql-bin.000003 | grep -i "DROP TABLE.*t1"
分析结果
分析mysql-bin.000001
输出结果表示 在 mysql-bin.000001文件中没有找到 t1表的 DROP语句。
root@debian-3:~# mysqlbinlog /var/lib/mysql/mysql-bin.000001 | grep -i "DROP TABLE.*t1"
分析mysql-bin.000002
输出结果表示 在 mysql-bin.000002文件中没有找到 t1表的 DROP语句。
root@debian-3:~# mysqlbinlog /var/lib/mysql/mysql-bin.000002 | grep -i "DROP TABLE.*t1"
分析mysql-bin.000003
输出结果表示 在 mysql-bin.000003文件中找到 t1 表的 DROP TABLE 语句（DROP TABLE t1 / generated by server /）。（这正是我们想知道的）
root@debian-3:~# mysqlbinlog /var/lib/mysql/mysql-bin.000003 | grep -i "DROP TABLE.*t1"
DROP TABLE `t1` /* generated by server */
分析结束
我们从之前对binlog的分析可得知mysql-bin.000002包含INSERT INTO（插入）语句和CREATE TABLE语句（创建）语句，mysql-bin.000003包含DROP（删除）语句
总结就是：
mysql-bin.000002：包含t1表的CREATE TABLE（创建表）和INSERT INTO（插入数据）语句。mysql-bin.000003：包含t1表的DROP TABLE（删除表）语句。mysql-bin.000001：未找到t1表的任何CREATE TABLE、INSERT INTO或DROP TABLE语句。
开始恢复前需要知道
MySQL 数据库恢复主要有 两种常用方式：
1. 通过 ROLLBACK 或备份文件（如 .sql 文件）恢复
适用场景
- 从 逻辑备份（如
mysqldump生成的.sql文件）恢复。 - 需要 还原到某个备份时间点 的完整数据。
- 数据库被完全删除或损坏后重建。
操作方法（仅供示例展示，请勿执行下面代码块的代码）
# 恢复整个数据库（从 mysqldump 备份）
mysql -u root -p < backup_full.sql
# 恢复单个数据库
mysql -u root -p test < backup_test.sql
特点
- ✅ 简单直接：适合全量恢复。
- ❌ 无法精确恢复增量数据：只能恢复到备份时的状态，之后的数据变更可能丢失。
2. 通过指定 binlog 恢复
适用场景
- 从 二进制日志（binlog） 恢复 增量数据。
- 需要 恢复到某个时间点（如误删数据前）。
- 配合全量备份使用（先恢复备份，再用 binlog 追增量）。
操作方法（仅供示例展示，请勿执行下面代码块的代码）
# 恢复到指定 binlog 文件（如 mysql-bin.000002）
mysqlbinlog /var/lib/mysql/mysql-bin.000002 | mysql -u root -p
# 按时间点恢复（如恢复到 2025-07-23 10:00:00）
mysqlbinlog --stop-datetime="2025-07-23 10:00:00" /var/lib/mysql/mysql-bin.* | mysql -u root -p
# 按事件位置恢复（精确控制）
mysqlbinlog --start-position=1991 --stop-position=2068 /var/lib/mysql/mysql-bin.000002 | mysql -u root -p
特点
- ✅ 支持增量恢复：可精确恢复到误操作前的状态。
- ❌ 依赖 binlog 完整性：如果 binlog 被删除或损坏，可能无法恢复。
3. 两种方式的结合使用（推荐）
实际生产中，通常 先恢复全量备份，再用 binlog 追增量（仅供示例展示，请勿执行下面代码块的代码）：
# 步骤 1：恢复全量备份
mysql -u root -p < backup_full.sql
# 步骤 2：恢复备份时间点之后的 binlog
mysqlbinlog --start-datetime="2025-07-22 00:00:00" /var/lib/mysql/mysql-bin.* | mysql -u root -p
区别
| 特性 | 通过 .sql 文件恢复 |
通过 binlog 恢复 |
|---|---|---|
| 数据范围 | 全量数据（备份时间点） | 增量数据（备份后的变更） |
| 精度 | 只能恢复到备份时刻 | 可精确到时间点或事件位置 |
| 依赖条件 | 需定期备份 | 需开启 binlog 且日志未丢失 |
| 典型场景 | 数据库崩溃后重建 | 误删数据、误更新后回滚 |
如何选择？
- 如果只有备份文件（如
rollback.sql）：- 优先用
.sql文件恢复（但会丢失备份后的新数据）。
- 优先用
- 如果没有备份文件（如
rollback.sql）：- 优先用
binlog进行恢复。
- 优先用
- 如果需要恢复误操作（如误删表）：
- 先用最近的全量备份恢复，再用 binlog 恢复到误操作前。
- 如果 binlog 已丢失：
- 只能依赖备份文件，无法恢复增量数据。
5. 开始恢复操作
这边演示最坏的情况，没有备份进行恢复，选择binlog进行恢复
已知的分析结果信息
mysql-bin.000002：包含CREATE TABLE t1和INSERT INTO t1（需恢复的数据）。mysql-bin.000003：包含DROP TABLE t1（需排除的操作）。
恢复步骤
非精细化恢复方案（非常简单）
1. 直接强制重放所有操恢复数据（仅用 mysql-bin.000002）
直接强制重放 000002 中的所有操作，且忽略所有错误（确保不包含删除），使用 --force 参数防止中断：
--force：忽略所有错误（如重复建表、用户已存在等）- 效果：
- 如果表已存在 → 跳过
CREATE TABLE错误 - 如果数据已存在 → 部分
INSERT可能报错，但后续操作会继续执行
- 如果表已存在 → 跳过
mysqlbinlog /var/lib/mysql/mysql-bin.000002 | mysql -u root -p --force
若能正常恢复数据请忽略控制台输出的报错
2. 验证数据
应该会出现表和数据的行数
mysql -u root -p -e "USE test; SHOW TABLES; SELECT COUNT(*) FROM t1;"
3. 验证行数恢复到 50 万
确认 test.t1 表行数是否恢复到 50 万。
执行位置：主库 (127.0.0.1:3306)
SELECT COUNT(*) FROM test.t1;
预期输出：
+----------+
| COUNT(*) |
+----------+
| 500000 |
+----------+
从库验证： 检查从库是否同步（主从复制应自动同步恢复的表和数据）：
执行位置：从库 (127.0.0.1:3307)
SELECT COUNT(*) FROM test.t1;
检查主从延迟：
SHOW SLAVE STATUS\G
确认 Seconds_Behind_Master 为 0。
数据完整性： 随机抽查数据：
SELECT * FROM test.t1 WHERE id IN (1, 1000, 500000) ORDER BY id;
或使用可视化客户端查看是否恢复数据：
4. 恢复 ProxySQL 路由
恢复完成后，重新启用 ProxySQL 路由规则。
执行位置：ProxySQL 管理端 (127.0.0.1:6032)
mysql -u admin -padmin -h 127.0.0.1 -P 6032
UPDATE mysql_query_rules SET active=1 WHERE rule_id IN (1,2,3,4,5);
LOAD MYSQL QUERY RULES TO RUNTIME;
验证 ProxySQL 路由： 通过 ProxySQL 执行查询，确认读写分离正常：
mysql -u proxyuser -p12345678 -h 127.0.0.1 -P 6033 -e "SELECT * FROM test.t1 LIMIT 1;"
检查路由统计：
SELECT * FROM stats_mysql_query_rules;
5. 清理与日志检查（可选，且非必要，谨慎执行）
清理 binlog： 恢复成功后，清理旧 binlog 文件：
PURGE BINARY LOGS TO 'mysql-bin.000002';检查日志：
tail -n 50 /var/log/mysql/error.log tail -n 50 /var/log/proxysql.log
如果需要精细化的恢复，请看下面的步骤，但是非常麻烦，本文选择使用上面较简单的非精细化恢复方案，直接重放 000002 中的所有操作。
精细化恢复方案（非常麻烦，且未验证）
1. 导出完整 binlog 到 SQL 文件
mysqlbinlog -v /var/lib/mysql/mysql-bin.000002 > recovery.sql
-v参数：将 ROW 格式的二进制日志转换为可读 SQL- 建议路径：保存到
/tmp/目录（避免权限问题）
2. 编辑 SQL 文件（Vim 示例）
vim recovery.sql
需要删除的内容：
/* 删除所有无关操作（保留 t1 表相关） */
DROP DATABASE `other_db`;
CREATE USER 'monitor'@'%';
ALTER TABLE `other_table` ...;
需要保留的内容：
CREATE TABLE `t1` (...);
INSERT INTO `t1` VALUES (...);
UPDATE `t1` SET ...;
编辑技巧：
- 搜索表名：
/t1 - 删除整行：
dd - 保存退出：
:wq
3. 执行编辑后的 SQL
mysql -u root -p test < recovery.sql
4. 验证恢复结果
mysql -u root -p -e "USE test; SHOW CREATE TABLE t1; SELECT COUNT(*) FROM t1;"
为什么这是最可靠的方法？
| 优势 | 说明 |
|---|---|
| 完全控制 | 可精确删除无关语句（如用户管理、其他表操作） |
| 避免管道错误 | 直接编辑文件比管道过滤更可靠 |
| 可审计性 | 保留 SQL 文件作为恢复记录 |
高级技巧
1. 按事务位置精准提取
mysqlbinlog \
--start-position=120 \
--stop-position=199 \
/var/lib/mysql/mysql-bin.000002 \
> partial_recovery.sql
2. 批量注释无关语句
sed -i '/^CREATE USER/d;/^DROP DATABASE/d' recovery.sql
3. 恢复前模拟测试
mysql -u root -p --execute="SOURCE recovery.sql" --force --show-warnings
完整示例流程
# 1. 导出并编辑
mysqlbinlog -v /var/lib/mysql/mysql-bin.000002 > /tmp/recovery.sql
vim /tmp/recovery.sql
# 2. 执行恢复（先测试后正式）
mysql -u root -p -e "SOURCE /tmp/recovery.sql" --show-warnings
mysql -u root -p test < /tmp/recovery.sql
# 3. 验证
mysql -u root -p -e "USE test; SELECT * FROM t1 LIMIT 10;"