How to make MySQL cluster completely failsafe?

Question

So I want to set up a MySQL database server with high availability - thus I got myself two devices (Banana Pi) and followed a tutorial on how to install the MySQL cluster software. I know that a constellation of only two devices is not exactly how the MySQL cluster is meant to be used, but for saving hardware costs I only got those two devices for now.

So far I've got the following configuration (sqlnode1 and sqlnode2 are the host names of the devices):

Management nodes (ndb_mgmd):

• sqlnode1 (node id: 1)
• sqlnode2 (node id: 2)

Storage engines (ndbd):

• sqlnode1: configured to use the management node at localhost (node id: 3)
• sqlnode2: configured to use the management node at localhost (node id: 4)

MySQL servers (mysqld):

• sqlnode1: configured to use both ndbd nodes (node id: 5)
• sqlnode2: configured to use both ndbd nodes (node id: 6)

So basically, every device has a running ndb_mgmd, ndbd and mysqld.

What I'd like to achieve is that if one of the devices fails, the other device should be completely usable (for some very specific reasons I can't use simple database replication here).

Once I start all the components, everything is working fine. If I change data on one MySQL host, the other one applies the changes immediately (and vice versa).

However, if I disconnect the LAN cable of one of the devices, the following happens:

• Disconnecting sqlnode2: The functioning node (sqlnode1) hangs for a short period of time and then continues to work properly (which is basically what I want)
• Disconnecting sqlnode1: The functioning node (sqlnode2) hangs for a short period of time and then doesn't work at all - all queries end in "4009 Cluster Failure" and queries only start working again after restoring the network connection of the node which has dropped out. After that, monitoring the ndb_mgmd daemon shows that node id 4 (the ndbd on sqlnode2) has been forced to shut down.

In summary, the whole system seems to depend on sqlnode1 and I really wonder why this is happening because I thought that if a component drops out (and another one of the same type is available), everything continues running.

I followed this tutorial for setting up two different management nodes: http://www.clusterdb.com/mysql-cluster/deploying-mysql-cluster-over-multiple-hosts

Did I miss anything? Or maybe I misunderstood how MySQL clustering works?

Answer 1

I resolved my problems by switching to Galera MySQL cluster which seems to do exactly what I want / need.

Answer 2

I haven't used Mysql Cluster but you should be able to do replication in it, also mysql cluster does quite a bit more than you are probably needing, if all you need is replication. MySQL cluster has the capability of routing to different sql servers on the fly if one goes down, thus reducing the need to modify the dns records of the host name everytime or setting up a temporary sql server or just letting it be dead until fixed. It can do other stuff as well. So to help you out, I suggest using the MySQL community edition as its more user friendly for you than mysql cluster.

Grab the mysql community edition, this will allow you to have replication, thus, you can set 1 mysql server as the master where everthing is edited and the second mysql server is the slave and it can just retrieve what the master has at a configurable interval. You can easily add more slaves in the future as well. Also, the slaves don't necessarily have to be on separate machines also, you can run more than 1 instance of MySQL. However this would defeat the purpose of replication and being failsafe. This is most likely what you are looking for. Also, mysql has a guide on their website for setting up replication for mysql community edition.

MySQL Community Server