百度360必应搜狗淘宝本站头条
tomcat 乱码无法与服务器建立安全连接是什么意思sysv-rc-confdocker映射容器目录到宿主机internet explorer 增强的安全配置已启用
当前位置：网站首页 > 技术文章 > 正文

「删库跑路」使用Binlog日志恢复误删的MySQL数据

nanshan 2024-11-27 18:14 9 浏览 0 评论

前言

“删库跑路”是程序员经常谈起的话题，今天，我就要教大家如何删！库！跑！路！

开个玩笑，今天文章的主题是如何使用Mysql内置的Binlog日志对误删的数据进行恢复，读完本文，你能够了解到：

  • MySQL的binlog日志是什么？通常是用来干什么的？
  • 模拟一次误删数据的操作，并且使用binlog日志恢复误删的数据。

写这篇文章的初衷，是有一次我真的险些把测试数据库的一张表给删除了，当时吓出一身冷汗。原因是由于Spring JPA的配置中，有一个spring.jpa.properties.hibernate.hbm2ddl.auto=create-drop，其用途是每次加载hibernate时根据model类生成表，但是sessionFactory一关闭,表就自动删除。，这个可不能随便配置上去，直接就把你原来存在的表给drop了！

好了，回归正题，这篇文章就是想让大家放心，MySQL就算进行了误删操作，也基本都能够抢救回来。尤其是大公司内，数据可不是你想删就能删掉的，有无数权限/备份阻拦着你。

正文

Binlog介绍

binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。
binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看MySQL执行过的所有语句。

看了上面binlog的定义，大家也应该能大致推理出binlog的三大用途：

  • 恢复数据：今天要说的重点
  • 数据库复制：主从数据库是通过将binlog传给从库，从库有两个线程，一个I/O线程，一个SQL线程，I/O线程读取主库传过来的binlog内容并写入到relay log,SQL线程从relay log里面读取内容，写入从库的数据库。
  • 审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

所以说，想要能够恢复数据，首先，你得打开Mysql的binlog，在平常你自己安装的单机Mysql中，默认情况下不会开启。下面就一步步地实践下如何开启你服务器上的Binlog日志。

在MySQL中开启Binlog

首先进入数据库控制台，运行指令：

mysql>?show?variables?like'log_bin%';
+---------------------------------+-------+
|?Variable_name???????????????????|?Value?|
+---------------------------------+-------+
|?log_bin?????????????????????????|?OFF???|
|?log_bin_basename????????????????|???????|
|?log_bin_index???????????????????|???????|
|?log_bin_trust_function_creators?|?OFF???|
|?log_bin_use_v1_row_events???????|?OFF???|
+---------------------------------+-------+
5?rows?in?set?(0.00?sec)

可以看到我们的binlog是关闭的，都是OFF。接下来我们需要修改Mysql配置文件，执行命令：

sudo?vi?/etc/mysql/mysql.conf.d/mysqld.cnf

在文件末尾添加：

log-bin=/var/lib/mysql/mysql-bin

保存文件，重启mysql服务：

sudo?service?mysql?restart

重启完成后，查看下mysql的状态：

systemctl?status?mysql.service

这时，如果你的mysql版本在5.7或更高版本，就会报错：

