内部群炸锅了，同事又删库了。。

ゝ一世哀愁。 2022-09-11 00:08 205阅读 0赞

**点击关注公众号，Java干货****及时送达**![d50693099b0e69f0b6d1452cfefe89ab.png][]

事件起因

#  #

我们的系统中有数据导入的功能，可以把特定的格式的excel数据导入到系统中来

由于客户电脑的文件比较多，很多文件的名字也比较相近，客户在导入excel时选错了文件

这个错误的excel文件的格式恰好能被系统解析，客户也没及时发现导错了文件，所以就将6万多条没用的数据导入到了系统中

![be82c2fb845ddf659ac5e4300f26f6b6.png][]

这6万多条数据对系统来说就是无用的数据，不会影响系统的运行，最多也就是占用一点数据库空间而已

客户只需要把正确的excel重新导入，就可以继续完成他的业务了

但是，客户是一个重度强迫症患者，他觉得在管理平台看到这6万多条没用的数据令他抓狂

客户想要把这些数据删除，我们系统又没有提供批量删除功能，只能单个删除，这无疑是一个巨大的工作量

客户就通过客服部门找到了研发团队，想让我们研发人员从数据库中直接删除

# 删库经过 #

虽然在生产环境直接操作数据库明显是违规操作，但客户的要求又不得不满足，谁让人家是爸爸呢

![15a1e2614f41969a057529b3d8735430.png][] 金主爸爸

> 由于生产环境的数据和表结构属于商业机密，我们讨论的重点也不在于数据和表结构，而是数据恢复的思路。所以我在测试环境新建了用户表，导入了一些测试数据，当作是生产环境进行操作

研发人员登录生产数据库，执行如下sql，找到了这6万多条错误数据。

select * from t_user where age>18 and deptid=100;

在确认这6万多条数据确实是错误导入的数据后就准备开始删除。由于表里面没有逻辑删除字段，所以只能进行物理删除

需要删除的数据已经确定，通常情况下把sql中的`select *`替换为`delete`去执行，出错的机率会小一点

但是，研发人员并没有去改原来的sql，而是重新写了一个删除语句并且执行

delete from t_user where age>18;

问题就这样出现了，在新写的删除语句中缺少了`deptid=100`的条件

> 不要问我为什么删除之前没有备份，这都是血泪的教训

重新查表后发现误删了10多万条数据

生产环境中，很多业务都依赖这个表，算是系统的核心表。虽然是只删除了10万条数据，但系统的很多功能无法正常使用，其实和删库没啥区别了

![b3dd7b8daab21f888ccfe26d95441582.png][]

研发人员发现删库后，第一时间报告给了领导（居然没有第一时间跑路）

领导当机立断，要求系统停止运行，给所有客户发送停服通知，打开所有客服通道，处理客户投诉和答疑

同时，也安排研发人员进行数据找回，要求尽快搞定

# 数据找回 #

我们找到删库的研发人员询问他有没有备份，他的回答是没有

我们又去咨询运维的同事，看看生产环境有没有开启数据库定期自动备份，运维的回答也是没有

事情比较难办了，只能把希望寄托于mysql的binlog了。

binlog二进制日志文件，数据库的insert、delete、update、create、alter、drop等写入操作都会被binlog记录（下文对binlog有详细介绍）另外，Linux 系列面试题和答案全部整理好了，微信搜索Java技术栈，在后台发送：面试，可以在线阅读。

binlog记录日志是需要开启配置的，希望生产环境的mysql数据库开启了binlog日志，否则只能找专业的磁盘数据恢复的第三方公司了

登录生产环境数据库，查看binlog是否开启

SHOW VARIABLES LIKE 'LOG_BIN%';

![6e88f48ed9d2b05567a1326fa1c439ca.png][]

从图中可以看到`log_bin`是处于`ON`的状态，说明binlog是开启的

悬着的心终于放下了一大半，接下来就是想办法从binlog中把数据恢复就行了

从上图中也可以看到`log_bin_basename`是`/var/lib/mysql/bin-log`，说明binlog是存放在mysql所在的服务器的`/var/lib/mysql`目录下，文件是以`bin-log`开头，比如：bin-log.000001

登录mysql所在的服务器，进入到binlog所在的目录

cd /var/lib/mysql

查看binlog日志文件  
![b620e468ed55afcd97da19c83ba04ddb.png][]

binlog日志文件是滚动生成的，从图中看到现在已经有4个文件了。

通常情况下，生产环境的binlog会有成百上千个，这时候就需要确认我们需要的数据是在第几个binlog中了，下文也会讲怎么确定我们需要的是第几个

