mysql主库delete一个没主键的表导致从库延迟很久问题处理

一 问题描述

发现线上环境一个从库出现延迟,延迟了2天了,还没追上主库。

查看当前运行的sql及事务,发现这个sql语句是在delete一个没主键的表。

二 问题模拟

这里在测试环境复现下这个问题。

2.1 在主库造数据

use baidd;

CREATE TABLE test2(id INT  primary key  AUTO_INCREMENT);

INSERT INTO test2(id) VALUES(1),(2),(3),(4);

INSERT INTO test2(id) SELECT id+(SELECT COUNT(*) FROM test2) FROM test2;

INSERT INTO test2(id) SELECT id+(SELECT COUNT(*) FROM test2) FROM test2;

……

多次运行如上sql。

2.2 在主库删除这个表的主键

alter table test2 modify id int;

alter table test2 drop primary key;

2.3 在主库清空这个表数据

#待这两百万条数据同步到从库后,在主库上删除这两百万条数据

delete from test2;

三 问题排查

3.1 在从库观察主从复制

#发现五分钟了从库还没同步删除掉,查看主从状态,发现复制位点很久都不变

root@localhost [(none)]>show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for source to send event

                  Master_Host: 10.106.210.206

                  Master_User: repl

                  Master_Port: 14728

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000025

          Read_Master_Log_Pos: 84258028

               Relay_Log_File: host01-relay-bin.000004

                Relay_Log_Pos: 63196660

        Relay_Master_Log_File: mysql-bin.000025

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 63196524

              Relay_Log_Space: 84258422

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 96

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 210206

                  Master_UUID: 408abbb7-dc1b-11ee-a91f-fefcfee1f844

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set: 408abbb7-dc1b-11ee-a91f-fefcfee1f844:1-1120,

b5896746-dc1a-11ee-b573-fefcfee443b5:1-1391

                Auto_Position: 0

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

       Master_public_key_path:

        Get_master_public_key: 0

            Network_Namespace:

1 row in set, 1 warning (0.00 sec)

ERROR:

No query specified

注意这里:

 Relay_Log_File: host01-relay-bin.000004

 Relay_Log_Pos: 63196660

3.2 查看当前运行的sql

#查看当前运行的事务

select * from information_schema.INNODB_TRX;

发现trx_query为空,以前线上从库出现延迟,能看到当前运行的sql的,不知为何这次看不到。

#查看该事务对应的进程

select * from information_schema.PROCESSLIST where id in(select trx_mysql_thread_id from information_schema.INNODB_TRX);

Info也是为空。

select * from `performance_schema`.threads where processlist_id =

(

select id from information_schema.PROCESSLIST where id in(select trx_mysql_thread_id from information_schema.INNODB_TRX)

);

3.3 查看binlog里对应的sql

用3.2命令查不到导致延迟的sql内容,只能去binlog里查看了。

查看从库中继日志里延迟时卡住的位置对应的sql。

上面3.1那里show slave status:

Relay_Log_File: host01-relay-bin.000004

Relay_Log_Pos: 63196660

mysqlbinlog -v host01-relay-bin.000004 > 000004.log

less 000004.log

搜索63196660

可以看到这个位置,在操作baidd.t2这个表:

#240427 17:47:17 server id 210206  end_log_pos 63196737 CRC32 0x71c38d61        Table_map: `baidd`.`test2` mapped to number 107

再一直往下翻,看到有很多对应的delete语句:

3.4 在从库查看捕获的慢sql有没有主键

show indexes from baidd.test2;

如果没有主键,就需要先在从库上为这个表建下主键,先解决这个延迟的问题。

四 着手处理

思路:

  • 先在从库杀掉这个慢的delete的sql
  • 在从库加主键,然后在从库delete就很快了
  • 在主库加主键,从库报主键冲突时,跳过这个错误,继续同步即可

4.1 在从库停掉主从复制

stop slave;

发现2分钟都stop不掉,因为当前有慢事务在运行。

4.2 在从库杀掉这个慢事务

#查看慢事务对应的进程id,注意别多杀了,只杀运行时间很久的,导致延迟的sql

select * from information_schema.PROCESSLIST where id in(select trx_mysql_thread_id from information_schema.INNODB_TRX);

这里是kill 55588

Kill完后,stop slave就能很快执行完了。

4.3 从库上给这个表加主键

set sql_log_bin=0;

SET  GLOBAL super_read_only=off;

show variables like '%super_read_only%';

alter table test2 add primary key(id);

SET  GLOBAL super_read_only=on;

4.4 开启同步

start slave;

set sql_log_bin=1;

发现很快就同步完了。

4.5 在主库上建下主键,避免以后再出现这个问题

alter table test2 add primary key(id);

4.6 在从库跳过这个建主键的事务

因为上面已经在从库建过主键了,所以从库复制会停止,提示主键冲突,可以通过跳过这个事务来处理。

4.6.1 查看从库复制状态

