程序员保命技能，Mysql binlog数据恢复，你还不知道吗？数据库迷途的博客-

大家好我是迷途，一个在互联网行业，摸爬滚打的学子。热爱学习，热爱代码，热爱技术。热爱互联网的一切。无论你是正在路上的旅人，还是背上行囊，整装待发的学子。 都可以关注一下帅途的动态 当然如果帅途拿到比较好的学习资料或者看见比较好的文章也会第一时间跟大家。

前言

前两天帅途在操作线上数据库的时候，不小心干掉了正式环境的用户数据，没有最新备份，没有开启事务，多么痛的领悟。（ps：这里是由于是周末加班，加上睡得迷迷糊糊，就偷了个懒，偷偷摸摸用root账号进行操作，各位小伙伴千万不要学帅途，一失足成千古恨。）还好帅途毕竟在互联网混迹了有一段时间了，立马就想到了使用bin_log日志解决。

正文

1、什么是bin_log？

bin_log是mysql的二级制日志记录文件，默认是关闭状态，他记录了我们对数据库的所有增删改查和事务等操作，一般来说我们主要用于数据恢复、和主备存储等，它我们在成熟项目中不可或缺的一部分，现在大部分云端数据库，在我们失误操作数据为我们恢复数据时，其实底层应用也是该文件。

2、bin_log的三种模式

• Row
  行模式，日志记录精确到行级别，例如我们一条update语句修改了三条数据，那么在bin_log记录中就会生成三条信息（我们的update和delete等操作事务的开启和提交也会记录）
• Statement
  语句模式，他不会记录我们每一行数据的边动，只会记录每次我们执行的sql
• Mixed
  混合模式，row和statement的混合使用，目前大家对Mixed的切换模式还有点模糊，帅途查阅了大量资料和操作简单了总结了一下,bin_log的模式切换主要跟事务隔离级别有关，在我们使用事务的时候，使用innodb.lock的时候bin_log模式都会使用row模式记录。（感兴趣的小伙伴可以自己去测试一下，一下经验）

3、数据准备

测试环境：Mysql 5.7 , navicat 12 , centos 7

测试数据：数据库（test），数据表（person） 字段（name，age，gender，school）
准备三条测试数据
1 张三 18 男 清华大学
2 李四 20 女 北京大学
3 王五 25 男 斯坦福大学

4、开启bin_log日志

首先我们需要查看我们的mysql服务器是否开启了bin_log日志，在mysql中执行命令：

show variables like '%log_bin%' -- 查询是否开启bin_log日志 

ON：标识开启日志
OFF：标识未开启日志

没有开启的小伙伴找到mysql的my.cnf文件末尾添加配置，默认在/etc/my.chf：

 server_id=1   --服务节点id 区间2^32   log_bin = mysql-bin --日志文件存储位置和名称，这里使用默认配置   binlog_format = ROW --日志级别   expire_logs_days = 30 --log日志留存时间 单位Day 

配置完成后重启mysql即可生效

service mysqld restart --重启mysql 

5、模拟误修改数据

这里模拟一下帅途执行了一个update语句但是忘记写update语句，执行成功之后三条数据的学校数据都被修改了。

update person set school = '家里蹲大学' 

发现误操作之后立即执行：

-- 查看所有bin_log日志 show master logs; -- 刷新日志，生成新的日志 flush logs; -- 然后执行，查询我们执行操作的pos_end节点，可以理解为动作节点 show binlog events in 'mysql-bin.000002'; -- 然后根据我们查询到的pos节点进行恢复，例如帅途这里的end_pos节点是1703 

这里立即刷新日志是因为，如果我们误操作正式环境的数据，toB稍微好一点，实时操作不会很多，如果我门做的是toC的系统，那么每过一分钟用户的操作量都是极其庞大的，为了防止我们找不到动作节点，所以这里刷新一下

这里可以看出，帅途执行完update后找到的对应这条update语句的动作节点，然后根据他的动作节点进行恢复即可。（如果开始执行是1775则我们恢复到1703动作节点即可）

在我们的mysql服务器上执行

根据结束动作点进行恢复，如果我们没有指定开始节点，那么默认会从当前bin_log文件开头执行到结束点
/usr/bin/mysqlbinlog –stop-position=1703 –database=test /var/lib/mysql/mysql-bin.000002 | /usr/bin/mysql -uroot -p123456 -v test

恢复某一个动作节点之间的数据

从1023动作开始执行到1703动作
/usr/bin/mysqlbinlog –start-position=1023 –stop-position=1703 –database=goteam_test /var/lib/mysql/mysql-bin.000002 | /usr/bin/mysql -uroot -p123456 -v goteam_test