因为我们删库是刚刚发生的事情，所以我们需要的数据大概率是在第4个文件中

直接去查看第4个binlog文件，看到的全都是乱码，就像下面这样，这是因为binlog文件是二进制的

![9cb55fa1804c957ce742b20411ae4363.png][]

我们需要借助mysql官方提供的`mysqlbinlog`命令去才能正确解析binlog文件

用mysqlbinlog命令可以打开binlog文件，但是一个binlog文件的大小可能有几百兆，要从几百兆日志中找到我们需要的日志，还是比较麻烦的

还好mysqlbinlog命令提供一些参数选项可以让我们对binlog文件进行筛选，最常用的参数就是时间参数（下文也会对mysqlbinlog的详细用法进行说明）

经过和删库的研发人员确定，删库的时间大概是**「10:40」**，那我们就以这个时间点为参考，找前后5分钟的日志

mysqlbinlog -v --start-datetime='2021-06-10 10:35:00' --stop-datetime='2021-06-10 10:45:00' bin-log.000004 | grep t_user

![4c7d907d7e0ac5c5fefdada895aedb54.png][]

从图中可以看到，这个时间点的日志确实包含我们删除数据的日志

接下来我们就需要把这些日志整理一下，然后想办法恢复到数据库就可以了。

首先，把我们需要的日志单独保存到tmp.log文件中，方便下载到本地

mysqlbinlog -v --start-datetime='2021-06-10 10:35:00' --stop-datetime='2021-06-10 10:45:00' bin-log.000004 > tmp.log

把tmp.log下载到本地，用文本编辑工具打开看一下，可以看到一堆伪sql  
![5dec428030cf50bcf7b0c04bcd635f47.png][]

> 在上图的伪sql中  
> @1表示第一个字段  
> @2表示第二个字段  
> 其他的以此类推

日志中包含的sql是一些伪sql，并不能直接在数据库执行，我们需要想办法把这些伪sql处理成可在数据库执行的真正的sql

我们使用的文本编辑工具的批量替换功能，就像下面这样  
![4d09b7eac18fce43e0665c9acf13a22d.png][]

最终处理好的sql就像是这样  
![278284b54c17c2b419f39b7e53a2b189.png][]

把处理好的sql在测试数据库验证一下没问题后直接在生产库执行

sql执行完以后，被误删除的数据就恢复回来了。

我们和删库的研发一起，把客户要求删除的6万多条数据重新给删除，算是完成了客户的要求

至此，删库事件就暂时告一段落。不要问删库的研发受到了什么处分，问就是什么处分都没有

分享资料：[Spring Boot 学习笔记][Spring Boot]

# 几点建议 #

删库跑路真的不只是一句玩笑话，如果真的不小心删库了而又无法找回数据的话，不仅仅是简单的罚款、扣绩效就完事了，甚至有可能会面临牢狱之灾

对于公司来说，一个不小心的删库操作，就有可能把公司删没了。毕竟删库造成的数据损失、经济损失不是所有公司都有能力承担的

所以，生产环境的数据安全一定是重中之重。根据我多年的删库经历，也总结了一些经验分享给你们，希望对你们有所帮助

**「1、研发人员不能直连生产库」**

生产库一般由DBA或者运维来维护，研发人员很少有需要登录生产数据库查看数据的需求，就算数据真的有问题，一般情况下DBA或运维人员也能解决

如果一个系统需要研发人员频繁的登录数据库去维护数据，这时就该考虑在系统中增加一个管理功能来使用，而不是频繁登录数据库

所以，研发就不应该具有生产库的登录权限。如果偶尔的需要登录生产库查看数据，可以找DBA开一个临时账号

**「2、登录生产库使用只读账号」**

大部分人使用数据库都会使用连接工具，比如Navicat、SQLyog等

每个人的电脑上，大概率也只有一个连接工具。开发库、测试库、生产库都在同一个连接工具中打开，有时只是想在开发库中修改一条数据，却不小心修改了生产库

而MySql的事务是自动提交的，在连接工具中，正在修改的当前行失去光标后就会自动提交事务，极其容易操作失误

所以，如果确实的需要登录生产库，尽量使用具有只读权限的账号登录

**「3、关闭autocomit、多人复核」**

如果确实需要在生产库进行数据的增加、修改或删除，在执行sql之前最好先关闭事务的自动提交

在需要登录生产库修改数据的情况下，想必问题也比较复杂，一条sql语句应该是完成不了，可能需要写N多个sql才能完成数据的修改

