List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:May 9 2007 2:35am
Subject:Re: duplicating a replicated slave environment
View as plain text  

Hank wrote:
> Hello All,
> I have a 4.1.14 mysql database master and slave set up.
> For this slave #1, I have the IO thread running constantly, and the SQL
> thread running once a day to update all pending updates from the master
> (then I shut it off).  So for most of the day, this database is static
> (except for the collecting relay logs).
> I have a new machine to be another slave of the same master (slave #2).
> I can not shut down or lock the master in order to copy the master database
> to the slave #2 (it is 44GB total, and would take over an hour to copy).
> I have copied the (static) database from Slave #1 to Slave #2.  How can I
> now configure Slave #2 to process the pending relay-logs and bring it up to
> date?
> Obviously I would need to copy (and rename?) the relay logs, but what about
> the and files?
> Or in other words, can I use the show slave status information on Slave #1
> to setup Slave #2 in the CHANGE MASTER TO command?

This shouldn't be too hard to do.  I'm curious why you don't leave the SQL 
thread running, but I guess that's off-topic.  Anyway, what you need to do is 
stop Slave A's SQL thread, look at Slave A's status, clone B from A, and then 
start Slave B from the master *at Slave A's Exec_Master file and position*.

Slave B should then ask the master for whatever binlogs it needs, beginning at 
the point corresponding to where it was cloned from.  If you want, you can copy 
the binlogs from Slave A over to it, but this is probably trickier. Not that 
there's anything wrong with doing this, but there might be more ways to make a 
mistake by looking at the wrong parameter, etc.

For the future, if you are running on Linux, one of my favorite things to do is 
put the MySQL data, temp files, and logs on LVM.  This way you can take an 
instantaneous snapshot of the data and copy it at your leisure.  You don't have 
to be down for an hour.  For some storage engines, you don't even have to shut 
MySQL down; you can just do FLUSH TABLES WITH READ LOCK.  Maybe that's still not 
possible for you, though.  Just a suggestion.

There is always some danger in cloning a slave from another slave.  What if 
Slave A has somehow gotten different data from the master?  If you are curious 
whether Slave A and B are really in sync with the master, mysql-table-checksum 
(a tool I wrote) might help you: 
It's probably easiest for you to use the --replicate option to get a consistent 
checksum.  (If you try it, let me know if that works well for you).

duplicating a replicated slave environmentHank8 May
  • Re: duplicating a replicated slave environmentBaron Schwartz9 May
Re: duplicating a replicated slave environmentBaron Schwartz9 May