HQY 一个和谐有爱的空间

背景

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

推荐本站淘宝优惠价购买喜欢的宝贝:

本文链接：https://sg.hqyman.cn/post/9532.html 非本站原创文章欢迎转载，原创文章需保留本站地址！

休息一下~~