Issues Heartbeat and Mon for SQL Node Failover in MySQL Cluster
 

Hi,

I set up MySQL cluster, which went fine but then I needed a mechanism to provide failover between the two sql nodes in my cluster. I searched online for software that would be appropriate for this and what I found was mainly t he use of Heartbeat and Mon.

The problem is that:

A) it appears as if the IP is tied to both boxes and in fact if I run a ping to the virutal IP the pings will hit both boxes:

http://pastebin.com/5jN0fZ1Y

B)Killing the MySQL instance doesn't cause anything to happen (I checked all error logs and nothing even registers the failure aside from the mysql error logs)

The output from the management node on the current state of my cluster:

http://pastebin.com/rKXwYGkk

The heartbeat configurations:

http://pastebin.com/FdY7jJ8u

The Mon Configurations:

http://pastebin.com/zyj8V4YC

Can anyone point me in the right direction on how to make this work?

Thanks.

Hello,

Just for clarity sake, did you set up MySQL Cluster (the product!) or a couple of stand alone MySQL boxes that you want to use in a failover setup?

Kind regards,

Eric

Mysql Cluster - the software.

2 management nodes
2 data nodes
2 sql nodes

Everything went find in sanity testing.

Hi,

If you use MySQL Cluster then why would you use HeartBeat and Mon on top of that? MySQL Cluster already takes care of that entirely. No need for an additional layer, you already have HA for MySQL. I'm not quite sure I understand what you want. Unless you're setting that up for the management nodes, which is unnecessary too.

Kind regards,

Eric

Is there some built in way of failover using a single IP that I am unaware of? I have a string of components who can only point to a single IP and am a student writing a thesis and thus not a liberty to mess with the components I started out with.

I am open to any option out there than can allow me to use a single IP to query a single mysql node at a time and failover when that one fails. I am not picky but heartbeat was the only one I found some documentation on.

Hi,

You can use something like mysql-proxy for that. Mysql-proxy, which will work as a 'traffic controller', is used by the clients (your components) as the MySQL hostname/instance and will be completely transparent in doing so. The clients won't know if a server goes down, mysql-proxy does automatic load-balancingj, etc... Of course if you only have one mysql-proxy than that will become your single point of failure in your setup between clients and your MySQL cluster. But you could use Corosync and Pacemaker to put that in HA on two boxes for example.

Kind regards,

Eric

If we are going to want to get into specifics of my Cluster setup I think I should put the following (very unpopular I might add) configurations forward:

1) I am making use of both NDB and MyISAM storage engines.
2) The MyISAM data is saved locally on the sql nodes and asynchronous mysql replication is happening between the two nodes
3) My components reading from and writing to the DB can only query a single IP
4) The mysql servers not only the data have to be highly available
5) Don't ask me why I can't just use only NDB tables because using multiple storage engines is built into my thesis

I beleive those are the only abnomalities about my model

the mysql-proxy software is considered in alpha mode and not recommended for production

http://dev.mysql.com/doc/refman/5.5/en/mysql-proxy.html