本文主要是介绍Mysql8利用binlog实现数据恢复，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
文章目录
- 1binlog基本概念
- 2 binlog相关常用命令
- 3 binlog工具mysqlbinlog
- 4 测试数据准备&导入数据
- 5 模拟误删表
- 6 数据恢复方式说明
- 7 数据恢复分析(偏移量方式恢复)
- 8 数据恢复
- 9 验证
- 10 数据恢复的局限性
- 11 总结
1binlog基本概念
binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它是一种逻辑日志，它记录了数据库所有执行的DDL 和 DML 等数据库更新事件的语句，并且不包含没有修改任何数据的语句（如数据查询语句select、show等）。
查看binlog是否已经启用：
mysql创建新的binglog文件的时机：
- binlog 当当前binlog文件大小到达max_binlog_size(默认1G)后，会自动创建新的binlog文件
- 重启数据库
- 执行flush logs命令
2 binlog相关常用命令
binlog日志操作相关SQL命令
- show variables like ‘%log_bin%’：查看binlog相关配置
- show binary logs：查看当前Mysql有哪些二进制日志文件
- show binlog events [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]: 查看特定binlog文件的各个执行事件。
#查看binlog相关配置
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/binlog |
| log_bin_index | /data/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------+
6 rows in set (0.01 sec)#查看当前Mysql有哪些二进制日志文件
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 1457 | No |
| binlog.000002 | 180 | No |
| binlog.000003 | 180 | No |
| binlog.000004 | 3290 | No |
| binlog.000005 | 1270 | No |
+---------------+-----------+-----------+
5 rows in set (0.00 sec)#查看某个binlog文件的执行事件
#（每各事件都会对应一个起始偏移量Pos和停止偏移量End_log_pos ）
mysql> show binlog events in 'binlog.000005' from 0 limit 25;
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000005 | 4 | Format_desc | 2 | 126 | Server ver: 8.0.34, Binlog ver: 4 |
| binlog.000005 | 126 | Previous_gtids | 2 | 157 | |
| binlog.000005 | 157 | Anonymous_Gtid | 2 | 236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000005 | 236 | Query | 2 | 314 | BEGIN |
| binlog.000005 | 314 | Table_map | 2 | 371 | table_id: 92 (test_db.t_test) |
| binlog.000005 | 371 | Write_rows | 2 | 419 | table_id: 92 flags: STMT_END_F |
| binlog.000005 | 419 | Xid | 2 | 450 | COMMIT /* xid=20 */ |
| binlog.000005 | 450 | Anonymous_Gtid | 2 | 527 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000005 | 527 | Query | 2 | 663 | use `test_db`; revoke all privileges on *.* from lb@'%' /* xid=22 */ |
| binlog.000005 | 663 | Anonymous_Gtid | 2 | 740 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000005 | 740 | Query | 2 | 837 | use `test_db`; flush privileges |
| binlog.000005 | 837 | Anonymous_Gtid | 2 | 916 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000005 | 916 | Query | 2 | 1270 | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test\_db`.* TO 'lb'@'%' WITH GRANT OPTION /* xid=47 */ |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 rows in set (0.00 sec)
3 binlog工具mysqlbinlog
- mysqlbinlog --no-defaults -v 指定的binlog文件路径：以sql形式展示某个binlog文件的内容
具体操作如下，下文打印内容和上文SQL的“show binlog events”的偏移量是能一一对应上的。下文的 # at 4 其实就是对应上文的pos偏移量
[root@localhost ~]# mysqlbinlog --no-defaults -v /data/mysql/binlog.000005
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240901 13:53:18 server id 2 end_log_pos 126 CRC32 0x24f3aa05 Start: binlog v 4, server v 8.0.34 created 240901 13:53:18 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
TgHUZg8CAAAAegAAAH4AAAABAAQAOC4wLjM0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABOAdRmEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
CigAAQWq8yQ=
'/*!*/;
# at 126
#240901 13:53:18 server id 2 end_log_pos 157 CRC32 0xca37ebbd Previous-GTIDs
# [empty]
# at 157
#240901 13:54:27 server id 2 end_log_pos 236 CRC32 0xb7848097 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1725170067575001 immediate_commit_timestamp=1725170067575001 transaction_length=293
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1725170067575001 (2024-09-01 13:54:27.575001 CST)
# immediate_commit_timestamp=1725170067575001 (2024-09-01 13:54:27.575001 CST)
/*!80001 SET @@session.original_commit_timestamp=1725170067575001*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 236
#240901 13:54:27 server id 2 end_log_pos 314 CRC32 0x2d8ee1a3 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1725170067/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=224/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 314
#240901 13:54:27 server id 2 end_log_pos 371 CRC32 0xb4a5dc9b Table_map: `test_db`.`t_test` mapped to number 92
# has_generated_invisible_primary_key=0
# at 371
#240901 13:54:27 server id 2 end_log_pos 419 CRC32 0x8a38f051 Write_rows: table id 92 flags: STMT_END_FBINLOG '
kwHUZhMCAAAAOQAAAHMBAAAAAFwAAAAAAAEAB3Rlc3RfZGIABnRfdGVzdAADAwMDAAYBAQCb3KW0
kwHUZh4CAAAAMAAAAKMBAAAAAFwAAAAAAAEAAgAD/wAtAQAAFgAAABMAAABR8DiK
'/*!*/;
### INSERT INTO `test_db`.`t_test`
### SET
### @1=301
### @2=22
### @3=19
# at 419
#240901 13:54:27 server id 2 end_log_pos 450 CRC32 0xa2163ecf Xid = 20
COMMIT/*!*/;
# at 450
#240901 13:57:22 server id 2 end_log_pos 527 CRC32 0xe80c9ead Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1725170242885508 immediate_commit_timestamp=1725170242885508 transaction_length=213
# original_commit_timestamp=1725170242885508 (2024-09-01 13:57:22.885508 CST)
# immediate_commit_timestamp=1725170242885508 (2024-09-01 13:57:22.885508 CST)
/*!80001 SET @@session.original_commit_timestamp=1725170242885508*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 527
#240901 13:57:22 server id 2 end_log_pos 663 CRC32 0xdd22129e Query thread_id=9 exec_time=0 error_code=0 Xid = 22
use `test_db`/*!*/;
SET TIMESTAMP=1725170242/*!*/;
revoke all privileges on *.* from lb@'%'
/*!*/;
# at 663
#240901 13:57:28 server id 2 end_log_pos 740 CRC32 0x62bbc60d Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no original_committed_timestamp=1725170248319634 immediate_commit_timestamp=1725170248319634 transaction_length=174
# original_commit_timestamp=1725170248319634 (2024-09-01 13:57:28.319634 CST)
# immediate_commit_timestamp=1725170248319634 (2024-09-01 13:57:28.319634 CST)
/*!80001 SET @@session.original_commit_timestamp=1725170248319634*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 740
#240901 13:57:28 server id 2 end_log_pos 837 CRC32 0x21ff80fd Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1725170248/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
flush privileges
/*!*/;
# at 837
#240901 13:58:35 server id 2 end_log_pos 916 CRC32 0xfc67c26e Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no original_committed_timestamp=1725170315734840 immediate_commit_timestamp=1725170315734840 transaction_length=433
# original_commit_timestamp=1725170315734840 (2024-09-01 13:58:35.734840 CST)
# immediate_commit_timestamp=1725170315734840 (2024-09-01 13:58:35.734840 CST)
/*!80001 SET @@session.original_commit_timestamp=1725170315734840*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 916
#240901 13:58:35 server id 2 end_log_pos 1270 CRC32 0x246ce28b Query thread_id=14 exec_time=0 error_code=0 Xid = 47
SET TIMESTAMP=1725170315/*!*/;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test\_db`.* TO 'lb'@'%' WITH GRANT OPTION
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
4 测试数据准备&导入数据
- 数据准备
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for t_video
-- ----------------------------
DROP TABLE IF EXISTS `t_video`;
CREATE TABLE `t_video` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',`user_id` bigint(20) NOT NULL COMMENT '用户id',`video_category_id` bigint(20) NOT NULL COMMENT '视频分类id',`title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '标题',`description` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '描述',`duration` int(11) NULL DEFAULT NULL COMMENT '时长。单位-秒',`cover_object_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '封面文件对象名',`cover_url` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '封面文件url',`video_object_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '视频文件对象名',`video_url` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '视频文件url',`like_count` int(11) NOT NULL DEFAULT 0 COMMENT '点赞数',`hate_count` int(11) NOT NULL DEFAULT 0 COMMENT '差评数',`collect_count` int(11) NOT NULL DEFAULT 0 COMMENT '收藏数',`coin_count` int(11) NOT NULL DEFAULT 0 COMMENT '硬币数',`play_count` int(11) NOT NULL DEFAULT 0 COMMENT '播放数',`comment_count` int(11) NOT NULL DEFAULT 0,`review_status` int(1) UNSIGNED ZEROFILL NULL DEFAULT NULL COMMENT '0-未审核，1-审核通过，2-审核不通过',`publish_time` datetime(0) NULL DEFAULT NULL COMMENT '发布日期',`is_published` tinyint(1) NULL DEFAULT NULL COMMENT '是否已发布。0-否，1-是。默认值1',`is_deleted` tinyint(1) NULL DEFAULT NULL COMMENT '是否已删除。0-否，1-是。默认值0',`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建日期',PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1515653549881692161 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of t_video
-- ----------------------------
INSERT INTO `t_video` VALUES (1484768137407762432, 2, 2, '鸡蛋和豆腐在家这样做，非常好吃！', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 91, '2022/01/22/534526686195', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/534526686195', '2022/01/22/191219296881', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/191219296881', 1, 0, 1, 0, 2, 1, 1, '2022-01-22 14:01:24', 1, 0, '2022-01-22 14:01:24');
INSERT INTO `t_video` VALUES (1484809248801165312, 2, 2, '如果你家里只剩下几个土豆，我也可以把它们做成三道超美味的零食', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 52, '2022/01/22/985163119131', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/985163119131', '2022/01/22/876195545628', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/876195545628', 1, 0, 0, 1, 1, 3, 1, '2022-01-22 16:44:46', 1, 0, '2022-01-22 16:44:46');
INSERT INTO `t_video` VALUES (1484810102337835008, 2, 2, '闺蜜打车60公里来我家，专门吃我做的麻辣烫，超好吃', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 24, '2022/01/22/916194686224', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/916194686224', '2022/01/22/465144214925', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/465144214925', 0, 0, 0, 0, 2, 0, 1, '2022-01-22 16:48:09', 1, 0, '2022-01-22 16:48:09');
INSERT INTO `t_video` VALUES (1484812043239428096, 2, 1, '绑匪竟然被一个熊孩子硬生生给搞崩溃了！', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 111, '2022/01/22/369458295969', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/369458295969', '2022/01/22/844938458474', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/844938458474', 0, 0, 1, 0, 2, 1, 1, '2022-01-22 16:55:52', 1, 0, '2022-01-22 16:55:52');
INSERT INTO `t_video` VALUES (1484823230278668288, 2, 1, '开头想到了，结果没想到系列', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 15, '2022/01/22/894565488931', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/894565488931', '2022/01/22/933884328194', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/933884328194', 2, 0, 2, 1, 4, 0, 1, '2022-01-22 17:40:19', 1, 0, '2022-01-22 17:40:19');
INSERT INTO `t_video` VALUES (1484823420528103424, 2, 1, '广东的靓仔和广西的叼毛？', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 14, '2022/01/22/948129317921', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/948129317921', '2022/01/22/895371817537', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/895371817537', 1, 0, 0, 0, 3, 1, 1, '2022-01-22 17:41:05', 1, 0, '2022-01-22 17:41:05');
INSERT INTO `t_video` VALUES (1484825844139560960, 1, 1, '潮汕元老级别吵架', '请大家给个赞呗~~~', 12, '2022/01/22/229564197328', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/229564197328', '2022/01/22/278615839229', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/278615839229', 1, 1, 2, 2, 4, 22, 1, '2022-01-22 17:50:43', 1, 0, '2022-01-22 17:50:43');
INSERT INTO `t_video` VALUES (1484826279772557312, 2, 3, '2021年超火歌曲排行榜', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 62, '2022/01/22/131176588931', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/131176588931', '2022/01/22/486548126445', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/486548126445', 0, 0, 1, 0, 2, 8, 1, '2022-01-22 17:52:26', 1, 0, '2022-01-22 17:52:26');
INSERT INTO `t_video` VALUES (1484827096726507520, 2, 3, '非洲好声音', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 23, '2022/01/22/768359698952', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/768359698952', '2022/01/22/791696711353', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/791696711353', 0, 0, 1, 0, 2, 10, 1, '2022-01-22 17:55:41', 1, 0, '2022-01-22 17:55:41');
INSERT INTO `t_video` VALUES (1484827412540821504, 2, 3, '整理一下音乐视频，准备迎接新的一年啦～', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 67, '2022/01/22/917386958991', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/917386958991', '2022/01/22/836748882416', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/836748882416', 0, 0, 0, 0, 2, 4, 1, '2022-01-22 17:56:57', 1, 0, '2022-01-22 17:56:57');
INSERT INTO `t_video` VALUES (1484828572991492096, 2, 3, '爱太廉价了 你醉倒街头 而他喜得新欢', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 12, '2022/01/22/883741558278', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/883741558278', '2022/01/22/999891995385', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/01/22/999891995385', 0, 0, 1, 0, 3, 0, 1, '2022-01-22 18:01:33', 1, 0, '2022-01-22 18:01:33');
INSERT INTO `t_video` VALUES (1492379146125447168, 3, 1, '三宝中游记 歪果仁玩成语接龙 郭杰瑞变成语制造机', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 34, '2022/02/12/182391256772', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/12/182391256772', '2022/02/12/319587577278', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/12/319587577278', 0, 0, 0, 0, 5, 0, 1, '2022-02-12 14:04:50', 1, 0, '2022-02-12 14:04:50');
INSERT INTO `t_video` VALUES (1492382685824421888, 3, 1, '三宝中游记 20000一晚的酒店，小伙一进厕所傻了', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 55, '2022/02/12/566295624247', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/12/566295624247', '2022/02/12/499697796274', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/12/499697796274', 0, 0, 0, 0, 3, 1, 1, '2022-02-12 14:18:54', 1, 0, '2022-02-12 14:18:54');
INSERT INTO `t_video` VALUES (1492407873844678656, 3, 1, '三宝中游记 郭杰瑞：我到底还快还是不快！外国人听不懂中文的亚', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 27, '2022/02/12/174546619674', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/12/174546619674', '2022/02/12/765785222685', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/12/765785222685', 0, 2, 2, 2, 6, 0, 1, '2022-02-12 16:03:00', 1, 0, '2022-02-12 15:58:59');
INSERT INTO `t_video` VALUES (1492418584184492032, 3, 1, '三宝中游记 纽约最便宜的酒店，小伙看到后爆笑不止', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 67, '2022/02/12/777763351741', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/12/777763351741', '2022/02/12/315966693288', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/12/315966693288', 1, 0, 1, 0, 3, 0, 1, '2022-02-12 16:41:33', 1, 0, '2022-02-12 16:41:33');
INSERT INTO `t_video` VALUES (1492419059042619392, 3, 1, '三宝中游记 对比中美两大神车，五菱宏光一骑绝尘', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 94, '2022/02/12/314972327699', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/12/314972327699', '2022/02/12/383125942558', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/12/383125942558', 0, 0, 1, 0, 5, 0, 1, '2022-02-12 16:53:00', 1, 0, '2022-02-12 16:43:26');
INSERT INTO `t_video` VALUES (1492425325391515648, 3, 1, '三宝中游记 郭杰瑞学唱闽南语被嘲笑跑调到美国去了', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 61, '2022/02/12/737531496762', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/12/737531496762', '2022/02/12/421845471498', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/12/421845471498', 0, 1, 1, 1, 10, 9, 1, '2022-02-12 17:10:00', 1, 0, '2022-02-12 17:08:20');
INSERT INTO `t_video` VALUES (1496732473202184192, 5, 1, '想脱单吗，花15秒时间看完就能学废脱单！你学会了吗？#益达当', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 15, '2022/02/24/697658983561', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/24/697658983561', '2022/02/24/498847642489', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/24/498847642489', 0, 0, 0, 0, 8, 15, 1, '2022-02-24 14:25:00', 1, 0, '2022-02-24 14:23:24');
INSERT INTO `t_video` VALUES (1496812670471507968, 6, 1, '哈哈哈哈第15秒笑死我了#反转 #一定要看到最后 #廖酸汤非', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 18, '2022/02/24/123165451844', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/24/123165451844', '2022/02/24/733863439584', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/24/733863439584', 0, 2, 1, 3, 8, 24, 1, '2022-02-24 19:42:05', 1, 0, '2022-02-24 19:42:05');
INSERT INTO `t_video` VALUES (1498263684869394432, 7, 5, '直板之间的巅峰对决 这水平放在国外能不能拿冠军！#运动', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 20, '2022/02/28/357974533127', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/28/357974533127', '2022/02/28/281121245299', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/28/281121245299', 0, 0, 0, 0, 3, 1, 1, '2022-02-28 19:47:54', 1, 0, '2022-02-28 19:47:54');
INSERT INTO `t_video` VALUES (1498265131254157312, 7, 5, '王曼昱对阵蒯曼 25拍极限对拉 #乒乓球 #运动', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 36, '2022/02/28/572621887549', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/28/572621887549', '2022/02/28/539776296299', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/28/539776296299', 0, 0, 0, 0, 2, 0, 1, '2022-02-28 19:55:00', 1, 0, '2022-02-28 19:53:38');
INSERT INTO `t_video` VALUES (1498266304086413312, 7, 5, '民间扣篮王上演逆天扣篮，惊呆佩顿，现场太燃了', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 29, '2022/02/28/941172684946', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/28/941172684946', '2022/02/28/512174372874', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/28/512174372874', 0, 0, 0, 0, 1, 0, 1, '2022-02-28 20:02:00', 1, 0, '2022-02-28 19:58:18');
INSERT INTO `t_video` VALUES (1498273317134667776, 7, 5, '今天打羽毛球被退役小哥哥虐的很惨，摁在地上摩擦，最后乖乖..', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 44, '2022/02/28/285427147561', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/28/285427147561', '2022/02/28/376864155652', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/02/28/376864155652', 0, 1, 0, 0, 6, 4, 1, '2022-02-28 20:26:10', 1, 0, '2022-02-28 20:26:10');
INSERT INTO `t_video` VALUES (1498567619291254784, 6, 4, '叼毛靓仔鬼畜版', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 20, '2022/03/01/664943686878', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/03/01/664943686878', '2022/03/01/539241193972', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/03/01/539241193972', 0, 0, 0, 0, 1, 0, 1, '2022-03-01 15:55:37', 1, 0, '2022-03-01 15:55:37');
INSERT INTO `t_video` VALUES (1498628554483699712, 6, 4, '搞笑：当经典电视剧遇上鬼畜会变得怎么样', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 87, '2022/03/01/711356665988', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/03/01/711356665988', '2022/03/01/392282511255', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/03/01/392282511255', 1, 0, 0, 0, 4, 0, 1, '2022-03-01 19:57:45', 1, 0, '2022-03-01 19:57:45');
INSERT INTO `t_video` VALUES (1506270974134325248, 1, 7, '悲伤逆流成河', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 73, '2022/03/22/298981584673', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/03/22/298981584673', '2022/03/22/955157856847', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/03/22/955157856847', 0, 0, 0, 0, 2, 1, 1, '2022-03-22 22:07:00', 1, 0, '2022-03-22 22:06:00');
INSERT INTO `t_video` VALUES (1507928586290597888, 12, 7, '王宝强最新参与的一部电影', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 29, '2022/03/27/642716373272', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/03/27/642716373272', '2022/03/27/853169385729', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/03/27/853169385729', 0, 0, 0, 0, 0, 0, 1, '2022-03-28 11:55:00', 1, 0, '2022-03-27 11:52:46');
INSERT INTO `t_video` VALUES (1507940197462052864, 12, 7, '史诗级越狱现场，苏文谦表演如何脱身', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 100, '2022/03/27/113992878528', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/03/27/113992878528', '2022/03/27/112534952231', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/03/27/112534952231', 0, 0, 0, 1, 1, 5, 1, '2022-03-27 12:40:00', 1, 0, '2022-03-27 12:38:54');
INSERT INTO `t_video` VALUES (1508371135253843968, 1, 1, '搞笑视频,看一遍笑一遍 ,专治不开心', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 31, '2022/03/28/946467734753', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/03/28/946467734753', '2022/03/28/996727751877', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/03/28/996727751877', 0, 1, 1, 1, 3, 6, 1, '2022-03-28 17:12:00', 1, 0, '2022-03-28 17:11:17');
INSERT INTO `t_video` VALUES (1515652343222374400, 1, 2, '两分钟就能搞定营养又美味的早餐，上班再也不迟到啦！', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 18, '2022/04/17/631146189439', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/04/17/631146189439', '2022/04/17/956336562781', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/04/17/956336562781', 0, 0, 0, 0, 1, 0, 1, '2022-04-17 19:24:13', 1, 1, '2022-04-17 19:24:13');
INSERT INTO `t_video` VALUES (1515653549881692160, 1, 2, '天冷了，一定要来一锅热乎乎的白菜豆腐煲，简单好吃又下饭#豆腐', '如果觉得视频对你有所帮助，请务必点个赞再走，谢谢啦', 17, '2022/04/17/353977745295', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/04/17/353977745295', '2022/04/17/669311847398', 'https://luanbu-oss-01.oss-cn-chengdu.aliyuncs.com/2022/04/17/669311847398', 0, 0, 0, 0, 2, 0, 1, '2022-04-17 19:30:00', 1, 0, '2022-04-17 19:29:00');SET FOREIGN_KEY_CHECKS = 1;- 导入数据：
[root@localhost ~]# mysql -uroot -p123456 test_db < /root/t_video.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]#
- 查看数据导入情况：
如下所示，可见一共有31条记录。
mysql> select count(1) from test_db.t_video;
+----------+
| count(1) |
+----------+
| 31 |
+----------+
1 row in set (0.05 sec)
5 模拟误删表
#模拟误删
mysql> drop table test_db.t_video;
Query OK, 0 rows affected (0.01 sec)
#查看当前库的所有表，发现表t_video已经不存在了
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test |
+-------------------+
1 row in set (0.00 sec)
#再次查询记录也发现查询不到了
mysql> select count(1) from test_db.t_video;
ERROR 1146 (42S02): Table 'test_db.t_video' doesn't exist
6 数据恢复方式说明
恢复原理：将创建表以及插入表数据的相关binlog内容从binlog文件提取出来并输出到一个临时文件，然后再让mysql执行这个临时文件
恢复方式(2种)：
- 按起始-停止偏移量实现恢复
- 按起始-停止日期实现恢复
按起始-停止偏移量实现恢复
mysqlbinlog --no-defaults --start-position=起始偏移量 --stop-position=停止偏移量 特定的binglog文件路径 > /root/recover_binlog.log
按起始-停止偏移量实现恢复
# 把下面的起始和停止日期替换成实际日期
mysqlbinlog --start-date="2004-12-25 11:25:56" --stop-date="2005-03-25 11:25:56" > /root/recover_binlog.log
7 数据恢复分析(偏移量方式恢复)
偏移量方式恢复主要考虑： 如何知道起始的偏移量位置以及停止的偏移量位置。
结合我们案例，应该为：
- 起始偏移量：创建t_video表时的偏移量
- 停止偏移量：删除t_video表的前一个偏移量 或 最后一条数据插入所在偏移量
当前数据库有哪一些binlog文件？
如下所示，可知我们当前有5个binlog文件
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 1457 | No |
| binlog.000002 | 180 | No |
| binlog.000003 | 180 | No |
| binlog.000004 | 3290 | No |
| binlog.000005 | 26548 | No |
+---------------+-----------+-----------+
5 rows in set (0.00 sec)
选择那个binlog文件进行恢复？
根据第一章节提到的mysql创建新的binglog文件的时机，由于删库后我们没有重启过数据库，binlog文件大小也没有超过1GB，所以 需要用来恢复的内容基本是可以锁定在最后的一个binlog文件，即“binlog.000005”，所以可以先对
binlog.000005进行排查，看是否含有创建表t_viddeo的日志，如果有那就是利用该文件进行恢复，如果没有，那么依次往前一个binlog文件进行查找。
起始偏移量分析：
执行命令如下命令：mysqlbinlog --no-defaults -v /data/mysql/binlog.000005 | grep -i -B 20 -A 10 “create table”
命令参数说明：
- -v：以sql形式展示，方便我们来过滤 sql语句中的关键字
- grep 过滤关键字
- -i 忽略大小写
- -B n 打印过滤出的关键字所在行的前n行
- -A n 打印过滤出的关键字所在行的后n行
如下打印内容所示，我们可以得出创建t_video表所在的偏移量为：# at 1565，即1566，但由于每次执行sql之前，会先执行 #1486 偏移量中的操作(Anonymous_Gtid事件)，所以我们这里把1486偏移量对应的内容也归为创建t_video表相关binlog日志的一部分。
[root@localhost ~]# mysqlbinlog --no-defaults -v /data/mysql/binlog.000005 | grep -i -B 20 -A 10 "create table"
# at 1347
#240901 15:47:02 server id 2 end_log_pos 1486 CRC32 0x8743419f Query thread_id=127 exec_time=0 error_code=0
use `test_db`/*!*/;
SET TIMESTAMP=1725176822/*!*/;
SET @@session.pseudo_thread_id=127/*!*/;
SET @@session.foreign_key_checks=0/*!*/;
DROP TABLE IF EXISTS `t_video` /* generated by server */
/*!*/;
# at 1486
#240901 15:47:02 server id 2 end_log_pos 1565 CRC32 0x53bf7276 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=no original_committed_timestamp=1725176822100461 immediate_commit_timestamp=1725176822100461 transaction_length=2160
# original_commit_timestamp=1725176822100461 (2024-09-01 15:47:02.100461 CST)
# immediate_commit_timestamp=1725176822100461 (2024-09-01 15:47:02.100461 CST)
/*!80001 SET @@session.original_commit_timestamp=1725176822100461*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1565
#240901 15:47:02 server id 2 end_log_pos 3646 CRC32 0x4e0df206 Query thread_id=127 exec_time=0 error_code=0 Xid = 151
SET TIMESTAMP=1725176822/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `t_video` ( # 关键字所在行`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',`user_id` bigint(20) NOT NULL COMMENT '用户id',`video_category_id` bigint(20) NOT NULL COMMENT '视频分类id',`title` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '标题',`description` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '描述',`duration` int(11) NULL DEFAULT NULL COMMENT '时长。单位-秒',`cover_object_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '封面文件对象名',`cover_url` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '封面文件url',`video_object_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '视频文件对象名',`video_url` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '视频文件url',
当然，我们也可以利用 show binlog events in ‘binlog.000005’ from 0 limit 999999999 做进一步判断，可以很直观看出，我们创建表结构所在的Pos偏移量是1565，同时每次执行sql的之前，都会有一个Anonynous_Gid的操作。故而我们将 1486定为我们的起始偏移量
停止偏移量分析：
执行命令 mysqlbinlog --no-defaults -v /data/mysql/binlog.000005 | grep -i -B 20 -A 10 "drop table "，然后过滤出 删表相关的binglog日志上下文。
如下所示，其实“drop table”找到了两部分。
- 第一部分是建表前的“drop table”，即“DROP TABLE IF EXISTS
t_video;”，这并不是我们想要的。 - 第二部分是 最后的删表语句DROP TABLE
t_video所在日志部分，这部分是我们需要的。
根据下面日志得出，删除t_video表所在的起始偏移量位置是at 26410，它前一个偏移量at 26333是Anonynous_Gid操作的起始位置，也是其上一个操作的停止偏移量，故而我们需要找的停止偏移量是at 26333
注：一般操作命令前都会自动执行一个Anonynous_Gid操作，所以下图中Anonynous_Gid操作对应的偏移量范围是(26333,26410]，故而它也是属于删除表操作的一部分，所以恢复时的停止偏移量应该是26333。
[root@localhost ~]# mysqlbinlog --no-defaults -v /data/mysql/binlog.000005 | grep -i -B 20 -A 10 "drop table "
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 916
#240901 13:58:35 server id 2 end_log_pos 1270 CRC32 0x246ce28b Query thread_id=14 exec_time=0 error_code=0 Xid = 47
SET TIMESTAMP=1725170315/*!*/;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test\_db`.* TO 'lb'@'%' WITH GRANT OPTION
/*!*/;
# at 1270
#240901 15:47:02 server id 2 end_log_pos 1347 CRC32 0x2bc7fc43 Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=no original_committed_timestamp=1725176822032814 immediate_commit_timestamp=1725176822032814 transaction_length=216
# original_commit_timestamp=1725176822032814 (2024-09-01 15:47:02.032814 CST)
# immediate_commit_timestamp=1725176822032814 (2024-09-01 15:47:02.032814 CST)
/*!80001 SET @@session.original_commit_timestamp=1725176822032814*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1347
#240901 15:47:02 server id 2 end_log_pos 1486 CRC32 0x8743419f Query thread_id=127 exec_time=0 error_code=0
use `test_db`/*!*/; # 这个是 关键字 所在行
SET TIMESTAMP=1725176822/*!*/;
SET @@session.pseudo_thread_id=127/*!*/;
SET @@session.foreign_key_checks=0/*!*/;
DROP TABLE IF EXISTS `t_video` /* generated by server */
/*!*/;
# at 1486
#240901 15:47:02 server id 2 end_log_pos 1565 CRC32 0x53bf7276 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=no original_committed_timestamp=1725176822100461 immediate_commit_timestamp=1725176822100461 transaction_length=2160
# original_commit_timestamp=1725176822100461 (2024-09-01 15:47:02.100461 CST)
# immediate_commit_timestamp=1725176822100461 (2024-09-01 15:47:02.100461 CST)
/*!80001 SET @@session.original_commit_timestamp=1725176822100461*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1565
--
### @18='2022-04-17 19:30:00'
### @19=1
### @20=0
### @21='2022-04-17 19:29:00'
# at 26302
#240901 15:47:02 server id 2 end_log_pos 26333 CRC32 0xf26e3ab2 Xid = 182
COMMIT/*!*/;#==================上面的建表前的删除语句，忽略上面日志================# at 26333
#240901 15:53:22 server id 2 end_log_pos 26410 CRC32 0xaad5b496 Anonymous_GTID last_committed=37 sequence_number=38 rbr_only=no original_committed_timestamp=1725177202843074 immediate_commit_timestamp=1725177202843074 transaction_length=215
# original_commit_timestamp=1725177202843074 (2024-09-01 15:53:22.843074 CST)
# immediate_commit_timestamp=1725177202843074 (2024-09-01 15:53:22.843074 CST)
/*!80001 SET @@session.original_commit_timestamp=1725177202843074*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 26410
#240901 15:53:22 server id 2 end_log_pos 26548 CRC32 0x1aac4970 Query thread_id=32 exec_time=0 error_code=0 Xid = 210
SET TIMESTAMP=1725177202/*!*/;
SET @@session.pseudo_thread_id=32/*!*/;
SET @@session.foreign_key_checks=1/*!*/;
DROP TABLE `t_video` /* generated by server */ # 这个是 关键字 所在行
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
当然，我们也可以利用 show binlog events in ‘binlog.000005’ from 0 limit 999999999 做进一步判断，可以很直观看出，我们删除表所在的Pos偏移量是26410，同时每次执行sql的之前，都会有一个Anonynous_Gid的操作，其起始偏移量为26333，该偏移量也是最后一次commit插入表数据的结束偏移量，故而我们将 26333 定为我们的停止偏移量
8 数据恢复
从上一站章节得出我们的起始偏移量为1486，停止偏移量为26333
# 将binlog内容导入到一个临时文件
[root@localhost ~]# mysqlbinlog --no-defaults --start-position=1486 --stop-position=26333 /data/mysql/binlog.000005 > /root/recover_binlog.log# 让mysql执行指定的binlog内容
[root@localhost ~]# mysql -uroot -p123456 < /root/recover_binlog.log
mysql: [Warning] Using a password on the command line interface can be insecure.
9 验证
如下所示，可以看到我们的表t_video已经被创建出来，同时31条的表数据也被恢复了。
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test |
+-------------------+
1 row in set (0.00 sec)mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test |
| t_video |
+-------------------+
2 rows in set (0.00 sec)mysql> select count(1) from t_video;
+----------+
| count(1) |
+----------+
| 31 |
+----------+
1 row in set (0.06 sec)
10 数据恢复的局限性
不过目前这种恢复方式还是比较鸡肋：
缺点1：如果要恢复的内容分散在多个binlog文件，需要依次按顺序对binlog文件进行恢复
缺点2：不能单独对某个表进行数据恢复，因为是mysqlbinlog是利用binlog日志文件进行恢复，而binlog文件可能包含对各个数据库或者对各个表的操作，所以在执行binlog文件的时候，相当于把整个日志中的数据库操作都执行一遍，如果日志内容包含对其他表的操作，那么再恢复的过程，会导致其他表的某些操作会被重复执行，从而会引发其它问题。
如果一定要恢复，例如出现了删库的情况，那么可以考虑把现有数据库所有数据先导出来。然后手动把相关业务数据库都给删掉，最后从第一个binlog文件开始进行恢复。不过这个过程需要确保数据库的所有binlog文件都是完整的，恢复的过程是将以前的DML和DDL重新执行一遍，对于一个运行很久的数据库来说，花费时间可能会很久，需要评估一下恢复时间。
总之，如果不是到迫不得已情况，最好不用采用该方式进行恢复。
11 总结
至此，我们进行了 建表、插入表数据，然后模拟删除表数据，最后利用mysqlbinlog日志偏移量方式完成对删除的表以及表数据进行恢复，同时也告知了利用binlog进行数据恢复的局限性。
这篇关于Mysql8利用binlog实现数据恢复的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
![C#实战|大乐透选号器[6]：实现实时显示已选择的红蓝球数量](https://i-blog.csdnimg.cn/direct/cda2638386c64e8d80479ab11fcb14a9.png)
