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

2024-04-27 23:52
文章标签 mysql 问题 处理 导致 主键 database 延迟 delete 主库 从库

本文主要是介绍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 eventMaster_Host: 10.106.210.206Master_User: replMaster_Port: 14728Connect_Retry: 60Master_Log_File: mysql-bin.000025Read_Master_Log_Pos: 84258028Relay_Log_File: host01-relay-bin.000004Relay_Log_Pos: 63196660Relay_Master_Log_File: mysql-bin.000025Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 63196524Relay_Log_Space: 84258422Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 96Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 210206Master_UUID: 408abbb7-dc1b-11ee-a91f-fefcfee1f844Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_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-1391Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:Master_public_key_path:Get_master_public_key: 0Network_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 eventMaster_Host: 10.106.210.206Master_User: replMaster_Port: 14728Connect_Retry: 60Master_Log_File: mysql-bin.000025Read_Master_Log_Pos: 84259101Relay_Log_File: host01-relay-bin.000005Relay_Log_Pos: 1195Relay_Master_Log_File: mysql-bin.000025Slave_IO_Running: YesSlave_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 1068Last_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: 0Exec_Master_Log_Pos: 84258897Relay_Log_Space: 84259617Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1068Last_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: 210206Master_UUID: 408abbb7-dc1b-11ee-a91f-fefcfee1f844Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State:Master_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp: 240427 18:04:40Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set: 408abbb7-dc1b-11ee-a91f-fefcfee1f844:1-1120,b5896746-dc1a-11ee-b573-fefcfee443b5:1-1391Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:Master_public_key_path:Get_master_public_key: 0Network_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很多数据，会导致从库出现很长时间延迟，因此需要严格把控，定期检查，确保主库上不存在没有主键的表。

这篇关于mysql主库delete一个没主键的表导致从库延迟很久问题处理的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/941831。

相关文章

MySQL 中的 CAST 函数详解及常见用法

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字
阅读更多...
Mysql实现范围分区表(新增、删除、重组、查看)

Mysql实现范围分区表(新增、删除、重组、查看)

《Mysql实现范围分区表(新增、删除、重组、查看)》MySQL分区表的四种类型（范围、哈希、列表、键值）,主要介绍了范围分区的创建、查询、添加、删除及重组织操作,具有一定的参考价值,感兴趣的可以了解... 目录一、mysql分区表分类二、范围分区（Range Partitioning1、新建分区表：2、分
阅读更多...
MySQL 定时新增分区的实现示例

MySQL 定时新增分区的实现示例

《MySQL定时新增分区的实现示例》本文主要介绍了通过存储过程和定时任务实现MySQL分区的自动创建,解决大数据量下手动维护的繁琐问题,具有一定的参考价值,感兴趣的可以了解一下... mysql创建好分区之后，有时候会需要自动创建分区。比如，一些表数据量非常大，有些数据是热点数据，按照日期分区MululbU
阅读更多...
SQL Server配置管理器无法打开的四种解决方法

SQL Server配置管理器无法打开的四种解决方法

《SQLServer配置管理器无法打开的四种解决方法》本文总结了SQLServer配置管理器无法打开的四种解决方法,文中通过图文示例介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的... 目录方法一：桌面图标进入方法二：运行窗口进入检查版本号对照表php方法三：查找文件路径方法四：检查 S
阅读更多...
Spring Boot @RestControllerAdvice全局异常处理最佳实践

Spring Boot @RestControllerAdvice全局异常处理最佳实践

《SpringBoot@RestControllerAdvice全局异常处理最佳实践》本文详解SpringBoot中通过@RestControllerAdvice实现全局异常处理,强调代码复用、统... 目录前言一、为什么要使用全局异常处理？二、核心注解解析1. @RestControllerAdvice2
阅读更多...
MySQL 删除数据详解(最新整理)

MySQL 删除数据详解(最新整理)

《MySQL删除数据详解(最新整理)》：本文主要介绍MySQL删除数据的相关知识,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、前言二、mysql 中的三种删除方式1.DELETE语句✅ 基本语法： 示例：2.TRUNCATE语句✅ 基本语
阅读更多...
MySQL中查找重复值的实现

MySQL中查找重复值的实现

《MySQL中查找重复值的实现》查找重复值是一项常见需求,比如在数据清理、数据分析、数据质量检查等场景下,我们常常需要找出表中某列或多列的重复值,具有一定的参考价值,感兴趣的可以了解一下... 目录技术背景实现步骤方法一：使用GROUP BY和HAVING子句方法二：仅返回重复值方法三：返回完整记录方法四：
阅读更多...
从入门到精通MySQL联合查询

从入门到精通MySQL联合查询

《从入门到精通MySQL联合查询》：本文主要介绍从入门到精通MySQL联合查询,本文通过实例代码给大家介绍的非常详细,需要的朋友可以参考下... 目录摘要1. 多表联合查询时mysql内部原理2. 内连接3. 外连接4. 自连接5. 子查询6. 合并查询7. 插入查询结果摘要前面我们学习了数据库设计时要满
阅读更多...
怎样通过分析GC日志来定位Java进程的内存问题

怎样通过分析GC日志来定位Java进程的内存问题

《怎样通过分析GC日志来定位Java进程的内存问题》：本文主要介绍怎样通过分析GC日志来定位Java进程的内存问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、GC 日志基础配置1. 启用详细 GC 日志2. 不同收集器的日志格式二、关键指标与分析维度1.
阅读更多...
MySQL查询JSON数组字段包含特定字符串的方法

MySQL查询JSON数组字段包含特定字符串的方法

《MySQL查询JSON数组字段包含特定字符串的方法》在MySQL数据库中,当某个字段存储的是JSON数组,需要查询数组中包含特定字符串的记录时传统的LIKE语句无法直接使用,下面小编就为大家介绍两种... 目录问题背景解决方案对比1. 精确匹配方案（推荐）2. 模糊匹配方案参数化查询示例使用场景建议性能优
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2