2.2.6.2 Setting Up Replication with Existing Data

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

Note

If the replication source server or existing replica that you are copying to create the new replica has any scheduled events, ensure that these are disabled on the new replica before you start it. If an event runs on the new replica that has already run on the source, the duplicated operation causes an error. The Event Scheduler is controlled by the event_scheduler system variable, which defaults to ON from MySQL 8.0, so events that are active on the original server run by default when the new replica starts up. To stop all events from running on the new replica, set the event_scheduler system variable to OFF or DISABLED on the new replica. Alternatively, you can use the ALTER EVENT statement to set individual events to DISABLE or DISABLE ON SLAVE to prevent them from running on the new replica. You can list the events on a server using the SHOW statement or the Information Schema EVENTS table. For more information, see Section 4.1.16, “Replication of Invoked Features”.

Choose one of the following procedures to import the data to the replica.

If you used mysqldump:

1. Start the replica, 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 replica's data directory. For example:

   shell> tar xvf dbdump.tar

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

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

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

4. Start the replication threads by issuing a START REPLICA | SLAVE statement.

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

The replica uses information logged in its connection metadata repository and applier metadata repository to keep track of how much of the source's binary log it has processed. From MySQL 8.0, by default, these repositories 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; expect them to 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 replica uses the values specified in the statement to update the replication metadata repositories automatically. See Section 5.4, “Relay Log and Replication Metadata Repositories”, for more information.

Note

The contents of the replica's connection metadata repository 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 source suffices for multiple replicas. To set up additional replicas, use the same source snapshot and follow the replica portion of the procedure just described.