galera backups

[Andy Thompson]

Been digging through the list looking for a backup implementation. Found some refs to the garbd daemon but nothing real clear, I played with it a bit and didn't really make it very far.

Anyway, is it sufficient to backup using mysqldump/mydumper/xtrabackup or equivalent on a single host? In the case of an entire cluster failure I assume that's sufficient to restore a working cluster. If just a single node fails, can that be used to restore a single node to avoid an entire SST? Or does it really matter much other than having a donor out of commission for a bit? I've got my cluster setup with a host that doesn't process transactions anyway so that won't really affect me much.

What are people are using out there?

thanks

-andy

[Alex Yurchenko]

On 2012-03-27 22:09, Andy Thompson wrote:
> Been digging through the list looking for a backup implementation.
> Found some refs to the garbd daemon but nothing real clear, I played
> with it a bit and didn't really make it very far.
>
> Anyway, is it sufficient to backup using
> mysqldump/mydumper/xtrabackup or equivalent on a single host?

It depends on how you're planning to do that and what do you expect to
get in the end.

> In the case of an entire cluster failure I assume that's sufficient
> to restore a working cluster.

Yep, in that case you can start global transaction ID sequence anew.

> If just a single node fails, can that be used to restore a single
> node to avoid an entire SST?

No, neither of these tools alone will give you global transaction ID of
the snapshot.

> Or does it really matter much other than having a donor out of
> commission for a bit?

It depends on your data size and hardware specs. I have yet to see
rsync transfers that exceed 100Mb/sec, and normally 100Gb SST will take
~20 minutes even on a rather high-end hardware. However, backup will be
no less intrusive than SST. So if your only purpose of making backups is
to save on SST, it may be not worth it (except that you can do backup at
low hours).

> I've got my cluster setup with a host that doesn't process
> transactions anyway so that won't really affect me much.
>
> What are people are using out there?

Not sure what others are using, but I'd really encourage you to figure
out garbd. Have you seen this post:
https://groups.google.com/d/msg/codership-team/OYA77HJBUNY/rj-g6A60bksJ
?

Doing backups through SST script has two important benefits:

1) it produces snapshot with a known global transaction ID, so you can
avoid SST on recovery.
2) it "desynchronizes" the node that is doing backup from the cluster,
so that it does not slow down other nodes.

You can use existing SST scripts as a basis for your own backup script.
E.g.
- to use mysqldump or mydumper see wsrep_sst_mysqldump
- to use xtrabackup see wsrep_sst_xtrabackup (in Percona XtraDB Cluster
package)
- to use rsync see wsrep_sst_rsync

Regards,
Alex

>
> thanks
>
> -andy

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

[Vadim Tkachenko]

Alex,

Quick note, that with xtrabackup you can get Global transaction ID of snapshot.
That is how we use it for xtrabackup SST.

> --
> You received this message because you are subscribed to the Google Groups
> "codership" group.
> To post to this group, send email to codersh...@googlegroups.com.
> To unsubscribe from this group, send email to
> codership-tea...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/codership-team?hl=en.
>

--
Vadim Tkachenko, CTO, Percona Inc.
Phone +1-925-400-7377,  Skype: vadimtk153
Schedule meeting: http://tungle.me/VadimTkachenko

Join us at Percona Live: MySQL Conference And Expo 2012
http://www.percona.com/live/mysql-conference-2012/

[Andy Thompson]

> On 2012-03-27 22:09, Andy Thompson wrote:
> > Been digging through the list looking for a backup implementation.
> > Found some refs to the garbd daemon but nothing real clear, I played
> > with it a bit and didn't really make it very far.
> >
> > Anyway, is it sufficient to backup using
> > mysqldump/mydumper/xtrabackup or equivalent on a single host?
>
> It depends on how you're planning to do that and what do you expect to
> get in the end.
>
> > In the case of an entire cluster failure I assume that's sufficient
> > to restore a working cluster.
>
> Yep, in that case you can start global transaction ID sequence anew.
>

This is primarily what I'm after. Backups in the event of a complete system failure or loss.

> Not sure what others are using, but I'd really encourage you to figure
> out garbd. Have you seen this post:
> https://groups.google.com/d/msg/codership-team/OYA77HJBUNY/rj-g6A60bksJ
> ?
>

Ya that's the post I was going through when I was tinkering around with garbd.

> Doing backups through SST script has two important benefits:
>
> 1) it produces snapshot with a known global transaction ID, so you can
> avoid SST on recovery.
> 2) it "desynchronizes" the node that is doing backup from the cluster,
> so that it does not slow down other nodes.
>
> You can use existing SST scripts as a basis for your own backup script.
> E.g.
> - to use mysqldump or mydumper see wsrep_sst_mysqldump
> - to use xtrabackup see wsrep_sst_xtrabackup (in Percona XtraDB Cluster
> package)
> - to use rsync see wsrep_sst_rsync
>

