Create a MySQL slave from another slave, but point it at the master
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.
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
I did what @RolandoMySQLDBA suggests but also adding a 6' and an 8' steps (this solves what @Hussain Tamboli comments.):
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 6') rsync /var/log/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 8') rsync /var/log/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)