Hi team,
I have facing a replication issue in my DB setup where we have a master-slave server and replication is ON between the servers.
Environment: MariaDB 10.6.11
DB size: approx. 1TB
While taking mariabackup, at the stage of preparing backup I see some interpretation in replication which I can see in MySQL logs. Backup was successful but the replication is not catching up with the master and able to see the slave worker are getting stuck forever (as per Processlist). Even if we stop the slave SQL thread or stop slave not fixing the issue. (WIthout backup process the replication is working fine without any delays)
Error Log:
[Note] Error reading relay log event: slave SQL thread was killed[Note] Slave SQL thread exiting, replication stopped in log 'binary-log.015277' at position 266164018; GTID position '0-2-439338736', master: 172.16.117.178:3307[Note] Slave SQL thread initialized, starting replication in log 'binary-log.015277' at position 266164018, relay log './mysql-1-relay-bin.004330' position: 94753264; GTID position '0-2-439338736'
ProcessList:
> show processlist; \! date
+--------+-------------+----------------------+------+--------------+--------+-----------------------------------------------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+--------+-------------+----------------------+------+--------------+--------+-----------------------------------------------+-----------------------+----------+
| 8 | system user | | NULL | Slave_IO | 600078 | Waiting for master to send event | NULL | 0.000 |
| 704611 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704614 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704612 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704613 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704615 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704617 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704616 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704619 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704618 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704627 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704620 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704621 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704622 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704623 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704625 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704629 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704626 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704624 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704632 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704628 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704630 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704636 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704631 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704634 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704637 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704633 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704635 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704643 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704638 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704639 | system user | | NULL | Slave_worker | 113000 | closing tables | NULL | 0.000 |
| 704641 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704642 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704651 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704652 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704645 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704654 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704648 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704646 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704649 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704656 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704650 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704644 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704657 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704653 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704640 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704655 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704647 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704658 | system user | | NULL | Slave_worker | 113000 | Waiting for prior transaction to commit | NULL | 0.000 |
| 704610 | system user | | NULL | Slave_SQL | 113039 | Waiting for room in worker thread event queue | NULL | 0.000 |
| 733190 | root | 172.16.117.210:49448 | NULL | Query | 46259 | Killing slave | STOP SLAVE SQL_THREAD | 0.000 |
| 733618 | root | 172.16.117.210:55994 | naf | Query | 0 | starting | show processlist | 0.000 |
+--------+-------------+----------------------+------+--------------+--------+-----------------------------------------------+-----------------------+----------+
52 rows in set (0.000 sec)
Wed Apr 5 14:10:00 CEST 2023
Does it mean running a backup in a slave might impact the replication? If this is expected behavior, do we have any proper way to achieve a backup on a daily basis in the replication server?
Or The backup is not the problem and some other factor (like a deadlock) is affecting the slave thread to lock?
Regards,
Ragul R