MySQL BinLog 数据还原恢复

博文目录

---

查看状态

查看 binlog 开关及存储路径

show variables like '%log_bin%';

查看 binlog 配置 如 存储格式 binlog_format

show variables like '%binlog_%';

查看当前还存在的日志

show master logs;

查看当前正在使用的日志

show master status;

切换日志

flush logs;

确定日志

确定日志文件

到 binlog 日志目录, 根据操作时间和日志时间, 即可确认记录保存在哪一个日志中

日志格式改写

在日志目录下, 使用 mysql 自带的工具 mysqlbinlog 将二进制日志转为文本日志, 便于查找

mysqlbinlog binlog.000016 > binlog.bak

日志简要说明

binlog 日志以事件为单位写入, 以 at 作为日志的开始, 两个 at 之间的内容就是一个完整的事件

下面是从日志中截取出来的一段, 是在 binlog.bak 中查找内容 tzh_ths_benchmark 找到的目标日志, 为了便于查看, 加入分割符用于间隔每一个日志, at 后面的数字就是本日志的起始点, end_log_pos 后面的数字就是本日志的结束点, at 下一行就是事件的日期时间, 可以结合操作时间定位, 再下面的内容就是具体的日志数据, 每一个日志都以 /*!*/; 字符作为结束标记

'/*!*/;
========== ========== ========== ========== ========== ========== ========== ========== ========== ==========
# at 361652921
#231208 11:07:08 server id 1  end_log_pos 361652952 CRC32 0x2f4eae24    Xid = 44300746
COMMIT/*!*/;
========== ========== ========== ========== ========== ========== ========== ========== ========== ==========
# at 361652952
#231208 15:43:25 server id 1  end_log_pos 361653031 CRC32 0x32ff5692    Anonymous_GTID  last_committed=753211   sequence_number=753212  rbr_only=no     original_committed_timestamp=1702021405548520   immediate_commit_timestamp=1702021405548520     transaction_length=1604
# original_commit_timestamp=1702021405548520 (2023-12-08 15:43:25.548520 CST)
# immediate_commit_timestamp=1702021405548520 (2023-12-08 15:43:25.548520 CST)
/*!80001 SET @@session.original_commit_timestamp=1702021405548520*//*!*/;
/*!80014 SET @@session.original_server_version=80030*//*!*/;
/*!80014 SET @@session.immediate_server_version=80030*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
========== ========== ========== ========== ========== ========== ========== ========== ========== ==========
# at 361653031
#231208 15:43:25 server id 1  end_log_pos 361654556 CRC32 0x07b1e62c    Query   thread_id=100812        exec_time=0     error_code=0    Xid = 44313321
SET TIMESTAMP=1702021405/*!*/;
SET @@session.foreign_key_checks=0/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `tzh_ths_benchmark_copy1` (`id` bigint unsigned NOT NULL AUTO_INCREMENT,`type` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '参数类型: FILL:填写, CAlC:计算',`relation` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '指标类型, SIMPLE:简单指标, MERGED:组合指标',`status` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '关联状态: LINKED:已关联, UNLINKED:未关联',`parameter_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '数据类型的id, 可由此拿到排放类型的一二级id',`merge_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '合并组的id',`energy_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '数据类型关联的耗能项',`formula_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '数据类型关联的耗能项的公式',`name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '排放名称, 可认为是指标名称',`formula` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '组合指标的消耗量计算公式, id+/-id, 包含关联项和关联关系',PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=220 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新能源云-碳核算-指标(参数关联)'
/*!*/;
========== ========== ========== ========== ========== ========== ========== ========== ========== ==========
# at 361654556
#231208 15:43:25 server id 1  end_log_pos 361654635 CRC32 0xd4b43d02    Anonymous_GTID  last_committed=753212   sequence_number=753213  rbr_only=yes    original_committed_timestamp=1702021405568586   immediate_commit_timestamp=1702021405568586     transaction_length=2068
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1702021405568586 (2023-12-08 15:43:25.568586 CST)
# immediate_commit_timestamp=1702021405568586 (2023-12-08 15:43:25.568586 CST)
/*!80001 SET @@session.original_commit_timestamp=1702021405568586*//*!*/;
/*!80014 SET @@session.original_server_version=80030*//*!*/;
/*!80014 SET @@session.immediate_server_version=80030*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
========== ========== ========== ========== ========== ========== ========== ========== ========== ==========
# at 361654635
#231208 15:43:25 server id 1  end_log_pos 361654712 CRC32 0x44693e7a    Query   thread_id=100812        exec_time=0     error_code=0
SET TIMESTAMP=1702021405/*!*/;
BEGIN
/*!*/;
========== ========== ========== ========== ========== ========== ========== ========== ========== ==========
# at 361654712
#231208 15:43:25 server id 1  end_log_pos 361654808 CRC32 0x53c97542    Table_map: `carbon`.`tzh_ths_benchmark_copy1` mapped to number 1003
========== ========== ========== ========== ========== ========== ========== ========== ========== ==========
# at 361654808
#231208 15:43:25 server id 1  end_log_pos 361656593 CRC32 0x0348b3f8    Write_rows: table id 1003 flags: STMT_END_FBINLOG '
HclyZRMBAAAAYAAAABhqjhUAAOsDAAAAAAEABmNhcmJvbgAXdHpoX3Roc19iZW5jaG1hcmtfY29w
eTEACggPDw8ICAgIDw8KIAAgACAAgAD8AwAAAQH4AgP8/wBCdclT
HclyZR4BAAAA+QYAABFxjhUAAOsDAAAAAAMAAgAK//8AAAkAAAAAAAAABEZJTEwGU0lNUExFCFVO
TElOS0VEGAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAoAAAAAAAAABEZJTEwG
:

确定日志位置

文本文件通过 less 命令可以检索到目标操作记录

• less binlog.bak: 用 less 方式打开日志
• /xxxxx, 在日志中查找字符串 xxxxx
• 键 N: 跳转到下一个 xxxxx
• 键 Shift + N, 跳转到上一个 xxxxx
• 键 G: 跳转到文本文件开始
• 键 Shift + G, 跳转到文本文件末尾

以事件为单位查看日志

将位置 361652952 作为起始点, 列出后续的事件列表, 包括起始位置 Pos 和 结束位置 End_log_pos, 和文本文件一一对应

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

show binlog events IN 'binlog.000016' FROM 361652952;

分析日志

本次回滚的目的是还原 tzh_ths_benchmark 表及其数据, 因为计划的操作流程是在 Navicat 中, 拷贝该表为 tzh_ths_benchmark_copy1, 然后修改拷贝表, 结果却误操作改了原表, 导致数据丢失 …

本次还原的方案是, 重现 binlog 中拷贝表的步骤, 还原 tzh_ths_benchmark_copy1 表, 然后重命名为原表. 全部流程对应下面选中的内容, 核心在 创建表 和 Write_rows 事件. 最终确定本次恢复数据的 binlog 起始点和结束点分别为 361653031 和 361656701

还原数据

根据确认的起始结束点位置还原数据, 执行完后, 刷新数据库即可看到 tzh_ths_benchmark_copy1 已经回来了, 且数据也都在

mysqlbinlog -v binlog.000016 --start-position=361653031 --stop-position=361656701 | mysql -uroot -p123456