mysql误操作更新删除数据恢复

数据恢复的前提

1、数据恢复的必要前提是mysql数据必须要已开启binlog二进制ddl日志操作记录

查看数据库是否开启了binlog

show variables like 'log_bin%';

 

 上面可以看到已经开启了log_bin，并且binlog的存放位置是 /var/lib/mysql/ 下面，在主机下去到这个目录下可以看到mysql-bin.0000xx文件，这个就是binlog日志记录

 

 2、如果是还没有开启这个设置，可以通过msyql的配置文件配置以下内容，然后重启mysql服务就可以开启binlog记录

通过rpm或者yum默认安装的mysql配置文件会在/etc/my.cnf，vi这个文件添加

# binlog configuration
server_id=2  # mysql的server_id,这个数字可以自己定义
log_bin=mysql-bin  # binlog存放的位置，可以自己自定义一个绝对路径
binlog_format=ROW  # binlog记录模式
binlog_rows_query_log_events=1

保存重启服务

 

模拟误操作恢复

1、不小心全表更新数据

 

 

 这时候要记录下当前的大概操作时间，我这里大概是2023.02.10 15:04 时间操作的，然后去到mysql的binlog目录下面去，找到当前的使用的binlog，数字后缀最大的那个

使用mysqlbinlog命令查到自己操作的记录，通过操作语句来恢复

mysqlbinlog -v -v --base64-output=DECODE-ROWS  --start-datetime='2023-02-10 15:00:00' --stop-datetime='2023-02-10 15:10:00' --database test01 mysql-bin.000002  >> bak.sql

 

 

这里我们看到完整的数据和执行的语句，如果是只有几行的数据，我们可以直接将他拷贝出来，手动处理这个恢复语句，大批量的话，我这里借用了binlog2sql来恢复的。

2、安装binlog2sql来获取恢复语句

这个是一个python的脚本，这里建议本地安装了python环境，然后安装第三方的pymsyql库，连接数据库来恢复

binlog2sql的下载地址：git clone https://github.com/danfengcao/binlog2sql.git

安装python库模块 ：pip install -r requirements.txt

 

 python binlog2sql.py --flashback  -hlocalhost -P3306 -uroot -p'root' -dtest01 -t test_user --start-file='mysql-bin.000002' --start-position=3959 --stop-position=3990 >> flashbak.sql

start-position是binlog的起始位置，通过前面的mysqlbinlog输出的end_log_pos来确定，如图

 # 新建一个用户需要的权限

select, super/replication client, replication slave

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test'@'%';

 找到第一个和需要结束的那个位置，通过脚本获取误删前的数据，这个会生成update语句，然后检查以下这个update语句，如果没有问题就可以执行恢复语句了

 

 3、在mysql的客户端下面执行source flashbak.sql 执行恢复数据

删除操作跟误更新操作一样

#########

生产环境（服务器不通外网）的数据库可能不让直连，如果可以登录到服务器做隧道代理出来到windows下面处理，如果不行的话，就需要各位想其他的办法在服务器上安装python的第三方库了，能安装成功的话可以直接在linux系统下操作

xshell做隧道代理

 

然后就可以在本地通过localhost+端口连接到mysql的真实环境啦

以上是个人的处理经验，如果有更好的方法可以评论区交流哈！