MySQL主从1205报错【转】

最新推荐文章于 2024-04-18 16:55:39 发布

转载

最新推荐文章于 2024-04-18 16:55:39 发布 · 479 阅读

文章标签：

#数据库 #运维 #前端 #ViewUI

本文详细介绍了遇到MySQL主从复制过程中出现的1205错误，即Slave SQL thread retried transaction超时问题。错误源于更新表时产生的锁导致的死锁。解决方案包括调整参数设置和监控锁的脚本。此外，分析了VIP漂移可能对主从复制造成的影响以及Heartbeat漂移的原因。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

主从报错1205 Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable

Last_Errno: 1205
Last_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable

解决办法：

stop slave;
start slave;
show slave status\G;

大家都知道DBA就像是消（背）防（锅）员（侠），因为前端应用还有开发上线的新版本都会影响到位于最底层的数据库，前方稍微有些风吹草动，就能反应在数据库的性能上。但是有的时候SQL不仅能决定数据库的性能，还能决定数据库的生死，今天的案例是开发的一条SQL，引起的MySQL数据库主从复制报错，vip漂移问题。

今天下午有客户反馈发消息有点慢，手机收到几条报警：

nagios报警如下大致意思就是

14:39分 vip发生了漂移

**** Nagios ***** 

Notification Type: PROBLEM 
Service: check_ha_mysqld 
Host: prod-bjuc-mysql1-vip 
Address: 192.168.87.74 
State: CRITICAL 
Date/Time: Wed Mar 28 14:39:27 CST 2018 
Additional Info: 
CRITICAL: HA Failover from 192.168.87.72 to 192.168.87.123,         
192.168.87.123 result:PROCS OK: 1 processes with command name /usr/sbin/mysqld, 
args  VIP=192.168.87.74, 192.168.87.72:PROCS OK: 1 processes with command name /usr/sbin/mysqld

14:46分主从复制中断

***** Nagios ***** 

Notification Type: PROBLEM 
Service: check_mysql_health_slave-sql-running 
Host: bjuc-mysql1 
Address: 192.168.87.72 
State: CRITICAL 
Date/Time: Wed Mar 28 14:46:06 CST 2018 
Additional Info: 
CRITICAL - Slave sql is not running

登录数据库服务器查看主从复制的状态，发现报错1205错

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.87.123
                  Master_User: zhu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000481
          Read_Master_Log_Pos: 218802975
               Relay_Log_File: mysql-relay-bin.000428
                Relay_Log_Pos: 2428110
        Relay_Master_Log_File: mysql-bin.000481
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: performance_schema,information_schema,test,mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: %.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1205
                   Last_Error: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'statusnet'. Query: 'UPDATE notice_status_new set valid = 0 WHERE conversation = 384667 AND user_id IN(4337475,4337529,4337471,4337365)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 182088298
              Relay_Log_Space: 39142986
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: