I'm tasked with starting a brand new replica of a client's MySQL 5.7.35 database server. While I've observed others doing this in the past in various circumstances, I haven't been in charge of this operation before, so I'd just like to confirm that my plan is correct and that I'm not missing anything crucial.
The data lives in an XFS-formatted LVM logical volume, inside a volume group that has enough free space to enable taking an LVM snapshot.
The database is a legacy mixture of MyISAM and InnoDB tables, over 200 GB in size at the moment. The replica server is set up with an identical MySQL version, and all of the prerequisites for row-based binlog replication (replica user, server ID, log_bin
etc.) are in order.
My process is this:
- On the master, run
FLUSH TABLES WITH READ LOCK
. Leave the client session open.- According to Percona, it's important to try to prevent long-running
SELECT
queries from being in the middle of execution when this is run, so we'll make an effort to ensure this.
- According to Percona, it's important to try to prevent long-running
- In another client session (don't know why, but the docs say so), run
SHOW MASTER STATUS
to get the binlog file name and position. - Create the LVM snapshot from the MySQL data volume. (Does not contain binlogs or relay logs.)
- In the original MySQL client session, run
UNLOCK TABLES
to restore normal operation. - Mount the snapshot (
-t xfs -o nouuid
) andrsync
the data directory from it onto the (shut down) replica server – excludingauto.cnf
and the autogenerated.pem
files (we're not using SSL to connect to MySQL, so they shouldn't particularly matter). - Unmount and
lvremove
the snapshot. - Start up MySQL on the replica and do the normal binlog replication initialization steps as outlined in the MySQL documentation, providing the
MASTER_LOG_FILE
andMASTER_LOG_POS
options toCHANGE MASTER
with the values from step 2, and then just wait for the replica to catch up.
I've tested this process to work on a trafficless staging server, but my main concern is FLUSH TABLES WITH READ LOCK
on the actual production instance. Is it sufficient to prevent long-running SELECT
s or is there some other danger I need to be aware of? Any clients attempting writes will naturally fail while the lock is in place, but the goal is to get steps 1–4 finished in under 30 seconds, which is an acceptable partial outage for us.
Also, since we're running mixed MyISAM and InnoDB, is FLUSH TABLES WITH READ LOCK
all we need to do, or does one of the engines require something more to ensure a consistent LVM snapshot?