Mysql8利用binlog实现数据恢复

已于 2024-09-01 18:35:25 修改
阅读量1.3k 收藏 25
点赞数 20
分类专栏： 数据库 运维 文章标签： mysql 运维 linux
于 2024-09-01 18:03:22 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jianghuchuang/article/details/141783841
版权

文章目录

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_F

BINLOG '
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 测试数据准备&导入数据

  1. 数据准备
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;
  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 ~]#
  1. 查看数据导入情况：
    如下所示，可见一共有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”找到了两部分。

  1. 第一部分是建表前的“drop table”，即“DROP TABLE IF EXISTS t_video;”，这并不是我们想要的。
  2. 第二部分是 最后的删表语句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进行数据恢复的局限性。

艾文伯特
关注
专栏目录
mysql binlog pid_MySQL日志binlog的三种模式
weixin_36397509的博客
01-19 353
1三种模式的介绍二进制日志binlog作用：1、以二进制形式记录更改数据库的SQL语句(insert,update,delete,create,drop,alter等)2、用于Mysql主从复制3、增量数据库备份及恢复1.1Row模式日志会记录成每一行数据被修改成的形式，然后再slave端再对相同的数据进行修改，只记录要修改的数据，只有value，不会有sql多表关联的情况。优点：在row模式下，...
Windows环境MySQL8.0使用binlog恢复
最新发布
qq_74770105的博客
02-25 2035
binlog 是记录所有数据库表结构变更以及表数据修改的二进制日志（不会记录 select和show这类操作）binlog 是逻辑日志,记录语句的原始逻辑。数据恢复: 可以通过 mysqlbinlog工具进行数据恢复。
mysql8通过binlog恢复数据
ganjing222的博客
02-05 756
【代码】mysql8通过binlog恢复数据。
mysql8从binlog恢复数据
java学习者
12-04 3493
mysql8从binlog恢复数据mysqlbinlog官方文档数据恢复数据恢复命令注意 mysqlbinlog官方文档 https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html 数据恢复 通过mysqlbinlog -v binlog.000001打印binlog信息，找到删库或者删表的位置 mysqlbinlog -v binlog.000001 指定位置范围 mysqlbinlog -v binlog.000001 --start-pos
MySQL8数据恢复
while(True): print('adorable')
04-23 1514
binglog数据恢复
mysql8 日志恢复数据
絮落锦乡的博客
04-12 503
mysqlbinlog --no-defaults --database= artsexam --start-datetime="2022-04-01 00:00:00" --stop-datetime="2022-04-12 18:0 2:00" E:\phpstudy_pro\Extensions\MySQL8.0.12\data\binlog.000030 >E:\11.sql
mysql如何利用binlog进行数据恢复详解
12-16
在本篇文章中，我们将深入探讨如何利用binlog进行数据恢复，以及binlog的基本配置和格式。 首先，当发生线上误操作时，如果数据库未开启其他备份策略，binlog就成为了恢复数据的唯一途径。在ROW模式下，binlog会...
Mysql的Binlog数据恢复:不小心删除数据库详解
12-16
本篇文章将详细解析如何利用MySQL的Binlog进行数据恢复。 首先，启用Binlog功能是恢复的前提。在MySQL中，Binlog默认可能是关闭的，需要手动开启。这通常涉及到修改MySQL的配置文件，如在Linux系统中的`/etc/my.cnf...
两行代码修复了解析MySQL8.x binlog错位的问题！！
冰河的专栏
01-15 1060
冰河带你解析MySQL8.x的binlog日志！！
MySQL8.0中binlog的深入讲解
01-21
1 简介 二进制日志，记录对数据发生或潜在发生更改的SQL语句，并以二进制形式保存在磁盘。 2 Binlog 的作用 主要作用：复制、恢复和审计。 3 开启Binlog 3.1 查询当前 MySQL 是否支持 binlog 如下OFF代表不支持 3.2 修改 my.cnf 文件以支持 binlog 查看my.cnf路径 mysql –help –verbose | grep my.cnf     在/etc 新建文件my.cnf并添加如下内容 注意添加 mysqld 组   重启 MySQL 再次查看是否支持binlog   3 binlog管理命令
Mysql8.0的bin log日志
阿干tkl的博客
07-12 1918
mysql bin log 日志
mysql8.0 使用binlog回复数据库
凌晨的博客
12-03 709
1找到mysqlbinlog命令 一般在mysql/bin文件夹下 2找到当前的需要恢复的日志show binary logs 3 把二进制日志 转换成能读取的日志 开始寻找数据回复的 开始点 和 结束点 /www/server/mysql/bin/mysqlbinlog /www/server/data/mysql-bin.000004 >> /www/1.txt 4进行回复步骤 /www/server/mysql/bin/mysqlbinlog /www/server...
测试mysql8.0通过binlog恢复数据
ajax_yan的博客
09-16 596
首先在装数据库时，查看是否开启，如果没有，先开启，然后重启mysql。环境变量Path添加mysql安装路径:.../mysqlxx/bin/ 不要等到用的时候再开启，晚了 show variables like '%log_bin%' 指定时间范围，去查找符合条件的日志 mysqlbinlog -v binlog.000001 --start-datetime="2020-10-24 00:00:00" --stop-datetime="2020-10-24 00:00:00" ..
MySQL 8.0文档阅读:通过binlog恢复数据
wzj_whut的专栏
03-14 2848
文章目录前言演练 前言 比如手贱, 误删了数据. 或者代码BUG, 导致需要数据还原. 前提是已经开启了binlog, 并且有定期的全量备份 binlog格式 https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog-row-events.html 演练 https://dev.mysql.com/doc/refman/8.0/en/mysqlbinl...
windows下误删数据库恢复（MySQL8.0）
Anlger的博客
02-28 2178
windows下误删数据库恢复
MySQL8备份恢复（一）
贺浦力特的博客
04-05 1344
mysql 备份恢复 navicat mysqldump
Mysql8通过Data文件恢复数据
孤星的博客
03-18 2601
我们将现有的无数据的Mysql叫做MysqlA，另一个有数据Mysql叫做MysqlB。 备份Data文件 将MysqlB的Data文件复制到桌面进行备份，如果你不确定Data文件在哪可以用以下指令查询 show global variables like "%datadir%"; 如果此时MysqlB已损坏，无法执行sql，那么此时分两种情况 你的Mysql是解压缩版 如果你的Mysql是解压缩版，那么大概率可以在系统环境变量中查询到Mysql\bin文件夹的位置，找到该文件夹后，往回退一级到Mysql
MySQL binlog数据恢复指南：利用binlog2sql进行恢复
"mysql如何利用binlog进行数据恢复详解" MySQL的二进制日志（Binary Log，简称binlog）是一种至关重要的功能，它记录了数据库中所有改变数据的操作，主要用于数据恢复和主从同步。当出现误操作或者数据丢失的情况时...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值