finding the most up to date node after a disaster

[Luke Bigum]

Hello list,

In the event of a disaster across a three node cluster (such as a power loss) is it possible to find the most up to date cluster node from MySQL / Galera itself? Imagine a database so huge that it is impossible to scan every table and find the node that had the last thing written to it - how would you know which node is the most up to date in order to start a new cluster?

MySQL bin log positions won't work as different cluster nodes roll their binary logs at different times. Is there a Galera way to solve this problem?

Thanks,

-Luke

[Alex Yurchenko]

You may try starting mysqld with --wsrep-recover option. Then it will
print UUID:seqno of the last committed transaction to the error log and
exit. DOes not work with 5.1.x series.

Regards,
Alex

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011

[Luke Bigum]

Hi Alex,

Thank you, that is exactly what I'm looking for.

I have a follow up question regarding Galera's behaviour. Will Galera
ever establish re-establish a cluster with the same UUID if all members
leave uncleanly (sequence number -1)?

I would like to start MySQL on boot and have a wsrep_url line like this:

wsrep_urls=gcomm://node1:4567,gcomm://node2:4567,gcomm://node3:4567

So if one or two members reboots, they can rejoin the remaining node(s)
and regain Sync. However, I want to protect against a disasters like
power outages where all nodes might reboot uncleanly. I don't want the
bootstrap url "gcomm://" in there as I want to be in complete control of
which cluster node becomes the new primary or seed server with
--wsrep-recover.

From my rudimentary testing by SEGFAULTing all nodes at the same time
with the above wsrep_urls line, none of the nodes will rejoin:

120928 09:51:35 mysqld_safe ERROR: none of the URLs in
'gcomm://node1:4567,gcomm://node2:4567,gcomm://node3:4567' is reachable.

Is this absolutely the case in all scenarios? Is there any situation
where an out of date cluster may be re-formed with these URLs and thus
MySQL could serve stale data or at worst data loss?

Thanks,

-Luke

On 27/09/12 20:05, Alex Yurchenko wrote:
>
> You may try starting mysqld with --wsrep-recover option. Then it will
> print UUID:seqno of the last committed transaction to the error log
> and exit. DOes not work with 5.1.x series.
>
> Regards,
> Alex
>


--

Luke Bigum
Senior Systems Engineer

Information Systems
Ph: +44 (0) 20 3192 2520
luke....@lmax.com | http://www.lmax.com
LMAX, Yellow Building, 1A Nicholas Road, London W11 4AN


FX and CFDs are leveraged products that can result in losses exceeding
your deposit. They are not suitable for everyone so please ensure you
fully understand the risks involved. The information in this email is not
directed at residents of the United States of America or any other
jurisdiction where trading in CFDs and/or FX is restricted or prohibited
by local laws or regulations.

The information in this email and any attachment is confidential and is
intended only for the named recipient(s). The email may not be disclosed
or used by any person other than the addressee, nor may it be copied in
any way. If you are not the intended recipient please notify the sender
immediately and delete any copies of this message. Any unauthorised
copying, disclosure or distribution of the material in this e-mail is
strictly forbidden.

LMAX operates a multilateral trading facility. Authorised and regulated
by the Financial Services Authority (firm registration number 509778) and
is registered in England and Wales (number 06505809).
Our registered address is Yellow Building, 1A Nicholas Road, London, W11
4AN.

[Alex Yurchenko]

Hi Luke,

I think you're mixing too many things together, So I'll reply only to
some.

1) wsrep_urls is a dirty hack, that's why it behaves this way. It will
be replaced by a better method in the near future.

2) a cluster can never form a primary component unless its first node
was started with wsrep_cluster_address=gcomm://. or it was bootstrapped
with pc.bootstrap Galera option.

3) what you want here is to
a. stop using mysqld_safe and any other sort of automatic restart - I
can't remember a single case when it helped. Normally it just cases
problems. It is very much similar to automatic failover - it restarts
the node without any concern to the overall situation.
b. find the most updated node
c. on that node start mysqld with --wsrep_cluster_address=gcomm://

Regards,
Alex

[Luke Bigum]

On 28/09/12 15:19, Alex Yurchenko wrote:
> Hi Luke,
>
> I think you're mixing too many things together, So I'll reply only to
> some.
>
> 1) wsrep_urls is a dirty hack, that's why it behaves this way. It will
> be replaced by a better method in the near future.
>

Looking at the mysqld_safe script, yes, I agree it's a bit dirty :-) Do
you have any time frame for the replacement method or any thoughts on
how it would work?

> 2) a cluster can never form a primary component unless its first node
> was started with wsrep_cluster_address=gcomm://. or it was
> bootstrapped with pc.bootstrap Galera option.

That is the answer I was hoping for :-) I really just wanted something
to put in /etc/my.cnf that would allow one node to rejoin if it
rebooted, but would require manual intervention if there was no
bootstrapped cluster already.

>
> 3) what you want here is to
> a. stop using mysqld_safe and any other sort of automatic restart - I
> can't remember a single case when it helped. Normally it just cases
> problems. It is very much similar to automatic failover - it restarts
> the node without any concern to the overall situation.
> b. find the most updated node
> c. on that node start mysqld with --wsrep_cluster_address=gcomm://
>

We're using mysqld_safe insomuch as it is what the Percona CentOS
packages use and in the LSB script for mysql. I do like the convenience
of "service mysql stop/start" to drop out and in of the cluster when I
feel like it and I don't have to lookup an address of a running node,
modify a config and/or start mysqld with a different argument.

I do see your point though and I'll take that on board - having more
manual control over mysqld itself (with no mysqld_safe) is something our
deployment software is capable of.

Thanks,

-Luke

[Alex Yurchenko]

On 2012-09-28 18:51, Luke Bigum wrote:
> On 28/09/12 15:19, Alex Yurchenko wrote:
>> Hi Luke,
>>
>> I think you're mixing too many things together, So I'll reply only
>> to some.
>>
>> 1) wsrep_urls is a dirty hack, that's why it behaves this way. It
>> will be replaced by a better method in the near future.
>>
>
> Looking at the mysqld_safe script, yes, I agree it's a bit dirty :-)
> Do you have any time frame for the replacement method or any thoughts
> on how it would work?

So there it is: 2.2rc1 supports multiple comma-separated addresses in
gcomm:// URL

>> 2) a cluster can never form a primary component unless its first
>> node was started with wsrep_cluster_address=gcomm://. or it was
>> bootstrapped with pc.bootstrap Galera option.
>
> That is the answer I was hoping for :-) I really just wanted
> something to put in /etc/my.cnf that would allow one node to rejoin
> if
> it rebooted, but would require manual intervention if there was no
> bootstrapped cluster already.
>
>>
>> 3) what you want here is to
>> a. stop using mysqld_safe and any other sort of automatic restart -
>> I can't remember a single case when it helped. Normally it just cases
>> problems. It is very much similar to automatic failover - it restarts
>> the node without any concern to the overall situation.
>> b. find the most updated node
>> c. on that node start mysqld with --wsrep_cluster_address=gcomm://
>>
>
> We're using mysqld_safe insomuch as it is what the Percona CentOS
> packages use and in the LSB script for mysql. I do like the
> convenience of "service mysql stop/start" to drop out and in of the
> cluster when I feel like it and I don't have to lookup an address of
> a
> running node, modify a config and/or start mysqld with a different
> argument.

Yes, I feel you pain, but this is exactly where you surrender control
over what's happening. Besides, you still can use mysqld_safe - just
comment out the loop that keeps on restarting mysqld - that's trivial,
but should boost your chances immensely. Too bad there is no option
--no-restart... maybe we should add it.