Dear everyone,
I have 3 cluster server: db1, db2, db3.
Then I configure master-master replication in db3 & db6.
When I execute "DROP TABLE IF EXISTS X" on db1, I noticed this query kept looping in cluster servers.
It must have something wrong on my setting, probably in db6. But I couldnt figure out what is wrong..
db1:
2017-03-08 19:26:33 140197234416384 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:242211
2017-03-08 19:26:33 140197234416384 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:242212
2017-03-08 19:26:33 140197234416384 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:242213
2017-03-08 19:26:33 140197234416384 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:242214
2017-03-08 19:26:33 140197234416384 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:242215
2017-03-08 19:26:33 140197234416384 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:242216
2017-03-08 19:26:33 140197234416384 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:242217
db2:
2017-03-08 19:25:36 139918336592640 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:238816
2017-03-08 19:25:36 139918336592640 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:238817
2017-03-08 19:25:36 139918336592640 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:238818
2017-03-08 19:25:36 139918336592640 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:238819
2017-03-08 19:25:36 139918336592640 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:238820
2017-03-08 19:25:36 139918336592640 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:238821
db3:
2017-03-08 19:27:18 140004951939840 [Note] WSREP: TO END: 244690, update seqno
2017-03-08 19:27:18 140004951939840 [Note] WSREP: TO END: 244690
2017-03-08 19:27:18 140004951939840 [Note] WSREP: apply_event_and_update_pos() result: 0
2017-03-08 19:27:18 140004951939840 [Note] WSREP: apply_event_and_update_pos() result: 0
2017-03-08 19:27:18 140004951939840 [Note] WSREP: TO BEGIN: -1, 0 : DROP TABLE IF EXISTS `contact_book` /* generated by server */
2017-03-08 19:27:18 140004951939840 [Note] WSREP: TO BEGIN: 244691, 2
2017-03-08 19:27:18 140004951939840 [Note] WSREP: avoiding binlog rotate due to TO isolation: 1
2017-03-08 19:27:18 140004951939840 [Note] WSREP: TO END: 244691, 2 : DROP TABLE IF EXISTS `contact_book` /* generated by server */
2017-03-08 19:27:18 140004951939840 [Note] WSREP: Set WSREPXid for InnoDB: a447d33a-031a-11e7-b1ad-4a248c99f1ef:244691
2017-03-08 19:27:18 140004951939840 [Note] WSREP: TO END: 244691, update seqno
2017-03-08 19:27:18 140004951939840 [Note] WSREP: TO END: 244691
2017-03-08 19:27:18 140004951939840 [Note] WSREP: apply_event_and_update_pos() result: 0
db6:
2017-03-08 18:28:28 139883918183168 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000001' at position 313, relay log './relay-bin.000001' position:
2017-03-08 18:28:29 139883839691520 [Note] Slave I/O thread: connected to master 're...@x.x.x.x:3306',replication started in log 'mysql-bin.000001' at position 313
setting in db3:
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
skip_name_resolve
user = mysql
symbolic-links = 0
init_connect = 'set autocommit=1'
gtid_domain_id = 1
server_id = 3
log_slave_updates = 1
thread_cache_size = 16
default_storage_engine = innodb
binlog_format = ROW
sync_binlog = 0
bind-address = 0.0.0.0
query_cache_type = 1
innodb_autoinc_lock_mode = 2
# wsrep
wsrep_on = ON
wsrep_debug = ON
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_provider_options = "gcache.size=300M; gcache.page_size=300M; gcs.fc_limit=128; gmcast.segment=0"
wsrep_cluster_name = MariaDB_Cluster
wsrep_node_name = db3
wsrep_node_address = "db3_ip"
wsrep_cluster_address = "gcomm://db1_ip,db2_ip,db3_ip"
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = sst_user:sst_password
# Replication
binlog_do_db = database1
binlog_do_db = database2
report-host = db3.domain.com
relay-log = relay-bin
replicate-do-db=database1
replicate-do-db= database2
slave_compressed_protocol = ON
[sst]
streamfmt = xbstream
progress = 1
time = 1
compressor = gzip
decompressor = 'gzip -dc'
[xtrabackup]
parallel = 8
compress
compress-threads = 8
[mysql_safe]
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
setting in db6:
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
skip_name_resolve
user = mysql
symbolic-links = 0
init_connect = 'set autocommit=1'
gtid_domain_id = 1
server_id = 4
log_slave_updates = 1
thread_cache_size = 16
default_storage_engine = innodb
binlog_format = ROW
sync_binlog = 0
bind-address = 0.0.0.0
query_cache_type = 1
innodb_autoinc_lock_mode = 2
# Replication
binlog_do_db = database1
binlog_do_db = database2
report-host = db6.domain.com
relay-log = relay-bin
replicate-do-db=database1
replicate-do-db= database2
slave_compressed_protocol = ON
[xtrabackup]
parallel = 8
compress
compress-threads = 8
[mysql_safe]
log-error = /var/log/mysql/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
Appreciate your time and advice.
Thanks!