MySQL数据被误删?

在开发和在生产中总会出现各种各样的失误和意味,当MySQL的数据或表被删除后不要慌,执行以下命令,查看mysql是否开启binlog,binlog会记录下数据库表结构的变更,因此强烈建议在部署MySQL的时候开启binlog.


— 查看是否开启binlog ON 为开启, OFF则为未开启
show variables like ‘%log_bin%’;

如果忘记自己binlog文件存放在哪里可以使用以下命令去查看,也可以到自己mysqld中去查看 。

show variables like '%datadir%';

我们进入存储binlog的目录可能会看到多个文件,可以使用下面一行命令查看最新的binlog文件,我们最近的操作日志就存在里面。

show master status;

在找到binlog文件就需要确定我们要恢复的范围,因为每个mysql-bin可能内容很多,我们需要根据时间区间,截取出需要数据恢复的那一部分。


## mysqlbinlog 在mysql/bin路径下的工具
./mysqlbinlog –no-defaults –database=www –start-datetime=’2022-08-26 00:00:00′ –stop-datetime=’2022-08-26 16:00:00′ /www/server/data/mysql-bin.000006
## 第一条指令会有base64可用下面一条转码
./mysqlbinlog –no-defaults –database=www –base64-output=decode-rows -v –start-datetime=’2022-08-27 00:36:00′ –stop-datetime=’2022-08-27 01:25:00′ /www/server/data/mysql-bin.000008 > day.txt

使用mysqlbinlog工具截取出binlog文件由字节文件转到day.txt文件中,转成我们可以读懂的日志,打开day.txt文件可以看到截取时间段内mysql执行过的指令。

上图我们只需要关注箭头所指的时间和end_log_pos 根据这两个参数进行数据恢复,使用以下指令,在服务器中执行。


## stop-ponsition对用我们end_log_pos,该指令会恢复到2679日志片段的数据,前面删去的数据都会恢复
./mysqlbinlog –no-defaults -v –stop-position=2679 -v /www/server/data/mysql-bin.000008 |./mysql -u root -p
## 以下指令则恢复2744 到 2949 区间的数据
./mysqlbinlog –no-defaults –start-position=2744 –stop-position=2949 /www/server/data/mysql-bin.000008 |./mysql -u root -p

还有一种方式查看end_log_pos的区间,在mysql中执行以下指令

show binlog EVENTS in 'mysql-bin.000008' ;

 mysqlbinlog恢复数据存在的问题

使用mysqlbinlog尝试了几次恢复数据,对于删除的数据是可以恢复的,然后使用对于修改过的数据测试后并没有恢复,而在服务器会报错 Can’t find record in table,不能直观展示需要回退的内容,很不友好。

binlog还原小工具

GitHub – danfengcao/binlog2sql: Parse MySQL binlog to SQL you wantParse MySQL binlog to SQL you want. Contribute to danfengcao/binlog2sql development by creating an account on GitHub.https://github.com/danfengcao/binlog2sql

binlog2sql是一个比较好用的mysql闪回工具,建议去上面的github查看,有例子,基本不踩坑,步骤也很简单,使用binlog2sql有前提,必须在my.cnf进行指定配置,如果是突发删除问题,自己数据库又没有配置,那就只能回去使用mysqlbinlog。


// 可能会用到的命令,自己记录一下
find / -name my.cnf
​​python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p -dwww -ttbl –start-file=’mysql-bin.000008′ –start-datetime=’2022-08-27 00:25:00′ –stop-datetime=’2022-08-27 13:30:00′
​​python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p -dwww -ttbl –start-file=’mysql-bin.000008′ –start-position=22577 –stop-position=23372 -B > rollback.sql | cat
mysql -h127.0.0.1 -P3306 -uroot -p < rollback.sql

如果表被删除的话binlog2sql也是没办法恢复得,所以删除表之前一定要谨慎再谨慎,同时做好备份。

可能踩坑问题整理

  1. 使用的恢复数据的mysql用户权限要给大一些,不然会遇到权限问题,很多指令无法执行。
  2. 在恢复数据的时候尽可能确定需要恢复的时间