当删库时如何避免跑路

延时节点解决方案

删库跑路也是个老梗了，可见在运维数据库的过程中误删除数据，或者开发的代码有bug，造成数据的误删除屡见不鲜。不过现在也有许多用于恢复或预防误删除的方案，例如SQL管理系统，将要执行的SQL先交由管理员审核，然后由管理员备份一个镜像数据库，在镜像上执行该SQL，并在执行后还原镜像。这样经过层层把关就可以大大减小出现误操作的几率。

成都创新互联公司是专业的舒城网站建设公司，舒城接单;提供网站制作、成都做网站,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行舒城网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!

另外，利用binlog日志也可以恢复误操作的数据，所以线上运行的数据库都会开启binlog日志功能。还有就是本小节要介绍的延时节点：在Replication集群中，可以设置一个延时节点，该节点的数据同步时间要慢于集群中的其他节点，当其他节点出现误操作后，若延时节点的数据还没有被影响就可以从延时节点进行恢复。

但如果现有的数据库组建的都是PXC集群，没有Replication集群可以采用该方案吗？也是可以的，PXC集群与Replication集群并非是互斥的，我们可以将PXC集群中的某个节点设置为Master，然后增加一个延时节点设置为Slave，让这两个节点构成Replication集群进行数据同步即可。如下所示：
当删库时如何避免跑路

本小节就简单演示一下如何搭建这种异构集群下的延时节点，我这里已经事先准备好了一个PXC集群和一个用作延时节点的数据库：
当删库时如何避免跑路

这里使用PXC集群中的PXC-Node3作为Master，让其与DelayNode组成主从，而DelayNode自然就是作为延时节点了。

关于PXC集群和Replication集群的搭建可以参考如下文章，这里由于篇幅有限就不进行说明了：

在CentOS8下搭建PXC集群
搭建高可用的Replication集群归档大量的冷数据

为PXC节点配置延时节点

接下来开始动手实践，首先需要将这两个节点上的MySQL服务都给停掉：

systemctl stop mysqld

主从节点的配置文件都要开启GTID，否则无法利用延时节点找回数据。主节点需要增加的配置如下：

