【MySQL主从复制架构】slave have equal MySQL Server UUIDs原因及解决

╰+攻爆jí腚メ 2022-05-19 06:43 187阅读 0赞

## slave equal MySQL Server UUIDs原因及解决 ##

最近在部署MySQL主从复制架构的时候，碰到了"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." 这个错误提示。即主从架构中使用了相同的UUID。检查server\_id系统变量，已经是不同的设置，那原因是？接下来为具体描述。

### 1、错误消息 ###

> mysql> show slave staus;  
> 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.

### 2、查看主从的server\_id变量 ###

### 3、解决故障 ###

**1）、查看auto.cnf文件**

> \[root@dbsrv1 ~\] cat /data/mysqldata/auto.cnf  \#\#\# 主上的uuid  
> \[auto\]  
> server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026
> 
> \[root@dbsrv2 ~\]\# more /data/mysqldata/auto.cnf \#\#\#从上的uuid，果然出现了重复，原因是克隆了虚拟机，只改server\_id不行  
> \[auto\]  
> server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026
> 
> \[root@dbsrv2 ~\]\# mv /data/mysqldata/auto.cnf  /data/mysqldata/auto.cnf.bk  \#\#\#重命名该文件  
> \[root@dbsrv2 ~\]\# service mysql restart          \#\#\#重启mysql  
> Shutting down MySQL.\[  OK  \]  
> Starting MySQL.\[  OK  \]  
> \[root@dbsrv2 ~\]\# more /data/mysqldata/auto.cnf  \#\#\#重启后自动生成新的auto.cnf文件，即新的UUID  
> \[auto\]  
> server-uuid=6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9

**2）、再次查看slave的状态已经正常**

> \[root@dbsrv1 ~\]\# mysql -uroot -pxxx -e "show slave status\\G"|grep Running  
> Warning: Using a password on the command line interface can be insecure.  
>             Slave\_IO\_Running: Yes  
>             Slave\_SQL\_Running: Yes  
>       Slave\_SQL\_Running\_State: Slave has read all relay log; waiting for the slave I/O thread to update it

**3）、主库端查看自身的uuid**

> master\_mysql> show variables like 'server\_uuid';  
> \+---------------+--------------------------------------+  
> | Variable\_name | Value                                |  
> \+---------------+--------------------------------------+  
> | server\_uuid  | 62ee10aa-b1f7-11e4-90ae-080027615026 |  
> \+---------------+--------------------------------------+  
> 1 row in set (0.00 sec)

**4）、主库端查看从库的uuid**

> master\_mysql> show slave hosts;  
> \+-----------+------+------+-----------+--------------------------------------+  
> | Server\_id | Host | Port | Master\_id | Slave\_UUID                          |  
> \+-----------+------+------+-----------+--------------------------------------+  
> |        33 |      | 3306 |        11 | 62ee10aa-b1f7-11e4-90ae-080027615030 |  
> |        22 |      | 3306 |        11 | 6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9 |  
> \+-----------+------+------+-----------+--------------------------------------+

### 4、延生参考 ###

**1）、有关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.

**2）、有关 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:  
>      a).  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.  
>     b). 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

参考地址：[http://www.linuxidc.com/Linux/2015-02/113564.htm][http_www.linuxidc.com_Linux_2015-02_113564.htm]

[http_www.linuxidc.com_Linux_2015-02_113564.htm]: http://www.linuxidc.com/Linux/2015-02/113564.htm