Master to Master replication between two mariadb galera clusters

[trupti mali]

Hi,

I have setup master to master async replication between two galera clusters's primary nodes.

e.g. 

3 nodes in Datacenter1 A, B ,C - A the primary node

3 nodes in Datacenter2 X, Y, Z - X the primary node

Now I setup a master - master async replication between A and X. For that I have setup following things. Notice the highlighted parameters. I have pruposely set gtid-domain-id as distinct for two masters. But whereas the database to be replicated from both the nodes A and X is same. When I setup wan replicaiton with this, I get an error in one of the slaves saying  

"Error 'Can't create database '<DBName>'; database exists' on query. Default database: '<DBName>'. Query: 'CREATE DATABASE <DBName>'"

-DBName is my app's database name. Am i doing anythiing wrong in setting up the config parameters here?

/etc/mysql/conf.d/galera.cnf - Node A

server-id=101

gtid-domain-id=1

binlog-format=ROW

log-slave-updates=1

#log-bin=binlog

wsrep-restart-slave=1

slave-skip-errors=1396

log_bin                 = /var/log/mysql/mariadb-bin

log_bin_index           = /var/log/mysql/mariadb-bin.index

relay_log               = /var/log/mysql/relay-bin

relay_log_index         = /var/log/mysql/relay-bin.index

/etc/mysql/conf.d/galera.cnf - Node X

server-id=201

gtid-domain-id=2

binlog-format=ROW

log-slave-updates=1

#log-bin=binlog

wsrep-restart-slave=1

slave-skip-errors=1396

log_bin                 = /var/log/mysql/mariadb-bin

log_bin_index           = /var/log/mysql/mariadb-bin.index

relay_log               = /var/log/mysql/relay-bin

relay_log_index         = /var/log/mysql/relay-bin.index

[Philip Stoev]

Hello,

Galera replicates all DDL statements, even those that have failed on the
master and returned an error to the client. If there is an error replicating
a DDL statement, this is reflected in the log but replication continues
unaffected. So if the database and its tables exist on all the nodes and
there are no other errors in the log, the error about the DDL can be
disregarded.

gtid-domain-id is a MariaDB-specific feature and it is best if you ask a
MariaDB Cluster-specific mailing list about it.

Philip Stoev

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

[Daniel Black]

As a reminder I've included my previous advice that you have ignored.

The cause is setting gtid-domain-id to different values on both sides, like I advised you not to.

Look in the binary log and you'll probably see the CREATE DATABASE on A and X occurring under different gtid-domains-ids.

They are replicate on way find and when it comes backwards on a different domain id and it gets applied again. And magic error.

see multi master ring about 1/2 way down:

https://mariadb.com/kb/en/mariadb/global-transaction-id/#use-with-multi-source-replication-and-other-multi-master-setups

I suppose you didn't listen this time so why should you now.

----- On 24 Sep, 2015, at 3:59 PM, trupti mali trupt...@gmail.com wrote:

> Hi,
> I have setup master to master async replication between two galera
> clusters's primary nodes.
> e.g.
> 3 nodes in Datacenter1 A, B ,C - A the primary node
> 3 nodes in Datacenter2 X, Y, Z - X the primary node
>
> Now I setup a master - master async replication between A and X. For that I
> have setup following things. Notice the highlighted parameters. I have
> pruposely set gtid-domain-id as distinct for two masters. But whereas the
> database to be replicated from both the nodes A and X is same. When I setup
> wan replicaiton with this, I get an error in one of the slaves saying

> *"*Error 'Can't create database '<DBName>'; database exists' on query.

> Default database: '<DBName>'. Query: 'CREATE DATABASE <DBName>'"
> -DBName is my app's database name. Am i doing anythiing wrong in setting up
> the config parameters here?
>

> */etc/mysql/conf.d/galera.cnf - Node A*
>
>
>
> * server-id=101 gtid-domain-id=1 binlog-format=ROW log-slave-updates=1

> #log-bin=binlog wsrep-restart-slave=1 slave-skip-errors=1396 log_bin
> = /var/log/mysql/mariadb-bin log_bin_index =
> /var/log/mysql/mariadb-bin.index relay_log =
> /var/log/mysql/relay-bin relay_log_index =
> /var/log/mysql/relay-bin.index/etc/mysql/conf.d/galera.cnf - Node
> Xserver-id=201gtid-domain-id=2binlog-format=ROWlog-slave-updates=1#log-bin=binlogwsrep-restart-slave=1slave-skip-errors=1396log_bin
> = /var/log/mysql/mariadb-binlog_bin_index =
> /var/log/mysql/mariadb-bin.indexrelay_log =
> /var/log/mysql/relay-binrelay_log_index =

> /var/log/mysql/relay-bin.index*

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

----- On 11 Sep, 2015, at 6:06 PM, Daniel Black daniel...@openquery.com.au wrote:

----- On 11 Sep, 2015, at 5:53 PM, Trupti trupt...@gmail.com wrote:

> Hi Daniel,
> This was regarding your response on http://mariadb.com/kb/en/hybrid-
> replication-issue/+comments/1693 thread.
>
> I tried initiating the discussion with maria-discussion DL but havent
> heard back from anyone yet. Hence contacting you directly.
>
> So I really was in urgent need to sort out the issue that I am facing.
>
> Per your response - 1) Yes I have learnt about split brain - but this is
> our test env so we may not continue having 2 node galera cluster in
> live.
> 2) The other node - single one is not a galera cluster - but again this
> was done this way since its test env.
>
> 3) we really need to achieve a to and fro asyn replication between two
> galera clusters located in two different data centers.

Don't use async when you write to both ends.

Use galera across the WAN. There is no other safe way.

> 4) Replication users...so I created replication users on node1 in DC1
> and node1 in DC2 and then in both the nodes added cross references to
> each other nodes as masters.
>
> 5) GTID_domain_id was kept as unique for DC1 galera and DC2 galera...

This isn't a magic solution. It is also probably wrong to set these to different values unless you are operating on independent databases on both sides.

>And
> server_ids for each node were kept unique. e.g. node_dc1 = server id =
> 101 , 102 and server ids in dc2 = 201.

good.

> Just wanted to understand , I can see replication working properly. But
> whats wrong with my config that my mysql data in node2 of dc1 is getting
> wiped off. And this error is repetitive - every time I recreate the env
> from scratch with same configuration as mentioned above.

Focusing on solving this in a poor architecture isn't good.

Get to a flat galera on the same domain id. There are is tips here on WAN usage http://galeracluster.com/documentation-webpages/index.html

raise gcs.fc_limit.



--
--
Daniel Black, Engineer @ Open Query (http://openquery.com.au)
Remote expertise & maintenance for MySQL/MariaDB server environments.

[trupti mali]

Hi Daniel ,

I appreciate your help. I had tried your suggestion before. Infact I also followed your suggestion of using 3 node galera cluster. Inspite of that when I am testing my Async master to master replication with various permutations combinations - I am facing issues. Hence I edited the config to have different gtid_domain_id instead of same as you advised before - it was one of the trials to fix the issues that  I am facing.

Now after your suggestion I again used same domain id. And while testing , I put the sql dump on both the galera nodes and then configured async replication between two galera masters. At that onset - I again faced the issue of Mysql.user table of secondary node of one of the galera clusters get wiped off.

The settings remain same as I discussed before. Only difference is gtid_domain id has been kept same now.