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