【MySQL】Server-id导致Slave_IO_Running: No主从复制故障

【问题描述 】：
在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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29773961/viewspace-2061572/