0

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.

1
  • "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, 2023 at 14:25

1 Answer 1

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.

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

Your Answer

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

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