I have the next infrastructure of mysql servers:
Node1 - MySQL 5.1.X
Node2 - MariaDB 10.3.X
Node3 - MariaDB 10.3.X
Node4 - MariaDB 10.3.X
Node1 is a master replication node.
Node2 is a slave replication node and a member of galera cluster.
Node3 and node4 are members of galera cluster.
When I start the galera cluster with the node2 and then start the replication from node1 all works perfectly and node2 recover all transactions and the parameter "Seconds_behind_master" is 0. The problem occurs when add node3 or node4 to de galera cluster then seconds_behind_master increase indefinitely (Problem happens after SST was recovery on node3 or node4). If I take off node3 from cluster then node2 start to recover perfectly and seconds_behind_master become to 0 again (same with node4).
If I start the galera cluster with the node2 (without replication) and add members node3 and node4 to the cluster every members are synced and the cluster is working properly.
show you the galera cluster configuration (Same on all nodes except node name and address):
#
# * Galera-related settings
#
[galera]
# Mandatory settings
bind-address=0.0.0.0
binlog_format=row
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name="CLST01"
wsrep_cluster_address="gcomm://192.168.231.92,192.168.231.93,192.168.231.94"
wsrep_provider_options="gcache.size=5G;pc.weight=2"
wsrep_sst_method=mariabackup
wsrep_sst_auth=user:p4ssw0rd
wsrep_node_address="192.168.231.92"
wsrep_node_name="node2"
wsrep_slave_threads= 128 # 4
wsrep_certify_nonPK=1
wsrep_max_ws_rows=0
wsrep_max_ws_size=2147483647
wsrep_debug=0
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=1
#wsrep_auto_increment_control=1
wsrep_drupal_282555_workaround=0
wsrep_causal_reads=0
wsrep_notify_cmd=
wsrep_gtid_mode = ON
wsrep_gtid_domain_id = 20
log_slave_updates = ON
UPDATE
I paste the output of "show slave status \G;" for more information:
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 59577 Server version: 10.3.35-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.231.67
Master_User: replica-1001
Master_Port: 9905
Connect_Retry: 60
Master_Log_File: mysql-bin.001917
Read_Master_Log_Pos: 620413972
Relay_Log_File: mariadb-relay-bin.000269
Relay_Log_Pos: 1002161069
Relay_Master_Log_File: mysql-bin.001906
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: ecap.tmp%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1002160774
Relay_Log_Space: 12431589187
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 295623 Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 550
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Apply log event
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0 1 row in set (0.000 sec)
ERROR: No query specified
UPDATE2:
Show you the output of show engine innodb status;:
MariaDB [(none)]> show engine innodb status \G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2022-10-14 08:35:48 0x7fb95ddf3700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 719027 srv_active, 0 srv_shutdown, 19551 srv_idle
srv_master_thread log flush and writes: 738578
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1285260
OS WAIT ARRAY INFO: signal count 1027458
RW-shared spins 911646, rounds 11771287, OS waits 207738
RW-excl spins 313700, rounds 6048610, OS waits 150643
RW-sx spins 1073, rounds 11991, OS waits 194
Spin rounds per wait: 12.91 RW-shared, 19.28 RW-excl, 11.18 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 382957728
Purge done for trx's n:o < 382957727 undo n:o < 0 state: running
History list length 13
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421924775645448, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 382957727, ACTIVE 0 sec
5 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 12129, OS thread handle 140434156287744, query id 106152533 Apply log event
---TRANSACTION 421924775637096, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
9742351 OS file reads, 85758158 OS file writes, 2353761 OS fsyncs
13.30 reads/s, 16384 avg bytes/read, 108.69 writes/s, 1.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 122884, seg size 122886, 3554770 merges
merged operations:
insert 3726793, delete mark 3801, delete 178
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 1328051, node heap has 656 buffer(s)
Hash table size 1328051, node heap has 4 buffer(s)
Hash table size 1328051, node heap has 3113 buffer(s)
Hash table size 1328051, node heap has 322 buffer(s)
Hash table size 1328051, node heap has 6 buffer(s)
Hash table size 1328051, node heap has 16 buffer(s)
Hash table size 1328051, node heap has 19556 buffer(s)
Hash table size 1328051, node heap has 496 buffer(s)
135.19 hash searches/s, 289.17 non-hash searches/s
---
LOG
---
Log sequence number 2381870291003
Log flushed up to 2381870271639
Pages flushed up to 2381677456675
Last checkpoint at 2381677456675
0 pending log flushes, 0 pending chkp writes
79217168 log i/o's done, 108.70 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 5498208256
Dictionary memory allocated 1931776
Buffer pool size 327640
Free buffers 8192
Database pages 295279
Old database pages 108837
Modified db pages 38731
Percent of dirty pages(LRU & free pages): 12.763
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5272470, not young 68984994
3.30 youngs/s, 38.70 non-youngs/s
Pages read 9741564, created 164105, written 6541450
13.30 reads/s, 0.50 creates/s, 0.00 writes/s
Buffer pool hit rate 994 / 1000, young-making rate 1 / 1000 not 16 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 295279, unzip_LRU len: 0
I/O sum[2248]:cur[24], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 40955
Free buffers 1024
Database pages 36974
Old database pages 13628
Modified db pages 5510
Percent of dirty pages(LRU & free pages): 14.500
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 719021, not young 9087125
0.70 youngs/s, 2.20 non-youngs/s
Pages read 1287471, created 21599, written 899770
2.10 reads/s, 0.30 creates/s, 0.00 writes/s
Buffer pool hit rate 993 / 1000, young-making rate 2 / 1000 not 6 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36974, unzip_LRU len: 0
I/O sum[281]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 40955
Free buffers 1024
Database pages 36881
Old database pages 13594
Modified db pages 3604
Percent of dirty pages(LRU & free pages): 9.508
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 615500, not young 8632929
0.40 youngs/s, 23.40 non-youngs/s
Pages read 1158552, created 20855, written 765872
1.70 reads/s, 0.10 creates/s, 0.00 writes/s
Buffer pool hit rate 989 / 1000, young-making rate 2 / 1000 not 148 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36881, unzip_LRU len: 0
I/O sum[281]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 40955
Free buffers 1024
Database pages 36971
Old database pages 13627
Modified db pages 4911
Percent of dirty pages(LRU & free pages): 12.925
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 712683, not young 8934194
0.40 youngs/s, 2.80 non-youngs/s
Pages read 1303827, created 18588, written 802873
1.90 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 995 / 1000, young-making rate 0 / 1000 not 6 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36971, unzip_LRU len: 0
I/O sum[281]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 40955
Free buffers 1024
Database pages 36792
Old database pages 13562
Modified db pages 4920
Percent of dirty pages(LRU & free pages): 13.010
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 645424, not young 8610556
0.40 youngs/s, 3.10 non-youngs/s
Pages read 1193580, created 20421, written 817856
2.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 992 / 1000, young-making rate 1 / 1000 not 12 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36792, unzip_LRU len: 0
I/O sum[281]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 40955
Free buffers 1024
Database pages 36990
Old database pages 13634
Modified db pages 4834
Percent of dirty pages(LRU & free pages): 12.716
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 634491, not young 7937938
0.30 youngs/s, 2.10 non-youngs/s
Pages read 1191553, created 21154, written 800380
1.80 reads/s, 0.10 creates/s, 0.00 writes/s
Buffer pool hit rate 994 / 1000, young-making rate 0 / 1000 not 6 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36990, unzip_LRU len: 0
I/O sum[281]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 40955
Free buffers 1024
Database pages 36965
Old database pages 13625
Modified db pages 4756
Percent of dirty pages(LRU & free pages): 12.519
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 633458, not young 8101905
0.50 youngs/s, 2.00 non-youngs/s
Pages read 1161175, created 19955, written 782866
1.50 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 994 / 1000, young-making rate 1 / 1000 not 6 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36965, unzip_LRU len: 0
I/O sum[281]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 40955
Free buffers 1023
Database pages 36913
Old database pages 13606
Modified db pages 5582
Percent of dirty pages(LRU & free pages): 14.714
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 697449, not young 9064750
0.40 youngs/s, 1.20 non-youngs/s
Pages read 1272947, created 20902, written 888280
1.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 997 / 1000, young-making rate 1 / 1000 not 3 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36913, unzip_LRU len: 0
I/O sum[281]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 40955
Free buffers 1025
Database pages 36793
Old database pages 13561
Modified db pages 4614
Percent of dirty pages(LRU & free pages): 12.200
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 614444, not young 8615597
0.20 youngs/s, 1.90 non-youngs/s
Pages read 1172459, created 20631, written 783553
1.30 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 992 / 1000, young-making rate 1 / 1000 not 11 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36793, unzip_LRU len: 0
I/O sum[281]:cur[3], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=934808, Main thread ID=140441754355456, state: sleeping
Number of rows inserted 30258359, updated 8304488, deleted 121585, read 404383748
41.40 inserts/s, 11.90 updates/s, 0.00 deletes/s, 68.09 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.001 sec)
ERROR: No query specified
UPDATE 3
If I disable the bin-log configuration then node3 joins to the cluster perfectly and node2 recover Seconds_behind_master to 0. The bin-log configuration was made on node3:
Previous configuration:
# BINARY LOGGING #
#skip-log-bin
log-bin = /var/log/mysql/bin/mysql-bin
log-slave-updates = 1
expire-logs-days = 14
sync-binlog = 1
binlog_cache_size = 1M
Current config:
# BINARY LOGGING #
skip-log-bin
#log-bin = /var/log/mysql/bin/mysql-bin
#log-slave-updates = 1
#expire-logs-days = 14
#sync-binlog = 1
#binlog_cache_size = 1M
Can't activate the log-bin configuration? Why the cluster becomes freeze if binary logging is enabled?
Could you please help me? some ideas?
Regards,
SHOW SLAVE STATUSon node2 show as the seconds behind master is increasing indefinitely. I assume it will show a SQL thread based error? Ensure you have primary/unique keys on all tables as a) a galera requirement, and b), lurking datatype bugs like MDEV-7980 as a few ancient binary log format messages don't have sufficient tests. Like Rick said, 10.3 eol soon, so a 10.4+ (that uses galera-4 making upgrades easier), probably 10.6 would be better however and migrate away from the 5.1 master as soon as stable.