1

I have to migrate from outdated standalone MySQL 5.6.33 to new server with 5.7.41(a separate server) without downtime (or just as short as possible). To accomplish that I'm going to use replication (than switch slave to standalone mode). We have a fleet of not small databases: 300-600GB and with 100-10k requests per second.

To create dumps I'm going to use the following command:

mysqldump -umyuser -ppassowrd --master-data --single-transaction --routines --triggers --databases db_1 db_1 db_1 db_1 > selected_dbs.sql

Question: Should I use "FLUSH TABLES WITH READ LOCK" to be safe and sure that a backup is consistent and usable for a slave if I'm going to use "--master-data --single-transaction"? Or "FLUSH TABLES WITH READ LOCK" isn't necessary?

Thank you for any advice.

1 Answer 1

0

Using --master-data=2 --single-transaction should be adequate.

Look for the replication log file and position around lines 23-25 in the dump.

Please do not use FLUSH TABLES WITH READ LOCK; as it is not effective against InnoDB. I wrote about this back on Nov 12, 2012 (How to run a cold backup with Linux/tar without shutting down MySQL slave?)

2
  • RolandoMySQLDBA Thank you for advice! Can you please clarify: "--master-data=2 --single-transaction" is enough or it doesn't guarantee consistency to and would be better to avoid any write actions at all (or for example, stop the application when creating a dump)? Feb 20, 2023 at 20:19
  • --master-data=2 simply writes the log file and position as a comment. As mentioned, what you have is adequate. Once you set up replication, any new writes that occurred on the 5.6 Master will be replayed on the 5.7 Replica. Feb 21, 2023 at 15:32

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.