is there a way to take node 'offline', I mean disconnect from the
cluster so wsrep_local_state will be 0?
The idea is to do maintenance on the node without stopping the whole
mysql daemon.
> is there a way to take node 'offline', I mean disconnect from the > cluster so wsrep_local_state will be 0? > The idea is to do maintenance on the node without stopping the whole > mysql daemon.
> Thx > Simon
mysql> SET GLOBAL wsrep_cluster_address='';
will do exactly what you want. But are you sure it is what you want? ;) What sort of maintenance this could be?
Maybe maintenance is the wrong word but I'm searching for a solution
to have a cold-standby instance when doing deployments with critical
database changes. Can't I just bootstrap a new cluster on this
standalone instance in case of a rollback scenario? Of course I have
to add addtl. nodes again afterwards, maybe after resetting somehow
nodes used in the previous cluster. This scenario could save a lot of
time when I'm working with bigger databases.
On 12 Feb., 16:01, Alex Yurchenko <alexey.yurche...@codership.com>
wrote:
> > is there a way to take node 'offline', I mean disconnect from the
> > cluster so wsrep_local_state will be 0?
> > The idea is to do maintenance on the node without stopping the whole
> > mysql daemon.
> > Thx
> > Simon
> mysql> SET GLOBAL wsrep_cluster_address='';
> will do exactly what you want. But are you sure it is what you want? ;)
> What sort of maintenance this could be?
> Maybe maintenance is the wrong word but I'm searching for a solution > to have a cold-standby instance when doing deployments with critical > database changes. Can't I just bootstrap a new cluster on this > standalone instance in case of a rollback scenario?
mysql> SET GLOBAL wsrep_cluster_address='gcomm://' will do exactly this.
CAUTION!!! This will make two clusters with the same state/history UUID. This can lead to data inconsistency. E.g. if you do some changes on this node and then rejoin it to old cluster, they may go unnoticed if you have configured IST and old cluster has more advanced sequence number. (I think this will have to be addressed in 2.1). In any case, operation on a disconnected node is highly discouraged. You don't have to be a rocket scientist to break Galera consistency control.
> Of course I have > to add addtl. nodes again afterwards, maybe after resetting somehow > nodes used in the previous cluster.
mysql> SET GLOBAL wsrep_cluster_address='gcomm://<the new cluster node>' will do exactly this.
Note however, that if those nodes are more advanced (which I as understand is the case), they will have to take full SST to be rolled back to that other node state. And for rsync/xtrabackup SST you will need to restart them. (And yes, that will reset the buffer pool, but it is still faster than mysqldump, cause mysqldump SST will totally flush it as well).
> This scenario could save a lot of > time when I'm working with bigger databases.
> On 12 Feb., 16:01, Alex Yurchenko <alexey.yurche...@codership.com> > wrote: >> On 2012-02-12 17:14, Simon Balz wrote:
>> > Hi all,
>> > is there a way to take node 'offline', I mean disconnect from the >> > cluster so wsrep_local_state will be 0? >> > The idea is to do maintenance on the node without stopping the >> whole >> > mysql daemon.
>> > Thx >> > Simon
>> mysql> SET GLOBAL wsrep_cluster_address='';
>> will do exactly what you want. But are you sure it is what you want? >> ;) >> What sort of maintenance this could be?
-- Alexey Yurchenko, Codership Oy, www.codership.com Skype: alexey.yurchenko, Phone: +358-400-516-011
<alexey.yurche...@codership.com> wrote: > On 2012-02-12 19:33, Simon Balz wrote:
>> Maybe maintenance is the wrong word but I'm searching for a solution >> to have a cold-standby instance when doing deployments with critical >> database changes. Can't I just bootstrap a new cluster on this >> standalone instance in case of a rollback scenario?
> mysql> SET GLOBAL wsrep_cluster_address='gcomm://' will do exactly this.
> CAUTION!!! This will make two clusters with the same state/history UUID. > This can lead to data inconsistency. E.g. if you do some changes on this > node and then rejoin it to old cluster, they may go unnoticed if you have > configured IST and old cluster has more advanced sequence number. (I think > this will have to be addressed in 2.1). In any case, operation on a > disconnected node is highly discouraged. You don't have to be a rocket > scientist to break Galera consistency control.
In this case it is easy to get rid of the UUID though.
Simon, can't you simply shut down one node? Then it's surely disconnected.
Your rollback scenario would then be:
1. shutdown all nodes in the botched cluster 2. on the cold standby: rm /var/lib/mysql/grastate.dat 3. start cold standby 4. add more nodes to cluster with the cold standby
Step 2 is the one that resets the UUID, now nodes from the old botched cluster cannot accidentally join this node, they will have to discard their data.
AFAIK this isn't possible without restarting MySQL. (But there seems to be many use cases now for a feature request to reset the UUID.)
> On Sun, Feb 12, 2012 at 7:58 PM, Alex Yurchenko > <alexey.yurche...@codership.com> wrote: >> On 2012-02-12 19:33, Simon Balz wrote:
>>> Maybe maintenance is the wrong word but I'm searching for a >>> solution >>> to have a cold-standby instance when doing deployments with >>> critical >>> database changes. Can't I just bootstrap a new cluster on this >>> standalone instance in case of a rollback scenario?
>> mysql> SET GLOBAL wsrep_cluster_address='gcomm://' will do exactly >> this.
>> CAUTION!!! This will make two clusters with the same state/history >> UUID. >> This can lead to data inconsistency. E.g. if you do some changes on >> this >> node and then rejoin it to old cluster, they may go unnoticed if you >> have >> configured IST and old cluster has more advanced sequence number. (I >> think >> this will have to be addressed in 2.1). In any case, operation on a >> disconnected node is highly discouraged. You don't have to be a >> rocket >> scientist to break Galera consistency control.
> In this case it is easy to get rid of the UUID though.
> Simon, can't you simply shut down one node? Then it's surely > disconnected.
> Your rollback scenario would then be:
> 1. shutdown all nodes in the botched cluster > 2. on the cold standby: rm /var/lib/mysql/grastate.dat > 3. start cold standby > 4. add more nodes to cluster with the cold standby
> Step 2 is the one that resets the UUID, now nodes from the old > botched > cluster cannot accidentally join this node, they will have to discard > their data.
> AFAIK this isn't possible without restarting MySQL. (But there seems > to be many use cases now for a feature request to reset the UUID.)
Hi Henrik,
Actually it is possible to do without restarting MySQL:
1. SET GLOBAL wsrep_provider='none'; 2. rm /var/lib/mysql/grastate.dat 3. SET GLOBAL wsrep_provider='/usr/lib64/galera/libgalera_smm.so'; 4. SET GLOBAL wsrep_cluster_address='gcomm://'; 5. add more nodes
But it is not really required, unless you decide to modify data in both clusters. Not changing UUID may actually be helpful since it allows you to remerge cold standby to original cluster without SST (given that critical database changes went alright).
Also, in order to maintain some degree of availability I'd advise to have at least 2-node cold standby _cluster_. It can be produced this way (the order is important):
1. On node1: SET GLOBAL wsrep_cluster_address=''; 2. On node2: SET GLOBAL wsrep_cluster_address=''; // more updated than node1 3. On node2: SET GLOBAL wsrep_cluster_address='gcomm://'; 4. On node1: SET GLOBAL wsrep_cluster_address='gcomm://node2'; // IST, synced with node2
<alexey.yurche...@codership.com> wrote: >> Simon, can't you simply shut down one node? Then it's surely disconnected.
>> Your rollback scenario would then be:
>> 1. shutdown all nodes in the botched cluster >> 2. on the cold standby: rm /var/lib/mysql/grastate.dat >> 3. start cold standby >> 4. add more nodes to cluster with the cold standby
>> Step 2 is the one that resets the UUID, now nodes from the old botched >> cluster cannot accidentally join this node, they will have to discard >> their data.
>> AFAIK this isn't possible without restarting MySQL. (But there seems >> to be many use cases now for a feature request to reset the UUID.)
> Hi Henrik,
> Actually it is possible to do without restarting MySQL:
> 1. SET GLOBAL wsrep_provider='none'; > 2. rm /var/lib/mysql/grastate.dat > 3. SET GLOBAL wsrep_provider='/usr/lib64/galera/libgalera_smm.so'; > 4. SET GLOBAL wsrep_cluster_address='gcomm://'; > 5. add more nodes
> But it is not really required, unless you decide to modify data in both > clusters. Not changing UUID may actually be helpful since it allows you to > remerge cold standby to original cluster without SST (given that critical > database changes went alright).
When the maintenance actions go as planned, the cold standby should of course just join back into the cluster when finished. The rm grastate.dat should be the first step only if the rollback procedure is actually executed.
I have an additional question:
What is the correct procedure to start up the origin cluster again,
after every node of the cluster was down, so I don't loose the UUID?
I mean the first node of the cluster won't find any partner when I try
to set the cluster address:
SET GLOBAL wsrep_cluster_address='gcomm://node2';
> I have an additional question: > What is the correct procedure to start up the origin cluster again, > after every node of the cluster was down, so I don't loose the UUID? > I mean the first node of the cluster won't find any partner when I > try > to set the cluster address: > SET GLOBAL wsrep_cluster_address='gcomm://node2';
> Thx > Simon
When all cluster nodes are down, you should choose the one that was down last to be the "seed" node and start it with wsrep_cluster_address='gcomm://'. Then join remaining nodes in whatever order.
Here by "down" I mean graceful shutdown - then global transaction ID is saved in grastate.dat. In case of a crash/abort you normally lose state information (as you normally have a broken state anywas) - and then a new state UUID will be generated on startup.
Regarding taking a node out of a cluster via ` SET GLOBAL wsrep_cluster_address='';` - What would be a reasonable expectation for time to complete?
I've tried setting it blank as above and also setting it to 'gcomm://' as mentioned below and both commands never return. Snippet from the node's log file which I'm trying to remove from the cluster. Last line from the log is " [Note] WSREP: rollbacker thread exiting".
As of right now - the command says it's still in progress;
# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 48 Server version: 5.5.17-22.1-log Percona XtraDB Cluster (GPL), Release 22.1, Revision 3683 wsrep_22.3.r3683
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
----- Original Message ----- From: "Alex Yurchenko" <alexey.yurche...@codership.com> To: codership-team@googlegroups.com Sent: Sunday, February 12, 2012 12:58:25 PM Subject: Re: [codership-team] Re: Disconnect from a cluster
On 2012-02-12 19:33, Simon Balz wrote: > Maybe maintenance is the wrong word but I'm searching for a solution > to have a cold-standby instance when doing deployments with critical > database changes. Can't I just bootstrap a new cluster on this > standalone instance in case of a rollback scenario?
mysql> SET GLOBAL wsrep_cluster_address='gcomm://' will do exactly this.
This is Percona's server, maybe something went wrong when they merged our patch. I suggest that you file a bug report there.
To be honest, before posting I tested those commands with the build from branch head only, so it may also be a bug in our previous release that have been fixed in launchpad.
And to answer your question - time to complete should be around 2 seconds (there are some sleeps).
Regards, Alex
On 2012-02-13 23:31, PATRICKZOBLIS...@comcast.net wrote:
> Regarding taking a node out of a cluster via ` SET GLOBAL > wsrep_cluster_address='';` - What would be a reasonable expectation > for time to complete?
> I've tried setting it blank as above and also setting it to > 'gcomm://' as mentioned below and both commands never return. Snippet > from the node's log file which I'm trying to remove from the cluster. > Last line from the log is " [Note] WSREP: rollbacker thread exiting".
> As of right now - the command says it's still in progress;
> # mysql -u root -p > Enter password: > Welcome to the MySQL monitor. Commands end with ; or \g. > Your MySQL connection id is 48 > Server version: 5.5.17-22.1-log Percona XtraDB Cluster (GPL), Release > 22.1, Revision 3683 wsrep_22.3.r3683
> Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights > reserved.
> Oracle is a registered trademark of Oracle Corporation and/or its > affiliates. Other names may be trademarks of their respective > owners.
> Type 'help;' or '\h' for help. Type '\c' to clear the current input > statement.
> mysql> set global wsrep_cluster_address='';
> Thanks > Patrick > ----- Original Message ----- > From: "Alex Yurchenko" <alexey.yurche...@codership.com> > To: codership-team@googlegroups.com > Sent: Sunday, February 12, 2012 12:58:25 PM > Subject: Re: [codership-team] Re: Disconnect from a cluster
> On 2012-02-12 19:33, Simon Balz wrote: >> Maybe maintenance is the wrong word but I'm searching for a solution >> to have a cold-standby instance when doing deployments with critical >> database changes. Can't I just bootstrap a new cluster on this >> standalone instance in case of a rollback scenario?
> mysql> SET GLOBAL wsrep_cluster_address='gcomm://' will do exactly > this.
-- Alexey Yurchenko, Codership Oy, www.codership.com Skype: alexey.yurchenko, Phone: +358-400-516-011
