MySQL in a Nutshell, 2nd Edition

Copying Databases and Starting Replication

If you’re setting up replication with an existing server that already contains data, you will need to make an initial backup of the databases and copy the backup to the slave server. I’ll list the recommended method first, followed by some alternatives and their limitations.

To get a snapshot of the database in a consistent state, you need to shut down the server while you make a copy of the data, or at least prevent users from changing data. Considering that once you set up replication you may never have to shut down your master server for backups again, explain to management that it’s worth inconveniencing the users this one time to get a clean, consistent backup. The following sections will explain how to lock the tables. Note that you can allow users to make changes as soon as your copy is made. If they make changes before replication starts, MySQL can easily recognize and incorporate those changes into the slave.

Using mysqldump

This utility, described in Chapter 16, creates a file of SQL statements that can later be executed to recreate databases and their contents. For the purposes of setting up replication, use the following options while running the utility from the command line on the master server:

mysqldump --user=root --password=my_pwd \
   --extended-insert --all-databases  \
   --ignore-table=mysql.users --master-data > /tmp/backup.sql

The result is a text file (backup.sql) containing SQL statements to create all of the master’s databases ...