MySQL Bugs: #42360: Support for MySQL + DRBD failover

Bug #42360	Support for MySQL + DRBD failover
Submitted:	27 Jan 2009 2:12	Modified:	30 Nov 2010 9:59
Reporter:	Meiji KIMURA	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Agent	Severity:	S4 (Feature request)
Version:	2.0	OS:	Any
Assigned to:	Mark Matthews	CPU Architecture:	Any
Tags:	ha

Description:
I know that latest MEM(MySQL Enterprise Monitor) doen't support any HA configuration, so agents don't support also that configuration.

I know that the feature request to MEM already filed as below,
Bug#39199

But please let me explain to concrete request to support specific configuration.

Recently many customer try to make a configuration of MySQL with DRBD as a reasonable HA configuration. Under this configuration, I prepare two machines(Active/Standby), so DRBD mirrors from Active to Standby. The heartbeat check Active machine and it will failover to Standby machine when Active machine fails.

I want to continue to Standby machine after failover, but now it is very difficult to do it. Please implement (or confirm) the feature(or procedure) described in 'Suggested fix:'.

How to repeat:
Assume two machines for MySQL + DRBD. One is for Active, another is for Standby.

(1) Install DRBD for mirroring from Active to Standby.
(2) Install agents into each machines as local agent.
(3) I can monitor Active machine with MEM.
(4) After failover, I activate the agent for Standby machine.

But mysql.inventory alread mirrored from Active machine to Standby machine, so 
this error displayed in Agent log of Standby machine like this,

2009-01-26 13:53:03: (critical) C:\cygwin\home\mysqldev\bs\merlin\agent-2.0\src\mysql-proxy-0.7.0r1185\plugins\agent\agent_mysqld.c:533: [mysql] the hostid from mysql.inventory doesn't match our agent's host-id (mac:{00166f6d8cb40001} != mac:{00166f6d8cb40000})
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.
2009-01-26 13:53:04: (critical) shutting down normally

Suggested fix:
[Workaround]

(1) Configuring an Agent to Monitor a Remote MySQL Server
http://dev.mysql.com/doc/refman/5.1/en/mem-install-agent-remote.html

  If I install an agent to Monitor a Remote(not local) MySQL Server and specify Virtual IP for that agent, it will work well after failover. But there is a limitation for getting an information OS and CPU of a machine of monitored MySQL. It is a severe limitation for DBAs.

(2) TRUNCATE mysql.inventory table on a standby machine before activating an agent for it.

  mysql.inventory was created when an agent was installed on an Active machine, so it was mirrored from Active machine to Standby machine. It cause the error message of 'How to repeat:' by inconsistency of host-id's.

I can continue to run by trancating mysql.inventory, but that operation re-create mysql.inventory on a standby machine and old mysql.inventory on MEM dashboard won't be deleted, so each operation makes old trash item. I don't know how to delete these items.

[Suggested fix]
(a) Please confirm to an operation of (2) (and add method for deleting obsoleted item on MEM, or other method if needed) as an authorized procedure.
(b) Implement features for (a) as a new feature of MEM(or Agents).

Regards,

[Suggested fix]
(3) provide a method to take over contens of mysql.inventory from Active to Client.

An example of contents of mysql.inventory is,
+--------+--------------------------------------+
| name   | value                                |
+--------+--------------------------------------+
| uuid   | 2273a4a8-4469-4779-9750-66bb33251bda |
| hostid | mac:{001c239da3e80000}               |
+--------+--------------------------------------+

uuid is changed by re-creating, but hostid is same.

I guess hostid is generated by these (1) or (2) methods.

(1) 'hostid' is generated by an identifier based on physical address of ethernet connection without SSL. (Here is an output of ipconfig /all on windows.)

Ethernet adapter local area connection:
   Connection-specific DNS Suffix:
   Description . . . . . . . . . : Broadcom 440x 10/100 Integrated Controller
   Physical Address. . . . . . . : 00-1C-23-9D-A3-E8

(2) 'hostid' is generated by using these command. It is displayed by a warning message of an agent.

(warning) agent.c:428: extracted the host-id from 'sh -c "ssh-keygen -l -f /etc/ssh/ssh_host_key.pub | awk '{print \"ssh:{\"$2\"}\"}'"'

I know that it is difficult to change physical address of (1), but it is easy to change an agument of (2) like,

'sh -c "ssh-keygen -l -f ssh_host_key_of_active_machine | awk '{print \"ssh:{\"$2\"}\"}'"'

Please consider this option when you implement new features for this feature request.

Regards,

Possibly duplicate of 40695.

We can use 'agent-host-id=HOSTID' for this configuration. See more details about this option, please refer these manual of MEM2.2.

[English]
http://dev.mysql.com/doc/mysql-monitor/2.2/en/mem-deployment-config-agent-options.html#opt...

[日本語]
http://dev.mysql.com/doc/mysql-monitor/2.2/ja/mem-deployment-config-agent-options.html#opt...

As a result, I closed this issue.

See also this, But#49260.

http://bugs.mysql.com/bug.php?id=49260

Now it is documented on MEM 2.0. So I supposed that this function is supported MEM 2.0 or later.

http://dev.mysql.com/doc/mysql-monitor/2.0/en/mem-deployment-config-agent.html#option_mysq...