解析版

#{mysqlbinlog} –stop-position=#{end_pos} –database=#{数据库名称} #{日志文件位置} | /#{mysql数据库所在位置} -u#{账号} -p#{密码} -v #{数据库}

然后我们的数据就成功恢复成执行update之前的样子了，感觉很酷的样子~

注意：有些同学到了这一步可能发现，我明明恢复到了执行update语句之前，为什么我数据还是修改之后的数据？或者我数据只有部分进行了恢复，还有一部分并没有恢复？甚至报错？

6、Mysql执行流程

其实到了这一步，我们就需要先了解bin_log日志对我们数据进行恢复的原理，首先我们来看看一条sql语句的执行流程

通过上面我们可以看出，我们日志的写入等操作是通过我们的执行器，并且如果我们的引擎是innodb的话，执行器会为我们默认加上事务操作。（注意：这里的事务只有增删改会为我们默认开启，select、drop等都是不会开启的）

redo_log:redo log通常是物理日志，记录的是数据页的物理修改，可以将他看为一个临时的日志记录，mysql的innodb引擎中默认为我们开启，在我们执行update语句的时候，特别是高并发情况下，我们每执行一次修改语句，都会先从磁盘中找到这条需要修改的数据，执行修改动作，然后再写入磁盘，在这一系列的操作过程中，消耗的io是极其恐怖的，所以mysql的研发工程师为了避免这一问题，就用到了redo log，也是对应MySQL里经常说到的WAL技术，WAL的全称是Write-Ahead Logging，在我们执行update的时候，会将我们的数据预先写入到redo log中，等待我们的server空闲的时候，再写入到磁盘当中。

下面我们来看看一条update语句在执行器中的流程

在我们写入日志文件的时候，我们的执行器会将执行语句进行拆分，也就是bin_log的row级别，例如在我们上面的数据中，有三条数据源，我们执行 update person set school=‘家里蹲大学’，那么我们的执行器会为我们将这条update解析成 update person set 列1=值1,列2=值2…… where 列1=值1 and 列二=值2……，由下图我们可以看出，一条多行操作的sql会被解析成多条语句，这里需要注意一下，如果我们执行的是drop操作，那么是不会被解析成行级的。

7、bin_log数据恢复原理

其实从上面MySQL的执行流程我们就可以看出来，bin_log日志是将我们操作的每一条sql，根据我们配置的log级别，解析，写入到二进制文件中，然后在我们使用mysqlbinlog控件进行恢复的时候，相当于，将我们定义的begin_pos和end_pos之间的语句再次执行一遍。所以很多小伙伴可能发现，明明我恢复到执行（删除、修改操作等）之前的动作节点或者时间了，为什么数据没有恢复成功，或者部分恢复，甚至报错？那是因为，在我们执行的这段动作或时间节点中，并没有数据操作动作，或者有操作动作，但是我们的数据结构已经被干掉了，所以一般当我们误操作数据库的时候，需要找到和我们当前时间线最近的备份数据，然后匹配bin_log日志里面的时间节点，匹配到我们的数据备份时刻，然后执行bin_log日志，进行恢复

8、根据时间节点恢复数据

-- 模拟我们在 2020-05-26 11:22:00 执行了该语句，数据库所有数据被篡改 update person set school='家里蹲大学' 

执行恢复语句

根据时间节点进行恢复
/usr/bin/mysqlbinlog –stop-datetime=‘2020-05-26 11:21:00’ –database=test /var/lib/mysql/mysql-bin.000001 | /usr/bin/mysql -uroot -p123456 -v test

由于帅途这里是新创建的数据库和文件，所以直接根据结束时间执行操作即可，如果我们只想恢复某一个时间节点操作的数据，则执行

从2020-05-26 00:00:00开始恢复到2020-05-26 11:20:00
/usr/bin/mysqlbinlog –start-datetime=“2020-05-26 00:00:00” –stop-datetime=“2020-05-26 11:20:00” –database=test /var/lib/mysql/mysql-bin.000001 | /usr/bin/mysql -uroot -p123456 -v test

查看恢复之后的数据，可以看出这里的数据已经恢复成修改之前的数据了。

9、将bin-log导出成sql文件

看到这里，可能有些同学会问，帅途帅途！我是做toC产品的，我今天误操作了一张表，我把一张表的数据全部清掉了，但是同一个时间段内有用户在操作，我不想影响其他用户数据怎么办？

没有问题，帅途来帮你。我们这里直接将bin_log转成sql文件，来进行手动恢复。

