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
  • 185,223
  • 33
  • 326
  • 536
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
  • 185,223
  • 33
  • 326
  • 536