Can I use Galera Replication to replicate between Data Centers?
Although Galera Replication is synchronous, it is possible to deploy a Galera Replication cluster across data centers. Synchronous replication is traditionally implemented via 2-phase commit, where messages are sent to all nodes in a cluster in a ‘prepare’ phase, and another set of messages are sent in a ‘commit’ phase. This approach is usually not suitable for geographically disparate nodes, because of the latencies in sending messages between nodes.
Galera Replication makes use of certification based replication, that is a form of synchronous replication with reduced overhead.
-
Galera using cert based replication is the fastest solution I've found for geographically dispersed data centers using mysql. There are a couple factors that always have to be taken into account.
Size of data being replicated?
Do you need synchronous replication? (Real Time commits)
In our situation, we have a small amount of data over all but we needed scalability, fault tolerance and speed.
Example MySQL Cluster: We tested MySQL cluster (7.2GA) across two different locations in the U.S. (one east coast, on central) with 4 data nodes. We dropped a 3.5MB database in and it took 18 minutes to replicate completely across the infrastructure.
Example Galera: That same database took literally 18 seconds in Galera with 6 nodes in the cluster.
If you are looking to do something like this, ask the guys at severanines or google wsrep_provider_options for WAN.
-
Edit: The MySQL Cluster was using node groups, we were testing a single cluster, multi-datacenter because MySQL claimed it worked. It did work, just not fast.
Why not use two clusters and master/slave them asynch across the WAN? because multiple clusters syncing via master/slave replication added points of failure we weren't willing to accept.
-
our galera cluster setup is like below
A and B are in one data center(galera cluster) . C and D are in another data center(galera cluster).
we have done master to master replication between B and C.
Now the issue is when i am inserting a record into A .it was replicating to C with server ID A ,due to this all transactions are went to infinite loop.
so whatever i am executing in A .it replicates to c and bouncing back to B because of this our replication got failed and data inconsistency was happen.
how to avoid infinite loop or bouncing back statements in our mater to master setup across data centers using galera cluster setup.
Thanks,
Ravi Kiran Pentyala.
-
Hi Ravi,
Sorry for the late reply, but i could not reproduce this. I am assuming you use normal asynchronous replication between the data centers.
I have two galera clusters C1 and C2 with the following nodes:
C1:
A
B
CC2:
D
E
FA is replicating from D, and D is replicating from A (circle).
The only thing i have extra in the my.cnf file, apart from the config that comes from the Configurator is:
On node A:
binlog_format=ROW
log-bin=binlog
log-slave-updates=1
server-id=1 #this is 2 on node DOn node D:
binlog_format=ROW
log-bin=binlog
log-slave-updates=1
server-id=2Are all your tables innodb tables?
Best regardsJohan
-
Hi Ravi,
Correct me if I am wrong.
Architecture 1:
|------->>------master-slave--->>-------|
( A <-galera-> B ) ( C <-galera-> D)Architecture 2:
( A <-galera-> B ) <-galera-> ( C <-galera-> D)
If you use architecture 1, then you need to enable log_slave_updates as it is needed by C. This is what Johan mentioned on the previous reply with proper server-id setup. This blog post: http://www.severalnines.com/blog/replicate-mysql-server-galera-cluster describes more details on the setup and implementation.
You may also use architecture 2, which is supported by Galera WAN replication. What is the MySQL and Galera version you used?
-
Hi,
we are using galera cluster in two data centers like C!,C2 .
each data center(c1/c2 ) contains 3 nodes.
C1:
A
B
CC2:
D
E
Fwe configured normal mysql replication in b/w two galera clusters(C1,C2) like A----> F ,E---->B
Recently we are facing a issues with sql thread in slave.
sql thread went into offline automatically we didn't find any error messages,after wards we are starting it manually and it catching fine.
why sql thread went into offline with out any error message in Galera .
-
Hi,
We are also implementing hybrid replication. i.e Async replication between two galera nodes. And I am also facing the same problem.
Datacenter A - two nodes in galera cluster( Please rule out split brain issue here . As this is for test purpose)
Node A1, A2
Datacentre B - just one node for now. with mariadb installed on it. But this can be expanded into a galera cluster in the future.
Node B1
So I have configured async replication between A1 and B1. Till the point A1 and A2 are configured as a galera node seperately , A1 and A2 sync data very well. But as soon as I configure async replicaiton between A1 And B1, somehow mysql.user table data on A2 is vanished. ANd I am no longer able to see any users in the A2 db. There is no trace in the logs which can justify this condition.
My configuraitons are as below
A1 Galera.cnf-
[mysqld]
#mysql settings
binlog_format=ROW
binlog-do-db=appmachinery
default-storage-engine=innodb
#innodb_autoinc_lock_mode=2
#query_cache_size=0
#query_cache_type=0
bind-address=0.0.0.0
log_slave_updates=1
#galera settings
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name=calif_galera_cluster
wsrep_cluster_address=gcomm://
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="root:XXX"
wsrep_restart_slave=1
# Galera Node Configuration
wsrep_node_address="192.168.XXX.YYY"
wsrep_node_name="192.168.XXX.YYY"
wan_repl.cnf is almost same on all the three nodes as below -
[mysqld]
server-id=201 on B1 and 101 on A1 and 102 on A2
gtid-domain-id=1
binlog-format=ROW
log-slave-updates=1
log-bin=binlog
wsrep-restart-slave=1
slave-skip-errors=1396
Galera.cnf on A2 -
[mysqld]
#mysql settings
binlog_format=ROW
binlog-do-db=appmachinery
default-storage-engine=innodb
#innodb_autoinc_lock_mode=2
#query_cache_size=0
#query_cache_type=0
bind-address=0.0.0.0
log_slave_updates=1
#galera settings
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name=calif_galera_cluster
wsrep_cluster_address=gcomm://192.168.PrimaNodeip,192.168.decond node ip
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="root:XXX"
wsrep_restart_slave=1
# Galera Node Configuration
wsrep_node_address="192.168.zzz.xxx"
wsrep_node_name="192.168.ZZZ.XXX"
Galera .cnf on B1 -
[mysqld]
#mysql settings
binlog_format=ROW
binlog-do-db=appmachinery
default-storage-engine=innodb
#innodb_autoinc_lock_mode=2
#query_cache_size=0
#query_cache_type=0
bind-address=0.0.0.0
log_slave_updates=1
#galera settings
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name=london_galera_cluster
wsrep_cluster_address=gcomm://
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="root:XXX"
wsrep_restart_slave=1
# Galera Node Configuration
wsrep_node_address="192.168.YYY.XXX"
wsrep_node_name="192.168.YYY.XXX"
Can anyone please help me with this?
Please sign in to leave a comment.
Comments
10 comments