How to shutdown and restart complete Galera Cluster for MySQL Setup

[Alok Pathak]

Hi Everyone,

I've just configured a Galera cluster for MySQL with 3 nodes. I've added below given setting in wsrep.conf file

wsrep.conf
Node1 : wsrep_cluster_address="gcomm://IP1,IP2,IP3"
Node2 : wsrep_cluster_address="gcomm://IP1,IP2,IP3"
Node3 : wsrep_cluster_address="gcomm://IP1,IP2,IP3"

Starting Cluster
Node1 : service mysql start --wsrep_cluster_address="gcomm://"
Node2 : service mysql start
Node3 : service mysql start

As expected, Node1 started a new cluster and other 2 nodes joined the cluster successfully and all 3 nodes were working fine. Now I had to shut down the all 3 machines of node1,2,3 as power cut was scheduled. So I did

Node1 : init 0
Node2 : init 0
Node3 : init 0

Now I tried to restart the machines of all 3 nodes and as mysql is added as startup service in each machine. It tried to start mysql on startup but startup of mysql failed on all nodes because it couldn't start a new cluster and other nodes didn't find any cluster to join. So I manually did.

Node1 : service mysql start --wsrep_cluster_address="gcomm://"
Node2 : service mysql start
Node3 : service mysql start

After doing this I was able to restart cluster and it was running fine. Now my questions is... Is it mandatory to manually start the new cluster if we want to restart the complete cluster setup. Suppose if all 3 machines in data center rebooted due to some error or power-cut in that case there will not be any automatic way to restart a new cluster so that other nodes can join that. Is there any way by which we can manage to start a new cluster if all other nodes are also down. Since manual start of cluster is required so we may face a downtime of some hours.

I agree that we cannot keep the wsrep_cluster_address="gcomm://" in config of Node1 because it will start a new cluster every time it gets rebooted.

Regards,
Alok

 

[Jay Janssen]

On May 10, 2013, at 6:44 AM, Alok Pathak <alo...@gmail.com> wrote:

. Is it mandatory to manually start the new cluster if we want to restart the complete cluster setup.

Yes

Suppose if all 3 machines in data center rebooted due to some error or power-cut in that case there will not be any automatic way to restart a new cluster so that other nodes can join that. Is there any way by which we can manage to start a new cluster if all other nodes are also down.

You don't want automation here.  Why do you assume all nodes went down simultaneously?  One may be further than the others, and that's the one you want to start first.  Manual intervention in this case is by design.

Since manual start of cluster is required so we may face a downtime of some hours.

Then you want some cluster node(s) /arbitrator(s) that are impossible to be affected by your datacenter power failure.   (or at least nodes on multiple UPS)

Jay Janssen, MySQL Consulting Lead, Percona

http://about.me/jay.janssen

[CesarJ M]

Hi,

We have encountered the same problem.
Restarting the three nodes, mysql not start at nothing (A scheduled stop of cluster, for maintenance, not a turn off all machines)
We resolved this (one simple scenario: exists one /var/lib/mysql/grastate.dat with safe_to_bootstrap: 1):
Execute as root:
echo "@reboot sleep 40; /sbin/service mysql bootstrap; sleep 10; /sbin/service mysql start" | crontab -

Regards,
Cesar Jorge