首頁  >  文章  >  資料庫  >  伺服器的 MySQL 伺服器 UUID 可能重複

伺服器的 MySQL 伺服器 UUID 可能重複

黄舟
黄舟原創
2017-02-15 10:40:281359瀏覽



    在mysql enterprise monitor監控過程中出現這樣的event事件,Topic: Possible MySQL server UUID duplication for server 事件,從該提示的描述來看似貌似是存在重複的uuid,而實際上主從關係uu並不存在重複的uuid。主從關係是透過xtrabackup來建構的。那到底是哪裡的問題呢?下文是描述基於xtrabackup複製時導致監控出現重複uuid的問題。


1、故障現象

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 PM

#of會出現這2個主機配置在不停的切換中

MySQL server afd6bca4-6636-11e3-9d60-74867ae1c47c changed its hostname 181 times between the following hostnames:DBSRV-TXT01DBSRV-SLAVE02
MySQL server afd6bca4-6636-11e3-9d60-74867ae1c47c changed its connection TCP properties 96 times between the following TCP properties:
127.0.0.1:3306127.0.0.1:3307
AdviceCheck 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、校驗uuid
sql

2、校驗uuidsql書被監控到聖經中被監控了用文的uuid資料

###查看主庫上的uuid及mysql.inventory表

[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} |
+--------+-------------------------------------------------------+

[root@DBSRV-SLAVE02 ~]# mysql -uroot -p -e "    ---Author : Leshami
> show variables like 'server_uuid';            ---Blog   : 
http://www.php.cn/
> select * from mysql.inventory;"
Enter password: 
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 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} |
+--------+-------------------------------------------------------+


###從上面的查詢結果得知,mysql.inventory表裡邊確實保存了相同的uuid

###這個相同的實例是因為使用了這個相同的實例。備,所以2個實例具有相同的uuid

###清空mysql.inventory,然後重啟監控agent(略),問題解決

[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 table mysql.inventory within      
the instance. MySQL Enterprise Monitor creates this table if it does not exist      
already.


Each MySQL Server has a UUID, stored in the          rest of MEM. The server          UUID is used to collat​​e information about a single MySQL          instance.

Each host (the machine on which the agent is running) has a   This is used to collat​​e 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 wasefore, to ident idaeen has          been upgraded. The host UUID is stored within the           hostid

row within the          
    mysql.inventory
  • table.

    UID is defined within the agent-uuid          parameter within the agent configuration file.與

    when restoring from        a backup so you do not have hosts with duplicated SSH keys or        UUIDs.
  • The MySQL Enterprise Monitor Agent and MySQL Enterprise Service Manager use the unique host    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 not host ID and store  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          truncate the mysql.inventory table:

    mysql> TRUNCATE mysql.inventory;

    Now restart the agent, which recreates the           mysql.inventory table with the updated          instance UUID and hostid information.

    以上就是Possible MySQL server UUID duplication for server 的内容,更多相关内容请关注PHP中文网(www.php.cn)!

    

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn