MySQL主从报错1594

深藏阁楼爱情的钟 2022-01-10 05:45 208阅读 0赞

## 一、主从报错 Relay log read failure ##

问题原因，MySQL主从使用的是kvm虚拟机，物理机超分严重，在负载高的情况下会kill掉占用资源最多的虚拟机，再启动后导致主从失败

mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 10.0.0.230
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master-bin.002783
              Read_Master_Log_Pos: 812026
                   Relay_Log_File: 10-0-0-236-relay-bin.000002
                    Relay_Log_Pos: 83853
            Relay_Master_Log_File: master-bin.002781
                 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: 973991562
                  Relay_Log_Space: 9657278
                  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: 1128
    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: 1
                      Master_UUID: f7c24af7-a54a-11e6-88b4-525400169c04
                 Master_Info_File: /Data/work/local/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: update
               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: 
                    Auto_Position: 0
    1 row in set (0.00 sec)

## 二、问题分析 ##

从上面的报错信息中我们可以看到应该是主库的binlog日志或者从库的relay日志损坏，导致从库读取日志的时候时候，从而导致从库复制线程报错。

主要原因是服务器断电导致从库的relay log损坏，从而导致从库复制线程报错。

## 三、解决方案 ##

在show slave status\G中找到如下信息：
    Relay_Master_Log_File: master-bin.002781    # slave库已读取的master的binlog
    Exec_Master_Log_Pos: 968089314           # 在slave上已经执行的position位置点
    
    停掉slave，以slave已经读取的binlog文件，和已经执行的position为起点，重新设置同步。
    mysql> stop slave;
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> change master to master_log_file='master-bin.002781', master_log_pos=968089314;
    Query OK, 0 rows affected (1.03 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.41 sec)

转载于:https://www.cnblogs.com/cyleon/p/10679341.html