Skip to Main Content
MySQL in a Nutshell, 2nd Edition
book

MySQL in a Nutshell, 2nd Edition

by Russell J. T. Dyer
April 2008
Intermediate to advanced content levelIntermediate to advanced
566 pages
21h 55m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL in a Nutshell

MySQL in a Nutshell

Russell J. T. Dyer
MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
MySQL Cookbook

MySQL Cookbook

Paul DuBois

Publisher Resources

ISBN: 9780596514334Errata Page