【MySQL】Server-id導致Slave_IO_Running: No主從複製故障

神諭丶發表於2016-03-22
MySqlServer
【問題描述】：
在master停機維護啟動服務之後，slave的Slave_IO_Running狀態被置為NO
Slave_SQL_Running為Yes，猜測應該是master的鍋
繼續檢查網路，許可權等問題之後仍然為不可用
也嘗試重新
change master to ...., ...., ....,
master_log_file = 'master-bin.034555',
master_log_pos = 98;
依舊不可用

  1. mysql> SHOW SLAVE STATUS\G
  2. *************************** 1. row ***************************
  3.              Slave_IO_State:
  4.                 Master_Host: *.*.*.*
  5.                 Master_User: repl
  6.                 Master_Port: 3306
  7.               Connect_Retry: 60
  8.             Master_Log_File: master-bin.034555
  9.         Read_Master_Log_Pos: 98
  10.              Relay_Log_File: mysqld-relay-bin.000001
  11.               Relay_Log_Pos: 98
  12.       Relay_Master_Log_File: master-bin.034555
  13.            Slave_IO_Running: No
  14.           Slave_SQL_Running: Yes
  15.             Replicate_Do_DB:
  16.         Replicate_Ignore_DB:
  17.          Replicate_Do_Table:
  18.      Replicate_Ignore_Table:
  19.     Replicate_Wild_Do_Table:
  20. Replicate_Wild_Ignore_Table:
  21.                  Last_Errno: 0
  22.                  Last_Error:
  23.                Skip_Counter: 0
  24.         Exec_Master_Log_Pos: 98
  25.             Relay_Log_Space: 98
  26.             Until_Condition: None
  27.              Until_Log_File:
  28.               Until_Log_Pos: 0
  29.          Master_SSL_Allowed: No
  30.          Master_SSL_CA_File:
  31.          Master_SSL_CA_Path:
  32.             Master_SSL_Cert:
  33.           Master_SSL_Cipher:
  34.              Master_SSL_Key:
  35.       Seconds_Behind_Master: NULL


檢視slave的errorlog，發現有報錯，報錯資訊十分明瞭：
並且丟擲： Got fatal error 1236 的錯誤
  1. 160322 9:42:51 [Note] Slave SQL thread initialized, starting replication in log 'master-bin.034555' at position 98, relay log './mysqld-relay-bin.0000
  2. 01' position: 4
  3. 160322 9:43:01 [Note] Slave I/O thread: connected to master 'repl@*.*.*.*:3306', replication started in log 'master-bin.034555' at position 98
  4. 160322 9:43:01 [ERROR] Error reading packet from server: Misconfigured master - server id was not set ( server_errno=1236)
  5. 160322 9:43:01 [ERROR] Got fatal error 1236: 'Misconfigured master - server id was not set' from master when reading data from binary log
  6. 160322 9:43:01 [Note] Slave I/O thread exiting, read up to log 'master-bin.034555', position 98
  7. 160322 9:43:07 [Note] Error reading relay log event: slave SQL thread was killed

檢查配置檔案發現#server-id = xxx 被註釋

【解決方案】：
由於server_id為“Dynamic Variable”
故到master中手動執行
  1. mysql> SET GLOBAL server_id=3028;
  2. Query OK, 0 rows affected (0.00 sec)

最後在將配置檔案中新增
server-id=3028

在slave上檢查：
  1. mysql> SHOW SLAVE STATUS\G
  2. *************************** 1. row ***************************
  3.              Slave_IO_State: Waiting for master to send event
  4.                 Master_Host: 192.168.30.28
  5.                 Master_User: repl
  6.                 Master_Port: 3306
  7.               Connect_Retry: 60
  8.             Master_Log_File: master-bin.034556
  9.         Read_Master_Log_Pos: 59312658
  10.              Relay_Log_File: mysqld-relay-bin.000002
  11.               Relay_Log_Pos: 64698308
  12.       Relay_Master_Log_File: master-bin.034555
  13.            Slave_IO_Running: Yes
  14.           Slave_SQL_Running: Yes
  15.                       …………………………
  16.       Seconds_Behind_Master: 10071
  17. 1 row in set (0.00 sec)

解決。


同樣，某些時候在start slave時報錯“The server is not configured as slave”，
同樣也很有可能是server-id的緣故，只需要手動在主和從庫上執行：
SET GLOBAL server_id = xxx，並保證兩臺server-id不一樣即可，最後寫入my.cnf中。


作者微信公眾號（持續更新）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-2061572/，如需轉載，請註明出處，否則將追究法律責任。

相關文章