Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Problem

I have MySQL replication setup between 2 servers, master (A) and slave (B). I need to add a new slave to the mix (C). I want this slave to get it's updates directly from the master, I do not want chain replication from the slave. However, the master is "hot", I usually use Xtrabackup to create a full backup of the master, but this will lock it for a good 10 minutes, as the database is around 20GB in size.

Possible Solution

FLUSH TABLES WITH READ LOCK on slave B, use SHOW SLAVE STATUS on B, write down binlog and position. Then backup database with Xtrabackup, ship the backup to C and use it to create the slave, and set replication to point to A with the binlog position I just wrote down.

Question

Is there a better way that doesn't require me to lock B for so long? Or something that is more easily automated?

share|improve this question
add comment

3 Answers 3

up vote 8 down vote accepted

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.

share|improve this answer
    
LOL. I was going to comment on the OP to use your suggestion, and low and behold its Mr. Rolando the "Fridge" DBA. Rolando hit the nail on the head and this is the preferred method without having to stop any Master and without stopping your B slave for too long a period. –  coderwhiz Apr 8 '11 at 20:38
    
Awesome solution! thanks so much! –  Alex Recarey Apr 25 '11 at 11:22
1  
I know this is a fairly old post but someone asked me about this method. This works fine assuming that the new slave and the old slave are EXACTLY the same. If the new slave is a different arch it won't work (iirc). And I'm almost sure that if you're using per-file innodb tablespaces it won't work. The safest solution is to do a full backup from the master if there's any doubt. –  lusis Sep 12 '11 at 18:26
    
@lusis - Your comment is very true. In a perfect world, which most mysql clients imagine they have, they want this done since all hardware specs are identical. In setups where hardware differs, mysqldumps and reload is the safest. You should submit your comment as an answer. I would upvote it. Let's see if others will to !!! –  RolandoMySQLDBA Sep 12 '11 at 18:34
add comment

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
share|improve this answer
    
I just had to do this myself this afternoon –  sreimer Apr 8 '11 at 20:18
add comment

You have the "LOAD DATA FROM MASTER" option but that is highly discouraged.

Do you take nightly / weekly backups on your system? If so, also note the position with your backup then you can use that backup for setting up a new slave. Just leave it be and allow it to get up to date for some time.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.