这么多的sql，很有可能在执行的时候会选错。有时你只是想执行一个select语句，结果发现执行的是delete

更坑爹的是，大部分的数据库连接工具有`执行当前选中内容`的功能。有时候你只想执行当前选中的内容，结果发现执行的是全部内容

如果关闭了自动提交，就算出现上面的情况，也还有机会挽回。比如下面这样

-- 关闭事务自动提交
    set @@autocommit=0;
    
    
    -- 查看需要删除的数据，共65600条
    select * from t_user where age>18 and deptid=100;
    -- 删除
    delete from t_user where age>18;
    
    
    -- 发现有问题，回滚
    select * from t_user where age>18 and deptid=100;
    rollback ;
    
    -- 确认没问题，提交
    -- commit;

另外，在`commit`之前需要至少再找一个同事进行确认。所谓当局者迷，自己有时可能处于一个错误的思路上，就想当然的认为结果没问题，这时就需要一个旁观者来指点迷津

两个人都确认没问题之后再提交，出错的机率也会小很多

**点击关注公众号，Java干货****及时送达**![9eb7624a998c00a569f4d625847eb4a6.png][]

**「4、修改数据之前先备份」**

备份、备份、备份，重要的事情说三遍

备份虽然会麻烦一点，但它是保证数据准确性最有效的手段

况且，掌握一些技巧后，备份也不是很麻烦的事情

比如，我们删除数据之前可以先这样备份

-- 创建一个和原表一样的备份表（包含索引）
    create table t_user_bak like t_user;
    
    -- 拷贝数据到备份表
    INSERT into t_user_bak select * from t_user;
    
    -- 确认数据拷贝完成
    select * from t_user_bak;

这样备份的数据，就算原表数据误删了，甚至都不用恢复数据，只需要把备份表的名字改成原表的名字直接使用就可以了

在生产库修改数据之前，一定要记得备份，一旦数据修改出错，这是成本最低并且最有效的恢复途径

**「5、设置数据库定期备份」**

生产环境，运维人员一定要设置数据库定期备份。研发人员也有义务提醒运维同事编写自动备份脚本，因为生产库一旦出现问题需要恢复数据，没有定期备份的话，麻烦的不只是运维人员，研发人员也要跟着麻烦

备份周期可以根据业务需要来决定。如果业务对数据要求的实时性比较高，备份周期相对短一点，恢复数据时可以最大程度的避免数据丢失；反之，备份周期可以长一点，节省磁盘空间

如果有必要，可以定期把备份文件拷贝到异地服务器，避免由于一些不可抗力因素导致的当前服务器磁盘损坏，如地震、台风等

# binglog日志 #

binlog即Binary Log，它是二进制文件，用来记录数据库写操作的日志

数据库的insert、delete、update、create、alter、drop等写入操作都会被binlog记录

因此，数据库的主从数据同步通常也是基于binlog完成的，本文只对binlog做一些简单介绍，后期会单独写一篇文章讲基于binlog的主从数据同步

binlog日志需要配置开启，可以通过脚本查看binlog是否开启

SHOW VARIABLES LIKE 'LOG_BIN%';

![62e64b9ed4978d5de1941f04b5704d62.png][]

如果`log_bin`参数显示的是`OFF`说明binlog是关闭状态，需要手动开启

开启binlog需要修改数据库的`my.cnf`配置文件，my.cnf文件通常在服务器的`/etc`目录下

打开`/etc/my.cnf`文件，配置binlog的相关参数，下文配置binlog的常用参数

# 启用binlog并设置binlog日志的存储目录
    log_bin = /var/lib/mysql/bin-log
    # 设置binlog索引存储目录
    log_bin_index = /var/lib/mysql/mysql-bin.index
    # 30天之前的日志自动删除
    expire_logs_days = 30
    # 设置binlog日志模式，共有3种模式：STATMENT、ROW、MIXED
     binlog_format = row

binlog的日志有三种格式，分别是STATEMENT、ROW、MIXED。在mysql5.7.7版本之前默认使用的是STATEMENT，之后的版本默认使用的是ROW。

另外，关注公众号Java技术栈，在后台回复：面试，可以获取我整理的 Linux 系列面试题和答案，非常齐全。

## ROW格式 ##

ROW格式下，binlog记录的是每一条数据被修改的详细细节。

比如，执行delete语句，删除的数据有多少条，binlog中就记录有多少条伪sql

delete from t_user where age>18;

![22a6a03a2d09e98e5b709d9f5acecf26.png][]

