首页 > 数据库 >MySQL从节点异常宕机重启后遇到主键冲突

MySQL从节点异常宕机重启后遇到主键冲突

时间：2024-10-31 13:09:10浏览次数：1  
标签：APPLIED 00 TRANSACTION LAST 宕机 TIMESTAMP MySQL 0000 主键

一台虚机在迁移过程中遇到点问题，然后运维做了重启的操作。重启后，发现该虚机中的 mysql 从库与主库的同步失败了。登录后查看，发现 sql thread 停止运行了：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 11.11.11.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000208
          Read_Master_Log_Pos: 157
               Relay_Log_File: myabc-relay-bin.000422
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000206
             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: 1062
                   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 master log mysql-bin.000206, end_log_pos 590031097. 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: 590022747
              Relay_Log_Space: 46906817
              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: 1062
               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 master log mysql-bin.000206, end_log_pos 590031097. 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: 1
                  Master_UUID: 123be9d9-2c3a-11sf-934f-003236b0e12b
             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: 241029 19:13:39
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                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)

 

查看一下具体的原因，发现是有主键冲突了：

mysql> select * from performance_schema.replication_applier_status_by_worker\G
*************************** 1. row ***************************
                                           CHANNEL_NAME: 
                                              WORKER_ID: 1
                                              THREAD_ID: NULL
                                          SERVICE_STATE: OFF
                                      LAST_ERROR_NUMBER: 1062
                                     LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000206, end_log_pos 590031097; Could not execute Write_rows event on table xxx.abce; Duplicate entry '123450002-x-102996-1-Bccount-20241030' for key 'abce.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 590031097
                                   LAST_ERROR_TIMESTAMP: 2024-10-29 19:16:41.967281
                               LAST_APPLIED_TRANSACTION: 
     LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
    LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
         LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
           LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
                                   APPLYING_TRANSACTION: ANONYMOUS
         APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2024-10-29 17:21:51.397102
        APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2024-10-29 17:21:51.397102
             APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2024-10-29 19:16:41.967042
                 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
   LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                     APPLYING_TRANSACTION_RETRIES_COUNT: 0
       APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
      APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
    APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 2. row ***************************
                                           CHANNEL_NAME: 
                                              WORKER_ID: 2
                                              THREAD_ID: NULL
                                          SERVICE_STATE: OFF
                                      LAST_ERROR_NUMBER: 0
                                     LAST_ERROR_MESSAGE: 
                                   LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                               LAST_APPLIED_TRANSACTION: 
     LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
    LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
         LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
           LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
                                   APPLYING_TRANSACTION: 
         APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
             APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
                 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
   LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                     APPLYING_TRANSACTION_RETRIES_COUNT: 0
       APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
      APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
    APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 3. row ***************************
                                           CHANNEL_NAME: 
                                              WORKER_ID: 3
                                              THREAD_ID: NULL
                                          SERVICE_STATE: OFF
                                      LAST_ERROR_NUMBER: 0
                                     LAST_ERROR_MESSAGE: 
                                   LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                               LAST_APPLIED_TRANSACTION: 
     LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
    LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
         LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
           LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
                                   APPLYING_TRANSACTION: 
         APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
             APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
                 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
   LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                     APPLYING_TRANSACTION_RETRIES_COUNT: 0
       APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
      APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
    APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
*************************** 4. row ***************************
                                           CHANNEL_NAME: 
                                              WORKER_ID: 4
                                              THREAD_ID: NULL
                                          SERVICE_STATE: OFF
                                      LAST_ERROR_NUMBER: 0
                                     LAST_ERROR_MESSAGE: 
                                   LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                               LAST_APPLIED_TRANSACTION: 
     LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
    LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
         LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
           LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
                                   APPLYING_TRANSACTION: 
         APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
        APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
             APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
                 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
   LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
                     APPLYING_TRANSACTION_RETRIES_COUNT: 0
       APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
      APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE: 
    APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000
4 rows in set (0.01 sec)

 

数据库版本是 8.0.30，从库也没有对外提供写入操作，排除了从库写入相同记录的问题。在主从同步过程中，发生这种问题只可能是应用 relay log的时候发生了问题。

 

根据复制配置的不同，relay log 中执行的事务顺序可能会出现不一致。可能存在以下类型的不一致：

(1).半应用事务。更新非事务表的事务应用了部分更改，但不是全部更改。

