2.2.5.1 Creating a Data Snapshot Using mysqldump (original) (raw)

2.2.5.1 Creating a Data Snapshot Using mysqldump

To create a snapshot of the data in an existing source database, use the mysqldump tool. Once the data dump has been completed, import this data into the replica before starting the replication process.

The following example dumps all databases to a file nameddbdump.db, and includes the--master-data option which automatically appends the CHANGE REPLICATION SOURCE TO | CHANGE MASTER TO statement required on the replica to start the replication process:

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

It is possible to exclude certain databases from the dump using the mysqldump tool. If you want to choose which databases to include in the dump, do not use--all-databases. Choose one of these options:

• Exclude all the tables in the database using--ignore-table option.
• Name only those databases which you want dumped using the--databases option.

Note

By default, if GTIDs are in use on the source (gtid_mode=ON),mysqldump includes the GTIDs from thegtid_executed set on the source in the dump output to add them to thegtid_purged set on the replica. If you are dumping only specific databases or tables, it is important to note that the value that is included by mysqldump includes the GTIDs of all transactions in thegtid_executed set on the source, even those that changed suppressed parts of the database, or other databases on the server that were not included in the partial dump. Check the description for mysqldump's --set-gtid-purged option to find the outcome of the default behavior for the MySQL Server versions you are using, and how to change the behavior if this outcome is not suitable for your situation.

For more information, see mysqldump — A Database Backup Program.

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.