2.1.5 Creating a Data Snapshot Using mysqldump

One way to create a snapshot of the data in an existing source database is to use the mysqldump tool to create a dump of all the databases you want to replicate. Once the data dump has been completed, you then import this data into the replica before starting the replication process.

The example shown here dumps all databases to a file named dbdump.db, and includes the --master-data option which automatically appends the CHANGE MASTER TO statement required on the replica to start the replication process:

$> mysqldump --all-databases --master-data > dbdump.db

If you do not use --master-data, then it is necessary to lock all tables in a separate session manually (using FLUSH TABLES WITH READ LOCK) prior to running mysqldump, then exiting or running UNLOCK TABLES from the second session to release the locks. You must also obtain binary log position information matching the snapshot, using SHOW MASTER STATUS, and use this to issue the appropriate CHANGE MASTER TO statement when starting the replica.

When choosing databases to include in the dump, remember that you need to filter out databases on each replica that you do not want to include in the replication process.

To import the data, either copy the dump file to the replica, or access the file from the source when connecting remotely to the replica.