root@localhost [(none)]>show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for source to send event

                  Master_Host: 10.106.210.206

                  Master_User: repl

                  Master_Port: 14728

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000025

          Read_Master_Log_Pos: 84259101

               Relay_Log_File: host01-relay-bin.000005

                Relay_Log_Pos: 1195

        Relay_Master_Log_File: mysql-bin.000025

             Slave_IO_Running: Yes

            Slave_SQL_Running: No

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 1068

                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000025, end_log_pos 84259101. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 84258897

              Relay_Log_Space: 84259617

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 1068

               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000025, end_log_pos 84259101. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 210206

                  Master_UUID: 408abbb7-dc1b-11ee-a91f-fefcfee1f844

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State:

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp: 240427 18:04:40

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set: 408abbb7-dc1b-11ee-a91f-fefcfee1f844:1-1120,

b5896746-dc1a-11ee-b573-fefcfee443b5:1-1391

                Auto_Position: 0

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

       Master_public_key_path:

        Get_master_public_key: 0

            Network_Namespace:

1 row in set, 1 warning (0.00 sec)

注意看这几个:

#查看具体报错

select * from performance_schema.replication_applier_status_by_worker

LAST_ERROR_MESSAGE显示报错:

Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000025, end_log_pos 84259101; Error 'Multiple primary key defined' on query. Default database: 'baidd'. Query: 'alter table test2 add primary key(id)'

4.6.2 在从库跳过这个事务

4.6.2.1 针对未开启GTID的从库

show variables like '%gtid_mode%'; #off表示未开启gtid

只需要在从库执行以下命令就能跳过一个事务。

set sql_log_bin=0;

set global sql_slave_skip_counter=1;

4.6.2.2 针对开启了GTID的从库

思路:

将gtid_next设置为自己环境Executed_Gtid_Set的下一个位置,然后开始一个空事务。

示例:

set sql_log_bin=0;

set gtid_next='408abbb7-dc1b-11ee-a91f-fefcfee1f844:1121';

begin;

commit;

set gtid_next='automatic';

4.6.3 开启同步

start slave;

set sql_log_bin=1;

4.6.4 验证主从同步状态

show slave status \G;

确保复制没问题。

五 教训

表没有主键的时候,delete很多数据,会导致从库出现很长时间延迟,因此需要严格把控,定期检查,确保主库上不存在没有主键的表。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/586975.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

arthas watch怎么监控指定参数值?

watch com.codex.terry.controller.HelloWorldController getUserInJson2 "{params,returnObj}" params[0] "world" -v 要分析的代码: 浏览器请求http://localhost:8080/say/tt,arthas控制台打印的信息如下: 浏览器请…

LeetCode45:跳跃游戏Ⅱ

题目描述 给定一个长度为 n 的 0 索引整数数组 nums。初始位置为 nums[0]。 每个元素 nums[i] 表示从索引 i 向前跳转的最大长度。换句话说&#xff0c;如果你在 nums[i] 处&#xff0c;你可以跳转到任意 nums[i j] 处: 0 < j < nums[i] i j < n 返回到达 nums[n …

C++成员初始化列表

我们在类的构造函数中使用成员初始化列表可以带来效率上的提升&#xff0c;那么成员初始化列表在编译后会发生什么就是这篇文章要探究的问题 文章目录 引入成员初始化列表用成员初始化列表优化上面的代码成员初始化列表展开成员初始化列表的潜在危险 参考资料 引入 考虑下面这…

TDengine写入2000万数据查询体验

最近在寻找时序数据库&#xff0c;想应用在公司的项目上。 上一篇文章实验了InfluxDB:windows上使用influx2.7学习,还学习了flux语言&#xff0c;最后发现宽表查询比较困难&#xff0c;就放弃了&#xff0c;于是决定试试国产时序数据库TDengine 参考 官方文档&#xff1a;htt…

有货源和分销单品爆款玩法课

该课程专注于教授如何利用有货源和分销渠道&#xff0c;打造单品爆款销售策略。学员将学习货源获取、产品定位、市场推广等关键技巧&#xff0c;通过实战案例和实操训练&#xff0c;掌握成功销售单品爆款的方法&#xff0c;提升销售业绩和市场竞争力。 课程大小&#xff1a;6.…

RabbitMq基础概念知识复习

消息拥有消息头和消息体&#xff0c;消息具有rounting key&#xff0c;主题交换机和扇形交换机都是发布与订阅的实现方式&#xff0c;主题交换机用于匹配接收的消息的rount key 动态匹配模式匹配到多个符合的队列&#xff0c;扇形fanout交换机则不会使用消息的路由key&#xff…

【AIGC调研系列】InternVL开源多模态模型与GPT-4V的性能对比

InternVL和GPT-4V都是多模态模型&#xff0c;但它们在性能、参数量以及应用领域上有所不同。 InternVL是一个开源的多模态模型&#xff0c;其参数量为60亿&#xff0c;覆盖了图像/视频分类、检索等关键任务&#xff0c;并在32个视觉-语言基准测试中展现了卓越性能[2]。InternV…

