maria-discuss team mailing list archive

Thread
Date
Backup on the replication server getting affected

To: MariaDB discuss <maria-discuss@xxxxxxxxxxxxxxxxxxx>
From: ragul rangarajan <ragulrangarajan@xxxxxxxxx>
Date: Fri, 19 May 2023 19:52:18 +0530
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


Replication as a Backup Solution - MariaDB Knowledge Base
<https://mariadb.com/kb/en/replication-as-a-backup-solution/> as per the
page "Running the backup from a slave has the advantage of being able to
shutdown or lock the slave and perform a backup without any impact on the
primary server."
*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*
Follow ups

Re: Backup on the replication server getting affected
From: andrei . elkin, 2023-05-22