MySQL AB :: MySQL 5.1 Reference Manual :: 6.1.1.8 Setting up replication with existing data

6.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:

If you have not already configured the server-id and binary logging, you will need to shutdown your master to configure these options. See Section 6.1.1.2, “Setting the Replication Master Configuration”.

If you have to shut down your master database, then this is a good opportunity to take a snapshot of the database. You should obtain the master status (see Section 6.1.1.4, “Obtaining the Master Replication Information”) before taking the database down, updating the configuration and taking a snapshot. For information on how to create a snapshot using raw data files, see Section 6.1.1.6, “Creating a Data Snapshot Using Raw Data Files”.
If your server is already correctly configured, obtain the master status (see Section 6.1.1.4, “Obtaining the Master Replication Information”) and then use mysqldump to take a snapshot (see Section 6.1.1.5, “Creating a Data Snapshot using mysqldump”) or take a raw snapshot of the live database using the guide in Section 6.1.1.6, “Creating a Data Snapshot Using Raw Data Files”.
With the MySQL master running, create a user to be used by the slave when connecting to the master during replication. See Section 6.1.1.1, “Creating a User For Replication”.
Update the configuration of the slave, see Section 6.1.1.3, “Setting the Replication Slave Configuration”.
The next step depends on how you created the snapshot of data on the master.

If you used mysqldump:
1. Startup the slave, skipping replication by using the --skip-slave option.
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 to match the configuration of your slave.
2. Startup the slave, skipping replication by using the --skip-slave option.
Configure the slave with the master status information. This will tell the slave the binary log file and position within the file where replication needs to start, and configure the login credentials and hostname of the master. For more information on the statement required, see Section 6.1.1.10, “Setting the Master Configuration on the Slave”.
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.

Once a slave is replicating, you can find in its data directory one file named master.info and another named relay-log.info. The slave uses these two files to keep track of how much of the master's binary log it has processed. Do not remove or edit these files 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 will use the values specified in the statement to update the status files automatically.

Note

The content of master.info overrides some of the server options specified on the command line or in my.cnf. See Section 6.1.3, “Replication Options and Variables”, for more details.

Once you have a snapshot of the master, you can use it to set up other slaves by following the slave portion of the procedure just described. You do not need to take another snapshot of the master; you can use the same one for each slave.

6.1.1.8. Setting up replication with existing data

Note

User Comments