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

已于 2024-04-27 21:36:21 修改
阅读量1.3k 收藏 23
点赞数 22
分类专栏： 2 MySql 学习 # MySql 报错集锦 文章标签： mysql 数据库
于 2024-04-27 19:17:20 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yabingshi_tech/article/details/138255803
版权
文章讲述了线上环境中从库因删除无主键表数据而延迟的问题，通过模拟场景、排查复制状态、调整事务处理和使用GTID策略，最终解决了延迟并吸取了教训：确保表有主键，防止此类问题再次发生。
摘要由CSDN通过智能技术生成

一 问题描述

发现线上环境一个从库出现延迟，延迟了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很多数据，会导致从库出现很长时间延迟，因此需要严格把控，定期检查，确保主库上不存在没有主键的表。

雅冰石
关注
专栏目录
mysql5.7性能提升一百倍调优宝典
lifetragedy的专栏
05-06 1万+
全文中一共有常用的（事实上你如果花1-2周阅读、理解、自己动手设一下后是需要这么多参数的）76个参数，我是按照： 每一个参数干吗？ 在某些典型硬件配置下的db上参数该设多少？ 设会怎么样？ 不设会怎么样？ 有什么坑如何填坑？ 有些参数怎么算、算法又如何 这种style来写的，具体请看文档！ 一千个DBA就有一千种配置方式! 大家一定记得不要轻易去看网上，要看只看官网！网上很多博客都...
MySQL主备同步与主从同步
Devil_B的博客
07-18 2034
MySQL主备同步与主从同步主备同步binlog三种格式对比循环复制问题主备延迟问题并行复制能力及其演变MySQL 5.5 版本的并行复制策略MySQL 5.6 版本的并行复制策略（用的不多）MariDB并行复制策略MySQL 5.7 的并行复制策略MySQL 5.7.22 的并行复制策略一主多从读写分离 主备同步 MySQL通过binlog实现主备同步，其中实现的主要原理是binlog。在主备同步中从库B会设置为readonly。在状态 1 中，客户端的读写都直接访问节点 A，而节点 B 是 A 的备库，
mysql从库同步delete不动了_MySQL主从同步报错故障处理集锦
weixin_42309083的博客
01-20 1385
前言在发生故障切换后，经常遇到的问题就是同步报错，下面是最近收集的报错信息。记录删除失败在master上删除一条记录，而slave上找不到Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1;Can't find record in 't1',Error_code: 1032; handler error HA_ER...
mysql找出没有主键的表
梦想小镇
10-20 712
mysql找出没有主键的表
MySQL无主键表查找
kunjian的博客
07-12 5321
前言： 在 MySQL 中，建表时一般都会要求有主键。若要求不规范难免会出现几张无主键的表，本篇文章让我们一起揪出那个无主键的表。 1.无主键表的危害 以 InnoDB 表为例，我们都知道，在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。一张 InnoDB 表必须有一个聚簇索引，当有主键时，会以主键作为聚簇索引；如果没有显式定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键。
MySQL记录一次因无主键大表大事务导致主备延迟的处理过程
运维那点事
05-23 499
整个处理过程关键点就是把无主键的表找出来，加上自增的主键，然后找腾讯云的客服人工干预，重建备库。最终扩容完成！
MySQL主库表没有主键导致主备延迟很大？
喝醉酒的小白
06-21 710
通常情况下，数据库表中都会有一个主键列，它可以是自增长的数字，也可以是某个实际意义上的字段，例如身份证号、邮箱等。MySQL 主备复制会在主库上对修改进行记录，并将对应的日志传输到备库进行重放，如果备库上的表没有主键，则 MySQL 复制程序会使用全表扫描方式匹配记录而不使用索引匹配记录。如果还是无法添加主键，可以考虑将数据表拆分成多个较小的表，这样可以减小单个表的数据量，提高查询和复制性能。总之，在MySQL主库表没有主键导致主备延迟很大时，我们需要根据实际情况选择最合适的解决方案。
MySQL主从不一致问题相关处理
热门推荐
企业实战系列集 ●●● https://ximenjianxue.blog.csdn.net
09-26 1万+
某业务采取mysql的主从架构，但因为存储的问题，导致备库一直无法存储，数据同步一致性问题一直也未恢复，某次安全检查要求完成主备倒换演练，必须限期恢复主备，但是在恢复过程中，同步显示正常一段时间后，便会出现sql线程异常，主备数据不一致导致的同步错误情况。主数据库上面设置的max_allowed_packet比从数据库大，当一个大的sql语句，能在主数据库上面执行完毕，从数据库上面设置过小，无法执行，导致的主从不一致。根据上面的NR行数，查看附近的行，定位数据不一致的地方，后续手动补全。
MySQL中的系统库
最新发布
m0_62943934的博客
06-03 1109
MySQL有几个系统数据库，这几个数据库包含了MySQL服务器运行过程中所需的一些信息以及一些运行状态信息，我们现在稍微了解一下。这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，算是对MySQL服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些是一些描述性信息，称之为元数据。sys。
大厂面试快问快答，10分钟搞定MySQL夺命20问，你都能接住吗？
华科硕士，大厂打工中。编程问题咨询，请私我
07-19 4732
号外号外！《死磕 Java 并发编程》系列连载中，大家可以关注一波： 「死磕 Java 并发编程05」阿里面试失败后，一气之下我图解了Java中18把锁 「死磕 Java 并发编程04」说说Java Atomic 原子类的实现原理 「死磕 Java 并发编程03」阿里二面，面试官：说说 Java CAS 原理？ 「死磕 Java 并发编程02」面试官：说说什么是 Java 内存模型（JMM）？ 「死磕 Java 并发编程01」10张图告诉你Java并发多线程那些破事 先看下目录： 1说说.
Mysql在没有主键情况下，删除重复数据
after_17的博客
05-26 2421
# 方法一 建一个临时表，并将筛选的数据导入临时表 CREATE TABLE shop_users2 AS ( SELECT DISTINCT * FROM shop_users ) 删除原表数据 DELETE FROM shop_users 将临时表数据插入原表 INSERT INTO shop_users SELECT * FROM shop_users2 ...
mysql没有主键导致延迟_Mysql slave 延迟故障一列(无主键)
weixin_34261759的博客
02-02 503
首先还是给出我见过的一些延迟可能：大事物延迟 延迟略为2*执行时间 状态为:reading event from the relay log大表DDL延迟 延迟略为1*执行时间 状态为:altering table长期未提交的事物延迟，会造成延迟的瞬时增加表上没有主键或者唯一键 状态为:system lock 或者 reading event from the relay log...
关于mysql的delete和主从同步问题
熊诗言的博客
08-09 2811
mysql的delete并不会真正删除磁盘空间，而只是标记相应的区域，在合适的时候还可以再利用。如果要真正腾出磁盘空间，还必须使用optimize table xxx进行磁盘碎片处理，但是这个命令会在相应的库下产生一个很大的#sql-xxx文件（增长速度特别快，根据清理的表的大小），所以不能等磁盘已经快满了才想起来清理，正所谓天晴修屋顶。optimize命令会锁表，一般根据table的增长速度和删...
mysql 处理大数据太慢_mysql的innodb引擎数据库批量删除大量数据很慢
weixin_39623350的博客
01-12 887
因为不能用Truncate(因为只是删除其中少部分数据)，由于要求此表中的数据只保留当天的数据，所以每当在凌晨的某一时刻当其他程序处理完其中的数据后要删除该表中昨天以及以前的数据，使用delete删除表中的上百万条记录时，MySQL删除速度非常缓慢每一万条记录需要大概4分钟左右，这样删除所有无用数据要达到八个小时以上，这是难以接受的。。网上搜到此文(http://blog.csdn.net/gao...
揭秘微小MySQL表DELETE操作的隐形杀手：事务超时大揭秘
赵KK的博客
03-20 1759
通过对MySQL事务超时的深入分析，我们了解到即使是对小表执行DELETE操作，也可能会因为多种原因导致事务超时。为了避免这种情况，我们需要合理设计事务，优化SQL查询，调整超时设置，并且在高并发环境下合理分配资源。你的每一次互动都是对我最大的支持。让我们一起在数据库优化的道路上不断探索和成长！🚀。
mysql主主不同步问题_Mysql主从（主从不同步解决办法，常见问题及解决办法，在线对mysql做主从复制）...
weixin_34438187的博客
01-19 579
一、主从不同步解决办法先上Master库：mysql>show processlist; 查看下进程是否Sleep太多。发现很正常。show master status; 也正常。mysql> show master status;+-------------------+----------+--------------+-------------------------------...
mysql的三种删除的区别
weixin_42608579的博客
11-16 1491
Delete 1、delete属于DML操作语言，只会删除数据不会删除表结构。会走事务。 2、在InnoDB中，delete实际不会真正将数据删除，只是逻辑删除，占用空间也不会释放，只是将数据设置为不可见。但下次插入数据的时候这部分空间可用，也就是重用->覆盖；而MyISAM则会立刻释放空间 3、delete执行时，会先将数据缓存到rollback segement中，事务commit后生效 4、 ...
Innodb 没有主键的表插入性能问题
gao1738的专栏
01-18 2041
问题： 这几天发现客户那里的mysql集群有时插入特别的慢，通过慢查询日志以及pt-query-digest得知主要是对一张 表的插入特别的慢。 这张表的数据量大概有3百多万行，插入操作特别的频繁。 通过开启innodb monitor得知， 并行的插入操作都阻塞在一个地方： --Thread 1284204864 has waited at dict0boot.ic line 45 for 3
MySQL 延迟复制
Jian Sun_的博客
05-27 803
一、什么是延迟复制 延迟复制就是将 Slaves 节点与 Master 节点保持指定时间的复制间隔。 所谓的延迟 ，只是对 SQL_Thread 的线程的延迟。IO_Thread 主库发生的任何操作的日志都会同步到 slave，也就是说 IO_Thread 线程和主库是没有延迟的。只是 SQL_Thread 与主库有延迟。只是执行时间延迟，而不是读取 binlog 时间延迟。 二、延迟复制使用场景 (1) 利用延迟复制做误操作恢复 提供 Master 节点意外错误的快速恢复机制，若 Master 节点
编写一个脚本可以检测到MySQL库中所有没有创建主键的表
05-28
好的，以下是一个Python脚本，可以用于检测MySQL数据库中所有没有创建主键的表： ```python import mysql.connector # 连接数据库 mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="yourdatabase" ) # 获取所有表名 mycursor = mydb.cursor() mycursor.execute("SHOW TABLES") tables = mycursor.fetchall() # 循环遍历每个表 for table in tables: table_name = table[0] mycursor.execute(f"DESCRIBE {table_name}") columns = mycursor.fetchall() # 判断是否存在主键 has_primary_key = False for column in columns: if column[3] == "PRI": has_primary_key = True break # 输出没有主键的表名 if not has_primary_key: print(f"表 {table_name} 没有主键") ``` 你可以将 `yourusername`、`yourpassword` 和 `yourdatabase` 替换为你自己的 MySQL 数据库连接信息，并执行以上脚本即可。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值