從站等待重新連接

我已將新的從屬主機添加到主控以進行複制，但缺少一些參數。

mysql> show slave status\G  
*************************** 1. row ***************************  
              Slave_IO_State: Waiting to reconnect after a failed master event read  
                 Master_Host: 10.10.2.12    
                 Master_User: slave_user    
                 Master_Port: 3306    
               Connect_Retry: 60  
             Master_Log_File: mysql-bin.000071   
         Read_Master_Log_Pos: 754916280  
              Relay_Log_File: mysqld-relay-bin.000001    
               Relay_Log_Pos: 4  
       Relay_Master_Log_File: mysql-bin.000071  
            Slave_IO_Running: Connecting    
           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: 754916280  
             Relay_Log_Space: 120  
             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: 0  
              Last_SQL_Error:  
 Replicate_Ignore_Server_Ids:  
            Master_Server_Id: 212  
                 Master_UUID:  
            Master_Info_File: /var/lib/mysql/master.info  
                   SQL_Delay: 0  
         SQL_Remaining_Delay: NULL  
     Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it  
          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)  

我按照下面的步驟仍然沒有運氣。GRANT創建並允許使用者權限後，我遇到了同樣的錯誤。如果我們遺漏了什麼，請告訴我。

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%';  
Query OK, 0 rows affected (0.08 sec)  

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'10.10.2.16';  
Query OK, 0 rows affected (0.03 sec)  

看看這個，是不是很奇怪。

[root@host ~]# netstat -tulpn | grep -i mysql   
tcp        0      0 :::3306                     :::*                        LISTEN      2135/mysqld  

更改日誌位置後，我們收到此錯誤。

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000071',   MASTER_LOG_POS=754916280;

mysql> show slave status\G  
*************************** 1. row ***************************  
              Slave_IO_State: Queueing master event to the relay log  
                 Master_Host: 10.10.2.12  
                 Master_User: slave_user  
                 Master_Port: 3306  
               Connect_Retry: 60  
             Master_Log_File: mysql-bin.000076  
         Read_Master_Log_Pos: 488743659  
              Relay_Log_File: mysqld-relay-bin.000002  
               Relay_Log_Pos: 557  
       Relay_Master_Log_File: mysql-bin.000071  
            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: 1146  
                  Last_Error: Error executing row event: 'Table   'example.tracker' doesn't exist'  
                Skip_Counter: 0  
         Exec_Master_Log_Pos: 754916567  
             Relay_Log_Space: 5112867650  
             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: 1146  
              Last_SQL_Error: Error executing row event: 'Table   'example.tracker' doesn't exist'     
 Replicate_Ignore_Server_Ids:  
            Master_Server_Id: 212     
                 Master_UUID:       
            Master_Info_File: /var/lib/mysql/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: 160127 04:51:22      
              Master_SSL_Crl:    
          Master_SSL_Crlpath:     
          Retrieved_Gtid_Set:        
           Executed_Gtid_Set:       
               Auto_Position: 0          
1 row in set (0.00 sec)        

添加輸出mysqlbinlog

[root@host mysql]# mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000089 | tail -n 5 | head -n 1  
ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 541414465, event_type: 116  
ERROR: Could not read entry at offset 184469: Error in log format or read error.  
DELIMITER ;  

[root@host mysql]# mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000086 | tail -n 5 | head -n 1
ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: -736930139, event_type: 71
ERROR: Could not read entry at offset 668651590: Error in log format or read error.
DELIMITER ;

執行此命令mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000004 | tail -n 5 | head -n 1時會出錯。經過調查，我們發現上週發生了電源故障，因此沒有發生複製。所以解決方法是對master進行新的備份並恢復複製。

引用自：https://dba.stackexchange.com/questions/127356