Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 37.9Mb
PDF (RPM) - 32.9Mb
HTML Download (TGZ) - 8.0Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 6.9Mb
Man Pages (TGZ) - 132.7Kb
Man Pages (Zip) - 189.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Adding Slaves to a Replication Environment Adding Slaves to a Replication Environment

You can add another slave to an existing replication configuration without stopping the master. Instead, set up the new slave by making a copy of an existing slave, except that you configure the new slave with a different server-id value.

To duplicate an existing slave:

  1. Shut down the existing slave:

    shell> mysqladmin shutdown
  2. Copy the data directory from the existing slave to the new slave. You can do this by creating an archive using tar or WinZip, or by performing a direct copy using a tool such as cp or rsync. Ensure that you also copy the log files and relay log files.

    A common problem that is encountered when adding new replication slaves is that the new slave fails with a series of warning and error messages like these:

    071118 16:44:10 [Warning] Neither --relay-log nor --relay-log-index were used; so
    replication may break when this MySQL server acts as a slave and has his hostname
    changed!! Please use '--relay-log=new_slave_hostname-relay-bin' to avoid this problem.
    071118 16:44:10 [ERROR] Failed to open the relay log './old_slave_hostname-relay-bin.003525'
    (relay_log_pos 22940879)
    071118 16:44:10 [ERROR] Could not find target log during relay log initialization
    071118 16:44:10 [ERROR] Failed to initialize the master info structure

    This situation can occur if the --relay-log option is not specified, as the relay log files contain the host name as part of their file names. This is also true of the relay log index file if the --relay-log-index option is not used. See Section 17.1.6, “Replication and Binary Logging Options and Variables”, for more information about these options.

    To avoid this problem, use the same value for --relay-log on the new slave that was used on the existing slave. If this option was not set explicitly on the existing slave, use existing_slave_hostname-relay-bin. If this is not possible, copy the existing slave's relay log index file to the new slave and set the --relay-log-index option on the new slave to match what was used on the existing slave. If this option was not set explicitly on the existing slave, use existing_slave_hostname-relay-bin.index. Alternatively, if you have already tried to start the new slave after following the remaining steps in this section and have encountered errors like those described previously, then perform the following steps:

    1. If you have not already done so, issue a STOP SLAVE on the new slave.

      If you have already started the existing slave again, issue a STOP SLAVE on the existing slave as well.

    2. Copy the contents of the existing slave's relay log index file into the new slave's relay log index file, making sure to overwrite any content already in the file.

    3. Proceed with the remaining steps in this section.

  3. If files have been used for the master info and relay log info repositories (see Section 17.2.4, “Replication Relay and Status Logs”), copy these files from the existing slave to the new slave. These hold the current log coordinates for the master's binary log and the slave's relay log. If tables have been used for the repositories, which is now the default, the tables are in the already copied data directory.

  4. Start the existing slave.

  5. On the new slave, edit the configuration and give the new slave a unique server-id not used by the master or any of the existing slaves.

  6. Start the new slave. The slave uses the information in its master info repository to start the replication process.

User Comments
  Posted by Pieter Ennes on October 22, 2007
I found that this does not work on 5.0... (maybe it does on 5.1)

The problem is that the relay log have the name of the old slave. I worked around it with a combination of using mysqlbinlog on the old slave's relay log, and a CHANGE MASTER TO statement to correctly set the new slave master info.

See Christine Korza's comment on mysqlbinlog on:

So, what i did now:
- get a copy of old slave
- move and to backup
- start new slave with skip-slave option in my.cnf
- feed old slave relay log to new slave using mysqlbinlog (use info from
- use CHANGE MASTER TO using info from
- remove skip-slave option from my.cnf

That should do it...
  Posted by Paul Sindelar on June 29, 2009
Don't forget to set the owner & permissions on the folder used for logging on the new slave.
  Posted by Paul Mietz Egli on October 1, 2009
I thought I'd expand a bit Pieter's process to reduce some of the trial-and-error that non-gurus like me would have to go through. Instructions are for UNIX; other platforms will vary...

1. Shut down your old slave server and make a copy of the data directory to your new slave. Make sure you get the old slave's and files. Once you have a copy of the data directory, you can restart the old slave if desired. The rest of these steps are performed on the new slave.
2. Rename the to and to
3. Edit /etc/my.cnf and add the line "skip-slave-start" (without the quotes) to the [mysqld] section.
4. Start the new slave. This will create new and files. Because you used "skip-slave-start", the slave thread doesn't automatically start up.
5. The file shows where the old slave stopped processing logs when you shut it down in step 1. Run "cat"; you will see something like this:
The first line is the filename of the relay log that the old slave was working on when you shut it down. Make sure that file exists on the new slave. The second line is the position of the last update on the old slave when it was shut down. These are the values that you are going to use to tell the new slave where to start. Run "mysqlbinlog --start-position=1834777 /var/lib/mysql/oldslave-relay-bin.010101 | mysql" to update the new slave log positions, substituting the start position and filename from your file.
6. Now you need to update the new slave's master information. Run "cat" and you will see something like this:
(blank lines removed)
Run mysql, and use the master log file name from line 2, the master log position from line 3, the master host from line 4, the master username from line 5, and the master password from line 6 in a CHANGE MASTER TO command in mysql:
change master to MASTER_HOST='',MASTER_USER='repl',MASTER_PASSWORD='password',MASTER_LOG_FILE='oldslave-bin.033726',MASTER_LOG_POS=1834641;
7. Start up the slave by running "start slave;". Now you can remove the "skip-slave-start" from /etc/my.cnf. Check your /var/log/mysql.log file and make sure you see something like this:

091001 18:04:36 [Note] Slave SQL thread initialized, starting replication in log 'oldslave-bin.033726' at position 1834641, relay log '/var/lib/mysql/oldslave-relay-bin.000001' position: 4
091001 18:04:36 [Note] Slave I/O thread: connected to master 'repl@', replication started in log 'oldslave-bin.033726' at position 1834641

I should probably put a disclaimer here, like "this worked for me on CentOS 5.3 and MySQL server version 5.0.77".
  Posted by Gary Fischman on November 11, 2013
This process can produce unpredictable results if the new slave is running a different version of MySQL from the existing slave. Instead, use mysqldump to create the new slave. It takes longer to import the dump file, but the result is a reliable, stable slave.

Procedure is documented in the mysqldump docs.
Sign Up Login You must be logged in to post a comment.