(2).间隙。间隙是指事务序列中有的事务没有应用，但是其后面的某个或某些事务已经被应用到从库。间隙只会出现在从库开启多线程复制的时候。为避免出现间隙，需要设置 slave_preserve_commit_order=1，这要求 slave_parallel_type=LOGICAL_CLOCK，并启用 log-bin和 log-slave-updates 。请注意，slave_preserve_commit_order=1 不会保留非事务性 DML 更新的顺序，因此这些更新可能会在 relay log 中它们之前的事务之前提交，这可能会导致间隙。

(3).源二进制日志位置滞后

即使没有间隙，Exec_master_log_pos 之后的事务也有可能被应用。在这种情况下，Exec _master_log_pos 是已应用事务的 "低水位标记"，滞后于最近应用事务的位置。这种情况只会发生在多线程从库上。启用 slave_preserve_commit_order 不能防止源二进制日志位置滞后。

 

以下一些场景会出现上面的三种不一致：

(1).当复制线程运行时，可能会出现间隙和半应用事务。

(2).mysqld 关闭。干净和不干净关闭都会中止正在进行的事务，并可能留下间隙和半应用事务。

(3).杀死复制线程（使用单线程复制时为 sql thread ，使用多线程复制时为协调器线程）。这会中止正在进行的事务，并可能留下间隙和半应用事务。

(4).应用线程(applier threads)出错。这可能会留下间隙。如果错误发生在 mixed 事务中，则该事务将被半应用。使用多线程从库时，未收到错误的 worker 会完成其队列，因此停止所有线程可能需要时间。

(5).使用多线程从库时，执行 stop slave。在发出stop slave 之后，从库会等待任何间隙被填补，然后更新 Exec_master_log_pos 。这可确保不会留下间隙或源二进制日志位置滞后，除非出现上述任何一种情况。

(6).如果 relay log 中的最后一个事务只接收了一半，而多线程从库协调器已开始将事务调度给 worker 线程，那么 stop slave 最多会等待 60 秒，等待接收事务。超时后，协调器会放弃并中止事务。如果事务是 mixed 类型的，则可能处于半完成状态。

(7).当正在进行的事务只更新事务表时执行 stop slave，事务会会回滚，stop slave 立即结束；如果正在进行的事务是 mixed 类型的事务，stop slave 最多会等待 60 秒，等待事务完成。超时后，stop slave 会中止事务，因此事务可能只完成了一半。

 

 

如果复制通道存在间隙，会产生以下后果

(1).从库处于主库可能从未存在过的状态

(2).show slave status 中的 Exec_master_log_pos 字段只是一个"低水位标记"。换句话说，在该位置之前出现的事务保证已提交，但该位置之后的事务可能已提交，也可能未提交

(3).除非应用线程正在运行，且 change master to 语句只设置了接收选项，否则该通道的 change master to 语句会出错

(4).如果使用 --relay-log-recovery 启动mysqld，则不会恢复该通道，并会打印警告

(5).如果使用 --dump-slave 启动 mysqldump，它不会记录间隙的存在

 

如果复制通道有源二进制日志位置滞后但无间隙，则上述第 2 至 5 种情况适用，但第 1 种情况不适用。

 

 

源二进制日志位置信息以二进制格式保存在内部表 mysql.slave_worker_info 中。start slave [sql_thread] 总是查询这些信息，以便只应用正确的事务。即使在 start slave 之前，slave_parallel_workers 已更改为 0；即使 start slave 与 until 子句一起使用，也是如此。

start slave until sql_after_mts_gaps 只应用所需的事务来填补空白。如果 start slave 与 until 子句一起使用，而 until 子句告诉 start slave 在填补所有间隙之前停止，那么它就会留下剩余的间隙。

 

使用基于 GTID 的复制时，从 MySQL 5.7.28 开始，多线程复制会首先检查 MASTER_AUTO_POSITION 是否设置为ON，如果是，则省略计算应跳过或未跳过事务的步骤。在这种情况下，恢复过程不需要旧的 relay log。

当 master_auto_position = 1设置为 1 时，在初始连接握手中，从库会发送一个 gtid 集，其中包含它已接收、已提交或已接收和已提交的事务。主库会做出响应，发送其二进制日志中记录的所有事务，这些事务的 gtid 未包含在从库发送的 gtid 集中。这种交换可确保主库只发送从库尚未记录或提交的 gtid 的事务。如果从库从多个主库接收事务，自动跳过功能可确保事务不会被应用两次。

 