那么row格式的日志的缺点就很明显，在发生批量操作时，日志文件中会记录大量的伪sql，占用较多的磁盘空间

尤其是当进行alter操作时，每条数据都发生变化，日志文件中就会有每一条的数据的日志。此时，如果表中的数据量很大的话，日志文件也会非常大

在mysql5.6版本之后，针对ROW格式的日志，新增了`binlog_row_image`参数。

当`binlog_row_image`设置为`minimal`时，日志中只会记录发生改变的列，而不是全部的列，这在一定程度上能减少binlog日志的大小

虽然记录每行数据的变化会造成日志文件过大，但这也是它的优点所在

因为它记录了每条数据修改细节，所以在一些极端情况下也不会出现数据错乱的问题。在做数据恢复或主从同步时能很好的保证数据的真实性和一致性

## STATEMENT格式 ##

STATEMENT格式下，日志中记录的是真正的sql语句，就像是这样  
![b5da2657bda2311ef35d1b1b971d8c6c.png][]

日志中的sql是直接可以拿到数据库运行的

STATEMENT格式的日志的优缺点和ROW格式的正好相反，它记录的是sql语句和执行语句时的上下文环境，而不是每一条数据。所以它的日志文件会比ROW格式的日志文件小一些

由于记录的只是sql语句和上下文的环境，STATEMENT格式的日志在进行主从数据同步时会有一些不可预估的情况出现，导致数据错乱。比如sleep()、last\_insert\_id()等函数会出现问题

## MIXED格式 ##

MIXED格式是STATEMENT和ROW的结合，mysql会根据具体执行的sql语句，来选择合适的日志格式进行记录

MIXED格式下，在执行普通的sql语句时会选STATEMENT来记录日志，在遇到复杂的语句或函数操作时会选择ROW来记录日志

# mysqlbinlog命令 #

mysql数据库的binlog文件是二进制的，基本看不懂，使用数据库自带的`mysqlbinlog`命令可以把二进制文件转换成能看懂的十进制文件

由于数据库的binlog文件可能会很大，查看起来会很麻烦，所以`mysqlbinlog`命令也提供了一些参数可以用来筛选日志

**「mysqlbinlog语法」**

mysqlbinlog [options] log-files

> `options`：可选参数  
> `log-files`：文件名称

**「options的常用值」**

> `-d`: 根据数据库的名称筛选日志  
> `-o`：跳过前N行日志  
> `-r, --result-fil`: 把日志输出到指定文件  
> `--start-datetime`: 读取指定时间之后的日志，时间格式：yyyy-MM-dd HH:mm:ss  
> `--stop-datetime`: 读取指定时间之前的日志，时间格式：yyyy-MM-dd HH:mm:ss  
> `--start-position`: 从指定位置开始读取日志  
> `--stop-position`: 读取到指定位置停止  
> `--base64-output`：在row格式下，显示伪sql语句  
> `-v, --verbose`：显示伪sql语句，-vv可以为sql语句添加备注

**「常用写法」**  
查看fusion数据库的日志

mysqlbinlog -d=fusion bin-log.000001

查看某个时间段内的日志

mysqlbinlog  --start-datetime='2021-06-09 19:30:00' --stop-datetime='2021-06-09 19:50:00' bin-log.000001

恢复数据，事件的开始位置是4300，结束位置是10345

mysqlbinlog --start-position 4300 --stop-position 10345 bin-log.000001 | mysql -uroot -p123456 fusion

![40744c829a7b14e1d26e795c7e562d9f.gif][]

![0d246ba8e818cb99f4c312fd9fe0a782.png][]

[![7aa1022933c00562edbddd9adeabaf33.png][]][7aa1022933c00562edbddd9adeabaf33.png 1]

[![c93d424ee60579424b39b1a71a52d29d.png][]][c93d424ee60579424b39b1a71a52d29d.png 1]

[![a165933c41417d13a7823af9039568ce.png][]][a165933c41417d13a7823af9039568ce.png 1]

[![6aefda8edcca26e74143aa3607d88699.png][]][6aefda8edcca26e74143aa3607d88699.png 1]

[![a4dd05a48da7e9b891a4aff0a1cfb50d.png][]][a4dd05a48da7e9b891a4aff0a1cfb50d.png 1]

**关注Java技术栈看更多干货**

![33a7d1fb280ce92716ca01dde3917d31.png][]

![c7900d3632a55810ec4523de2ef2c861.gif][]

获取 Spring Boot 实战笔记！

