51工具盒子

依楼听风雨
笑看云卷云舒,淡观潮起潮落

MySQL通过binlog找回误删的数据

背景

在操作线上数据库时要尤其小心,但有时候难免会出现误删数据的情况,在出现误删后,如果有数据库备份,只需要还原备份文件即可。但有时候备份频率不高,例如一天备份一次,而误删的数据恰好是今天新增的数据,该数据并不在备份中,此时就需要通过MySQL的binlog日志来恢复了。

原理

MySQL开启了binlog后,会将数据库的变更情况以文件的形式详细的记录下来,包括在什么时间发生了什么操作(增、删、改)及操作了哪些数据(增加、删除、修改了哪些数据,如果是删除和修改,还会记录删除前和修改前的数据)。

binlog三种模式

| format | 定义 | 优点 | 缺点 |

|-----------|--------------------|-----------------|-------------------------|

| statement | 记录的是修改SQL语句 | 日志文件小,节约I0,提高性能 | 准确性差,对一些系统函数不能准确复制或不能复制 |

| row | 记录的是每行实际数据的变更 | 准确性强,能准确复制数据的变更 | 日志文件大,较大的网络IO和磁盘IO |

| mixed | statement和row模式的混合 | 准确性强,文件大小适中 | 有可能发生主从不一致问题 |


推荐使用的是row模式,准确性高,虽然说文件大,但是现在有SSD和万兆光纤网络,这些磁盘IO和网络IO都是可以接受的。

前提条件

确认是否开启binlog

show VARIABLES like '%log_bin%'

确认binlog_format的值是否为row

show VARIABLES like '%binlog_format%'

开启binlog

如果没有开启需要my.ini文件的[mysqld]后面添加:

server-id=1
log_bin = mysql-bin
max_binlog_size = 1G
binlog_format = ROW
binlog_row_image = full

总体步骤

模拟误操作

建表

CREATE TABLE `students` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`sex` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

插入数据

INSERT INTO `students` VALUES (1, '张三', '男', 16);
INSERT INTO `students` VALUES (2, '李四', '女', 15);
INSERT INTO `students` VALUES (3, '王五', '男', 17);
INSERT INTO `students` VALUES (4, '赵六', '男', 15);

效果如下:

误删数据

模拟误操作,例如删除表时忘记写where条件:

DELETE FROM `students`

效果如下:

恢复数据

flush logs

出现误删后,首先要做的事情就是刷新binlog日志:

flush logs

执行以上语句,将产生一个新的binlog文件
查看当前使用的日志文件:

show master status


mysql-bin.000308就是执行flush命令后新生成的文件,那么误操作时MySQL记录的日志就存放在了mysql-bin.000307文件中。

下载binlog日志 {#xia_zaibinlog_ri_zhi}

下载mysql-bin.000307文件到本地计算机。例如:D:\test\mysql-bin.000307

导出binlog日志到sql文件

mysqlbinlog  --no-defaults --database=test --base64-output=decode-rows -v D:\test\mysql-bin.000307 > D:\test\307.sql

将mysql-bin.000307日志文件中涉及test数据库的日志记录导出至307.sql文件

找到误删的SQL语句

打开上一步导出的307.sql文件,通过搜索可以找到如下语句:

### DELETE FROM `test`.`students`
### WHERE
###   @1=1
###   @2='张三'
###   @3='男'
###   @4=16
### DELETE FROM `test`.`students`
### WHERE
###   @1=2
###   @2='李四'
###   @3='女'
###   @4=15
### DELETE FROM `test`.`students`
### WHERE
###   @1=3
###   @2='王五'
###   @3='男'
###   @4=17
### DELETE FROM `test`.`students`
### WHERE
###   @1=4
###   @2='赵六'
###   @3='男'
###   @4=15

可以看到,虽然我们只执行了DELETE FROM students这一条语句,但binlog日志会记录所有受影响的数据。

将删除语句转换为插入语句

接下来只需要将上一步的DELETE语句转为INSERT语句即可
转换步骤:
1.删除前面的###
2.将DELETE FROM替换为INSERT INTO,将WHERE替换成SELECT
3.将@1=@2=@3=所在行的末尾添加,
3.在@4所在行的末尾添加
4.删除@1=@2=@3=@4=
最终转换后的语句:

INSERT INTO `test`.`students`
SELECT
  1,
  '张三',
  '男',
  16;
INSERT INTO `test`.`students`
SELECT
  2,
  '李四',
  '女',
  15;
INSERT INTO `test`.`students`
SELECT
  3,
  '王五',
  '男',
  17;
INSERT INTO `test`.`students`
SELECT
  4,
  '赵六',
  '男',
  15;

恢复数据

在数据库中执行上一步转换后的插入语句即可恢复数据。

其他

查看binlog

#查看指定binlog文件的内容
show binlog events in 'mysql-bin.000002';

#获取binlog文件列表
show binary logs;

`#查看当前正在写入的binlog文件
show master status;
`

参考

https://blog.csdn.net/Sebastien23/article/details/131492310
https://www.cnblogs.com/zuouncle/p/17268414.html

赞(7)
未经允许不得转载:工具盒子 » MySQL通过binlog找回误删的数据