Galera cluster as slave

[Michael Eklund]

I have seen in several places that when the Galera cluster is a slave of another mysql server that you should set the server_id the same on all nodes.  I assume in that situation the slave is only started on one server and that you have to manually shift the slave in the case of an outage.  I am looking for guidance or a documented setup if one exists.

Thanks,

Mike E.

[Egor Shevtsov]

Hi Mike,

I think this one is a very useful feature to be able to replicate from or to the cluster with original MySQL asynchronous type of replication and have all updates regardless to the galera node they were written to be replicated properly to the slave MySQL.

I have 3 galera nodes setup all with the same server_id:

default_storage_engine = InnoDB
log_slave_updates      = 1
log-bin=/var/log/mysql/mysql-bin/mysql-bin
log-bin-index=/var/log/mysql/mysql-bin/mysql-bin.index
server_id              = 111111
binlog_format          = ROW

  

plus few other parameters like binlog_format, log_slave_updates

Replicating from one of the Galera nodes (Master) to Backup MySQL (slave) goes with no issue if server_id is the same on all Galera nodes and will miss those updates originated on diff Galera nodes if server_id are different on all Galera nodes.

All the data to Galera cluster is written through HAProxy in round_robin style, so to each of the nodes, and yet everything was replicated without any issues.

If you use Galera node as a slave however, I think it will propagated replicated data through out the cluster to keep it consistent even if all the nodes have diff server_id, But I'd rather run all the nodes in the cluster under one server_id anyway.

       

Cheers,

Igor

[Henrik Ingo]

Actually, it is more important in the opposite case: When Galera
Cluster is a master and another MySQL server is a slave. Then
transactions that originate within the Galera Cluster will always have
the same server-id, which usually leads to the behavior you want.

But in summary: It is good practice to always set server-id to the
same value on all Galera nodes. (...unless you have a good reason to
do something differently.)

henrik

> --
> 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/groups/opt_out.
>
>



--
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

[Scott A. Haas]

Just wondering the affects of having different server_id for nodes in a galera cluster (assuming no asynchronous replication, strictly PXC).  What types of behaviors or issues would be encountered, if known?

Thanks,

Scott

---

From: "Henrik Ingo" <henri...@avoinelama.fi>
To: "Michael Eklund" <mike....@gmail.com>
Cc: codersh...@googlegroups.com
Sent: Tuesday, March 19, 2013 8:10:29 AM
Subject: Re: [codership-team] Galera cluster as slave

[Federico Nebiolo]

Il giorno lunedì 18 marzo 2013 17:50:54 UTC+1, Igor Shevtsov ha scritto:

Hi Mike,

I think this one is a very useful feature to be able to replicate from or to the cluster with original MySQL asynchronous type of replication and have all updates regardless to the galera node they were written to be replicated properly to the slave MySQL.

I have 3 galera nodes setup all with the same server_id:

default_storage_engine = InnoDB
log_slave_updates      = 1
log-bin=/var/log/mysql/mysql-bin/mysql-bin
log-bin-index=/var/log/mysql/mysql-bin/mysql-bin.index
server_id              = 111111
binlog_format          = ROW

  

plus few other parameters like binlog_format, log_slave_updates

Replicating from one of the Galera nodes (Master) to Backup MySQL (slave) goes with no issue if server_id is the same on all Galera nodes and will miss those updates originated on diff Galera nodes if server_id are different on all Galera nodes.

All the data to Galera cluster is written through HAProxy in round_robin style, so to each of the nodes, and yet everything was replicated without any issues.

If you use Galera node as a slave however, I think it will propagated replicated data through out the cluster to keep it consistent even if all the nodes have diff server_id, But I'd rather run all the nodes in the cluster under one server_id anyway.

       

Cheers,

Igor

Igor and all,
I also have a 3-nodes galera cluster performing quite well, but would like to attach a couple of slaves to it (replicating a single database among those on the galera cluster). Would it be possible to bind those slaves to an haproxy pointing to the whole galera cluster? How would a slave react to a galera node down? What should I consider in case of failure or out of sync?

Cheers
federico

[Alex Yurchenko]

It is all in the internets:
http://www.mysqlperformanceblog.com/2013/06/21/changing-an-async-slave-of-a-pxc-cluster-to-a-new-master/

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

[Jay Janssen]

On Jun 27, 2013, at 6:55 AM, Federico Nebiolo <fneb...@gmail.com> wrote:

I also have a 3-nodes galera cluster performing quite well, but would like to attach a couple of slaves to it (replicating a single database among those on the galera cluster). Would it be possible to bind those slaves to an haproxy pointing to the whole galera cluster?

No, binlog positions will be different on the different nodes, so a VIP for the master is not usable.  See my blog post on changing masters to a new node in the cluster.

How would a slave react to a galera node down?

It will stop getting replication and the IO thread will be in the 'connecting' state.  

Jay Janssen, MySQL Consulting Lead, Percona

http://about.me/jay.janssen

[Roel Van de Paar]

Late reply, but this was what was originally requested:

http://severalnines.com/blog/replicate-mysql-server-galera-cluster