Binlog（三） 之 数据误删恢复
通过上两个博客的学习，知道了Binlog的作用，与Binlog的内部结构，这次主要将binlog应用于实战，从基于偏移量与时间点对数据库/表误删情况做恢复。
目录
- 一、实操
- 1.1 查看Binlog选项是否已经开启
- 1.2 打开Binlog日志
- 1.3 创建Demo数据库表并插入数据
- 1.4 模拟误删除表格所有记录
- 1.5 分析Binlog日志
- 1.6 基于时间恢复
- 1.7 基于偏移量恢复
- 二、参考
- 三、相关文章
一、实操
1.1 查看Binlog选项是否已经开启
SHOW VARIABLES LIKE '%log_bin%';
-- 如果log_bin的选项为ON则表示打开，为OFF则与之相反
1.2 打开Binlog日志
打开my.ini或my.cnf文件，具体打开哪个文件取决于MySQL服务所在的操作系统，这里以Windows为例
[mysqld]
# 这里将binlog模式指定为Row模式
log-bin=mysqlbinlog
binlog-format=ROW
上述配置修改完毕之后，记得重启MySQL服务，这里就不再赘述。
1.3 创建Demo数据库表并插入数据
CREATE TABLE `instruct` (
`salary` decimal(10,2) DEFAULT NULL,
`dep` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入模拟数据
INSERT INTO
swtest.instruct
VALUES
(101.00, '计算机', '张三'),
(102.00,'金融', '李四'),
(103.00,'金融', '榕榕'),
(104.00, '飞机', '无言以对');
1.4 模拟误删除表格所有记录
-- 清空表数据
TRUNCATE swtest.instruct;
1.5 分析Binlog日志
# 这里将binlog日志解析并定向到mysqlbinlog.sql中方便查看
shell> mysqlbinlog "mysqlbinlog.000005" > mysqlbinlog.sql
下面贴出mysqlbinlog.sql中的全部内容：
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210627 15:58:07 server id 1 end_log_pos 123 CRC32 0x4cc84ce4 Start: binlog v 4, server v 5.7.32-log created 210627 15:58:07
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
jy/YYA8BAAAAdwAAAHsAAAABAAQANS43LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AeRMyEw=
'/*!*/;
# at 123
#210627 15:58:07 server id 1 end_log_pos 154 CRC32 0x4632c7a2 Previous-GTIDs
# [empty]
# at 154
#210627 15:59:30 server id 1 end_log_pos 219 CRC32 0x59098a08 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210627 15:59:30 server id 1 end_log_pos 472 CRC32 0x9c9c03f6 Query thread_id=2 exec_time=0 error_code=0
use `swtest`/*!*/;
SET TIMESTAMP=1624780770/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE `instruct` (
`salary` decimal(10,2) DEFAULT NULL,
`dep` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!*/;
# at 472
#210627 16:01:16 server id 1 end_log_pos 537 CRC32 0x98b45022 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 537
#210627 16:01:16 server id 1 end_log_pos 611 CRC32 0xc27584a5 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1624780876/*!*/;
BEGIN
/*!*/;
# at 611
#210627 16:01:16 server id 1 end_log_pos 672 CRC32 0xdaadbb0b Table_map: `swtest`.`instruct` mapped to number 114
# at 672
#210627 16:01:16 server id 1 end_log_pos 804 CRC32 0x289c917a Write_rows: table id 114 flags: STMT_END_F
BINLOG '
TDDYYBMBAAAAPQAAAKACAAAAAHIAAAAAAAEABnN3dGVzdAAIaW5zdHJ1Y3QAA/YPDwYKAv0C/QIH
C7ut2g==
TDDYYB4BAAAAhAAAACQDAAAAAHIAAAAAAAEAAgAD//iAAABlAAkA6K6h566X5py6BgDlvKDkuIn4
gAAAZgAGAOmHkeiejQYA5p2O5Zub+IAAAGcABgDph5Hono0GAOamleamlfiAAABoAAYA6aOe5py6
DADml6DoqIDku6Xlr7l6kZwo
'/*!*/;
# at 804
#210627 16:01:16 server id 1 end_log_pos 835 CRC32 0xcd823a7f Xid = 228
COMMIT/*!*/;
# at 835
#210627 16:03:38 server id 1 end_log_pos 900 CRC32 0xb3c031a6 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 900
#210627 16:03:38 server id 1 end_log_pos 974 CRC32 0xd0ee691d Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1624781018/*!*/;
BEGIN
/*!*/;
# at 974
#210627 16:03:38 server id 1 end_log_pos 1035 CRC32 0x0dcda6ef Table_map: `swtest`.`instruct` mapped to number 114
# at 1035
#210627 16:03:38 server id 1 end_log_pos 1127 CRC32 0x4640dab9 Update_rows: table id 114 flags: STMT_END_F
BINLOG '
2jDYYBMBAAAAPQAAAAsEAAAAAHIAAAAAAAEABnN3dGVzdAAIaW5zdHJ1Y3QAA/YPDwYKAv0C/QIH
76bNDQ==
2jDYYB8BAAAAXAAAAGcEAAAAAHIAAAAAAAEAAgAD///4gAAAaAAGAOmjnuacugwA5peg6KiA5Lul
5a+5+IAAAGgABgDnp5HmioAMAOaXoOiogOS7peWvubnaQEY=
'/*!*/;
# at 1127
#210627 16:03:38 server id 1 end_log_pos 1158 CRC32 0x53a3fe0c Xid = 235
COMMIT/*!*/;
# at 1158
#210627 16:05:52 server id 1 end_log_pos 1223 CRC32 0xb04a4126 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1223
#210627 16:05:52 server id 1 end_log_pos 1316 CRC32 0x1707e2d9 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1624781152/*!*/;
TRUNCATE swtest.instruct
/*!*/;
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*/;
可以进行分析，在at 537也就是时间210627 16:01:16一直到at 1127也就是时间210627 16:03:38就是上述所有SQL操作（除了最后的TRUNCATE swtest.instruct）；
基于上述的分析，可以使用基于时间或者日志偏移量进行恢复
1.6 基于时间恢复
mysqlbinlog --start-datetime="2021-06-27 16:01:16" --stop-datetime="2021-06-27 16:03:38" mysqlbinlog.000005 | mysql -u root -p123456
1.7 基于偏移量恢复
mysqlbinlog --start-position=537 --stop-position=1127 mysqlbinlog.000005 | mysql -u root -p123456
二、参考
三、相关文章
- [Binlog（一） 之 作用、三种模式介绍] : https://www.jianshu.com/p/bc5a5236c2c1
- [Binlog（二）之 文件结构与文件解读] : https://www.jianshu.com/p/b809e4c64ee1
网友评论