回到本次问题的本身，因为负责数据库的管理员，在配置主从复制的时候，并没有使用基于gtid的复制，而是基于日志位置的复制，且开启了多线程复制。因而猜测在从库宕机重启过程中，遇到了上面提到的第三种不一致：源二进制日志位置滞后。从而发生了从库遇到主键冲突的问题。

标签：APPLIED,00,TRANSACTION,LAST,宕机,TIMESTAMP,MySQL,0000,主键
From： https://www.cnblogs.com/abclife/p/18515513

相关文章

  • 小白手把手教学用spring框架实现mybatis和mysql以及工作原理
    Maven_Mybatis_Mysql什么是MybatisMyBatis是一款优秀的持久层框架，它支持自定义SQL、存储过程以及高级映射。MyBatis免除了几乎所有的JDBC代码以及设置参数和获取结果集的工作。MyBatis可以通过简单的XML或注解来配置和映射原始类型、接口和JavaPOJO（PlainOldJavaObj......
  • MySQL的主从切换在什么情况下使用
    主从切换一般在如下情况下才进行：1、例行的主库重启，主库运行一段时间后需要将主库进行重启扫盘，降低主库死机的风险；2、主库异常情况下切到从库。3、分机房网络调整涉及到主库的情况。一、MySQL的主从切换在什么情况下使用主从切换一般在如下情况下才进行：1、例行的主库重启，主库......
  • 遇到慢查询怎么办？一文解读MySQL 8.0查询分析工具
    本文分享自华为云社区《【华为云MySQL技术专栏】MySQL8.0EXPLAINANALYZE工具介绍》，作者：GaussDB数据库。1.EXPLAINANALYZE可以解决什么问题MySQL8.0.18版本开始支持查询分析工具EXPLAINANALYZE，该工具不仅会实际执行SQL语句，还会展示SQL语句详细的执行信息，包含执行算子（It......
  • 织梦取消MySQL错误日志生成文件功能防止暴露后台和管理员
    问题描述织梦程序在MySQL错误时会生成 mysql_error_trace.inc 文件，记录错误信息，可能导致后台目录和管理员账号信息泄露。解决方法编辑 dedesql.class.php 文件打开 /include/dedesql.class.php 文件。删除错误日志生成代码找到以下代码并删除：//保存MySql错......
  • MySQL max_allowed_packet参数设置
    MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败。(比方说导入数据库,数据表)查看目前配置：代码如下:mysql>showVARIABLESlike'%max_allowed_packet%';+--------------------------+--------......
  • mysql 一次插入多少数据性能最佳
    mysql一次插入多少条是最佳性能我们经常会浏览，分享，点赞，都会产生数据，这些数可能会被存储到不同的地方，其中最常见的存储载体就是--数据库。根据场景和数据特性，可以有关系型数据库mysql，也有非关系数据库，例如：Redis，比如说：当你在网站点赞的时候，为了快速响应，可能是一个基于内存的......
  • 基于Java+SpringBoot+Mysql实现的古诗词平台功能设计与实现九
    一、前言介绍：1.1项目摘要随着信息技术的迅猛发展和数字化时代的到来，传统文化与现代科技的融合已成为一种趋势。古诗词作为中华民族的文化瑰宝，具有深厚的历史底蕴和独特的艺术魅力。然而，在现代社会中，由于生活节奏的加快和信息获取方式的多样化，古诗词的传播和阅读面临着一定的挑......
  • 基于Java+SpringBoot+Mysql实现的古诗词平台功能设计与实现十
    一、前言介绍：1.1项目摘要随着信息技术的迅猛发展和数字化时代的到来，传统文化与现代科技的融合已成为一种趋势。古诗词作为中华民族的文化瑰宝，具有深厚的历史底蕴和独特的艺术魅力。然而，在现代社会中，由于生活节奏的加快和信息获取方式的多样化，古诗词的传播和阅读面临着一定的挑......
  • 免费送源码：Java+ssm+MySQL+Ajax ssm第二课堂管理系统 计算机毕业设计原创定制
    摘要随着互联网的高速发展，教育进入了信息化时代，促使了多种混合式教学模式的出现。第二课堂管理系统是这一时期新型混合式教学模式的代表，它的出现改变了传统教学模式，将知识传递置于课前，将学习知识的主动性交给学生，促使学生的素质全面发展。第二课堂管理系统以“以学生为......
  • MySQL索引
    索引概述介绍索引（index）是帮助MySQL高效获取数据的数据结构（且有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。优缺点索引结构MySQL的索引是在存储引......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99