89301

Creating a Data Snapshot Using mysąldump

One way to create a snapshot of the data in an existing master database is to use

the mysąldump 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 mysąldump:

1.    If you have not already locked the tables on the server to prevent statements that update data from

executing:

Start a session on the server by connecting to it with the command-line Client, and flush all tables

and błock write statements by executing the FLUSH TABLES WITH READ LOCK statement:

ray3ql>FLUSH TABLES WITH READ LOCK;

Remember to use SHOW MASTER STATUS and record the binary log detailsfor use when starting

up the slave. The point in time of your snapshot and the binary log position must match. See

Section 16.1.1.4, “Obtaining the Replication Master Binary Log Coordinates”.

2.    In another session, use mysąldump to create a dump either of all the databases you want to

replicate, or of selected individual databases. For example:

shel l>nysqldump —all-databases —lock-all-tables >dbdump.db

An alternative to using a bare dump, is to use the —master-data option, which automatically

appends the CHANGE MASTER TO statement reąuired on the slave to start the replication process.

shell>itysqldump —all-databases —master-data >dbdump.db

3.    In the Client where you acąuired the read lock, release the lock:

mysql>UNLOCK TABLES;

Alternath/ely, exit the first session to release the read lock.

When choosing databases to include in the dump, remember to filter out databases on each slave that

you do not want to include in the replication process.

Either to copy the dump file to the slave, or use the file from the master when connecting remotely to the slave to import the data.