best way to clone mysql from galera cluster to standalone version via Galera

[Erik]

Hi all,

I was wondering what's the best way to achieve following scenario:

- source: Existing 3 node Galera MySQL cluster

- goal: create a separate MySQL instance which is a clone of the Galera MySQL - best case to do this on-the-fly

I was thinking about:

- create a 4the Galera node

- join it to the existing Galera cluster: my.cnf contains wsrep_cluster_address="gcomm://10.23.1.1" (ip is existing node)

- situation is now that the 4th node is part of the cluster: at a specific moment we want to separate it from the cluster 

- edit my.cnf of 4th node with wsrep_cluster_address="gcomm://" 

- at that specific moment: restart the MySQL node, hence gcomm is empty, it is restarted on its own

this means there is a downtime - time taken to restart MySQL node.

Is this a good way to do it ? 

is there a way to "separate" the 4th node from the cluster on-the-fly, instead of restarting it ?

Thx for any input !

Erik

[Alex Yurchenko]

Unless something got broken lately

mysql> SET GLOBAL wsrep_provider='none';

should give you a standalone MySQL server

mysql> SET GLOBAL wsrep_cluster_address='gcomm://';

should give you 1-node cluster

Regards.
Alex

[Dibesh Shrestha]

Hi Alex,

Thank you for the info.

So how do i get back to the mulitple  nodes cluster  again from standalone or 1 node cluster?

Thanks for your reply in advance

[yan.zhang]

Hi

I don't understand "get back" to the multiple nodes cluster. All you need to do it to change other nodes my.cnf "wsrep_clutser_address=gcomm://<4th-node-ip>" and start them. They will connect with the standalone node and form a cluster eventually.

[Dibesh Shrestha]

Hi yan.

Thank you for your response and sorry for the ambiguity. 

Suppose I have 3 machines running an application which uses galera cluster Mariadb. Now, because of some reasons there is  network failure.

So, a machine is set to run in standalone and the application accesing the db still runs in standalone situation. Now, the network recovered and 

lets suppose the the machine became the clustered node again. Now,what happens to the database which was edited during its standalone condition?

Thanks for your reply in advance

[yan.zhang]

And during the network failure, application does not commit transactions to the other two machines, right ? 

if yes, I think you probably don't need to do anything. You just need to start the other nodes, and they will get the lagged transactions during the network failure from the standalone node automatically. In another word, no data and transactions will be lost. 

[Dibesh Shrestha]

Hi yan.
Thank you for the reply. Yes there is no transaction to other machines during network failure. for clarity Lets say take a specific example, suppose we use moodle e-learnng system in three machines which uses the clustered mariadb database. Now there is network failure. so the different contents are added to each of the moodle instances at the state of network failure. When network is reconnected will all these three contents from three machine b stored in clustered envirobment or will it b overwritten by one of them?
As usual look forward for your reply.

[yan.zhang]

Case is a little complex. if A and B were a cluster. And after network failure, A and B were written to different contents. Then A can not rejoin to B because galera can not merge data. One possible solution is you can wipe A's data, and joins to B, then use B's data. 

But I think this maybe not what you expect. And what galera cluster do is(maybe is what you expected), if network failure happens, partitioned nodes will go down which prevent to write different contents to them. For example, you have 3 nodes ABC. If A has network connection problems with BC, then A is partitioned node. Then you can not write to A, but you can still write to BC. 

You must to know that galera cluster provides strong consistency. So it expects data on every nodes is the same. 

I'm not sure if I answer your question. 

[erkan yanar]

On Thu, May 01, 2014 at 05:42:55PM -0700, yan.zhang wrote:
> Case is a little complex. if A and B were a cluster. And after network
> failure, A and B were written to different contents. Then A can not rejoin
> to B because galera can not merge data. One possible solution is you can
> wipe A's data, and joins to B, then use B's data.

