MySQL Cluster on Raspberry Pi - Sub-second failover

MySQL Cluster claims to achieve sub-second failover without any data loss for commited transactions. And I always wanted to show this in a demo. Now we created that demo finally. See Mark's blog and Keith's blog for setting up MySQL Cluster on RaspberryPi.
The nice thing about the RPis is that you can easily pull the plug to test failover. Ok, that is only one possible failure scenario but for sure the most obvious and more impressive than "kill -9".

That demo application is constantly using the database for storing new lines, removing old lines and reading all line data for the graphical view. There is no caching. It uses JDBC directly.
To document the setup here is the config.ini file for MySQL Cluster:

[ndb_mgmd]
hostname=192.168.0.101
NodeId=1

[ndbd default]
diskless=1
noofreplicas=2
DataMemory=2M
IndexMemory=1M
DiskPageBufferMemory=4M
StringMemory=5
MaxNoOfConcurrentOperations=1K
MaxNoOfConcurrentTransactions=500
SharedGlobalMemory=500K
LongMessageBuffer=512K
MaxParallelScansPerFragment=16
MaxNoOfAttributes=100
MaxNoOfTables=20
MaxNoOfOrderedIndexes=20
HeartbeatIntervalDbDb=10

[ndbd]
hostname=192.168.0.6
datadir=/home/pi/mysql/ndb_data
NodeId=3

[ndbd]
hostname=192.168.0.11
datadir=/home/pi/mysql/ndbd_data
NodeId=4

[mysqld]
NodeId=50

I made the cluster diskless so it will not write any logs and table spaces to disk. The SD card performance was not great and it does not affect failover behavior.
I also reduced the HeatbeatIntervallDbDb so that nodes detect immediately (well, 10ms) if a heartbeat is missed. After a few missed heartbeats cluster reconfigures and the remaining node takes responsibility and service continues.
BTW: Pulling the plug is nice but every now and then I had to manually fsck the root-fs during reboot.

Installing MEM agent on a cluster on the logical host

The goal is to have only one entry in the Enterprise Monitor Dashboard that shows the status of the MySQL instance, no matter on which physical server in runs. There are two ways to achieve this:

• You can install the agent on both physical nodes
• You can install the agent on a shared storage.

In either case you have to make sure, that only one agent runs at a time. You have to integrate the agent into your cluster framework. I will not describe how this works, as it is highly dependant on your cluster framework.

The following description assumes, that you will install the agent on both physical nodes.

1. Install the agent but DO NOT START the agent yet.
   
   
2. Edit the [agent-installdir]/etc/mysql-monitor-agent.ini
   In the [mysql-proxy] section add the following line:
   agent-host-id=[logical hostname]
3. Do steps 1. and 2. for the other cluster node as well.
   
   
4. Include the agent in the cluster's failover group so that it will start automatically on that node, where the MySQL instance is running.
   
   
5. Start the agent via the cluster framework.
   
   
6. The entry in MEM will get the hostname of the first node that runs MySQL. Even after a failover this name will stay. You should change that name to the virtual hostname of the virtual ip adress of your MySQL service. Use the MEM BUI, choose the tab "Settings". From the secondary menu right below the tabs choose "Manage Servers". If you move the mouse over the server name a submenu will appear. Choose "Rename" and enter the name of the virtual IP.

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.