[实操记录]mysql5.7如何恢复被删除数据
原理:使用row模式的binlog日志修改对应的sql语句后执行
步骤
1.检查binlog日志是否开启
在mysql中输入如下命令,为ON即为开启,OFF为关闭
show variables like '%log_bin%';
说明
log_bin
: 值为ON为开启,OFF为关闭
log_bin_basename
: binlog日志的详细存储位置,最后一个log是文件的前缀
log_bin_index
: logbin日志文件的后面动态的索引部分,上面的属性和当前属性加起来就是binlog日志文件的全局路径,比如 /var/lib/mysql/mysql-bin.000001
其余三个待定;
2.如果binlog日志没有开启,则开启
2.1查找mysql的配置文件
mysql --help|grep my.cnf
说明:图中圈出的是配置文件的访问优先级顺序,从高到低,同时也指明了配置文件的位置
2.2 在my.cnf中添加binlog配置
有两种配置方式:
第一种(验证过)
# binlog配置
log-bin=mysql-bin
server-id=1
binlog_format=ROW
说明:
log-bin
: binlog日志文件前缀,应该也可以使用绝对路径,不过没验证过
server-id
: 这是在集群中用来确认机器唯一的
binlog_format
:binlog记录日志的方式,有三种,在这里只有设置成ROW模式,这种恢复方式才可行,具体这种模式是怎样的,有兴趣的可以百度查看
注意:
log-bin
中间不是下划线
第二种(未验证过)
# binlog配置
log_bin=ON
log_bin_basename=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index
3.检查binlog模式是否是row
用以下sql语句执行
show variables like '%format%';
说明:
binlog_format
:binlog日志记录方式,ROW模式是本文实现的基础
4.查找binlog日志存储位置
用sql语句
show variables like '%log_bin%';
说明:圈出的就是binlog日志的存储路径
5.定位binlog日志
首先确定自己删除数据的时间,然后根据这个时间在两个相邻binlog文件的更新时间中判断具体那个文件
6.用mysqlbinlog工具解析日志
找到文件后,进入目录,执行命令
mysqlbinlog --base64-output=decode-rows --start-datetime='2022-04-26 14:49:00' --stop-datetime='2022-04-26 14:53:00' --database=’test‘ --vv mysql-bin.000002 >mysql-bin.000002.short.sql
说明
--base64-output=decode-rows
:源文件是base64加密的,用这个参数来解密
--start-datetime
:截取开始时间的binlog日志
--stop-datetime
:截取结束时间的binlog日志
--database
:截取指定数据库的binlog日志
--vv
:输出注释(可选)
mysql-bin.000002
:binlog日志文件
mysql-bin.000002.short.sql
:输出的sql文件,这是恢复我们数据的关键文件
注意:上述参数是我使用的,常用的还有根据定位截取,在此不作展开,有兴趣的可以研究下,给个参考:mysql-mysqlbinlog的使用详解
7.恢复数据的两种方式
终于到了最后一步,这里有两种方式。
一种是以最新备份的数据库为起点,将之后的所有binlog日志中误操作执行的删除语句手工去除后,将修改后的sql语句全部执行一遍。
另一种是直接对删除语句进行修改变成插入语句,然后执行。
我实操的是第二种。
说明:这是binlog日志解析后的一个片段,将圈中的语句手工改成插入语句后执行sql,数据就恢复了。
常见问题
mysql 无法启动
这个问题多种多样,不过八成是配置文件的问题,这里给出检查方式
1.查看mysql状态
systemctl status mysqld.service
说明:如果启动失败,通常有错误日志提示
2.如果上面无法直接看出报错原因,通常会有日志提示执行journalctl -xe
这个命令
journalctl -xe
3.查看详细日志
在my.cnf文件中有配置日志的路径
log-error=/var/log/mysqld.log
binlog文件太大无法打开
推荐使用 EmEditor 工具
声明:本文纯原创,转载请注明出处。