MySQL AB :: MySQL 5.1 Reference Manual :: 6.1.1.5 Creating a Data Snapshot using mysqldump

6.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. Once the data dump has been completed, you then import this data into the slave before starting the replication process.

To obtain a snapshot of the data using mysqldump:

If you haven't already locked the tables on the server to prevent queries that update data from executing:

Start the command line client and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:
```
mysql> FLUSH TABLES WITH READ LOCK;
```
Remember to use SHOW MASTER STATUS and record the binary log details for use when starting up the slave. The point in time of your snapshot and the binary log position must match. See Section 6.1.1.4, “Obtaining the Master Replication Information”.
In another session, use mysqldump to create a dump either of all the databases you want to replicate, or by selecting specific databases individually. For example:
```
shell> mysqldump --all-databases --lock-all-tables >dbdump.db
```
An alternative to using a bare dump, is to use the --master-data option, which will automatically append the CHANGE MASTER statement required on the slave to start the replication process.
```
shell> mysqldump --all-databases --master-data >dbdump.db
```

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

You will need either to copy the dump file to the slave, or to use the file from the master when connecting remotely to the slave to import the data.

6.1.1.5. Creating a Data Snapshot using mysqldump

User Comments

6.1.1.5. Creating a Data Snapshot using `mysqldump`