MHA集群报错：The slave I/O thread stops because master and slave have equal MySQL server UUIDs

小鱼儿 2024-04-19 15:52 62阅读 0赞

MHA集群报错：The slave I/O thread stops because master and slave have equal MySQL server UUIDs  
实验环境  
操作系统：CentOS Linux release 7.3.1611 (Core)

数据库系统： MySQL 5.7.19

问题重现  
在虚拟机上配置MySQL的MHA集群时，在配置主从复制模式的时候发现配置失败：

mysql> show slave status\\G;

Last\_IO\_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.  
1  
2  
3  
问题分析  
问题提示主从使用了相同的server UUID，一个个的检查：

检查主从server\_id

主库：

检查主从状态：

主库：

mysql> show master status;  
\+------------------+----------+--------------+------------------+-------------------+  
| File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
\+------------------+----------+--------------+------------------+-------------------+  
| mysql-bin.000001 |      154 |              |                  |                   |  
\+------------------+----------+--------------+------------------+-------------------+  
1 row in set (0.00 sec)  
1  
2  
3  
4  
5  
6  
7  
从库：

mysql> show master status;  
\+------------------+----------+--------------+------------------+-------------------+  
| File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
\+------------------+----------+--------------+------------------+-------------------+  
| mysql-bin.000001 |      306 |              |                  |                   |  
\+------------------+----------+--------------+------------------+-------------------+  
1 row in set (0.00 sec)  
1  
2  
3  
4  
5  
6  
7  
File一样，排除。

最后检查发现他们的auto.cnf中的server-uuid是一样的。。。

\[root@localhost ~\]\# vim /var/lib/mysql/auto.cnf

\[auto\]  
server-uuid=4f37a731-9b79-11e8-8013-000c29f0700f  
1  
2  
3  
4  
问题解决  
停止从库的mysqld服务，删除他的auto.cnf文件，再启动数据库服务即可：

\[root@localhost mysql\]\# systemctl stop mysqld.service

\[root@localhost mysql\]\# mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak

\[root@localhost mysql\]\# systemctl start mysqld.service  
1  
2  
3  
4  
5  
此时再去查看从库auto.cnf，已自动生成新的server-uuid：

\[root@localhost mysql\]\# vim /var/lib/mysql/auto.cnf

\[auto\]  
server-uuid=2682888d-994a-11e8-aaf0-000c298cdafc  
1  
2  
3  
4  
5  
再查看从库状态，已正常

mysql> show slave status\\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State: Waiting for master to send event  
                  Master\_Host: 192.168.232.128  
                  Master\_User: repl  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000001  
          Read\_Master\_Log\_Pos: 1208  
               Relay\_Log\_File: localhost-relay-bin.000003  
                Relay\_Log\_Pos: 1374  
        Relay\_Master\_Log\_File: mysql-bin.000001  
             Slave\_IO\_Running: Yes  
            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: 1208  
              Relay\_Log\_Space: 1585  
              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: 0  
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: 1  
                  Master\_UUID: 574ce30b-9637-11e8-b98b-000c29e7e754  
             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 more updates  
           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  
         Replicate\_Rewrite\_DB:   
                 Channel\_Name:   
           Master\_TLS\_Version:   
1 row in set (0.00 sec)

ERROR:   
No query specified  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
51  
52  
53  
54  
55  
56  
57  
58  
59  
60  
61  
62  
63  
原理分析  
我搭建集群是先在虚拟机上搭好主库环境，然后直接克隆镜像当作从库，这样会导致主从库生成相同的server-uuid。只需删除一个server-uuid并重启数据库服务即可解决问题。

下面附上官网对于相关配置的描述：

a、有关server\_id的描述  
The server ID, used in replication to give each master and slave a unique identity. This variable is set  
by the –server-id option. For each server participating in replication, you should pick a  
positive integer in the range from 1 to 232– 1(2的32次方减1) to act as that server’s ID.

b、有关 server\_uuid的描述  
Beginning with MySQL 5.6, the server generates a true UUID in addition to the –server-id  
supplied by the user. This is available as the global, read-only variable server\_uuid(全局只读变量)

When starting, the MySQL server automatically obtains a UUID as follows:  
1. Attempt to read and use the UUID written in the file data\_dir/auto.cnf (where data\_dir is  
the server’s data directory); exit on success.  
2. Otherwise, generate a new UUID and save it to this file, creating the file if necessary.  
The auto.cnf file has a format similar to that used for my.cnf or my.ini files. In MySQL 5.6,  
auto.cnf has only a single \[auto\] section containing a single server\_uuid \[1992\] setting and  
value;

Important  
The auto.cnf file is automatically generated; you should not attempt to write  
or modify this file

Also beginning with MySQL 5.6, when using MySQL replication, masters and slaves know one  
another’s UUIDs. The value of a slave’s UUID can be seen in the output of SHOW SLAVE HOSTS. Once  
START SLAVE has been executed (but not before), the value of the master’s UUID is available on the  
slave in the output of SHOW SLAVE STATUS.

In MySQL 5.6.5 and later, a server’s server\_uuid is also used in GTIDs for transactions originating  
on that server. For more information, see Section 16.1.3, “Replication with Global Transaction