9

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?

Alex Recarey
  • 441
  • 1
  • 6
  • 14

6 Answers6

22

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.

RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
  • 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 08 '11 at 20:38
  • 2
    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
  • I followed the procedure. After starting mysql back on SlaveC I get an error saying "Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files". And on `start slave` (SlaveC) I get "Failed to open the relay log '/var/log/mysql/mysql-relay-bin.001603" – Hussain Tamboli Mar 11 '15 at 10:35
  • This way you can easily lose data on ServerC. – akuzminsky Mar 18 '17 at 01:42
4

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
sreimer
  • 2,168
  • 14
  • 17
3

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)

Community
  • 1
Pere Barceló
  • 131
  • 2
  • your answer is not complete, and it references other things. it's not a forum, improve your answer to be complete on its own. – asdmin Jun 14 '16 at 06:44
0

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.

jishi
  • 858
  • 1
  • 11
  • 25
0

I tried Rolando answers and worked fine, but it was started replaying from the beginning and I had to add more error code to skip (I know its not recommended, but I know what I was doing).

Once done with the step 7, I have checked in the mysql log and noted down the bin log name and position and continued till 9th step. Before 10th step, I just executed change master for log file and log position. And continued from step 11. All looks fine to me.

Spike
  • 143
  • 2
  • 9
-2

You need to change the slave uuid in auto.cnf so the master can differentiate the two slaves.

Nazgul
  • 1