Documentation Home
MySQL Replication
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.5Mb
PDF (A4) - 1.5Mb
HTML Download (TGZ) - 384.8Kb
HTML Download (Zip) - 399.1Kb


2.2.5.1 Creating a Data Snapshot Using mysqldump

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

The following example 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
Note

If you do not use --master-data, then it is necessary to lock all tables in a separate session manually. See Section 2.2.4, “Obtaining the Replication Master Binary Log Coordinates”.

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.

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

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