CYP76AKs的功能分化塑造了鼠尾草属中松香烷型二萜化合物的化学多样性-比较转录组-文献精读12

Functional divergence of CYP76AKs shapes the chemodiversity of abietane-type diterpenoids in genus Salvia "CYP76AKs的功能分化塑造了鼠尾草属中松香烷型二萜化合物的化学多样性"&#xff0c;一片比较转录组的文献&#xff0c;类似比较转录组分析揭示了116种…

数据仓库Data Warehouse

数据仓库Data Warehouse 数仓是一种思想,数仓是一种规范,数仓是一种解决方案 1. 数据处理方式 数据处理大致可以分成两大类: 联机事务处理OLTP(on-line transaction processing)联机分析处理OLAP(On-Line Analytical Processing)1.1. OLTP OLTP的全称是On-line Transa…

O2OA开发平台前端源码级二次开发(Vue3,React)

在使用O2OA进行项目定制化开发时&#xff0c;我们可以开发新的前端组件&#xff08;x_component&#xff09;以扩展O2OA来实现更多的业务。这种新增前端组件或者前端业务的开发通常会配合后端自定义应用实现的服务来完成系统内数据的交互。在当系统默认的界面不符合系统UI/UE设…

283. 移动零 11. 盛最多水的容器

283. 移动零 把非零的数全交换到前面去 11. 盛最多水的容器 双指针&#xff1a; 双指针一个在头 一个在尾部 计算两个边的盛水量并更新数值 左右两边 哪边矮就移动哪边

区块链快速参考(三)

原文&#xff1a;zh.annas-archive.org/md5/b5e57485b0609afbfba46ff759c5d264 译者&#xff1a;飞龙 协议&#xff1a;CC BY-NC-SA 4.0 第十七章&#xff1a;去中心化应用程序 去中心化应用&#xff08;DApps&#xff09;是在去中心化网络上运行的应用程序&#xff0c;不受集…

2024五一杯数学建模B题思路分析 - 未来新城背景下的交通需求规划与可达率问题

文章目录 1 赛题选题分析 2 解题思路详细的思路过程放在文档中 ! ! &#xff01;&#xff01;&#xff01;&#xff01;&#xff01;3 最新思路更新 1 赛题 B题 未来新城背景下的交通需求规划与可达率问题 随着城市化的持续发展&#xff0c;交通规划在新兴城市建设中显得尤为关…

Linux搭建靶场

提前准备&#xff1a; 文章中所使用到的Linux系统&#xff1a;Ubantu20.4sqlilabs靶场下载地址&#xff1a;GitHub - Audi-1/sqli-labs: SQLI labs to test error based, Blind boolean based, Time based. 一. 安装phpstudy phpstudy安装命令&#xff1a;wget -O install.sh h…

Map和Set基础

目录 一、导论 二、Map 三、Set 本文找先不涉及两种数据结构的底层&#xff0c;目标是&#xff1a; 理解Map和Set的大体框架&#xff0c;了解他们有什么用&#xff0c;用在哪里的&#xff0c;然后再从浅层深入底层。 小编认为&#xff1a; 先了解也下Map和Set大体是用来做…

Mysql--创建数据库

一、创建一个数据库 “db_classes” mysql> create database db_classes; mysql> show databases; -------------------- | Database | -------------------- | db_classes | | information_schema | | mysql | | performance_schema | |…

开通Jetbrains个人账号,赠送这些付费插件

开通Jetbrains个人账号&#xff0c;或者Jetbrains现成账号的, 可赠送以下付费插件 现成账号&#xff1a;https://web.52shizhan.cn/activity/xqt8ly 个人账号&#xff1a;https://web.52shizhan.cn/legal 账号支持全家桶系列&#xff1a;AppCode,CLion,DataGrip,GoLand,Intell…

Codeforces Round 941 (Div. 2) (A~D)

1966A - Card Exchange 题意&#xff1a; 思路&#xff1a;手玩一下发现当存在某个数字个数超过k个&#xff0c;那么就能一直操作下去。那么答案就是k-1. void solve() {cin >> n >> m;map<int,int>mp;int maxx 1;for(int i 0 ; i < n ; i ){int x;c…

手把手教数据结构与算法:优先级队列(银行排队问题)

队列 基本概念 队列的定义 队列&#xff08;Queue&#xff09;&#xff1a;队列是一种常见的数据结构&#xff0c;遵循先进先出&#xff08;First-In-First-Out, FIFO&#xff09;的原则。在队列中&#xff0c;元素按照进入队列的顺序排列。队列是一个线性的数据结构&#x…

深入解析yolov5,为什么算法都是基于yolov5做改进的?(一)

YOLOv5简介 YOLOv5是一种单阶段目标检测算法&#xff0c;它在YOLOv4的基础上引入了多项改进&#xff0c;显著提升了检测的速度和精度。YOLOv5的设计哲学是简洁高效&#xff0c;它有四个版本&#xff1a;YOLOv5s、YOLOv5m、YOLOv5l、YOLOv5x&#xff0c;分别对应不同的模型大小…