mariadb, galera cluster, haproxy, keepalived Problem On Recovery

[chrys...@trading-point.com]

hello guys

Recently I've setup a galera cluster with haproxy/keepalived as follow

*3 nodes setup
Node1 act as writing node
node2,3 are for reading. ( in case of failure of node1 , node2 will be the writing node )

* i have haproxy-keepalived on all 3 machines.
* i have a VIP pointing at node1 but the reading sequence configured on haproxy is node2,node3,node1.

my problem is when i was testing the failover scenario.
I shutdown node1 and everything moved to node2/3 ( node2 took the place of writing node )

after getting back node1 the mysql was not able to start as was not able to sync from the other 2 nodes.


my configuration on 3 nodes is as following

#node1
binlog_format = ROW
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name = "cluster"
wsrep_cluster_address = gcomm://
wsrep_node_address = 192.168.116.68
wsrep_node_name = 'MariaDBNode1'
wsrep_node_incoming_address = 192.168.116.68
wsrep_sst_method = rsync
wsrep_sst_donor = 192.168.116.69,192.168.116.70

#node2
binlog_format = ROW
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name = "cluster"
wsrep_cluster_address = gcomm://192.168.116.68,192.168.116.70
wsrep_node_address = 192.168.116.69
wsrep_node_name = 'MariaDBNode2'
wsrep_node_incoming_address = 192.168.116.69
wsrep_sst_method = rsync

#node3

binlog_format = ROW
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name = "cluster"
wsrep_cluster_address = gcomm://192.168.116.68,192.168.116.69
wsrep_node_address = 192.168.116.70
wsrep_node_name = 'MariaDBNode3'
wsrep_node_incoming_address = 192.168.116.70
wsrep_sst_method = rsync


please note that before my final testing i had 
wsrep_cluster_address = gcomm://192.168.116.68,192.168.116.69,192.168.116.70
on all nodes.

i changed it now as shown on the above configuration 

i would appreciate if you could help me.

[Donovan Sydow]

Your node1 configuration has no cluster nodes defined for  wsrep_cluster_address. You should have all other nodes in that list, otherwise this tells mysql to bootstrap (i.e. I am the master node that all others should sync from). As soon as you add the other nodes in there and restart mysql it should grab down all changes or rebuild itself from one of the other nodes.

[chrys...@trading-point.com]

Donovan

thanks

that's how i had it before i changed it to that option.

ive changed it back , the only change i've also done is to specify the wsrep_sst_auth username and password

will try again shutting node 1 and see if it will work.

do you suggest anything else?

[Will Fong]

Hi,

> On Feb 5, 2015, at 7:22 PM, chrys...@trading-point.com wrote:
>
> after getting back node1 the mysql was not able to start as was not able to sync from the other 2 nodes.

Can you provide the error logs from all three nodes when node1 tries to join back into the cluster?


Thanks,
-will


--
Will Fong, Support Engineer
MariaDB Corporation

[chrys...@trading-point.com]

WIll,

I've tried it again

shut down node 1 

created new db, tables entries from real2 and when node 1 was back online it took all the changes.

i am guessing the changes i've done setting sst_auth user:pass and putting back wsrep_cluster_address done the magic will try it a bit more and get back to you

[chrys...@trading-point.com]

Seems it works i've tried it again and db restored successfully.