Shulou(Shulou.com)06/01 Report--

Yesterday, I encountered a strange imagination when doing MySQL SwitchOver. The structure diagram before and after switching is as follows:

After I switched everything over, I restarted mysql on 04 according to other requirements, and then showed slave status\G found an error:

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).

After checking the server_id of 03/04, we found that their server_id is the same. We know that the server_id of master/slave cannot be the same, but why didn't I find an error when I showed slave status\G before I restarted?? And I specifically checked several parameters of slave:

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

This means that the slave has completely caught up with the master and is executed via the SQL_thread thread.

Question: If the server_id is the same, why didn't slave report an error before, and can still continue to apply master's binlog???

Without further ado, the test is as follows:

In a master/slave environment, at first the server_id is different, everything is normal, the data can also be synchronized, and the server_id of my slave is modified to be the same as that of the 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)

Then show slave status\G, there is no error;

So I insert a piece of data on master, observe whether slave has synchronized in the past, show slave status\G found that the value of position has changed, but when I select this table, I did not find the piece of data just inserted, indicating that the data has not synchronized in the past.

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

You can also see from the show relay log events command that the insert statement is not written to the relay log, so the data is not synchronized, which means that after we modify the server_id, the results displayed by show slave status\G are not so reliable.

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 */ |+-------------------------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------+

Finally, when I stop slave; && start slave;, I get the same server_id error I got at the beginning of the article.

Summary:

(1) master/slave must ensure that server_id is different;

(2) After modifying the server_id, you need to execute stop/start slave; it is best to restart the mysql database;

(3) Don't just check whether the synchronization is correct by showing slave status\G, but also check whether the data is really synchronized in the past;

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.