복제되지 않은 DB에 쓰는 경우 MariaDB(10.5.8) Galera 클러스터 노드의 GTID가 동기화되지 않음
MariaDB 공식 도커 10.5.8을 사용하여 3개의 노드로 구성된 Galera 클러스터를 구성했습니다.처음에는 일관된 가치를 확인할 수 있었습니다.gtid_binlog_pos모든 노드에서.그러나 얼마 후 복제해서는 안 되는 데이터베이스가 필요하여 데이터베이스를 만들었습니다.localA다음과 같이 구성됩니다.binlog_ignore_db = localA그 후에, 내가 무언가를 쓸 때마다.localA노드 A의gtid_binlog_pos노드 B와 노드 C의 값은 동일하게 유지되는 동안 증가했습니다.제 의심은 노드가 로컬 시퀀스 번호를 유지하고 이를 사용하여 자체 GTID를 생성한다는 것입니다.로컬 트랜잭션이 다른 노드로 전파되지 않기 때문에 해당 노드의 seq는 서로 이동하지 않습니다.
이게 예상된 건가요?아니면 제가 설정에서 무언가를 놓쳤나요?
비동기 복제본을 설정하려고 하는데 Galera 노드 중 하나로 마스터를 변경할 수 있기를 원하는데 이 동기화되지 않은 GTID가 차단됩니다.
다음은 Galera 노드의 구성입니다. 노드 A:
[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
노드 B:
[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
노드 C:
[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
언급URL : https://stackoverflow.com/questions/64892105/mariadb-10-5-8-galera-cluster-nodes-have-out-of-sync-gtid-if-writes-to-not-rep
'programing' 카테고리의 다른 글
| 브라우저별 AJAX(XmlHttpRequest) 시간 초과 길이 (0) | 2023.08.20 |
|---|---|
| 'computer-instance'는 동일한 이미지에 대해 여러 인스턴스를 생성합니다. (0) | 2023.08.20 |
| 잠금을 시도할 때 교착 상태가 발견되지 않도록 합니다. 중복 키 업데이트 시 MariaDB(MySQL) INSERT에서 트랜잭션을 다시 시작해 보십시오. (0) | 2023.08.20 |
| NSTime에서 변환시간, 분, 초, 밀리초 단위의 swift 간격 (0) | 2023.08.20 |
| Python의 예외 처리기 비용 (0) | 2023.08.20 |