Installing MEM agent in a cluster on the physical hosts

To install the MEM agent in a way that both physical servers are listed in the MEM dashboard, you have to install the agent on both physical nodes. But: Do not start the agent after the installation!

There are three different IDs in MEM: agent-uuid, mysql-uuid and host-id. Usually they are generated automatically and you will never notice these IDs. For more information about the meaning of the different IDs look at this very good explanation from Jonathon Coombes.

The agent stores the uuid and the hostid in a MySQL table called mysql.inventory. After a failover the other agent on the new node will notice "wrong" hostid and uuid entries in the inventory table. The agent will stop and ask you to TRUNCATE mysql.inventory. But with this procedure MEM creates a new instance, so all old data is lost. Not good for a failover environment.

So in case of a failover you have to provide the mysql.inventory table, that the agent expects.

And here is how you can achieve this:

1. Install the MEM agent on the node that currently runs MySQL. Start the agent.
   
   
2. Make a table that stores the hostid and uuid for every physical host:
   USE mysql;
   CREATE TABLE inventory_hostname LIKE inventory;
   DROP INDEX `PRIMARY` ON inventory_hostname;
   ALTER TABLE inventory_hostname ADD COLUMN (hostname VARCHAR(64));
   ALTER TABLE inventory_hostname ADD PRIMARY KEY (hostname,name);
   INSERT INTO inventory_hostname SELECT *,@@hostname FROM inventory;
   SELECT * FROM inventory_hostname;
   
   The newly created table should look like this:
   

   +--------+--------------------------------------+----------+
   | name   | value                                | hostname |
   +--------+--------------------------------------+----------+
   | uuid   | 96936e90-56bd-4eb1-aef3-e708d149a4cb | wclus-1  |
   | hostid | mac:{005056a138c10000}               | wclus-1  |
   +--------+--------------------------------------+----------+

   
   (Notice that the hostid is based on the mac address in my case. Usually this is the public ssh host key.)
   
   
3. Stop the agent
   
   
4. Empty the inventory_table:
   TRUNCATE mysql.inventory;
   
   
5. Failover the MySQL instance to the other node.
   
   
6. Install and start the agent on the other node. It will save new values in inventory.
   
   
7. Copy these new values to the inventory_hostname table:
   USE mysql;
   INSERT INTO inventory_hostname SELECT *,@@hostname FROM inventory;
   Both nodes should be visible in MEM dashboard right now.
   
   With every failover we need to populate the inventory table with the host-specific rows. Easiest way (and independent on the operating system and cluster framework) is to define an init-file:
   
   
8. On both nodes create a file named [MySQL Basedir]/mysql_init_HA_MEM.sql with the following statements:
   USE mysql;
   REPLACE INTO inventory SELECT name,value FROM inventory_hostname WHERE hostname=@@hostname;
   
   
9. On both nodes edit you my.cnf or my.ini file. In the section [mysqld] add the following line:
   init-file=[MySQL Basedir]/mysql_init_HA_MEM.sql
   (If you already have an init-file defined you can add the commands "USE mysql; REPLACE..." to you init-file.)
   
   
10. Start all agents and try to failover the MySQL instance. Check that you init-file really modifies the inventory table.