执行命令
/usr/bin/mysqlbinlog –base64-output=DECODE-ROWS -v /var/lib/mysql/mysql-bin.000003 > /logs/mysqlLogs/1.sql

如果使用的默认配置，那么日志文件的地址在/var/lib/mysql/中，具体查看etc/my.cnf文件配置
/usr/bin/mysqlbinlog –base64-output=DECODE-ROWS -v #{日志存储位置} > #{输出位置}

导出之后，查看我们生成的sql文件

由上图我们可以看出，bin_log日志记录的delete和update方式都是全匹配方式，所以我们可以通过匹配的数据，即可手动恢复

10、恢复drop文件

哎呀帅途！我一不小心把我的表（数据库）给drop了，怎么恢复？

咳咳，这又啥可难的，帅途来教你，例如这里帅途不小心把person表给干掉了

• 首先找到我们最近的备份数据，将表结构恢复，（注意：如果在我们当前bin_log文件里面没有某个数据的结构或者修改之前的数据行会报错）

帅途这里模拟一下，例如这里帅途备份是2020.05.25 12:00:00备份，然后在2020.05.26 19:00:00误操作将表给干掉了，备份时数据如下。

• 根据我们备份的时间节点，执行bin_log文件，我们备份时间是 2020.05.25 12:00:00那么我们的开始执行时间节点就是2020.05.25 12:00:00，结束时间放到误操作之前一分钟2020.05.26 18:59:00,执行语句的时间可以通过上面的命令导成sql文件查看,下图是恢复之后的数据

执行命令
/usr/bin/mysqlbinlog –start-datetime=“2020.05.25 12:00:00” –stop-datetime=“2020.05.26 18:59:00” –database=test /var/lib/mysql/mysql-bin.000002 | /usr/bin/mysql -uroot -p8856769abcd -v test

如果经过一天的操作，我们的bin_log日志超过max_binlog_size配置，重新创建了新的日志文件，则我们将日志文件依次执行即可。

那帅途，我从来没有备份怎么办？

别着急！先看看下面两个问题。

• 我的bin_log文件全吗？

如果全，那么恭喜你，问题不大，我们直接从第一个bin_log依次执行即可，因为bin_log里面存储了我们所以的操作，无论是DML还是DML，相当于将我们这个数据库创建之初到目前，所以操作重写一遍。如果不全，不要慌，接下来看下面一个问题。

• 数据结构，总还有吧！

先将我们误删除的数据结构恢复（也就是表结构，数据库结构等），根据数据丢失情况，定位到对应bin_log，例如这张表是我半个月之前创建的，但是我最近的bin_log是一周之前的，那就拿到最早的那个bin_log，然后不要管，直接执行。帅途下面模拟一下。

这是帅途目前剩下的数据，丢失了一条数据

丢失数据：
4 赵六 30 女 厦门大学

执行bin_log发现报错

报错提示：
ERROR 1032 (HY000) at line 37: Can’t find record in ‘person’
然后我们导出sql文件，根据提示，找到sql文件对应报错位置

发现，原来是我们修改的时候，由于我们这条数据已经丢失，但是进行修改报错。我们根据update语句的条件进行手动恢复即可。

以上方法虽然可以恢复部分数据，但是由于我们bin_log文件和备份文件的丢失，但是还是会有一部分日志记录时未操作数据无法恢复，so 各位小伙伴一定要慎重！

总结

最后帅途在这里总结一下，希望大家在操作正式数据的时候一定要慎重！毕竟虽然压箱底的保命技能虽然我们学到了，但是还是有一定的局限性。bin_log数据恢复并不适用于所有场景，例如在我们数据结构缺失，bin_log文件不全的情况下。毕竟在电视剧里所有的江湖大虾在使用压箱底的绝技时，一般都离领盒饭不远了。如果是特别重要的数据丢失，照成的损失非常庞大的话，那么还是建议各位找专业的第三方数据恢复公司，自己不要瞎搞

• 误操作数据库之后的恢复流程

1、flush logs 刷新bin_log日志，防止日志刷新过于频繁，找不到我们误操作的时间动作节点（最好备份一下日志）
2、找到我们最近的备份，一般来说在项目当中，都会备份一次数据。
3、根据我们最近的备份时间节点与误操作时间节点，使用mysqlbinlog进行恢复
4、如果没有备份，需要查看bin_log日文文件是否齐全，如果齐全则直接重新执行一边即可
5、如果不齐全，则需要我们手动导出sql文件，根据数据缺失情况手动恢复
6、以上操作建议在不影响正式环境情况下操作，处理好数据在导入到正式

_{看到这了就快关注一下吧}