Mysql8利用binlog实现数据恢复

2024-09-02 00:20

本文主要是介绍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 测试数据准备&导入数据

  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进行数据恢复的局限性。

这篇关于Mysql8利用binlog实现数据恢复的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1128466

相关文章

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

hdu1043(八数码问题,广搜 + hash(实现状态压缩) )

利用康拓展开将一个排列映射成一个自然数,然后就变成了普通的广搜题。 #include<iostream>#include<algorithm>#include<string>#include<stack>#include<queue>#include<map>#include<stdio.h>#include<stdlib.h>#include<ctype.h>#inclu

【C++】_list常用方法解析及模拟实现

相信自己的力量,只要对自己始终保持信心,尽自己最大努力去完成任何事,就算事情最终结果是失败了,努力了也不留遗憾。💓💓💓 目录   ✨说在前面 🍋知识点一:什么是list? •🌰1.list的定义 •🌰2.list的基本特性 •🌰3.常用接口介绍 🍋知识点二:list常用接口 •🌰1.默认成员函数 🔥构造函数(⭐) 🔥析构函数 •🌰2.list对象

【Prometheus】PromQL向量匹配实现不同标签的向量数据进行运算

✨✨ 欢迎大家来到景天科技苑✨✨ 🎈🎈 养成好习惯,先赞后看哦~🎈🎈 🏆 作者简介:景天科技苑 🏆《头衔》:大厂架构师,华为云开发者社区专家博主,阿里云开发者社区专家博主,CSDN全栈领域优质创作者,掘金优秀博主,51CTO博客专家等。 🏆《博客》:Python全栈,前后端开发,小程序开发,人工智能,js逆向,App逆向,网络系统安全,数据分析,Django,fastapi

让树莓派智能语音助手实现定时提醒功能

最初的时候是想直接在rasa 的chatbot上实现,因为rasa本身是带有remindschedule模块的。不过经过一番折腾后,忽然发现,chatbot上实现的定时,语音助手不一定会有响应。因为,我目前语音助手的代码设置了长时间无应答会结束对话,这样一来,chatbot定时提醒的触发就不会被语音助手获悉。那怎么让语音助手也具有定时提醒功能呢? 我最后选择的方法是用threading.Time

Android实现任意版本设置默认的锁屏壁纸和桌面壁纸(两张壁纸可不一致)

客户有些需求需要设置默认壁纸和锁屏壁纸  在默认情况下 这两个壁纸是相同的  如果需要默认的锁屏壁纸和桌面壁纸不一样 需要额外修改 Android13实现 替换默认桌面壁纸: 将图片文件替换frameworks/base/core/res/res/drawable-nodpi/default_wallpaper.*  (注意不能是bmp格式) 替换默认锁屏壁纸: 将图片资源放入vendo

C#实战|大乐透选号器[6]:实现实时显示已选择的红蓝球数量

哈喽,你好啊,我是雷工。 关于大乐透选号器在前面已经记录了5篇笔记,这是第6篇; 接下来实现实时显示当前选中红球数量,蓝球数量; 以下为练习笔记。 01 效果演示 当选择和取消选择红球或蓝球时,在对应的位置显示实时已选择的红球、蓝球的数量; 02 标签名称 分别设置Label标签名称为:lblRedCount、lblBlueCount

Kubernetes PodSecurityPolicy:PSP能实现的5种主要安全策略

Kubernetes PodSecurityPolicy:PSP能实现的5种主要安全策略 1. 特权模式限制2. 宿主机资源隔离3. 用户和组管理4. 权限提升控制5. SELinux配置 💖The Begin💖点点关注,收藏不迷路💖 Kubernetes的PodSecurityPolicy(PSP)是一个关键的安全特性,它在Pod创建之前实施安全策略,确保P

工厂ERP管理系统实现源码(JAVA)

工厂进销存管理系统是一个集采购管理、仓库管理、生产管理和销售管理于一体的综合解决方案。该系统旨在帮助企业优化流程、提高效率、降低成本,并实时掌握各环节的运营状况。 在采购管理方面,系统能够处理采购订单、供应商管理和采购入库等流程,确保采购过程的透明和高效。仓库管理方面,实现库存的精准管理,包括入库、出库、盘点等操作,确保库存数据的准确性和实时性。 生产管理模块则涵盖了生产计划制定、物料需求计划、

C++——stack、queue的实现及deque的介绍

目录 1.stack与queue的实现 1.1stack的实现  1.2 queue的实现 2.重温vector、list、stack、queue的介绍 2.1 STL标准库中stack和queue的底层结构  3.deque的简单介绍 3.1为什么选择deque作为stack和queue的底层默认容器  3.2 STL中对stack与queue的模拟实现 ①stack模拟实现