在部署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变量
master_mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 10 |
+---------------+-------+ slave_mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 11 |
+---------------+-------+
-- 从上面的情形可知,主从mysql已经使用了不同的server_id 3、解决故障
###查看auto.cnf文件
[root@dbsrv1 ~] cat /data/mysqldata_3306/auto.cnf ### 主上的uuid
[auto]
server-uuid=9a5386d8-cd13-11e8-96cb-c68297e2a5d7 [root@dbsrv2 ~]# more /data/mysqldata_3306/auto.cnf ###从上的uuid,果然出现了重复,原因是克隆了虚拟机,只改server_id不行
[auto]
server-uuid=9a5386d8-cd13-11e8-96cb-c68297e2a5d7 [root@dbsrv2 ~]# mv /data/mysqldata/auto.cnf{,.20181031} ###重命名该文件
[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 ###再次查看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
###主库端查看自身的uuid
master_mysql> show variables like 'server_uuid';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid |9a5386d8-cd13-11e8-96cb-c68297e2a5d7 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
###主库端查看从库的uuid
master_mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 11 | | 3306 | 10 | 8691da38-dce7-11e8-be00-9268b554fce7 |
+-----------+------+------+-----------+--------------------------------------+