MySQL :: MySQL 5.5 Reference Manual :: 17.1.1.9 Introducing Additional Slaves to an Existing Replication Environment

MySQL 5.5 Reference Manual / ... / Introducing Additional Slaves to an Existing Replication Environment

17.1.1.9 Introducing Additional Slaves to an Existing Replication Environment

To add another slave to an existing replication configuration, you can do so 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:

Shut down the existing slave:
```
shell> mysqladmin shutdown
```
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 is due to the fact that, if the --relay-log option is not specified, 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.3, “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 feasible, 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.
Copy the master.info and relay-log.info files from the existing slave to the new slave if they were not located in the data directory. These files hold the current log coordinates for the master's binary log and the slave's relay log.
Start the existing slave.
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.
Start the new slave. The slave will use the information in its master.info file to start the replication process.

PREV HOME UP NEXT

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:
http://dev.mysql.com/doc/refman/5.0/en/replication.html

So, what i did now:
- get a copy of old slave
- move master.info and relay-log.info 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 relay-log.info.oldslave)
- use CHANGE MASTER TO using info from master.info.oldslave
- START SLAVE
- 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 master.info and relay-log.info 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 master.info to master.info.oldslave and relay-log.info to relay-log.info.oldslave.
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 master.info and relay-log.info files. Because you used "skip-slave-start", the slave thread doesn't automatically start up.
5. The relay-log.info.oldslave file shows where the old slave stopped processing logs when you shut it down in step 1. Run "cat relay-log.info.oldslave"; you will see something like this:
/var/lib/mysql/oldslave-relay-bin.010101
1834777
oldslave-bin.033726
1834641
4
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 relay-log.info.oldslave file.
6. Now you need to update the new slave's master information. Run "cat master.info.oldslave" and you will see something like this:
14
oldslave-bin.033726
1834641
192.168.0.1
repl
password
3306
60
0
(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='192.168.0.1',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 '[email protected]:3306', 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.
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html