MHA集群报错:The slave I/O thread stops because master and slave have equal MySQL server UUIDs
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 variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
从库:
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.01 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
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