0

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,

18
  • Only node1 is writable?
    – Rick James
    Oct 13, 2022 at 15:46
  • I am seriously concerned about the Primary being 5.1 -- this is antique. Both MySQL and Galera have evolved a lot in the 8 years before 10.3. 5.1 EOL'd in 2015; 10.3 will EOL in a few months.
    – Rick James
    Oct 13, 2022 at 15:50
  • I am also concerned about trying to mix MySQL and MariaDB in a single cluster. MariaDB started as a fork of MySQL in 2010, but now these should be considered two different database products, and there is no expectation that they are compatible. Oct 13, 2022 at 23:07
  • What does SHOW SLAVE STATUS on 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.
    – danblack
    Oct 14, 2022 at 2:27
  • @RickJames thanks for your answer. At this time we are testing the environment for a future migration to Mariadb and only node1 is receiving activity which is replicated to other MySQL nodes and now to MariaDB nodes. Then the right steps will be to update the MySQL replication environment first and then try to add the MariaDB nodes.
    – Miguelitio
    Oct 14, 2022 at 6:00

1 Answer 1

0

I think there are a couple problems here that may be interfering with clear debugging.

You have ancient versions, and are mixing Mysql and MariaDB.

Other than those two issues which will bring unknown problems to the table, it could be possible that one node is just too slow to participate in the cluster (eg. disk I/O), as a galera cluster will be as slow as the slowest participating node.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.