Thats wrong.
There had been a nice post from Alexey I can't find anymore.
(Used to be http://codership.com/content/order-business.)

Anyway joining a cluster is not about merging data. In fact
A could join B if seqno(A) <= seqno(B).
If there is a conflict while replicating (if any) is another story.

>
> But I think this maybe not what you expect. And what galera cluster do
> is(maybe is what you expected), if network failure happens, partitioned
> nodes will go down which prevent to write different contents to them. For
> example, you have 3 nodes ABC. If A has network connection problems with
> BC, then A is partitioned node. Then you can not write to A, but you can
> still write to BC.
>
> You must to know that galera cluster provides strong consistency. So it
> expects data on every nodes is the same.
>
> I'm not sure if I answer your question.
>
> On Thursday, May 1, 2014 10:16:40 PM UTC+8, Dibesh Shrestha wrote:
> >
> > Hi yan.
> > Thank you for the reply. Yes there is no transaction to other machines
> > during network failure. for clarity Lets say take a specific example,
> > suppose we use moodle e-learnng system in three machines which uses the
> > clustered mariadb database. Now there is network failure. so the different
> > contents are added to each of the moodle instances at the state of network
> > failure. When network is reconnected will all these three contents from
> > three machine b stored in clustered envirobment or will it b overwritten
> > by one of them?
> > As usual look forward for your reply.
>

> --
> You received this message because you are subscribed to the Google Groups "codership" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to codership-tea...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

--
über den grenzen muß die freiheit wohl wolkenlos sein

[Dibesh Shrestha]

Thank you for replies.
How r sequence nmbers calculated?
IF I HAV A connected to B , B TO C AND C to D .They are galera cluster nodes. Now if connection between B and C breaks, will A and B maintain galera cluster ?what happens to C and D which are still connected with each other.
Thanks in advance.

[yan.zhang]

On Friday, May 2, 2014 9:52:32 AM UTC+8, erkules wrote:

On Thu, May 01, 2014 at 05:42:55PM -0700, yan.zhang wrote:
> Case is a little complex. if A and B were a cluster. And after network
> failure, A and B were written to different contents. Then A can not rejoin
> to B because galera can not merge data. One possible solution is you can
> wipe A's data, and joins to B, then use B's data.

Thats wrong.
There had been a nice post from Alexey I can't find anymore.
(Used to be http://codership.com/content/order-business.) 

You can still read it from here. http://web.archive.org/web/20131014075240/http://www.codership.com/content/order-business

 

Anyway joining a cluster is not about merging data. In fact
A could join B if seqno(A) <= seqno(B).
If there is a conflict while replicating (if any) is another story.

Yes, if A and B still have same state UUID, and seqno(A) <= seqno(B), they A could join B. But after network failure and they are written into different contents, I don't think A could join B anymore. 

[yan.zhang]

On Friday, May 2, 2014 11:52:43 PM UTC+8, Dibesh Shrestha wrote:

Thank you for replies.
How r sequence nmbers calculated?

sequence number is a internal concept of galera. you can read it from here http://galeracluster.com/documentation-webpages/glossary.html?highlight=gtid

The GTID consists of:

• A state UUID, which uniquely identifies the state and the sequence of changes it undergoes
• An ordinal sequence number (seqno, a 64-bit signed integer) to denote the position of the change in the sequence

you can perceive every transaction committed to cluster will make seqno + 1.

 

IF I HAV A connected to B , B TO C AND C to D .They are galera cluster nodes. Now if connection between B and C breaks, will A and B maintain galera cluster ?what happens to C and D which are still connected with each other.

This involves concept of "component", which could be found here http://galeracluster.com/documentation-webpages/weightedquorum.html?highlight=primary%20component

Galera only allows one component accept writes, which is called "primary component". Non-primary components don't accept any write. Intuitively, you can think primary component is the component with major nodes. (but this could be affected by node weight in quorum) 

So to your case, there are two components AB and CD, that's half and half. Neither of them is primary component(so neither of them maintains galera cluster). So this situation is called 'brain-split'. But if only D is disconnected from them, then there are two components ABC(primary) and D(non-primary), so ABC will maintain galera cluster. 

 

 

Thanks in advance.

[erkan yanar]

On Fri, May 02, 2014 at 05:05:27PM -0700, yan.zhang wrote:
>
>
> On Friday, May 2, 2014 9:52:32 AM UTC+8, erkules wrote:
> >
> > On Thu, May 01, 2014 at 05:42:55PM -0700, yan.zhang wrote:
> > > Case is a little complex. if A and B were a cluster. And after network
> > > failure, A and B were written to different contents. Then A can not
> > rejoin
> > > to B because galera can not merge data. One possible solution is you can
> > > wipe A's data, and joins to B, then use B's data.
> >
> > Thats wrong.
> > There had been a nice post from Alexey I can't find anymore.
> > (Used to be http://codership.com/content/order-business.)
> >
>
> You can still read it from here.
> http://web.archive.org/web/20131014075240/http://www.codership.com/content/order-business
>
> > Anyway joining a cluster is not about merging data. In fact
> > A could join B if seqno(A) <= seqno(B).
> > If there is a conflict while replicating (if any) is another story.
> >
> > Yes, if A and B still have same state UUID, and seqno(A) <= seqno(B), they
> A could join B. But after network failure and they are written into
> different contents, I don't think A could join B anymore.
>

Again: The different content (different data for same seqno) is not
about joining the cluster per se.
As long as there are no conflicts while doing the IST or later on the cluster w
will continue to work. Which is bad of course.

Rephrased.
A and B have a set of seqno with different transactions.
A wants to join B (possible because of \le seqno).
A joins the Cluster (B)

IF no IST -> SST everyhing is fine
IF a conflict occurs while IST -> break
Now all nodes are synced with different data.

[yan.zhang]

I did some test, and yes you are right. thanks for correcting me. 

[Dibesh Shrestha]

Thank you for your replies

WIll it be possible to replace rsync with unison which is two way synchronizer?

 

[yan.zhang]

Theoretically yes. But currently galera only supports 3 state snapshot transfer methods. xtrabackup is recommended. 

Method	Speed	Blocks the donor?	Available on live node?	Logical/Physical	Requires root access to MySQL server?
mysqldump	slow	yes	yes	logical	both donor and joiner
rsync	fastest	yes	no	physical	none
xtrabackup	fast	For a short time	no	physical	donor only

[erkan yanar]

Ahoi Shrestha,

As Yan already mentioned, it is possible.
But as you emphasize the "two way" synchronize ability, don't think about it.
It would break in many ways.


erkan

[erkan yanar]

On Sun, May 04, 2014 at 06:46:28AM -0700, yan.zhang wrote:
> Theoretically yes. But currently galera only supports 3 state snapshot
> transfer methods. xtrabackup is recommended.
>
> MethodSpeedBlocks the donor?Available on live node?Logical/PhysicalRequires
> root access to MySQL server?mysqldumpslowyesyeslogicalboth donor and joiner
> rsyncfastestyesnophysicalnonextrabackupfastFor a short timenophysicaldonor
> only

Uhh html mails :)

I recommend rsync. It misses the online capability but it is
* more robust
* easy to maintain/setup

So choose your death :)

[Dibesh Shrestha]

Hi all,
Thank you for reponses.
Is there any method so that i can merge database updated at offline at two or more nodes?
Something which works as similar principle to dropbox.Dropbox is about files though.
Thank you for reply in advance

[erkan yanar]

On Sun, May 04, 2014 at 06:58:38PM -0700, Dibesh Shrestha wrote:
> Hi all,
> Thank you for reponses.
> Is there any method so that i can merge database updated at offline at two or more nodes?

^^^^^^

> Something which works as similar principle to dropbox.Dropbox is about files though.
> Thank you for reply in advance
>

Nope.

[Dibesh Shrestha]

hi all,
what happens if i have 7 galera cluster nodes and it breaks into 3 and 4 nodes respectively. will the cluster run at both cOmponents?
Besides, i found a solution for merging db, that is i configured standalone db which performs master master replication wth galera cluster. so when galera cluster is not running application can acess this standalone db and replicates to and from galera when cluster is running.

[yan.zhang]

Cluster could be divided into server components, but only primary component could accept reads and writes. And primary component is the one with majority. So to your cases, only the 4 nodes component runs. 

Your solution seems work. But there are some problems:

1. you have to make sure standalone db won't go down.

2. there is a paradox. Since standalone db performs master-master replication with galera cluster, so standalone db *is part of* galera cluster. So If galera cluster is not running, then the standalone db is not running either, and application can not access this standalone db anymore. 

[Dibesh Shrestha]

Hi Yan,

Yes, the standalone server will not go down. Standalone db is not a part of galera cluster, only particular database is replicated from mysql-cluster.So, while the cluster is disconnected, application updates the standalone database and later when cluster is back, the database is replicated from standalone to galera cluster and vice-versa. I have experimented it sucessfully.