[d50693099b0e69f0b6d1452cfefe89ab.png]: /images/20220829/f8cbe716e007468290d4fe72a4418f77.png
[be82c2fb845ddf659ac5e4300f26f6b6.png]: /images/20220829/6b9cc7f5819841049cf21ea9cb3110b5.png
[15a1e2614f41969a057529b3d8735430.png]: /images/20220829/b9c143be85094a11805e512592d71465.png
[b3dd7b8daab21f888ccfe26d95441582.png]: /images/20220829/2285dbffa3aa4ccca9073c9ab460c3e8.png
[6e88f48ed9d2b05567a1326fa1c439ca.png]: /images/20220829/5a8e45d3cb7249e9a70076fc8e08fa3c.png
[b620e468ed55afcd97da19c83ba04ddb.png]: /images/20220829/d94cdc39801743b1b2ed8ab6a34d862f.png
[9cb55fa1804c957ce742b20411ae4363.png]: /images/20220829/7ebb2748ea734c3285e610405d953b07.png
[4c7d907d7e0ac5c5fefdada895aedb54.png]: /images/20220829/b43031eaf1be49f6a80d18e473691e7c.png
[5dec428030cf50bcf7b0c04bcd635f47.png]: /images/20220829/66a23f56f6d44dfcbd1c97312b5d99a3.png
[4d09b7eac18fce43e0665c9acf13a22d.png]: /images/20220829/2f22047c283649c0aa6c954a88e39114.png
[278284b54c17c2b419f39b7e53a2b189.png]: /images/20220829/48bb472b1eaa4a83b4574d7b9492c8e9.png
[Spring Boot]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&chksm=eb50a5e5dc272cf320c15c50f14bc97d3b98aa1e60fec5460824be0f057cb15ed82ba5ed2787&idx=2&mid=2247540947&scene=21&sn=7faf14de2e31d03167128d6d3cc8c795#wechat_redirect
[9eb7624a998c00a569f4d625847eb4a6.png]: /images/20220829/d9869a6f244d4602a3e03d34f67edbb0.png
[62e64b9ed4978d5de1941f04b5704d62.png]: /images/20220829/9c3c011809e847c3850dfb3b6c2e8304.png
[22a6a03a2d09e98e5b709d9f5acecf26.png]: /images/20220829/8daeaf79851b41f4bfb014c07e5acbdb.png
[b5da2657bda2311ef35d1b1b971d8c6c.png]: /images/20220829/c372b2f88dc64a689c35ec19a8a6a58e.png
[40744c829a7b14e1d26e795c7e562d9f.gif]: /images/20220829/97f2705c821d4fd89abb7d87e41b92ec.png
[0d246ba8e818cb99f4c312fd9fe0a782.png]: /images/20220829/b4e61ac5f0724dafa0e17f8b3af144e1.png
[7aa1022933c00562edbddd9adeabaf33.png]: /images/20220829/a19974fa1ca24d278ee07d28bda223b8.png
[7aa1022933c00562edbddd9adeabaf33.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=1&mid=2247539795&scene=21&sn=1efca511169265e8146e90db220159d5#wechat_redirect
[c93d424ee60579424b39b1a71a52d29d.png]: /images/20220829/9ffe4f28dea54a3884d1b397ed4996a6.png
[c93d424ee60579424b39b1a71a52d29d.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=2&mid=2247539679&scene=21&sn=c197d84d39f21de9f12987e1781d1dfc#wechat_redirect
[a165933c41417d13a7823af9039568ce.png]: /images/20220829/d8f9dbcd1ee44d71bcda557485d6ba59.png
[a165933c41417d13a7823af9039568ce.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=2&mid=2247540613&scene=21&sn=acbe3189c3e5d11419b8f383f36b145f#wechat_redirect
[6aefda8edcca26e74143aa3607d88699.png]: /images/20220829/981dba1fbf72474eab8115817396270b.png
[6aefda8edcca26e74143aa3607d88699.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=1&mid=2247537730&scene=21&sn=3869e164e482b838671fa8896cb33cdb#wechat_redirect
[a4dd05a48da7e9b891a4aff0a1cfb50d.png]: /images/20220829/4d85e3b7344d4b14b17fbd10ef27631c.png
[a4dd05a48da7e9b891a4aff0a1cfb50d.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=1&mid=2247540947&scene=21&sn=a4941f93acb4ee551534a4a1f57d9376#wechat_redirect
[33a7d1fb280ce92716ca01dde3917d31.png]: /images/20220829/0a819c405bd041e18631498f7009bc1d.png
[c7900d3632a55810ec4523de2ef2c861.gif]: /images/20220829/e53612eba7a34d33818fc713ca004696.png