> 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
> Obviously I would need to copy (and rename?) the relay logs, but what about
> the master.info and relay-log.info 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: http://sourceforge.net/projects/mysqltoolkit/
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).