MySQL master-slave replication error: slave have equal MySQL server UUIDs

Recently, when I deployed The MySQL master-slave replication architecture, I encountered "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. "error message. That is, the same UUID is used in the master-slave architecture. Check the server_id System Variables. They are already different settings. Why? The following is a detailed description.

1. Error message

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. View the server_id variable of the master/slave node.

Master_mysql> show variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 33 |
+ --------------- + ------- +
Slave_mysql> show variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 11 |
+ --------------- + ------- +
-- From the above situation, we can see that the master and slave mysql have used different server_id

3. Troubleshooting

### View the auto. cnf file
[Root @ dbsrv1 ~] Cat/data/mysqldata/auto. cnf ### uuid on the master
[Auto]
Server-uuid = 62ee10aa-b1f7-11e4-90ae-080027615026
[Root @ dbsrv2 ~] # More/data/mysqldata/auto. cnf ### the previous uuid already exists. The reason is that the virtual machine is cloned, but the server_id cannot be changed.
[Auto]
Server-uuid = 62ee10aa-b1f7-11e4-90ae-080027615026
[Root @ dbsrv2 ~] # Mv/data/mysqldata/auto. cnf/data/mysqldata/auto. cnf. bk ### rename the file
[Root @ dbsrv2 ~] # Service mysql restart ### restart mysql
Shutting down MySQL. [OK]
Starting MySQL. [OK]
[Root @ dbsrv2 ~] # More/data/mysqldata/auto. cnf ### automatically generate a new auto. cnf file after restart, that is, the new UUID
[Auto]
Server-uuid = 6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9
### Check whether the slave status is normal again
[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
### View the uuid of the master database

Master_mysql> show variables like 'server _ uuid ';
+ --------------- + -------------------------------------- +
| Variable_name | Value |
+ --------------- + -------------------------------------- +
| Server_uuid | 62ee10aa-b1f7-11e4-90ae-080027615026 |
+ --------------- + -------------------------------------- +
1 row in set (0.00 sec)
### View the uuid of the slave database on the master database
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 |
+ ----------- + ------ + ----------- + -------------------------------------- +
### Author: Leshami

The following is an English translation.

4. Yansheng reference

1. SERVER_ID description

Server ID, which is used by REPLI
The unique identifier of each host and slave. This variable is set
By The-server-id option. For each server involved in replication, you should select
The server ID is a positive integer ranging from 1 to 232-1 (the 32 power of 2 minus 1.

B. Description of server_uuid

Starting from MySQL 5.6, the server generates a real UUID in addition to-server-ID.
Provided by users. This is a global, read-only variable server_uuid (Global read-only variable)

At startup, the following MySQL server will automatically obtain a UUID:
One ). Read and use the UUID written in the DATA_DIR/auto. cnf File (where DATA_DIR is
In the data directory of the server); exported successfully.
B. Otherwise, generate a new UUID and save it to this file. If necessary, create a file.
The auto. cnf file has a format similar to the file used for my. cnf or my. INI files. In MySQL 5.6,
Auto. cnf only has one [car] that contains one server_uuid [1992] setting section,
Value;

Important

The auto. cnf file is automatically generated; you should not try to write
Or modify this file.

It also starts with MySQL 5.6 and uses MySQL replication. When the master and slave know one
Another is UUID. The UUID value of a SLAVE can be seen in the output of show slave hosts. Once
Start slave has been executed (rather than before), the UUID value of the master can be in
SLAVE output in show slave status.

In MySQL 5.6.5 or later, the server_uuid of the server is also used in GTIDs to initiate a transaction.
On this server. For more information, see 16.1.3, "Global Transaction replication