Jan?06?15:49:58?VM-0-11-ubuntu?mysqld[5930]:?2020-01-06T07:49:58.190791Z?0?[Warning]?Changed?limits:?max_open_files:?1024?(requested?5000)
Jan?06?15:49:58?VM-0-11-ubuntu?mysqld[5930]:?2020-01-06T07:49:58.190839Z?0?[Warning]?Changed?limits:?table_open_cache:?431?(requested?2000)
Jan?06?15:49:58?VM-0-11-ubuntu?mysqld[5930]:?2020-01-06T07:49:58.359713Z?0?[Warning]?TIMESTAMP?with?implicit?DEFAULT?value?is?deprecated.?Please?use?--explicit_defaults_for_timestamp?server?option?(se
Jan?06?15:49:58?VM-0-11-ubuntu?mysqld[5930]:?2020-01-06T07:49:58.361395Z?0?[Note]?/usr/sbin/mysqld?(mysqld?5.7.28-0ubuntu0.16.04.2-log)?starting?as?process?5930?...
Jan?06?15:49:58?VM-0-11-ubuntu?mysqld[5930]:?2020-01-06T07:49:58.363017Z?0?[ERROR]?You?have?enabled?the?binary?log,?but?you?haven't?provided?the?mandatory?server-id.?Please?refer?to?the?proper?server
Jan?06?15:49:58?VM-0-11-ubuntu?mysqld[5930]:?2020-01-06T07:49:58.363747Z?0?[ERROR]?Aborting
Jan?06?15:49:58?VM-0-11-ubuntu?mysqld[5930]:?2020-01-06T07:49:58.363922Z?0?[Note]?Binlog?end
Jan?06?15:49:58?VM-0-11-ubuntu?mysqld[5930]:?2020-01-06T07:49:58.364108Z?0?[Note]?/usr/sbin/mysqld:?Shutdown?complete
Jan?06?15:49:58?VM-0-11-ubuntu?systemd[1]:?mysql.service:?Main?process?exited,?code=exited,?status=1/FAILURE

You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server

之前我们的配置，对于5.7以下版本应该是可以的。但对于高版本，我们需要指定server-id。

如果你不是分布式的部署Mysql，这个server-id随机给个数字就可以。

server-id=123454

模拟删除数据并恢复

  1. 首先新建数据库mytest，新建一张表table1，结构见下方SQL代码
CREATE?DATABASE?`test`?;

USE?`test`;

DROP?TABLE?IF?EXISTS?`table1`;

CREATE?TABLE?`table2`?(
??`id`?int(11)?DEFAULT?NULL,
??`name`?varchar(20)?DEFAULT?NULL
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4;
  1. 插入两条数据，分别是 (1,'A'),(2,'B')
INSERT?INTO?`table1`?VALUES?(1,'A'),(2,'B');
  1. 我们看一下binlog日志的状态，使用show master status
mysql>?show?master?status
????->?;
+------------------+----------+--------------+------------------+-------------------+
|?File?????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|?Executed_Gtid_Set?|
+------------------+----------+--------------+------------------+-------------------+
|?mysql-bin.000001?|??????690?|??????????????|??????????????????|???????????????????|
+------------------+----------+--------------+------------------+-------------------+
1?row?in?set

binlog日志特征：每当我们重启MySQL一次，会自动生成一个binlog文件，当然，我们也可以手动的来刷新binlog文件，通过 flush logs,同样会新创建一个binlog文件。实际上当服务器在重启时，也会调用flush logs操作。

上图代码中可以看到，现在我们正在使用 mysql-bin.0000001 ，并且这个文件现在正在记录到690行。

  1. 然后，使用flush logs来主动刷新一次binlog
mysql>?flush?logs;
Query?OK,?0?rows?affected

mysql>?show?master?status
????->?;
+------------------+----------+--------------+------------------+-------------------+
|?File?????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|?Executed_Gtid_Set?|
+------------------+----------+--------------+------------------+-------------------+
|?mysql-bin.000002?|??????154?|??????????????|??????????????????|???????????????????|
+------------------+----------+--------------+------------------+-------------------+
1?row?in?set

可以看到，现在日志文件在 mysql-bin.000002 文件中，位置为154。也就是我们主动刷新了一次binlog，生成了新的000002，而000001则已经归档了，不会再写入新的日志进去了。

  1. 接下来我们在插入两条数据
insert?into?table1?values?(3,'C');
insert?into?table1?values?(4,'D');

mysql>?select?*?from?table1;
+----+----+
|?id?|name|
+----+----+
|??1?|?A??|
|??2?|?B??|
|??3?|?C??|
|??4?|?D??|
+----+----+
  1. 这时候我们已经有了四条数据，我们再次flush logs，把mysql-bin.000002日志存档，开启新的mysql-bin.000003日志，这样，每次我们插入的数据彼此独立。实际情况下，binlog会比较复杂，这里也是做了简化，为了理解更方便。
mysql>?flush?logs;
Query?OK,?0?rows?affected

mysql>?show?master?status;
+------------------+----------+--------------+------------------+-------------------+
|?File?????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|?Executed_Gtid_Set?|
+------------------+----------+--------------+------------------+-------------------+
|?mysql-bin.000003?|??????154?|??????????????|??????????????????|???????????????????|
+------------------+----------+--------------+------------------+-------------------+
1?row?in?set
  1. 然后我们删除id为4的数据（4，D），并且再次刷新binlog，如此一来，binlog.000003里面只有一条删除操作。
mysql>?delete?from?table1?where?id?=?4;
Query?OK,?1?row?affected

mysql>?show?master?status;
+------------------+----------+--------------+------------------+-------------------+
|?File?????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|?Executed_Gtid_Set?|
+------------------+----------+--------------+------------------+-------------------+
|?mysql-bin.000003?|??????423?|??????????????|??????????????????|???????????????????|
+------------------+----------+--------------+------------------+-------------------+
1?row?in?set

mysql>?flush?logs;
Query?OK,?0?rows?affected

mysql>?show?master?status;
+------------------+----------+--------------+------------------+-------------------+
|?File?????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|?Executed_Gtid_Set?|
+------------------+----------+--------------+------------------+-------------------+
|?mysql-bin.000004?|??????154?|??????????????|??????????????????|???????????????????|
+------------------+----------+--------------+------------------+-------------------+
1?row?in?set
  1. 让我们来好好观察下mysql-bin.00002和mysql-bin00003两个binlog，使用命令：show binlog events in 'mysql-bin.000003'
mysql>?show?binlog?events?in?'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
|?Log_name?????????|?Pos?|?Event_type?????|?Server_id?|?End_log_pos?|?Info???????????????????????????????????????????????????|
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
|?mysql-bin.000003?|???4?|?Format_desc????|????123456?|?????????123?|?Server?ver:?5.7.28-0ubuntu0.16.04.2-log,?Binlog?ver:?4?|
|?mysql-bin.000003?|?123?|?Previous_gtids?|????123456?|?????????154?|????????????????????????????????????????????????????????|
|?mysql-bin.000003?|?154?|?Anonymous_Gtid?|????123456?|?????????219?|?SET?@@SESSION.GTID_NEXT=?'ANONYMOUS'???????????????????|
|?mysql-bin.000003?|?219?|?Query??????????|????123456?|?????????293?|?BEGIN??????????????????????????????????????????????????|
|?mysql-bin.000003?|?293?|?Table_map??????|????123456?|?????????343?|?table_id:?108?(test.table1)??????????????????????????????|
|?mysql-bin.000003?|?343?|?Delete_rows????|????123456?|?????????392?|?table_id:?108?flags:?STMT_END_F????????????????????????|
|?mysql-bin.000003?|?392?|?Xid????????????|????123456?|?????????423?|?COMMIT?/*?xid=39?*/????????????????????????????????????|
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
7?rows?in?set

mysql>?show?binlog?events?in?'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
|?Log_name?????????|?Pos?|?Event_type?????|?Server_id?|?End_log_pos?|?Info???????????????????????????????????????????????????|
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
|?mysql-bin.000002?|???4?|?Format_desc????|????123456?|?????????123?|?Server?ver:?5.7.28-0ubuntu0.16.04.2-log,?Binlog?ver:?4?|
|?mysql-bin.000002?|?123?|?Previous_gtids?|????123456?|?????????154?|????????????????????????????????????????????????????????|
|?mysql-bin.000002?|?154?|?Anonymous_Gtid?|????123456?|?????????219?|?SET?@@SESSION.GTID_NEXT=?'ANONYMOUS'???????????????????|
|?mysql-bin.000002?|?219?|?Query??????????|????123456?|?????????293?|?BEGIN??????????????????????????????????????????????????|
|?mysql-bin.000002?|?293?|?Table_map??????|????123456?|?????????343?|?table_id:?108?(test.table1)??????????????????????????????|
|?mysql-bin.000002?|?343?|?Write_rows?????|????123456?|?????????390?|?table_id:?108?flags:?STMT_END_F????????????????????????|
|?mysql-bin.000002?|?390?|?Xid????????????|????123456?|?????????421?|?COMMIT?/*?xid=34?*/????????????????????????????????????|
|?mysql-bin.000002?|?421?|?Anonymous_Gtid?|????123456?|?????????486?|?SET?@@SESSION.GTID_NEXT=?'ANONYMOUS'???????????????????|
|?mysql-bin.000002?|?486?|?Query??????????|????123456?|?????????560?|?BEGIN??????????????????????????????????????????????????|
|?mysql-bin.000002?|?560?|?Table_map??????|????123456?|?????????610?|?table_id:?108?(test.table1)??????????????????????????????|
|?mysql-bin.000002?|?610?|?Write_rows?????|????123456?|?????????659?|?table_id:?108?flags:?STMT_END_F????????????????????????|
|?mysql-bin.000002?|?659?|?Xid????????????|????123456?|?????????690?|?COMMIT?/*?xid=35?*/????????????????????????????????????|
|?mysql-bin.000002?|?690?|?Rotate?????????|????123456?|?????????737?|?mysql-bin.000003;pos=4?????????????????????????????????|
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------+
13?rows?in?set

虽然有很多看似复杂的指令，但是还是不难看出，在02里，有两条写操作，03里有一条删除操作。

一条插入操作的完整日志是这样：

|?mysql-bin.000002?|?154?|?Anonymous_Gtid?|????123456?|?????????219?|?SET?@@SESSION.GTID_NEXT=?'ANONYMOUS'???????????????????|
|?mysql-bin.000002?|?219?|?Query??????????|????123456?|?????????293?|?BEGIN??????????????????????????????????????????????????|
|?mysql-bin.000002?|?293?|?Table_map??????|????123456?|?????????343?|?table_id:?108?(test.table1)??????????????????????????????|
|?mysql-bin.000002?|?343?|?Write_rows?????|????123456?|?????????390?|?table_id:?108?flags:?STMT_END_F????????????????????????|
|?mysql-bin.000002?|?390?|?Xid????????????|????123456?|?????????421?|?COMMIT?/*?xid=34?*/????????????????????????????????????|
  1. 我们的目的是恢复误删的数据，其实就是将binlog.000002日志的两条插入记录重演一遍，而不需要取理会binlog.000003的操作（因为删除是一个误操作）

所以现在能理解为什么我们频繁刷新binlog了吧，当然，在实际的线上环境中，我们肯定需要将binlog导出后，仔细筛选出误操作，并将其排除，之后再运行binlog。

在本文中，我们只做一个恢复两条插入语句的操作，执行语句：

sudo?mysqlbinlog?/var/lib/mysql/mysql-bin.000002?--start-position?154?--stop-position?690?|?mysql?-uroot?-p?mytest

注意：这里填写的路径/var/lib/mysql/mysql-bin.000002需要具体到你的binlog目录，网上大部分文章只写到mysql-bin.000002，如果你不在目录里，mysqlbinlog命令并不会自动定位binlog所在路径。

参数描述：

--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间

--stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间?取值和上述一样

--start-position：从二进制日志中读取指定position?事件位置作为开始。

--stop-position：从二进制日志中读取指定position?事件位置作为事件截至

执行成功后，再次查看表table1，可以看到两条新的id=3和4的数据被插入了进来。恢复成功了。

mysql>?select?*?from?table1;
+----+----+
|?id?|name|
+----+----+
|??1?|?A??|
|??2?|?B??|
|??3?|?C??|?
|??3?|?C??|
|??4?|?D??|
+----+----+
6?rows?in?set

延伸思考

Binlog在什么情况下无法恢复数据？

结语

删库跑路不用怕，其他开发运维都等着恢复你的数据呢，多好的练手机会是不是。

当然，看完binlog日志恢复数据的原理，希望大家以后在定期备份数据库的脚本里，也能够加上刷新binlog日志的命令，这样一旦某天丢失数据，可以将当天binlog数据单独拿出来还原，做到清晰可辨，也加快恢复效率。

参考

https://www.cnblogs.com/rjzheng/p/9721765.html

https://blog.csdn.net/king_kgh/article/details/74890381

https://www.jianshu.com/p/564fcc2b5e31

https://blog.csdn.net/king_kgh/article/details/74833539

关注我

我是一名后端开发工程师。

主要关注后端开发，数据安全，爬虫，物联网，边缘计算等方向，欢迎交流。

各大平台都可以找到我

  • 微信公众号：后端技术漫谈
  • Github：@qqxx6661
  • CSDN：@后端技术漫谈
  • 知乎：@后端技术漫谈
  • 简书：@后端技术漫谈
  • 掘金：@后端技术漫谈

原创博客主要内容

  • 后端开发相关技术文章
  • Java面试知识点复习全手册
  • 设计模式/数据结构
  • Leetcode/剑指offer 算法题解析
  • SpringBoot/SpringCloud 入门实战系列
  • 爬虫相关技术文章
  • 逸闻趣事/好书分享/个人兴趣

个人公众号：后端技术漫谈

公众号：后端技术漫谈.jpg

如果文章对你有帮助，不妨收藏，投币，转发，在看起来~

相关推荐

今晚拿下PHP反序列化的一系列操作

引言在CTF中反序列化类型的题目还是比较常见的，之前有学习过简单的反序列化，以及简单pop链的构造。这次学习内容为php内置的原生类的反序列化以及一点进阶知识。在题目给的的代码中找不到可利用的类时，这...

Win10安装Apache和PHP（apache安装php模块）

说明：虽然PHPStudy之类的软件给我们提供了方便的集成环境，但是其使用的是非线程安全的PHP版本（NotThreadSafe，NTS），某些功能不可以使用。所以，我们还需要自己再安装一个Apa...

腾讯云云函数部署laravel项目（腾讯云函数 selenium）

1、购买函数套餐包在Serverless控制台，选择函数套餐包进行购买2、新建函数服务2.1、模板创建选择函数URL：新建函数URL，启用公网访问：2.1.1、postman访问首页2.1.2、pos...

站长教你搭建属于自己的网站（站长教你搭建属于自己的网站是什么）

每个人都希望可以有自己的网站，因为那样高端大气上档次，低调奢华有内涵，尤其是公司用户，一般会有自己的网站。而个人呢，也可以搭建自己的网站博客，论坛等，但是一般个人都是搭建博客的。好了，那么下面站长教你...

微信公众号开发出现 cURL error 60: SSL certificate problem ssl证书

在phpstudy的环境下如果出现这样的报错cURLerror60:SSLcertificateproblem:unabletogetlocalissuercertificat...

【网络安全】关于PHP Study nginx解析高危漏洞的预警通报

网络安全近日，山石网科安全研究院监测发现PHPStudyWindows最新版本存在nginx解析漏洞，可以造成任意代码执行。一、漏洞情况phpStudy是一个PHP调试环境的程序集成包，该程序包集成...

PHP 环境 搭建教程（php环境搭建教程linux）

PHP是一种编程语言，很多网站都用PHP语言编写，我们有时候需要测试一个网站，就需要PHP环境才能运行，又要安装Apache、又要安装MySQL……真的非常麻烦。其实我们可以使用PHP集成...

黑客搭建钓鱼平台，手把手教你如何钓鱼?

跨站脚本攻击XSS：通过HTML注入篡改了网页，插入了恶意的脚本，从而用户浏览网页时，控制用户浏览器的一种攻击那么，我们搭建一个XSS钓鱼平台吧，注意：这个平台仅用于学习和测试，小伙伴们不要动有坏心思...

php源码网站搭建方法和过程（php网站源码完整）

web网站是我们上网的窗口，而网站是如何搭建的呢？今天我们来做一个介绍，以php代码为例来进行介绍（后续会介绍一下java代码搭建，如果想要我这里涉及的工具或源码请私信我）。1、首先你需要去网上下载你...

使用VS Code调试PhpStudy环境里的代码

最近几个月把所有项目都迁过来VSCode了（除了因为Unity调试问题反而用回了VisualStudio），PHP也就抛弃了最强的PhpStorm。这段时间抽空在帮朋友处理PHP项目，然...

phpstudy搭建PHP+Mysql服务（用phpstudy搭建服务器）

PHP是一种创建动态交互性站点的强有力的服务器端脚本语言。PHP是免费的，并且使用非常广泛。同时，对于像微软ASP这样的竞争者来说，PHP无疑是另一种高效率的选项。（1）PHP环境搭建使用V...

Windows安装phpstudy（Windows安装mysql）

说明：phpstudy是一个PHP+MySQL+Apache的集成环境，可以减少单独部署各个所需软件的麻烦，以及更加方便地切换版本。phpenv、wamp等软件的作用一样。由于环境的不同，安装过程中可...

phpstudy安装及简单使用教程（phpstudy安装教程详解）

phpstudy前不久爆出有后门，我的看法是，去看下是哪个版本有后门，为啥会有后门，怎么解决掉这个后门，而不是听到后门就弃用了。毕竟phpstudy绿色安装，配置简单，多版本融合，真香。前言：关于开发...

如何对dedeCMS的开源程序进行二次开发

二次开发，简单的说就是在现有的软件上进行定制修改，功能的扩展，然后达到自己想要的功能和效果，一般来说都不会改变原有系统的内核。为了让更多人了解二次开发，并更方便的了解DedeCMS的二次开发，下面将会...

mysql基础问题三问(底层逻辑;正在执行;日志观察)

背景：经常面试会遇到且实际工作中也会应用到的三个场景：目录：一.mysql查询时的底层原理是什么？二.如何查看正在执行的mysql语句？三.如何观察mysql运行过程中的日志信息？-----...

取消回复欢迎 你 发表评论:

一周热门
最近发表
标签列表