使用binlog把数据恢复到指定时间

最新推荐文章于 2024-09-15 16:12:48 发布

chenabin_php

最新推荐文章于 2024-09-15 16:12:48 发布

阅读量1.7k

点赞数 1

文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/chenabin_/article/details/129798588

版权

文章介绍了在数据库发生故障时如何利用凌晨3点的全备份和之后的二进制日志恢复数据至故障点之前的状态。通过开启并配置MySQL的binlog，结合mysqldump和mysqlbinlog工具，可以实现数据的精确恢复。同时提到了binlog的不同格式以及一些关键配置参数。

摘要由CSDN通过智能技术生成

使用场景

我们的数据库都有在凌晨3点做数据全备份，假如我们的数据库在上午10点出了故障，那我们就可以用备份把数据还原到凌晨3点时的状态，但是3点到10点间的数据变动是没有的，如果要将数据库恢复到故障点之前，那么需要有数据库全备和全备之后产生的所有二进制日志进行还原。

原理

利用全备的备份集将数据库恢复到上一次完整备份的位置之后，需要对上一次全备之后数据库产生的所有动作进行重做，而重做的过程就是解析二进制日志文件为SQL语句，然后放到数据库里面再次执行。binlog记录了所有的DML数据变化，对于insert，binlog记录了insert的行数据；对于update，binlog记录了改变前的行数据和改变后的行数据；对于delete，binlog记录了删除前的数据。假如用户不小心误执行了DML操作，可以使用mysqlbinlog将数据库恢复到故障点之前。

步骤

开启binlog，修改mysql配置文件，修改参数如下：

binlog_format = ROW //二进制日志的格式有三种，默认是Mixed，还有ROW,STATEMENT,经测试这三种格式都可恢复数据。（在网上看了很多帖子，很多都说要设置成ROW或者Mixed，但是我测试了STATEMENT效果是一样的，关于这三种格式的区别，参考https://www.liuyanzhao.com/9322.html）

log-bin = e:/mysqlbin/binlog.log //日志位置

log-bin-index = e:/mysqlbin/binlog.index

expire_logs_days = 7 //保留近7天的日志

slave_skip_errors=1062,1032,1452 //如果出现了有跟slave相关错误时就加这个配置

注意：改完后一定要重启mysql的服务才会成效。

查看是否配置成功show variables like 'log_bin%';

事前要有全备份 mysqldump -uroot -p st user > e:usre.sql
恢复数据库备份 mysql -uroot -p st<e:st.sql
利用二进制日志恢复到时间点
mysqlbinlog --start-date="2021-12-15 17:32:59" --stop-date="2021-12-15 17:41:59" E:\mysqlbin\binlog.000012 | mysql -uroot -proot st

四、关于二进制日志的简单命令行（感兴趣的话可以去更多的了解）

show master status; //找到当前mysql记录的binlog文件

mysqlbinlog mysql_bin.000001 //日志文件要绝对路径

mysqlbinlog mysql_bin.000001 –vv // 看到更详细的信息

show binlog events in 'mysql_bin.000001 //查看操作的位置

以sql形式展示binlog:

Mysqlbinlog --no-defaults --database=test2 --base64-output=decode-rows -v --start-position=154 --stop-position=427 D:\mysql-5.7.28-winx64\data\binlog\mysql-bin.000001

或者不定位看全部

mysqlbinlog --no-defaults --database=test2 --base64-output=decode-rows -v D:\mysql-5.7.28-winx64\data\binlog\mysql-bin.000001