温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

当删库时如何避免跑路

发布时间:2020-02-28 06:36:28 来源:网络 阅读:515 作者:ZeroOne01 栏目:MySQL数据库

延时节点解决方案

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

另外,利用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_RunningSlave_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 tabletruncate 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后,可以看到成功恢复了商品表中被删除的数据:
当删库时如何避免跑路

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI