Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.4Mb
PDF (A4) - 31.5Mb
PDF (RPM) - 30.0Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 181.2Kb
Man Pages (Zip) - 292.4Kb
Info (Gzip) - 2.8Mb
Info (Zip) - 2.8Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Setting Up Replication with Existing Data

17.1.1.8 Setting Up Replication with Existing Data

When setting up replication with existing data, you will need to decide how best to get the data from the master to the slave before starting the replication service.

The basic process for setting up replication with existing data is as follows:

  1. With the MySQL master running, create a user to be used by the slave when connecting to the master during replication. See Section 17.1.1.3, “Creating a User for Replication”.

  2. If you have not already configured the server-id and enabled binary logging on the master server, you will need to shut it down to configure these options. See Section 17.1.1.1, “Setting the Replication Master Configuration”.

    If you have to shut down your master server, this is a good opportunity to take a snapshot of its databases. You should obtain the master status (see Section 17.1.1.4, “Obtaining the Replication Master Binary Log Coordinates”) before taking down the master, updating the configuration and taking a snapshot. For information on how to create a snapshot using raw data files, see Section 17.1.1.6, “Creating a Data Snapshot Using Raw Data Files”.

  3. If your master server is already correctly configured, obtain its status (see Section 17.1.1.4, “Obtaining the Replication Master Binary Log Coordinates”) and then use mysqldump to take a snapshot (see Section 17.1.1.5, “Creating a Data Snapshot Using mysqldump”) or take a raw snapshot of the live server using the guide in Section 17.1.1.6, “Creating a Data Snapshot Using Raw Data Files”.

  4. Update the configuration of the slave. See Section 17.1.1.2, “Setting the Replication Slave Configuration”.

  5. The next step depends on how you created the snapshot of data on the master.

    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.

  6. 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 17.1.1.10, “Setting the Master Configuration on the Slave”.

  7. Start the slave threads:

    mysql> START SLAVE;
    

After you have performed this procedure, the slave should connect to the master and catch up on any updates that have occurred since the snapshot was taken.

If you have forgotten to set the server-id option for the master, slaves cannot connect to it.

If you have forgotten to set the server-id option for the slave, you get the following error in the slave's error log:

Warning: You should set server-id to a non-0 value if master_host
is set; we will force server id to 2, but this MySQL server will
not act as a slave.

You also find error messages in the slave's error log if it is not able to replicate for any other reason.

The slave uses information stored in its master info repository to keep track of how much of the master's binary log it has processed. The repository can be in the form of files or a table, as determined by the value set for --master-info-repository. When a slave runs with --master-info-repository=FILE, you can find in its data directory two files, named master.info and relay-log.info. If --master-info-repository=TABLE instead, this information is saved in the table master_slave_info in the mysql database. In either case, do not remove or edit the files or table 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 can use the values specified in the statement to update the status files automatically. See Section 17.2.2, “Replication Relay and Status Logs”, for more information.

Note

The contents of the master info repository override some of the server options specified on the command line or in my.cnf. See Section 17.1.4, “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
  Posted by Keith Waters on December 12, 2008
If you need start your slave with a fresh copy of the master database, you will need to issue the commands
STOP SLAVE and RESET SLAVE before you do a CHANGE MASTER to give it the new file name and position (and before you restart the slave!)
Otherwise, it tries to pick up where it left off, using the old master file and position.

I have also found that I needed to UNLOCK TABLES on the slave before starting the slave

  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.

Sign Up Login You must be logged in to post a comment.