Create a MySQL slave from another slave, but point it at the master

Solution 1:

Hey I know a crazy method to create a slave without augmenting any operation of master (ServerA) or slave (ServerB)

Step 1) Setup a New Server (ServerC)

Step 2) On ServerC, Install MySQL (same version as ServerB)

Step 3) On ServerC, service mysql stop

Step 4) Copy /etc/my.cnf from ServerB to ServerC

Step 5) On ServerC, change server_id to a value different from ServerA and ServerB

Step 6) rsync /var/lib/mysql on ServerB to ServerC

Step 7) When rsync is completed, run "STOP SLAVE;" on ServerB

Step 8) rsync /var/lib/mysql on ServerB to ServerC

Step 9) On ServerB, run "START SLAVE;"

Step 10) On ServerC, service mysql start

Step 11) On ServerC, run "START SLAVE;" (Do this if skip-slave-start is in /etc/my.cnf)

Give it a Try !!!

BTW I have the utmost confidence this will work because I just did this for client over the last 2 days. Client had 2.7TB of data on a slave. I rsyncd to another server while the slave was still active. rsync took like 11 hours. I then ran STOP SLAVE; on the first slave and ran rsync again. That took another hour. I then performed the above step and everything is done.

Solution 2:

When we add a slave to our mix, we do the following:

• take one slave offline
• copy the database data directory to the new slave (slave setings -binlog position, master host etc - will be correct since we copied from a slave)
• start up the original slave
• modify server-id in my.cnf for the new slave
• start new slave