Start MySQL Replication on a slave

Had a problem with a slave database being a different (newer) version of MySQL than the master. After downgrading to the correct version of MySQL, the replicated data was no longer consistent with the master. ( For a good tool that can check the slave data’s consistency table by table, see here http://www.maatkit.org/doc/mk-table-checksum.html )

To fix this, the following steps were taken:
1) stop mysql on the broken slave.
2) on another good slave, in mysql prompt type ‘stop slave’, then (may want to remove some updatelogs first) tar up the /var/lib/mysql directory like so: ‘tar -cvpf good_slave.tar /var/lib/mysql’, then type ‘start slave’
3) Copy the tar to the broken slave with sftp or what have you
4) on the broken slave, move the /var/lib/mysql folder somewhere else for safekeeping such as ‘mv /var/lib/mysql /var/lib/broken_mysql’
5) untar the good_slave.tar file and make sure it goes in /var/lib/mysql
6) Edit relay-log.info file, change the hostname, see step 6 here: http://www.redips.net/mysql/add-new-slave/
# first line before change
./existing_slave_name-relay-bin.000076
# first line after change
./new_slave_name-relay-bin.000001

7) (not needed in this case since broken slave has it already, but for a new slave) Copy my.cnf and increment server-id – NOTE: edit the “relay-log” line with the new hostname!
‘8) start mysql on the broken slave. check ‘show slave status’ and hopefully seconds behind decreases to zero quickly!

This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to Start MySQL Replication on a slave

  1. admin says:

    Ran into an error while trying to do this:

    120430 11:13:39 [ERROR] Failed to open the relay log ‘/var/lib/mysql/mysql-relay-bin.000461’ (relay_log_pos 268803286)
    120430 11:13:39 [ERROR] Could not find target log during relay log initialization
    120430 11:13:39 [ERROR] Failed to initialize the master info structure

    Turns out I needed to rename a couple of files as well as edit this file relay-bin.index and change the filename listed to reflect the new slave’s hostname.

    • admin says:

      hmmm more recently I did this I just needed to change the “relay-log” name in the my.cnf file to reflect the slave’s hostname.

      • admin says:

        I did this today and I need to:
        1) remove the /var/lib/mysql/my.cnf so the copy in /etc/ would be prevalent
        2) update /var/lib/mysql/master.info to reflect that this server is remote (needs to connect to a WAN address), whereas the good slave was local (LAN address)

  2. Pingback: How to add a new MySQL slave | 各路

Leave a Reply

Your email address will not be published. Required fields are marked *