Possible MySQL server UUID duplication for server, uuidduplication

Possible MySQL server UUID duplication for server, uuidduplication

This event occurs during mysql enterprise monitor monitoring. The Topic: Possible MySQL server UUID duplication for server event appears. From the description of this prompt, it seems that a duplicate uuid exists, in fact, there is no duplicate uuid in the master-slave relationship. The master-slave relationship is built through xtrabackup. So where is the problem? The following describes the problem that duplicate uuid occurs during monitoring based on xtrabackup replication.

1. Fault
Topic: Possible MySQL server UUID duplication for server afd6bca4-6636-11e3-9d60-74867ae1c47c: NOTICE
Categories: Monitoring and Support Services
Current State: Open
Auto-Closes by Default: Yes
Advisor: Duplicate MySQL Server UUID
Current Status: Notice
Last Checked: May 4, 2015 2:18:02

### Prompt that the uuid has changed N multiple times. During the MySQL Instances monitoring meeting, the configurations of the two hosts are continuously Switching
MySQL server afd6bca4-6636-11e3-9d60-74867ae1c47c changed its hostname 181 times between the following hostnames:
DBSRV-TXT01
DBSRV-SLAVE02

MySQL server afd6bca4-6636-11e3-9d60-74867ae1c47c changed its connection TCP properties 96 times between the following TCP properties:

127.0.0.1: 3306
127.0.0.1: 3307

Advice
Check that you are not monitoring more than one instance with the following server UUID: afd6bca4-6636-11e3-9d60-74867ae1c47c.
This can be caused by server or host cloning. If this is expected (example: HA scenarios), then please ignore this notice.

2. Verify uuid
### As described in google, mysql. inventory stores uuid information used by mysql enterpirse moitor
### View the uuid and mysql. inventory tables on the master database
[Root @ DBSRV-TXT01 ~] # Mysql-uroot-p-e"
Show variables like 'server _ uuid ';
Select * from mysql. inventory; "-S/tmp/mysql3307.sock
Enter password:
+ --------------- + -------------------------------------- +
| Variable_name | Value |
+ --------------- + -------------------------------------- +
| Server_uuid | 1ed85852-dd27-11e4-aa4a-44a8420ba7a5 |
+ --------------- + -------------------------------------- +
+ -------- + ------------------------------------------------------- +
| Name | value |
+ -------- + ------------------------------------------------------- +
| Uuid | afd6bca4-6636-11e3-9d60-74867ae1c47c |
| Hostid | ssh: {8a: c7: a9: 42: 3a: 6b: 06: ad: fa: ed: 04: ac: a5: fa: f0: b5} |
+ -------- + ------------------------------------------------------- +

### View the uuid and mysql. inventory tables in the slave Database
[Root @ DBSRV-SLAVE02 ~] # Mysql-uroot-p-e "--- Author: Leshami
> Show variables like 'server _ uuid '; --- Blog: http://blog.csdn.net/leshami
> Select * from mysql. inventory ;"
Enter password:
+ --------------- + -------------------------------------- +
| Variable_name | Value |
+ --------------- + -------------------------------------- +
| Server_uuid | the f7e00194-2f59-11e4-bcf6-b82a72d46b21 |
+ --------------- + -------------------------------------- +
+ -------- + ------------------------------------------------------- +
| Name | value |
+ -------- + ------------------------------------------------------- +
| Uuid | afd6bca4-6636-11e3-9d60-74867ae1c47c |
| Hostid | ssh: {8a: c7: a9: 42: 3a: 6b: 06: ad: fa: ed: 04: ac: a5: fa: f0: b5} |
+ -------- + ------------------------------------------------------- +

### The preceding query results show that the same uuid is saved in the mysql. inventory table.
### This same uuid is because instance-level hot standby is used, so the two instances have the same uuid

### Clear mysql. inventory, restart the Monitoring agent (omitted), and solve the problem
[Root @ DBSRV-SLAVE02 ~] # Mysql-uroot-p-e "truncate table mysql. inventory"-S/tmp/mysql3307.sock
Enter password:

3. MySQL mem uuid duplication

MySQL Enterprise Monitor uses a number of unique values known as UUIDs to identify the different components, including the MySQL instance being monitored. UUID values related to the MySQL instance and the host on which it runs are stored in a tablemysql.inventoryWithin the instance. MySQL Enterprise Monitor creates this table if it does not exist already.

• Each MySQL Server has a UUID, stored inmysql.inventoryTable, that uniquely identifies the MySQL server to the rest of MEM. The server UUID is used to collate information about a single MySQL instance.

• Each host (the machine on which the agent is running) has a UUID to uniquely identify the host to the rest of MySQL Enterprise Monitor. this is used to collate the OS information (such as CPU, RAM and disk data ). the host ID also determines whether the MySQL server is on the same host as it was before, to identify when data has been moved between machines, or when a machine has been upgraded. the host UUID is stored withinhostidRow withinmysql.inventoryTable.

• Each agent has a UUID to identify the agent to MEM. The agent UUID is defined withinagent-uuidParameter within the agent configuration file.

These UUIDs are used in combination to register and collate information, and to determine the location and source of an issue.

Note

Because each host must be unique, be careful when restoring from a backup so you do not have hosts with duplicated SSH keys or UUIDs.

Http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-introduction-mysql-server.html

The MySQL Enterprise Monitor Agent and MySQL Enterprise Service Manager use the unique host ID, stored withinmysql.inventoryTable on the monitored MySQL Server, to determine whether the instance being monitored is a clone. the host ID of the current server is checked against the stored value when the agent starts. if the generated host ID and stored host ID do not match, you get an error similar to the following in the agent log file:

%s: [%s] the hostid from mysql.inventory doesn't match our agent's host-id (%s != %s)We assume that this is a cloned host and shutdown now.Please TRUNCATE TABLE mysql.inventory on this mysql-instance and restart the agent.If this is a master for replication, please also run SET SQL_LOG_BIN = 0; first.

To fix the problem, connect to the MySQL server using the credentials configured when you installed the agent, and then truncatemysql.inventoryTable:

mysql> TRUNCATE mysql.inventory;

Now restart the agent, which recreatesmysql.inventoryTable with the updated instance UUID and hostid information.

Http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-troubleshooting-agent-start.html