Documentation Home
MySQL Replication
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.6Mb
PDF (A4) - 1.6Mb
HTML Download (TGZ) - 390.2Kb
HTML Download (Zip) - 404.5Kb


2.2.6.2 Setting Up Replication with Existing Data

When setting up replication with existing data, transfer the snapshot from the master to the slave before starting replication. The process for importing data to the slave depends on how you created the snapshot of data on the master.

Choose one of the following:

If you used mysqldump:

  1. Start the slave, using the --skip-slave-start option so that replication does not start.

  2. Import the dump file:

    shell> mysql < fulldb.dump

If you created a snapshot using the raw data files:

  1. Extract the data files into your slave data directory. For example:

    shell> tar xvf dbdump.tar

    You may need to set permissions and ownership on the files so that the slave server can access and modify them.

  2. Start the slave, using the --skip-slave-start option so that replication does not start.

  3. Configure the slave with the replication coordinates from the master. This tells the slave the binary log file and position within the file where replication needs to start. Also, configure the slave with the login credentials and host name of the master. For more information on the CHANGE MASTER TO statement required, see Section 2.2.7, “Setting the Master Configuration on the Slave”.

  4. Start the slave threads:

    mysql> START SLAVE;

After you have performed this procedure, the slave connects to the master and replicates any updates that have occurred on the master since the snapshot was taken. Error messages are issued to the slave's error log if it is not able to replicate for any reason.

The slave uses information logged in its master info log and relay log info log to keep track of how much of the master's binary log it has processed. From MySQL 8.0, by default, the repositories for these slave status logs are tables named slave_master_info and slave_relay_log_info in the mysql database. The alternative settings --master-info-repository=FILE and --relay-log-info-repository=FILE, where the repositories are files named master.info and relay-log.info in the data directory, are now deprecated and will be removed in a future release.

Do not remove or edit these tables (or files, if used) unless you know exactly what you are doing and fully understand the implications. Even in that case, it is preferred that you use the CHANGE MASTER TO statement to change replication parameters. The slave uses the values specified in the statement to update the slave status logs automatically. See Section 5.4, “Replication Relay and Status Logs”, for more information.

Note

The contents of the master info log override some of the server options specified on the command line or in my.cnf. See Section 2.6, “Replication and Binary Logging Options and Variables”, for more details.

A single snapshot of the master suffices for multiple slaves. To set up additional slaves, use the same master snapshot and follow the slave portion of the procedure just described.


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 Brian Kinney on September 10, 2015
I had a slave lose sync, and was trying to restore everything, with a minimum of effort.
When you want to resync an existing slave that failed and won't resync, I highly recommend using the mysqldump --master-data method.

1) It grabs all files.
2) It will put them back where they belong for you.
3) It collects the master status, and embeds it as SQL code for use upon restore.
4) It does not require you to stop the existing service, as it does the lock and unlock tables for you.

Reminder: you have to START SLAVE; once your MySQL < dump.db is applied.
Please then wait a good 90 seconds before expecting a valid answer to the question SHOW SLAVE STATUS;
You are looking for a YES on Slave_IO_Running and Slave_SQL_Running fields.
Even if you are not immediately in sync, you probably will be soon.