[root@PXC-Node3 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
...

# 设置节点的id
server_id=3
# 开启binlog
log_bin=mysql_bin
# 开启GTID
gtid_mode=ON
enforce_gtid_consistency=1

从节点需要增加的配置如下：

[root@delay-node ~]# vim /etc/my.cnf
[mysqld]
...

server_id=102
log_bin=mysql_bin
# 从节点需要开启relay_log
relay_log=relay_bin
gtid_mode=ON
enforce_gtid_consistency=1

完成配置文件的配置后，启动这两个节点：

systemctl start mysqld

接着配置Slave对Master的主从关系，进入Master的MySQL命令行终端，通过如下语句查询Master当前正在使用的二进制日志及当前执行二进制日志位置：

mysql> flush logs;  -- 刷新日志
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| PXC-Node3-bin.000003 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

记录下以上执行结果后，进入Slave的MySQL命令行终端，分别执行如下语句：

mysql> stop slave;  -- 停止主从同步
mysql> change master to master_log_file='PXC-Node3-bin.000003', master_log_pos=154, master_host='192.168.190.134', master_port=3306, master_user='admin', master_password='Abc_123456';  -- 配置Master节点的连接信息，以及从Master二进制日志的哪个位置开始复制
mysql> start slave;  -- 启动主从同步

Tips：通常配置主从同步会单独创建一个用于同步账户，这里为了简单起见就直接使用了现有的账户。另外，如果不想设置主库的binlog偏移量，则使用master_auto_position=1参数即可

配置完主从关系后，使用show slave status\G;语句查看主从同步状态，Slave_IO_Running和Slave_SQL_Running的值均为Yes才能表示主从同步状态是正常的：
当删库时如何避免跑路

主从关系配置完成后，接着测试一下主从的数据同步是否正常。在Master上执行一些SQL语句，如下：

mysql> create database test_db;
mysql> use test_db;
mysql> CREATE TABLE `student` (
          `id` int(11) NOT NULL,
          `name` varchar(20) NOT NULL,
          PRIMARY KEY (`id`)
       ); 
mysql> INSERT INTO `test_db`.`student`(`id`, `name`) VALUES (1, 'Jack');

执行完成后，看看Slave上是否有正常同步：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test_db;
mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | Jack |
+----+------+
1 row in set (0.00 sec)

mysql>

验证了主从节点能正常同步数据后，我们就可以设置Slave节点的同步延时了。在Slave节点上分别执行如下语句：

mysql> stop slave;
mysql> change master to master_delay=1200;  -- 设置同步延时为1200秒
mysql> start slave;

同样，重新配置了主从关系后，需要确认主从同步状态是正常的：
当删库时如何避免跑路

模拟误删除数据

接着演示下延时节点的作用，首先到Master节点上，将student表中的数据给删除掉，模拟误删除的情况：

mysql> use test_db;
mysql> delete from student;  -- 删除student表中的所有数据
mysql> select * from student;  -- Master上已经查询不到数据了
Empty set (0.00 sec)

mysql>

此时，因为延时同步的原因，在Slave节点上依旧能够正常查询到被删除的数据：

mysql> use test_db;
mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | Jack |
+----+------+
1 row in set (0.00 sec)

mysql>

现在就轮到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节点上分别执行如下语句：

mysql> stop slave;  -- 停止主从同步
mysql> set gtid_next='d36eaafb-c653-ee15-4458-5d6bc793bd7a:4';  -- 设置需要跳过的GTID
mysql> begin; commit;  -- 开启并提交事务，即模拟Slave同步了该GTID，后续就不会再进行同步，从而达到了跳过的效果
mysql> set gtid_next='automatic';  -- 恢复gtid的设置
mysql> change master to master_delay=0;  -- 设置同步延时为0是为了马上进行同步跳过该GTID
mysql> start slave;

完成以上操作后，此时Slave上依旧存在着误删除的数据：
当删库时如何避免跑路

而Master上的student表依旧为空：
当删库时如何避免跑路

完成以上的操作后，恢复同步延时的设置：

mysql> stop slave;
mysql> change master to master_delay=1200;  -- 设置同步延时为1200秒
mysql> start slave;

恢复Master节点误删除的数据

让Slave节点跳过误删除操作的GTID后，就可以开始恢复Master节点的数据了。首先停止业务系统对Master节点所在的PXC集群的读写操作，避免还原的过程中造成数据混乱。然后导出Slave节点的数据：
当删库时如何避免跑路

在Master节点上创建临时库，这是为了先在临时库验证了数据的正确性之后再导入到业务库中，避免出现意外：

create database temp_db;

然后导入数据：
当删库时如何避免跑路

把Master节点上的数据表重命名：

rename table test_db.student to test_db.student_bak;

把临时库的数据表迁移到业务库中：

rename table temp_db.student to test_db.student;

此时就成功恢复了Master节点上误删除的数据：
当删库时如何避免跑路

日志闪回方案

之前也提到了除延时节点这种解决方案外，使用binlog日志也是可以实现数据恢复的，这种恢复数据的方式通常被称为日志闪回。这里之所以还要介绍这种方案，是因为延时节点方案存在着一定的局限性：一旦在延时阶段没有发现问题并解决问题的话，那么当主从数据同步后，也无法利用从节点去实现误删除的恢复。

日志闪回方案相对于延时节点方案来说要简单一些，不需要增加额外的节点，利用当前节点就可以恢复数据。但该方案也并非全能，例如binlog日志不会记录drop table、truncate table等操作所删除的数据，那么也就无法通过日志恢复了。不过这两种方案并不冲突，可以同时使用以提高数据恢复的可能性。

日志闪回的前提是要开启binlog日志，然后通过一些闪回工具将binlog日志解析成SQL，接着将SQL中的delete语句转换成insert语句，或者找到被误删除的数据的insert语句。最后将这些insert语句重新在数据库中执行一遍，这样就实现了数据的恢复：
当删库时如何避免跑路

闪回工具有很多，本文中采用的是binlog2sql，它是大众点评开源的基于Python编写的MySQL日志闪回工具。

安装binlog2sql

该工具的安装步骤如下：

# 安装前置工具
[root@PXC-Node3 ~]# yum install -y epel-release
[root@PXC-Node3 ~]# yum install -y git python3-pip

# 克隆binlog2sql的源码库，并进入源码目录
[root@PXC-Node3 ~]# git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

# 安装binlog2sql所依赖的Python库
[root@PXC-Node3 ~/binlog2sql]# pip3 install -r requirements.txt

在MySQL配置文件中配置如下参数，因为binlog2sql是基于row格式的binlog进行解析的：

[mysqld]
...

binlog_format = row
binlog_row_image = full

模拟误删除数据

我这里有一张商品表，该表中有如下数据：
当删库时如何避免跑路

使用delete语句删除该表中的数据来模拟误删除：

delete from flash.goods;

然后再插入一些数据，模拟误删除后新增的数据：

INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (6, '苹果', 'xxxx', '1');
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (7, '香蕉', 'xxxx', '1');

通过binlog2sql恢复数据

恢复前的准备工作：

停止应用对数据库的读写操作，避免还原后覆盖新写入的数据
热备份数据库，以保证还原工作万无一失，关于备份相关内容可以参考：关于数据库的各种备份与还原姿势详解
清空需要恢复数据的数据表的全部记录，避免主键、唯一键约束的冲突

因为要恢复的是商品表，所以清空商品表的全部记录：

delete from flash.goods;

之前也提到了最近的操作一般记录在序号最大的binlog文件中，所以得查询数据库中的binlog文件名：
当删库时如何避免跑路

然后使用binlog2sql解析指定的binlog日志，具体命令如下：

[root@PXC-Node3 ~/binlog2sql]# python3 binlog2sql/binlog2sql.py -uadmin -p'Abc_123456' -dflash -tgoods --start-file='PXC-Node3-bin.000003' > /home/PXC-Node3-bin.000003.sql

binlog2sql/binlog2sql.py：被执行的Python文件
-u：用于连接数据库的账户
-p：数据库账户的密码
-d：指定逻辑库的名称
-t：指定数据表的名称
--start-file：指定需要解析的binlog的文件名
/home/PXC-Node3-bin.000003.sql：指定将解析生成的SQL写到哪个文件

接着查看解析出来的SQL内容：cat /home/PXC-Node3-bin.000003.sql。这里截取了有用的部分，如下图，可以看到delete语句和insert语句都有我们要恢复的数据：
当删库时如何避免跑路

能得到这些语句接下来就简单了，要么将delete语句转换成insert语句，要么直接复制insert部分的SQL语句到数据库上执行即可。我这里就直接复制insert语句了：

INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (1, '蛋糕', '好吃', '1'); #start 3170 end 3363 time 2020-01-27 18:00:11
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (2, '柠檬茶', '爽过吸大麻', '1'); #start 3459 end 3664 time 2020-01-27 18:00:56
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (3, '豆奶', '好喝', '0'); #start 3760 end 3953 time 2020-01-27 18:01:10
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (4, '窝窝头', '一块钱四个', '1'); #start 4049 end 4254 time 2020-01-27 18:01:37
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (5, '鸡腿', '鸡你太美', '0'); #start 4350 end 4549 time 2020-01-27 18:02:08
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (6, '苹果', 'xxxx', '1'); #start 5052 end 5243 time 2020-01-27 18:06:24
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (7, '香蕉', 'xxxx', '1'); #start 5339 end 5530 time 2020-01-27 18:06:24

执行完以上SQL后，可以看到成功恢复了商品表中被删除的数据：
当删库时如何避免跑路

分享题目：当删库时如何避免跑路
分享网址：http://chengdu.cdxwcx.cn/article/ijeipd.html

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

首页

网站建设

网站制作案例

解决方案

网站设计报价

网站制作动态

关于我们

联系我们

成都网站建设设计将想法与焦点和您一起共享

当删库时如何避免跑路

延时节点解决方案

为PXC节点配置延时节点

模拟误删除数据

恢复Master节点误删除的数据

日志闪回方案

安装binlog2sql

模拟误删除数据

通过binlog2sql恢复数据

其他资讯

mysql反单引号怎么打,mysql的反引号怎么打出来

android服务器,安卓推送服务

JavaScript循环,javascript循环图片

mysql怎么安装在c盘,mysql数据库怎么安装

c语言字符串函数替换,c语言替换字符串

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

成都网站建设设计 将想法与焦点和您一起共享

当删库时如何避免跑路

延时节点解决方案

为PXC节点配置延时节点

模拟误删除数据

恢复Master节点误删除的数据

日志闪回方案

安装binlog2sql

模拟误删除数据

通过binlog2sql恢复数据

其他资讯

mysql反单引号怎么打,mysql的反引号怎么打出来

android服务器,安卓推送服务

JavaScript循环,javascript循环图片

mysql怎么安装在c盘,mysql数据库怎么安装

c语言字符串函数替换,c语言 替换字符串

成都网站建设设计将想法与焦点和您一起共享

c语言字符串函数替换,c语言替换字符串