2

Anybody has experience with setting up replication between Galera Cluster and standalone MySQL slave?

I have a three nodes in Galera Cluster and I want to set up gtid replication to MySQL slave. The problem that is all Galera nodes write their own bin logs and seems it causing duplication queries. As result, I get infinite errors like this:

Cannot add or update a child row: a foreign key constraint fails ..., Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW; the event's master log mysql-bin.000003, end_log_pos 76110900

replication settings on Galera nodes:

[mysqld]
log_slave_updates=1
log_bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency=1
expire_logs_days=7
server_id = 3 # 1 for node1, 2 for node2, 3 for node3
binlog_format=ROW

Maybe do you know how is possible to resolve it ?

For setting I used this topic: http://severalnines.com/blog/how-set-asynchronous-replication-galera-cluster-standalone-mysql-server-gtid

Galera nodes version: 5.6.25

MySQL slave version: 5.6.27

jumpy
  • 317
  • 1
  • 12
  • Do you connect the slave to 1 Galera Node without a proxy or MaxScale ? – Bernd Buffen Jun 07 '16 at 08:43
  • Galera nodes and MySQL slave placed in the different AWS regions, but I set up nat instance, so they can reach each other via IP without problems – jumpy Jun 07 '16 at 09:18
  • so the slave goes alway to the same galera node und did not switch to a other galera node ? i do it with the same config without problems – Bernd Buffen Jun 07 '16 at 09:29
  • yep, it's initial setup replication. do you have a different server_ids for each node in cluster? – jumpy Jun 07 '16 at 10:00
  • yep i have different id for each node, but sorry i dont understand why there are duplications in one binlog file – Bernd Buffen Jun 07 '16 at 11:01
  • i have also testet 2 galera cluster (each 3 Nodes) connected with Master / Master replication and it works pretty – Bernd Buffen Jun 07 '16 at 11:03
  • @BerndBuffen, anyway thanks for reply. May I ask your versions of Galera nodes and MySQL slave? – jumpy Jun 07 '16 at 13:29
  • i use 10.1.14-MariaDB-1~jessie, but i have testet it before and there is a bug in Master / Master replication between Galera Cluster. I nearly sure that is fi in Version 10.0.23 – Bernd Buffen Jun 07 '16 at 13:34

1 Answers1

0

I set the same server_id values for all nodes in Galera clustes and now all works properly.

jumpy
  • 317
  • 1
  • 12