Below are the steps to fix this issue:
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/oldsername-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/oldsername-relay-bin.010101 | mysql -uroot -p
” 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 1234 192.168.0.1 replication_user replication_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:
mysql -uroot -p change master to MASTER_HOST='192.168.0.1',MASTER_USER='repl',MASTER_PASSWORD='password',MASTER_LOG_FILE='oldslave-bin.033726',MASTER_LOG_POS=1234;
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