maria-discuss team mailing list archive

[AskMonty KB <noreply@xxxxxxxxxxxx>]

Hello,

A new question has been asked in "Replication, Cluster, & Multi-Master" by truptimali. Please answer it at http://mariadb.com/kb/en/hybrid-replication-issue/ as the person asking the question may not be subscribed to the mailing list.

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

To view or answer this question please visit: http://mariadb.com/kb/en/hybrid-replication-issue/