【mysql】基于binlog数据恢复指令和坑

1.binlog相关配置

binlog会记录mysql的增、删、改操作，通过binlog可以实现数据恢复、数据备份。

---

是否开启binlog

show  variables like '%log_bin%';#查询是否开启binlog，mysql8默认开启

binlog.index是binlog索引目录，记录生成的binlog日志，日志内容在，对应的binlog下，通过后缀区分。

---

binlog日志格式

show global variables like '%binlog_format%';#查询binlog格式，必须是row格式

2.导出binlog日志

可以通过mysqlbinlog指令，对binlog进行操作，通常我们使用这个指令将binlog导出为文本或sql。

mysqlbinlog指令

# 使用 mysqlbinlog 增量备份数据库
mysqlbinlog -v --start-datetime="2023-12-04 21:40:00" --stop-datetime="2023-12-05 00:00:00" $log_path --base64-output=decode-rows > $backup_file
# 使用 mysqlbinlog 增量备份数据库通过position
mysqlbinlog -v --start-position=258879782 --stop-position=258880137 $log_path --base64-output=decode-rows > $backup_file

• -v ：用于显示伪sql语句
• -datetime：时间条件
• -position：位置条件
• $log_path：binlog日志所在的绝对路径（根据实际情况修改）
• –base64-output=decode-rows：将Base64编码的输出解码为可读的格式
• $backup_file：输出内容位置（根据实际情况修改）

update

打开输出的binlog，可以看到伪sql，通过这些我们可以找到想要的日志和操作。

insert

delete

---

binlog中的事件

在binlog中，begin和commit对应事务的开始和提交

show BINLOG EVENTS in 'binlog.000037';#显示binlog中的事件

完整的一条update操作（delete、insert类似）

3.数据恢复

在binlog中找到对应需要恢复的日志，就可以进行数据恢复
数据恢复需要从，begin位置开始，到commit位置，注意区分，是使用End_log_pos这个作为起始
例如，在原有数据为

然后，update，某一个字段

现在想要回到，之前的数据，那么可以查询binlog，找到刚刚update的，上一条日志

找到错误操作的，上一条事务操作，使用begin到commit的End_log_pos作为条件，那么恢复指令为：

mysqlbinlog  --no-defaults --start-position=267706997 --stop-position=267707266 binlog.000037 | mysql -uroot -p123456

填写对应的start和end位置，在后面加上账号密码，执行，发现数据已经恢复

4.特别注意的坑

为什么bash脚本执行mysqlbinlog，无法找到指令

需要使用mysqlbinlog的绝对地址

 whereis mysqlbinlog # 获取绝对地址

改为：

/usr/local/mysql/bin/mysqlbinlog  --no-defaults --start-position=267705701 --stop-position=267705953 binlog.000037 | mysql -uroot -p

---

为什么执行mysqlbinlog，无法数据恢复

mysqlbinlog恢复数据，需要使用同一表的，pos信息进行恢复，如果跨表操作，可以通过查看伪sql进行辅助恢复