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 16.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
.)
If this is not feasible, copy the existing slave's relay
log index file to the new slave and set the
existing_slave_hostname
-relay-bin--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
.)
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:
existing_slave_hostname
-relay-bin.index
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.
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.
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.
User Comments
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...
Don't forget to set the owner & permissions on the folder used for logging on the new slave.
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 'repl@192.168.0.1: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".
Add your own comment.