当删库时如何避免跑路

删库跑路也是个老梗了，可见在运维数据库的过程中误删除数据，或者开发的代码有bug，造成数据的误删除屡见不鲜。不过现在也有许多用于恢复或预防误删除的方案，例如SQL管理系统，将要执行的SQL先交由管理员审核，然后由管理员备份一个镜像数据库，在镜像上执行该SQL，并在执行后还原镜像。这样经过层层把关就可以大大减小出现误操作的几率。另外，利用binlog日志也可以恢复误操作的数据，所以线上运行的数据库都会开启binlog日志功能。还有就是本小节要介绍的延时节点：在Replication集群中，可以设置一个延时节点，该节点的数据同步时间要慢于集群中的其他节点，当其他节点出现误操作后，若延时节点的数据还没有被影响就可以从延时节点进行恢复。但如果现有的数据库组建的都是PXC集群，没有Replication集群可以采用该方案吗？也是可以的，PXC集群与Replication集群并非是互斥的，我们可以将PXC集群中的某个节点设置为Master，然后增加一个延时节点设置为Slave，让这两个节点构成Replication集群进行数据同步即可。如下所示：
本小节就简单演示一下如何搭建这种异构集群下的延时节点，我这里已经事先准备好了一个PXC集群和一个用作延时节点的数据库：
这里使用PXC集群中的PXC-Node3作为Master，让其与DelayNode组成主从，而DelayNode自然就是作为延时节点了。关于PXC集群和Replication集群的搭建可以参考如下文章，这里由于篇幅有限就不进行说明了：接下来开始动手实践，首先需要将这两个节点上的MySQL服务都给停掉：主从节点的配置文件都要开启GTID，否则无法利用延时节点找回数据。主节点需要增加的配置如下：从节点需要增加的配置如下：完成配置文件的配置后，启动这两个节点：接着配置Slave对Master的主从关系，进入Master的MySQL命令行终端，通过如下语句查询Master当前正在使用的二进制日志及当前执行二进制日志位置：记录下以上执行结果后，进入Slave的MySQL命令行终端，分别执行如下语句：配置完主从关系后，使用show slave statusG;语句查看主从同步状态，Slave_IO_Running和Slave_SQL_Running的值均为Yes才能表示主从同步状态是正常的：
主从关系配置完成后，接着测试一下主从的数据同步是否正常。在Master上执行一些SQL语句，如下：执行完成后，看看Slave上是否有正常同步：验证了主从节点能正常同步数据后，我们就可以设置Slave节点的同步延时了。在Slave节点上分别执行如下语句：同样，重新配置了主从关系后，需要确认主从同步状态是正常的：
接着演示下延时节点的作用，首先到Master节点上，将student表中的数据给删除掉，模拟误删除的情况：此时，因为延时同步的原因，在Slave节点上依旧能够正常查询到被删除的数据：现在就轮到GTID上场了，我们得先让Slave节点跳过删除操作的GTID，后续才能让Master从Slave上恢复数据。否则Slave同步了该GTID的话，Slave节点上的数据也会被删除，即便在同步之前恢复了Master的数据也会造成主从数据不一致的问题。GTID是记录在binlog中的，由于误删除操作是在Master上进行的，所以首先在Master节点上使用show master logs;语句查询binlog日志名称：
接下来我们需要在binlog文件中找到误删除操作的记录及其GTID，因为binlog文件的序号是递增的，所以最近的操作一般记录在序号最大的binlog文件中。因此执行show binlog events in 'PXC-Node3-bin.000003';语句，并从结果集中查找误删除操作的记录及其GTID。如下图所示：
在Master节点上找到误删除操作的GTID后，复制该GTID。然后到Slave节点上分别执行如下语句：完成以上操作后，此时Slave上依旧存在着误删除的数据：
而Master上的student表依旧为空：
完成以上的操作后，恢复同步延时的设置：让Slave节点跳过误删除操作的GTID后，就可以开始恢复Master节点的数据了。首先停止业务系统对Master节点所在的PXC集群的读写操作，避免还原的过程中造成数据混乱。然后导出Slave节点的数据：
在Master节点上创建临时库，这是为了先在临时库验证了数据的正确性之后再导入到业务库中，避免出现意外：然后导入数据：
把Master节点上的数据表重命名：把临时库的数据表迁移到业务库中：此时就成功恢复了Master节点上误删除的数据：
之前也提到了除延时节点这种解决方案外，使用binlog日志也是可以实现数据恢复的，这种恢复数据的方式通常被称为日志闪回。这里之所以还要介绍这种方案，是因为延时节点方案存在着一定的局限性：一旦在延时阶段没有发现问题并解决问题的话，那么当主从数据同步后，也无法利用从节点去实现误删除的恢复。日志闪回方案相对于延时节点方案来说要简单一些，不需要增加额外的节点，利用当前节点就可以恢复数据。但该方案也并非全能，例如binlog日志不会记录drop table、truncate table等操作所删除的数据，那么也就无法通过日志恢复了。不过这两种方案并不冲突，可以同时使用以提高数据恢复的可能性。日志闪回的前提是要开启binlog日志，然后通过一些闪回工具将binlog日志解析成SQL，接着将SQL中的delete语句转换成insert语句，或者找到被误删除的数据的insert语句。最后将这些insert语句重新在数据库中执开发云主机域名行一遍，这样就实现了数据的恢复：
闪回工具有很多，本文中采用的是binlog2sql，它是大众点评开源的基于Python编写的MySQL日志闪回工具。该工具的安装步骤如下：在MySQL配置文件中配置如下参数，因为binlog2sql是基于row格式的binlog进行解析的：我这里有一张商品表，该表中有如下数据：
使用delete语句删除该表中的数据来模拟误删除：然后再插入一些数据，模拟误删除后新增的数据：恢复前的准备工作：因为要恢复的是商品表，所以清空商品表的全部记录：之前也提到了最近的操作一般记录在序号最大的binlog文件中，所以得查询数据库中的binlog文件名：
然后使用binlog2sql解析指定的binlog日志，具体命令如下：接着查看解析出来的SQL内容：cat /home/PXC-Node3-bin.000003.sql。这里截取了有用的部分，如下图，可以看到delete语句和insert语句都有我们要恢复的数据：
能得到这些语句接下来就简单了，要么将delete语句转换成insert语句，要么直接复制insert部分的SQL语句到数据库上执行即可。我这里就直接复制insert语句了：执行完以上SQL后，可以看到成功恢复了商品表中被删除的数据：

相关推荐: MySQL5.7应当注意的参数

简介： 本篇文章主要介绍 MySQL 初始化应当注意的参数，对于不同环境间实例迁移，这些参数同样应当注意。注： 本文介绍的参数都是在配置文件 [mysqld] 部分。这几个系统变量通常成对出现，当我们想指定log_bin 选项时，必须也要指定server_id…