Skip navigation links

User Comments

Posted by Pieter Ennes on October 22 2007 11:25am[Delete] [Edit]

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 4:43pm[Delete] [Edit]

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 10:28pm[Delete] [Edit]

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".

Posted by Gary Fischman on November 11 2013 4:31pm[Delete] [Edit]

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