master/slave 相同server_id引起的同步失敗

昨天在做MySQL SwitchOver遇到一個(gè)詭異的想象,切換前后的結(jié)構(gòu)圖如下：

當(dāng)我把一切都切換好之后，應(yīng)其他需求，重啟了04上的mysql,然后show slave status\G發(fā)現(xiàn)報(bào)錯(cuò)：

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

查看了一下03/04的server_id才發(fā)現(xiàn)，原來(lái)它們的server_id相同，我們知道，master/slave的server_id是不能相同的，但是為什么在我重啟之前我show slave status\G的時(shí)候沒(méi)有發(fā)現(xiàn)報(bào)錯(cuò)？？？ 并且我特別檢查了一下slave幾個(gè)參數(shù)：

Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 38593
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 38593

表示slave已經(jīng)完全追上master,并且通過(guò)SQL_thread線程執(zhí)行。

問(wèn)題：在server_id相同的情況下，slave為什么之前沒(méi)有報(bào)錯(cuò)呢，并且還可以繼續(xù)應(yīng)用master的binlog呢？？？

廢話不多說(shuō)了，測(cè)試如下：

在一個(gè)master/slave環(huán)境下,剛開始server_id不同，一切都很正常，數(shù)據(jù)也可以同步過(guò)去，我slave的server_id修改成與master的相同：

master:
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|      583306 |
+-------------+
1 row in set (0.00 sec)

slave:
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|      593306 |
+-------------+
1 row in set (0.00 sec)

mysql> set global server_id=583306;

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|      583306 |
+-------------+
1 row in set (0.00 sec)

然后show slave status\G，還沒(méi)有報(bào)錯(cuò)；

于是我在master上insert了一條數(shù)據(jù)，觀察一下slave有沒(méi)有同步過(guò)去，show slave status\G 發(fā)現(xiàn)，position的值變化了，但是當(dāng)我select這個(gè)表的時(shí)候，確沒(méi)有找到剛才插入的那條數(shù)據(jù)，說(shuō)明數(shù)據(jù)沒(méi)有同步過(guò)去。

Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 38857
Relay_Master_Log_File: mysql-bin.000001
Exec_Master_Log_Pos: 38857

通過(guò)show relaylog events命令也可以看到，insert語(yǔ)句并沒(méi)有寫到relay log中，所以數(shù)據(jù)沒(méi)有同步過(guò)來(lái)，這就說(shuō)明當(dāng)我們修改server_id之后，show slave status\G顯示的結(jié)果并不是這么可靠，

mysql> SHOW RELAYLOG EVENTS in 'sht-sgmhadoopdn-02-relay-bin.000005';
+-------------------------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
| Log_name                            | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                |
+-------------------------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+
| sht-sgmhadoopdn-02-relay-bin.000005 |    4 | Format_desc    |    593306 |         123 | Server ver: 5.7.21-log, Binlog ver: 4                               |
| sht-sgmhadoopdn-02-relay-bin.000005 |  123 | Previous_gtids |    593306 |         194 | 8b94d944-34c8-11e8-9e15-0050568211bd:1-144                          |
| sht-sgmhadoopdn-02-relay-bin.000005 |  194 | Rotate         |    583306 |           0 | mysql-bin.000001;pos=4                                              |
| sht-sgmhadoopdn-02-relay-bin.000005 |  241 | Format_desc    |    583306 |         123 | Server ver: 5.7.21-log, Binlog ver: 4                               |
| sht-sgmhadoopdn-02-relay-bin.000005 |  360 | Rotate         |         0 |         407 | mysql-bin.000001;pos=154                                            |
| sht-sgmhadoopdn-02-relay-bin.000005 |  407 | Rotate         |         0 |         454 | mysql-bin.000001;pos=37623                                          |
| sht-sgmhadoopdn-02-relay-bin.000005 |  454 | Gtid           |    583306 |       37688 | SET @@SESSION.GTID_NEXT= '8b94d944-34c8-11e8-9e15-0050568211bd:145' |
| sht-sgmhadoopdn-02-relay-bin.000005 |  519 | Query          |    583306 |       37807 | use `testdb`; DROP TABLE `t1` /* generated by server */             |
| sht-sgmhadoopdn-02-relay-bin.000005 |  638 | Gtid           |    583306 |       37872 | SET @@SESSION.GTID_NEXT= '8b94d944-34c8-11e8-9e15-0050568211bd:146' |
| sht-sgmhadoopdn-02-relay-bin.000005 |  703 | Query          |    583306 |       37964 | use `testdb`; create table t1 like t2                               |
| sht-sgmhadoopdn-02-relay-bin.000005 |  795 | Gtid           |    583306 |       38029 | SET @@SESSION.GTID_NEXT= '8b94d944-34c8-11e8-9e15-0050568211bd:147' |
| sht-sgmhadoopdn-02-relay-bin.000005 |  860 | Query          |    583306 |       38148 | use `testdb`; DROP TABLE `t1` /* generated by server */             |
| sht-sgmhadoopdn-02-relay-bin.000005 |  979 | Gtid           |    583306 |       38213 | SET @@SESSION.GTID_NEXT= '8b94d944-34c8-11e8-9e15-0050568211bd:148' |
| sht-sgmhadoopdn-02-relay-bin.000005 | 1044 | Query          |    583306 |       38329 | use `testdb`; create table t1(c1 int,c2 varchar(20))                |
| sht-sgmhadoopdn-02-relay-bin.000005 | 1160 | Gtid           |    583306 |       38394 | SET @@SESSION.GTID_NEXT= '8b94d944-34c8-11e8-9e15-0050568211bd:149' |
| sht-sgmhadoopdn-02-relay-bin.000005 | 1225 | Query          |    583306 |       38468 | BEGIN                                                               |
| sht-sgmhadoopdn-02-relay-bin.000005 | 1299 | Table_map      |    583306 |       38518 | table_id: 228 (testdb.t1)                                           |
| sht-sgmhadoopdn-02-relay-bin.000005 | 1349 | Write_rows     |    583306 |       38562 | table_id: 228 flags: STMT_END_F                                     |
| sht-sgmhadoopdn-02-relay-bin.000005 | 1393 | Xid            |    583306 |       38593 | COMMIT /* xid=3689 */                                               |
+-------------------------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+

最后當(dāng)我stop slave; && start slave;之后，就出現(xiàn)了文章一開始報(bào)錯(cuò)的server_id相同的錯(cuò)誤。

總結(jié)：

（1）master/slave要保證server_id不同；

（2）當(dāng)修改server_id之后，需要執(zhí)行stop/start slave;最好重啟mysql數(shù)據(jù)庫(kù)；

（3）不要僅僅通過(guò)show slave status\G查看同步是否正確，還在查看數(shù)據(jù)是否真正的同步過(guò)去;