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.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Artsiom Shymanouski
  • 13
  • 3

1 Answers1

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?)

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 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)? – Artsiom Shymanouski Feb 20 '23 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. – RolandoMySQLDBA Feb 21 '23 at 15:32