背景
在操作线上数据库时要尤其小心,但有时候难免会出现误删数据的情况,在出现误删后,如果有数据库备份,只需要还原备份文件即可。但有时候备份频率不高,例如一天备份一次,而误删的数据恰好是今天新增的数据,该数据并不在备份中,此时就需要通过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