Async replication between two Galera Clusters. Hybrid replication issue.

[trupti mali]

Hi,

I have been trying to implement a Hybrid replication.

There is Async replication configured between two galera clusters. To begin with I have been trying this out on two datacenters dc1 and dc2.

dc1 has two node galera cluster -- node1_dc1 , node2_dc1

dc2 has one node - just one primary galera  node . node1_dc2

I have configured async replication between node1_dc1 and node1_dc2.

But I am facing strange problem here. I could see replication happening nicely on all the three nodes involved. But When i tried to restart node2_dc1 ,(the secondary node from dc1's galera cluster) - i am not able to login to this node's mysql with  root user. Or any user for that matter. Upon investigation using skip grant, found that the mysql.user table is emptied. (You can refer to the attached logs of node2_dc1 where I have highlighted removal messages in log). My doubts/concerns - 

1) I am not sure what could have triggered this mysql table's removal in first place.

2) Inspite of this - I could get this node join the cluster. (Atleast as per the wsrep_cluster_size attribute on primary node)

sharing my configurations on all the three nodes - 

1) node1_dc1 (Primary Node)

 galera_cnf : 

[mysqld]

#mysql settings

binlog_format=ROW

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.XXX.YYY"

wsrep_node_name="192.168.XXX.YYY"

************************************

wan.cnf:

[mysqld]

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

 

2) node2_dc1 (Secondary node):

galera.cnf:

[mysqld]

#mysql settings

binlog_format=ROW

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://192.168.XXX.YYY,192.168.YYY.ZZ

wsrep_sst_method=xtrabackup-v2

wsrep_sst_auth="root:XXX"

wsrep_restart_slave=1

# Galera Node Configuration

wsrep_node_address="192.168.YYY.ZZ"

wsrep_node_name="192.168.YYY.ZZ"

wan.cnf:

[mysqld]

server-id=102

gtid-domain-id=1

binlog-format=ROW

log-slave-updates=1

log-bin=binlog

wsrep-restart-slave=1

slave-skip-errors=1396

Node1_dc2 - Single node on datacenter-2 :

galera.cnf:

[mysqld]

#mysql settings

binlog_format=ROW

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.ZZZ.XXX"

wsrep_node_name="192.168.ZZZ.XXX"

wan_repl.cnf:

[mysqld]

server-id=201

gtid-domain-id=1

binlog-format=ROW

log-slave-updates=1

log-bin=binlog

wsrep-restart-slave=1

slave-skip-errors=1396

~                           

Note unique server-ids and common gtid-domain-id.

Attaching logs of both the nodes in galera cluster in datacenter 1.

lines to look for in node2.txt - file 

***********

Sep 10 21:06:21 localhost mysqld: 150910 21:06:21 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 0)

Sep 10 21:06:21 localhost mysqld: 150910 21:06:21 [Note] WSREP: Requesting state transfer: success, donor: 0

Sep 10 21:06:22 localhost mysqld: WSREP_SST: [INFO] Proceeding with SST (20150910 21:06:22.394)

Sep 10 21:06:22 localhost mysqld: WSREP_SST: [INFO] Cleaning the existing datadir (20150910 21:06:22.400)

Sep 10 21:06:22 localhost mysqld: removed ?~@~X/var/lib/mysql/mysql_upgrade_info?~@~Y

Sep 10 21:06:22 localhost mysqld: removed ?~@~X/var/lib/mysql/multi-master.info?~@~Y

Sep 10 21:06:22 localhost mysqld: removed ?~@~X/var/lib/mysql/mysql/procs_priv.MYI?~@~Y

Sep 10 21:06:22 localhost mysqld: removed ?~@~X/var/lib/mysql/mysql/user.MYI?~@~Y

*****************

Sep 10 21:25:51 localhost /etc/mysql/debian-start[16787]: Triggering myisam-recover for all MyISAM tables

Sep 10 21:25:53 localhost mysqld: 150910 21:25:53 [Note] WSREP: (81dce617, 'tcp://0.0.0.0:4567') turning message relay requesting off

Sep 10 21:32:01 localhost mysqld: 150910 21:32:01 [ERROR] Slave SQL: Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'SET PASSWORD FOR 'root'@'localhost'='*FD9D25929BE2AE3A18896D5D93AB9A7DD78D22E4'', Internal MariaDB error code: 1133

Sep 10 21:32:01 localhost mysqld: 150910 21:32:01 [Warning] WSREP: RBR event 1 Query apply warning: 1, 19504

******************