I will look those over and try to sort something out. Imagine I will be back with questions :)

thanks for the info

-andy

[Andy Thompson]

Can you expand on that a little bit more? Is there anything special I have to do in order to enable it? Or just setup xtrabackup as normal and let it run? What is the process for restoring?

thanks

-andy

>>> On 3/27/2012 at 04:50 PM, in message
<CAKL74PNNWapxY7mgzPf9SN-Oom+ctYW8bLHnnPYySd=NRn...@mail.gmail.com>, Vadim

[Vadim Tkachenko]

Andy,

You need Percona XtraBackup 1.9.1 or newer.
When you do backup you specify --galera-info parameter in command line,
it will produce a file with Galera Transaction ID.

Recovery procedure is still can be complicated. Maybe Alex can explain this.

--

[Alex Yurchenko]

Yes, recovery...

After you have recovered your datadir from backup, i.e. you're ready to
start a node, you need to specify the GTID identifying the current node
state. There are several ways to do that, the most fool-proof is
probably as follows.

There is grastate.dat text file in your datadir (create it if this is a
new node, don't forget about permissions). It should look like that:

# GALERA saved state, version: 0.8, date: (todo)
uuid: 00000000-0000-0000-0000-000000000000
seqno: -1
cert_index:

fill in uuid and seqno fields from the file that xtrabackup creates.
Start the node and join it to cluster.

[Henrik Ingo]

If I do xtrabackup --galera-info, why doesn't xtrabackup just create
such a grastate.dat file? The information value is the same, but it
would work without having to manually do anything.

(Btw, I've always had problems restoring MySQL replicating slaves from
xtrabackup, it is easy to mix the files with master info vs slave
info.)

henrik

--
henri...@avoinelama.fi
+358-40-8211286 skype: henrik.ingo irc: hingo
www.openlife.cc

My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559

[Andy Thompson]

Ok. At that point in time is galera going to do an IST to the joining server to catch it back up then?

I guess I've never been real clear on the process that occurs when you have a cluster server down for a restart or maintenance. Kind of getting OT but what process does galera go thru to catch a server up in either this or a restart situation?

>>> Alex Yurchenko 03/29/12 2:02 AM >>>
Yes, recovery...

After you have recovered your datadir from backup, i.e. you're ready to
start a node, you need to specify the GTID identifying the current node
state. There are several ways to do that, the most fool-proof is
probably as follows.

There is grastate.dat text file in your datadir (create it if this is a
new node, don't forget about permissions). It should look like that:

# GALERA saved state, version: 0.8, date: (todo)
uuid: 00000000-0000-0000-0000-000000000000
seqno: -1
cert_index:

fill in uuid and seqno fields from the file that xtrabackup creates.
Start the node and join it to cluster.

On 2012-03-28 18:30, Vadim Tkachenko wrote:
> Andy,
>
> You need Percona XtraBackup 1.9.1 or newer.
> When you do backup you specify --galera-info parameter in command
> line,
> it will produce a file with Galera Transaction ID.
>
> Recovery procedure is still can be complicated. Maybe Alex can
> explain this.
>
>
> On Wed, Mar 28, 2012 at 5:56 AM, Andy Thompson

> wrote:
>> Can you expand on that a little bit more? Is there anything special
>> I have to do in order to enable it? Or just setup xtrabackup as
>> normal and let it run? What is the process for restoring?
>>
>> thanks
>>
>> -andy
>>
>>>>> On 3/27/2012 at 04:50 PM, in message
>>

>> ,
>> Vadim

>> Tkachenko wrote:
>>> Alex,
>>>
>>> Quick note, that with xtrabackup you can get Global transaction ID
>>> of
>>> snapshot.
>>> That is how we use it for xtrabackup SST.
>>>
>>>
>>> On Tue, Mar 27, 2012 at 1:14 PM, Alex Yurchenko

[Alex Yurchenko]

On 2012-03-29 15:13, Andy Thompson wrote:
> Ok. At that point in time is galera going to do an IST to the
> joining server to catch it back up then?
>
> I guess I've never been real clear on the process that occurs when
> you have a cluster server down for a restart or maintenance. Kind of
> getting OT but what process does galera go thru to catch a server up
> in either this or a restart situation?

1. The joining node sees the gap between its and cluster state and
sends state transfer request (STR) to cluster

2. The donor specified in wsrep_sst_donor, or automatically chosen by
cluster, delivers either SST or IST to joiner. In the latter case an
empty SST is faked (so you'll see SST messages in log). The choice
between SST and IST depends on whether the donor has enough transactions
in gcache. Hence gcache.size option form wsrep_provider_options is
important.

[Andy Thompson]

> Ok. At that point in time is galera going to do an IST to the
> joining server to catch it back up then?
>
> I guess I've never been real clear on the process that occurs when
> you have a cluster server down for a restart or maintenance. Kind of
> getting OT but what process does galera go thru to catch a server up
> in either this or a restart situation?

>1. The joining node sees the gap between its and cluster state and
>sends state transfer request (STR) to cluster

>2. The donor specified in wsrep_sst_donor, or automatically chosen by
>cluster, delivers either SST or IST to joiner. In the latter case an
>empty SST is faked (so you'll see SST messages in log). The choice
>between SST and IST depends on whether the donor has enough transactions
>in gcache. Hence gcache.size option form wsrep_provider_options is
>important.

Ok good information, thanks. I've left the cache size at the default for the time being, I'll wait and see how that goes, probably will have to increase it at some point down the road.

Is there any configuration for IST or does it use the SST directive? I saw a reference somewhere to a listen ore receive address which led me to believe there was something specific to IST but I can't recall where. I haven't shut a server down quite yet and looked at the logs to see what happens, only added a couple new servers and they got a full SST.

thanks

-andy

[Alex Yurchenko]

On 2012-03-29 17:56, Andy Thompson wrote:
>> Ok. At that point in time is galera going to do an IST to the
>> joining server to catch it back up then?
>>
>> I guess I've never been real clear on the process that occurs when
>> you have a cluster server down for a restart or maintenance. Kind
>> of
>> getting OT but what process does galera go thru to catch a server up
>> in either this or a restart situation?
>
>>1. The joining node sees the gap between its and cluster state and
>>sends state transfer request (STR) to cluster
>
>>2. The donor specified in wsrep_sst_donor, or automatically chosen by
>>cluster, delivers either SST or IST to joiner. In the latter case an
>>empty SST is faked (so you'll see SST messages in log). The choice
>>between SST and IST depends on whether the donor has enough
>> transactions
>>in gcache. Hence gcache.size option form wsrep_provider_options is
>>important.
>
>
> Ok good information, thanks. I've left the cache size at the default
> for the time being, I'll wait and see how that goes, probably will
> have to increase it at some point down the road.
>
> Is there any configuration for IST or does it use the SST directive?

Only ist.recv_addr if you want to be specific about the interface IST
is using (should go into wsrep_provider_options, like anything not
prefixed with wsrep_). There is a bug about it however:
wsrep_node_incoming_address seems to override it. So it is sort of
useless ATM. Will be committing fix to LP tonight.

> I saw a reference somewhere to a listen ore receive address which led
> me to believe there was something specific to IST but I can't recall
> where. I haven't shut a server down quite yet and looked at the logs
> to see what happens, only added a couple new servers and they got a
> full SST.
>
> thanks
>
> -andy

--

[Andy Thompson]

So when I boot strapped my cluster after changing the cluster network configuration, I fired up the first server and pointed the other four servers to it. My running cluster shows the respective cluster_address settings with an empty cluster address on the first server and all others pointing to that box. Do I need reset those or just leave it alone until I have to restart a node and have it reset at runtime by what I have configured in the my.cnf file?

thanks

-andy

[Henrik Ingo]

After startup, the variable value has no meaning. It just tells you
the value that was used but doesn't do anything with it anymore.

In other words, all nodes are connected to each other, even if
cluster_address just points to one node (or zero).

henrik

> --
> You received this message because you are subscribed to the Google Groups "codership" group.
> To post to this group, send email to codersh...@googlegroups.com.
> To unsubscribe from this group, send email to codership-tea...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/codership-team?hl=en.
>

--

[alexey.yurchenko]

Wrong!

Henrik, I know what you mean, but universally right answer is
"Thou shalt never leave wsrep_cluster_address=gcomm:// in thy my.cnf."

This should be considered as a bootstrap -only option.

Cheers,
-Alex

[Andy Thompson]

Ya I've got that reset correctly on the first server after getting the cluster started up again, was just looking things over and saw the running value was still gcomm:// so I thought I'd ask.

thanks guys

-andy

>>> On 4/2/2012 at 11:21 AM, in message
<k6xc1gadwdxrpx0v9y5...@email.android.com>, "alexey.yurchenko"

[Henrik Ingo]

In my.cnf yes, on the running server no.

henrik