图片来自Pexels01事件。我们系统的起因是有数据导入功能，可以将特定格式的excel数据导入系统。由于客户电脑上的文件较多，而且很多文件的名称相似，导致客户在导入excel时选错了文件。这个错误的excel文件的格式可以被系统解析出来，客户没有及时发现导入错误的文件，结果导入了6万多条无用的数据！这六万条数据是无用数据，不会影响系统的运行，最多只是占用一点数据库空间。客户只需重新导入正确的excel，即可继续完成业务。然而，客户是一名重度强迫症患者，看到管理平台上这六万多条无用的数据，他觉得快把他逼疯了。客户想删除这些数据，而我们的系统没有提供批量删除功能，只能逐个删除，这无疑是一个巨大的工作量。客户通过客服找到了研发团队，希望直接从数据库中删除我们的研发人员。02删除数据库虽然在生产环境直接操作数据库显然是违法的，但是客户的要求又得满足，谁让谁当爹了？由于生产环境中的数据和表结构属于商业机密，所以我们讨论的重点不是数据和表结构，而是数据恢复的思路。于是我在测试环境新建了一张用户表，导入了一些测试数据，作为生产环境运行。研发人员登录生产数据库，执行如下sql，发现错误数据6万多条：select*fromt_userwhereage>18anddeptid=100;在确认这6万多条数据确实是错误导入的数据后，准备删除。由于表中没有逻辑删除字段，只能进行物理删除。需要删除的数据已经确定。通常把sql中的select*换成delete来执行，出错的概率会小一些。但是研发人员并没有改变原来的sql，而是重新写了一条删除语句并执行：deletefromt_userwhereage>18;问题是这样出现的，新写的delete语句中缺少deptid=100条件。不要问我为什么删除前没有备份，这是血泪的教训。再次查表，发现误删了10万多条数据。在生产环境中，很多业务都依赖这张表，它是系统的核心表。虽然只删除了10万条数据，但系统的很多功能都无法正常使用。其实和删除数据库没什么区别！研发人员发现数据库被删除后，第一时间向领导汇报（并没有马上跑掉）。领导当机立断，要求系统停止运行，向所有客户发出暂停服务通知，开通所有客服渠道，处理客户投诉，解答疑问。03数据找回我们找到删除数据库的开发人员，问他有没有备份，他的回答是没有。我们去咨询运维的同事，看看生产环境有没有开启数据库定时自动备份，运维的回答也是没有。事情比较难办，只能寄希望于mysql的binlog。binlog二进制日志文件，对数据库的写入操作，如insert、delete、update、create、alter、drop等，都会被binlog记录下来（后面会详细介绍binlog）。需要开启binlog日志记录的配置。希望生产环境的mysql数据库开启binlog日志记录。否则只能找专门做磁盘数据恢复的第三方公司了。登录生产环境数据库查看binlog是否开启：SHOWVARIABLELIKE'LOG_BIN%';从图中我们可以看到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日志文件：binlog日志文件是滚动生成的，现在有4个文件如图所示。通常，生产环境中有成百上千条binlog。这个时候我们需要确认一下我们需要的数据在哪个binlog中，下面会讲解如何确定我们需要的是哪个binlog。因为我们刚刚删除了数据库，所以我们需要的数据极有可能在第四个文件中。直接去查看第四个binlog文件，看到的都是乱码，如下图，因为binlog文件是二进制的。我们需要使用mysql提供的mysqlbinlog命令来正确解析binlog文件。binlog文件可以用mysqlbinlog命令打开，但是一个binlog文件的大小可能有几百兆，要从几百兆的日志中找到我们需要的日志还是挺麻烦的。幸运的是，mysqlbinlog命令提供了一些参数选项，可以让我们过滤binlog文件。最常用的参数是时间参数。（mysqlbinlog的详细使用方法下文也会说明）经与删除数据库的研发人员确认，删除数据库的时间是10点40分左右。那么我们就以这个时间点为参考，查找前后5分钟的日志：mysqlbinlog-v--start-datetime='2021-06-1010:35:00'--stop-datetime='2021-06-1010:45:00'bin-log.000004|grept_user从图中可以看出，这个时间点日志确实包含了我们删除数据的日志。接下来，我们需要对这些日志进行整理，然后想办法将它们恢复到数据库中。首先将我们需要的日志单独保存在tmp.log文件中，方便下载到本地：mysqlbinlog-v--start-datetime='2021-06-1010:35:00'--stop-datetime='2021-06-1010:45:00'bin-log.000004>tmp.log下载tmp.log到本地，用文本编辑工具打开，可以看到一堆伪sql：在上图中的伪sql中:@1表示第一个字段@2表示第二个字段，依此类推。日志中包含的sql是一些伪sql，不能直接在数据库中执行。我们需要想办法将这些伪sql处理成数据库中可执行的真实sql。我们使用的文本编辑工具的批量替换功能是这样的：最终处理后的sql是这样的：在验证测试库没有问题后，直接在生产库执行处理后的sql。sql执行后，误删除的数据会恢复。加上数据库删除的研发，我们删除了客户要求删除的6万多条数据，也算是完成了客户的要求。至此，删除数据库事件暂时告一段落。不要问研发删库受到什么处罚，问了就没有处罚。04建议删库跑路真的不是开玩笑的。如果真的不小心删除了数据库，无法找回数据，那不仅是简单的罚款和业绩扣分，甚至可能面临牢狱之灾。对于一个公司来说，一个粗心的数据库删除操作可能会删除这个公司。毕竟不是所有的公司都能承受数据库删除造成的数据丢失和经济损失。因此，生产环境中的数据安全必须是重中之重。根据我多年删除数据库的经验，我也总结了一些经验分享给大家，希望对大家有所帮助。①研发人员无法直接连接生产数据库。生产数据库一般由DBA或运维人员维护。研发人员很少需要登录生产数据库查看数据。即使数据出现问题，DBA或运维人员通常也能解决。如果一个系统需要研发人员经常登录数据库维护数据，那么就该考虑给系统增加一个管理功能，而不是频繁登录数据库。所以R&D不应该有生产库的登录权限。如果偶尔需要登录生产数据库查看数据，可以要求DBA开一个临时账号。②登录生产数据库，使用只读账户。大多数人在使用数据库时都会使用连接工具，比如Navicat、SQLyog等，每个人的电脑上很有可能只有一个连接工具。开发库、测试库、生产库都在同一个连接工具中打开。有时候只想修改开发库中的一条数据，却不小心修改了生产库。MySQL事务自动提交。在连接工具中，当前被修改的行丢失游标后会自动提交事务，极易出错。因此，如果确实需要登录生产库，请尽量使用只读权限的账号登录。③关闭autocomit和多人review如果确实需要在生产数据库中增加、修改或删除数据，最好在执行sql之前关闭事务的自动提交。当需要登录生产数据库修改数据时，问题一定比较复杂。一条sql语句应该是完成不了的，可能需要写N多条sql才能完成数据修改。这么多sql，执行的时候很有可能选错了。有时您只想执行一个select语句，却发现执行的是delete。更糟糕的是，大多数数据库连接工具都具有执行当前选定内容的功能。有时候只想执行当前选中的内容，结果发现所有内容都执行了。如果关闭自动提交，即使出现上述情况，仍有机会恢复。例如如下：--关闭事务autocommitset@@autocommit=0;--查看需要删除的数据，共65600条select*fromt_userwhereage>18anddeptid=100;--删除deletefromt_userwhereage>18;--如果有问题，返回rollselect*fromt_userwhereage>18anddeptid=100;rollback;--确认没有问题，提交--commit；另外，在commit之前，你需要至少找一位同事确认。所谓的当局者迷，有时可能会出现错误的思维方式，想当然地认为结果会没事。这时候就需要一个旁观者来引导迷宫。如果双方都确认没有问题再提交，出错的几率就会小很多。④备份、备份、修改数据前先备份，重要的事情说三遍！！！备份虽然麻烦一些，但却是保证数据准确性最有效的手段。再说了，掌握了一些技巧之后，备份也不是什么很麻烦的事情了。比如我们在删除数据之前，可以这样备份：--像原表createtablet_user_bakliket_user一样创建一个备份表（包括索引）；--复制数据到备份表INSERTintot_user_bakselect*fromt_user;--确认数据拷贝完成select*fromt_user_bak;对于备份的数据，即使不小心删除了原表数据，也不需要恢复数据，只需将备份表的名称改成原表的名称即可，直接使用即可。在修改生产数据库中的数据之前，一定要记得备份。一旦数据被错误修改，这是成本最低、最有效的恢复方式。⑤设置数据库定期备份生产环境，运维人员必须设置数据库定期备份。研发人员也有义务提醒运维同事写自动备份脚本，因为一旦生产库出现问题，就需要恢复数据。如果没有定期的备份，不仅是运维人员，连研发人员都会有麻烦。备份周期可根据业务需要确定。如果业务对数据实时性要求高，备份周期相对较短，恢复数据时可以最大程度避免数据丢失；否则，备份周期可以更长以节省磁盘空间。如有必要，您可以定期将备份文件复制到远程服务器，避免因地震、台风等不可抗力因素损坏当前服务器磁盘。五、binglog日志binlog即BinaryLog，是用来记录数据库写操作日志的二进制文件。insert、delete、update、create、alter、drop等数据库写操作都会被binlog记录下来。因此，数据库的主从数据同步通常是基于binlog来完成的。本文只对binlog做一个简单的介绍。后面会单独写一篇文章，讲一下基于binlog的主从数据同步。需要配置并开启binlog日志。可以通过脚本查看binlog是否开启：SHOWVARIABLELIKE'LOG_BIN%';如果log_bin参数显示为OFF，则表示binlog已禁用，需要手动启用。启用binlog需要修改数据库的my.cnf配置文件。my.cnf文件一般在服务器的/etc目录下。#启用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、MIXEDbinlog_format=rowbinlog日志有三种格式，分别是STATEMENT、ROW、MIXED。mysql5.7.7版本之前默认使用STATEMENT，以后版本默认使用ROW。①ROW格式在ROW格式中，binlog记录了每条数据被修改的详细信息。比如执行delete语句时，删除了多少条数据，binlog记录了多少条伪sql：deletefromt_userwhereage>18；那么行格式的日志的缺点就很明显了。当发生批量操作时，日志文件中会记录大量的条目。伪sql，占用磁盘空间较多。尤其是在进行alter操作的时候，每条数据都会发生变化，在日志文件中会有每条数据的日志。这时候如果表的数据量很大，日志文件也会很大。mysql5.6版本后，为ROW格式的日志增加了binlog_row_image参数。当binlog_row_image设置为minimal时，日志中只记录变化的列，而不是所有的列，可以在一定程度上减小binlog日志的大小。虽然记录每一行数据的变化会导致日志文件过大，但这也是它的优点。因为它记录了每一次数据修改的细节，所以在某些极端情况下不会出现数据混乱的情况。在做数据恢复或者主从同步时，可以很好的保证数据的真实性和一致性。②STATEMENT格式在STATEMENT格式中，真正的sql语句记录在日志中，像这样：日志中的sql可以直接从数据库运行。STATEMENT格式的日志的优缺点与ROW格式的正好相反。是sql语句和执行语句时的上下文，不是每条数据。因此，它的日志文件会比ROW格式的日志文件小。由于只记录sql语句和上下文环境，STATEMENT格式的日志在主从数据同步时会出现一些不可预知的情况，造成数据混乱。.比如sleep()和last_insert_id()等函数就会有问题。③MIXED格式MIXED格式是STATEMENT和ROW的组合，mysql会根据具体执行的sql语句选择合适的日志格式进行记录。在MIXED格式中，执行普通SQL语句时会选择STATEMENT记录日志，遇到复杂语句或函数操作时会选择ROW记录日志。06mysqlbinlog命令mysql数据库的binlog文件是二进制的，基本看不懂。使用数据库自??带的mysqlbinlog命令将二进制文件转换成可以理解的十进制文件。因为数据库的binlog文件可能很大，查看起来会很麻烦，所以mysqlbinlog命令也提供了一些参数来过滤日志。mysqlbinlog语法：options：可选参数log-files：文件名mysqlbinlog[options]log-files选项常用值：-d：根据数据库名过滤日志-o：跳过前N行日志-r,--result-fil：输出日志到指定文件--start-datetime：读取指定时间后的日志，时间格式：yyyy-MM-ddHH:mm:ss--stop-datetime：读取指定时间之前的日志，时间格式：yyyy-MM-ddHH:mm:ss--start-position：从指定位置开始读取日志--stop-position：读取到指定位置停止--base64-output:行格式，显示伪sql语句-v,--verbose:显示伪sql语句，-vv可以为sql语句添加备注。mysqlbinlog--start-datetime='2021-06-0919:30:00'--stop-datetime='2021-06-0919:50:00'bin-log.000001恢复数据，事件起始位置为4300，结束位置为10345：mysqlbinlog--start-position4300--stop-position10345bin-log.000001|mysql-uroot-p123456fusion作者：王小武编辑：陶家龙来源：转载自公众号HelianXiaowu
