[实操记录]mysql5.7如何恢复被删除数据

[实操记录]mysql5.7如何恢复被删除数据

• • 步骤
  • • 1.检查binlog日志是否开启
    • 2.如果binlog日志没有开启，则开启
    • • 2.1查找mysql的配置文件
      • 2.2 在my.cnf中添加binlog配置
    • 3.检查binlog模式是否是row
    • 4.查找binlog日志存储位置
    • 5.定位binlog日志
    • 6.用mysqlbinlog工具解析日志
    • 7.恢复数据的两种方式
  • 常见问题
  • • mysql 无法启动
    • binlog文件太大无法打开

原理：使用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 工具

声明：本文纯原创，转载请注明出处。