Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.1Mb
PDF (A4) - 31.1Mb
PDF (RPM) - 29.3Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 176.3Kb
Man Pages (Zip) - 286.5Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Creating a Data Snapshot Using mysqldump

17.1.1.5 Creating a Data Snapshot Using mysqldump

One way to create a snapshot of the data in an existing master 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 slave 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 slave to start the replication process:

shell> 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 slave.

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

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


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Ilan Hazan on April 7, 2011
Restoring a dump table into the MySQL master server can lead to serious replication delay.
To overcome the replication delay, caused by restoring the dump table on the master, there is a need to widespread the massive inserts. This can be done by the MySQL SLEEP command.
See http://www.mysqldiary.com/as-restoring-a-dump-table-into-the-mysql-master-you-better-get-some-sleep/
  Posted by Ender Li on July 5, 2011
If get a "Access denied for user 'ODBC'@'localhost'" error message.

1.run "MySQLInstanceConfig.exe".
2.Select "Create An Anonymous Account" at "Please set the security options".
  Posted by Stephen Wylie on January 25, 2012
When using --master-data, as Sprout has pointed out, not all the CHANGE MASTER statements required are in the dumpfile.
His suggestion of editing the dumpfile will work, but not trying to do another CHANGE MASTER to set the host, user etc after importing the dump. That will only work if you put in the filename and log co-ordinates again, as I found to my cost.
It won't tell you what's wrong either!
Every execution of CHANGE MASTER blows off the co-ords, as it assumes you are changing to a new master.
I think you can set the user, etc before importing and it should work.
Sign Up Login You must be logged in to post a comment.