How to backup a replica Using mysqldump?

Question

From MySQL Documents, and This answer they said:

When using mysqldump, you should stop replication on the replica before starting the dump process to ensure that the dump contains a consistent set of data:

Since my DBs are big, I thought to use --single-transaction --quick to take a snapshot of my DBs and continue to receive data from the master.

My question is, what will happen if I do not stop the replica and receive new data during backup time?

Note: I know --single-transaction take a snapshot, But I want to know if my approach has any disadvantages.

"Consistent snapshot" is a current state of table(s) with undo log applied up to the moment CS has been created. If tables are modified intensively then a significant overhead (CPU/RAM/IO) can appear. You have to try it with your actual database to decide if this overhead is affordable to you. — Kondybas, Feb 20 '23 at 14:25

Rick James · Answer 1 · 2023-02-20T21:08:02.897

0

This will decrease the contention between the Replica's clients and the dump:

STOP REPLICA SQL_THREAD; And leave the IO_THREAD running.

It leaves the I/O going to minimize potential loss if the Primary crashes.

If you need the Replica's clients to still hit the db, don't bother with this STOP.

edited Feb 20 '23 at 21:08

answered Feb 20 '23 at 18:03

Rick James

78,038
5
47
113

can you please let me know why my approach is wrong, and how your approach solves this problem? – Niyaz Feb 20 '23 at 20:06
Sorry, I did intend to invalidate your answer. I updated my Answer. – Rick James Feb 20 '23 at 21:09

How to backup a replica Using mysqldump?

1 Answers1