I've configured a Galera cluster using MariaDB official docker 10.5.8, consisting of 3 nodes.
At first, I could see consistent value of gtid_binlog_pos in all nodes. But after a while I needed a database that shouldn't be replicated, so I created a database localA and configured like binlog_ignore_db = localA. After that, whenever I write something to localA, nodeA's gtid_binlog_pos increased while those of nodeB and nodeC stayed same. My suspicion is that nodes maintain local sequence number, and they use it to independently create a their own GTID. Because local transaction is not propagated to other nodes, their seq no drift from each other.
Is this expected? Or have I missed something in the setting?
I'm trying to set up an async replica and want to be able to change master to any of Galera node, but this out-of-sync GTID is blocking me.
Below is the configuration of Galera nodes: nodeA:
[mysqld]
user = mysql
server_id = 11
port = 13306
default-time-zone = '+0:00'
autocommit=0
skip_name_resolve = ON
max_connections = 1000
max_connect_errors = 1000
default_storage_engine = InnoDB
binlog_format = ROW
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/mysql-relay-bin.log
log_slave_updates = on
binlog_cache_size = 32768
binlog_row_event_max_size = 8192
binlog_ignore_db = localA
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_autoinc_lock_mode = 2
innodb_lock_schedule_algorithm = FCFS # MariaDB >10.1.19 and >10.2.3 only
innodb_rollback_on_timeout = 1
bind-address = 0.0.0.0
performance_schema = ON
innodb_print_all_deadlocks = ON
wsrep_log_conflicts = ON
general_log_file = '/var/log/mysql/mycustom.log'
log_output = 'FILE'
wsrep_on = ON
wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_sst_method = mariabackup
wsrep_gtid_mode = ON
wsrep_gtid_domain_id = 9999
wsrep_cluster_name = mycluster
wsrep_cluster_address = gcomm://10.1.50.27:7567,10.1.52.27:7567,10.1.54.27:7567
wsrep_sst_auth = repl:secret
wsrep_node_address = 10.1.50.27:7567
wsrep_provider_options = "ist.recv_addr=10.1.50.27:7568;socket.ssl_cert=/etc/mysql/certificates/maria-server-cert.pem;socket.ssl_key=/etc/mysql/certificates/maria-server-key.pem;socket.ssl_ca=/etc/mysql/certificates/maria-ca.pem;evs.keepalive_period=PT1S;evs.install_timeout=PT7.5S;evs.inactive_check_period=PT0.5S;evs.inactive_timeout=PT15S;evs.suspect_timeout=PT5S;gcs.max_packet_size=64500;evs.send_window=4;evs.user_send_window=2;gcs.fc_limit=16;gcache.size=128M;gcs.fc_factor=1"
wsrep_sst_receive_address = 10.1.50.27:7444
gtid_domain_id = 9011 # should be different for all nodes and from wsrep_gtid_domain_id
ssl_cert = /etc/mysql/certificates/maria-server-cert.pem
ssl_key = /etc/mysql/certificates/maria-server-key.pem
ssl_ca = /etc/mysql/certificates/maria-ca.pem
# File Key Management
plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/encryption/keyfile.enc
file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
file_key_management_encryption_algorithm = AES_CTR
# Enables table encryption, but allows unencrypted tables to be created
innodb_encrypt_tables = OFF
# Encrypt the Redo Log
innodb_encrypt_log = ON
# Binary Log Encryption
encrypt_binlog=ON
nodeB:
[mysqld]
user = mysql
server_id = 12
port = 13306
default-time-zone = '+0:00'
autocommit=0
skip_name_resolve = ON
max_connections = 1000
max_connect_errors = 1000
default_storage_engine = InnoDB
binlog_format = ROW
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/mysql-relay-bin.log
log_slave_updates = on
binlog_cache_size = 32768
binlog_row_event_max_size = 8192
binlog_ignore_db = localA
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_autoinc_lock_mode = 2
innodb_lock_schedule_algorithm = FCFS # MariaDB >10.1.19 and >10.2.3 only
innodb_rollback_on_timeout = 1
bind-address = 0.0.0.0
performance_schema = ON
innodb_print_all_deadlocks = ON
wsrep_log_conflicts = ON
general_log_file = '/var/log/mysql/mycustom.log'
log_output = 'FILE'
wsrep_on = ON
wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_sst_method = mariabackup
wsrep_gtid_mode = ON
wsrep_gtid_domain_id = 9999
wsrep_cluster_name = mycluster
wsrep_cluster_address = gcomm://10.1.50.27:7567,10.1.52.27:7567,10.1.54.27:7567
wsrep_sst_auth = repl:secret
wsrep_node_address = 10.1.52.27:7567
wsrep_provider_options = "ist.recv_addr=10.1.52.27:7568;socket.ssl_cert=/etc/mysql/certificates/maria-server-cert.pem;socket.ssl_key=/etc/mysql/certificates/maria-server-key.pem;socket.ssl_ca=/etc/mysql/certificates/maria-ca.pem;evs.keepalive_period=PT1S;evs.install_timeout=PT7.5S;evs.inactive_check_period=PT0.5S;evs.inactive_timeout=PT15S;evs.suspect_timeout=PT5S;gcs.max_packet_size=64500;evs.send_window=4;evs.user_send_window=2;gcs.fc_limit=16;gcache.size=128M;gcs.fc_factor=1"
wsrep_sst_receive_address = 10.1.52.27:7444
gtid_domain_id = 9012 # should be different for all nodes and from wsrep_gtid_domain_id
ssl_cert = /etc/mysql/certificates/maria-server-cert.pem
ssl_key = /etc/mysql/certificates/maria-server-key.pem
ssl_ca = /etc/mysql/certificates/maria-ca.pem
# File Key Management
plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/encryption/keyfile.enc
file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
file_key_management_encryption_algorithm = AES_CTR
# Enables table encryption, but allows unencrypted tables to be created
innodb_encrypt_tables = OFF
# Encrypt the Redo Log
innodb_encrypt_log = ON
# Binary Log Encryption
encrypt_binlog=ON
nodeC:
[mysqld]
user = mysql
server_id = 13
port = 13306
default-time-zone = '+0:00'
autocommit=0
skip_name_resolve = ON
max_connections = 1000
max_connect_errors = 1000
default_storage_engine = InnoDB
binlog_format = ROW
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/mysql-relay-bin.log
log_slave_updates = on
binlog_cache_size = 32768
binlog_row_event_max_size = 8192
binlog_ignore_db = localA
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_autoinc_lock_mode = 2
innodb_lock_schedule_algorithm = FCFS # MariaDB >10.1.19 and >10.2.3 only
innodb_rollback_on_timeout = 1
bind-address = 0.0.0.0
performance_schema = ON
innodb_print_all_deadlocks = ON
wsrep_log_conflicts = ON
general_log_file = '/var/log/mysql/mycustom.log'
log_output = 'FILE'
wsrep_on = ON
wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_sst_method = mariabackup
wsrep_gtid_mode = ON
wsrep_gtid_domain_id = 9999
wsrep_cluster_name = mycluster
wsrep_cluster_address = gcomm://10.1.50.27:7567,10.1.52.27:7567,10.1.54.27:7567
wsrep_sst_auth = repl:secret
wsrep_node_address = 10.1.54.27:7567
wsrep_provider_options = "ist.recv_addr=10.1.54.27:7568;socket.ssl_cert=/etc/mysql/certificates/maria-server-cert.pem;socket.ssl_key=/etc/mysql/certificates/maria-server-key.pem;socket.ssl_ca=/etc/mysql/certificates/maria-ca.pem;evs.keepalive_period=PT1S;evs.install_timeout=PT7.5S;evs.inactive_check_period=PT0.5S;evs.inactive_timeout=PT15S;evs.suspect_timeout=PT5S;gcs.max_packet_size=64500;evs.send_window=4;evs.user_send_window=2;gcs.fc_limit=16;gcache.size=128M;gcs.fc_factor=1"
wsrep_sst_receive_address = 10.1.54.27:7444
gtid_domain_id = 9013 # should be different for all nodes and from wsrep_gtid_domain_id
ssl_cert = /etc/mysql/certificates/maria-server-cert.pem
ssl_key = /etc/mysql/certificates/maria-server-key.pem
ssl_ca = /etc/mysql/certificates/maria-ca.pem
# File Key Management
plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/encryption/keyfile.enc
file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
file_key_management_encryption_algorithm = AES_CTR
# Enables table encryption, but allows unencrypted tables to be created
innodb_encrypt_tables = OFF
# Encrypt the Redo Log
innodb_encrypt_log = ON
# Binary Log Encryption
encrypt_binlog=ON
