(CCing the list again so others can see...)
> Thanks for your reply. Two things -- I start/stop the sql-thread once
> as a backup strategy. This slave has no application readers or
> writers. If
> anything should go wrong with the master, I have one full day to either
> correct the problem, or restore the master from the slave in case of
> disaster. I have another server that does the same thing except on a
> two-day schedule, so in case something goes wrong with the master and the
> first slave, and I don't get to it in time, I also have a snapshot from two
> days ago. (these servers do other things, and aren't dedicated mysql rep
Makes sense to me.
> Second, in your suggestion you say to stop the sql-thread -- I'm assuming
> you mean stop the IO thread and capture the master log file position --
> since that's the only time I'll be able to get the master bin log position
> AND file in the 'show slave status' command on Slave A.
There are actually three file/position markers here:
Relay_Master_Log_File and Exec_Master_Log_Pos are the slave's coordinates relative to
the master. This shows you the position, on the master, to which the slave has
replayed changes. The I/O thread is independent of this as you know, so all you need
to do is make sure these files haven't been deleted from the master, and you can start
the second slave from here. It will re-fetch logs from the master as needed.
These values always confuse me -- I always have to refer to the manual page:
http://dev.mysql.com/doc/refman/5.0/en/show-slave-status.html. The most relevant parts
for you, I think, are
The name of the master binary log file containing the most recent event executed by the
The position of the last event executed by the SQL thread from the master's binary log
(Relay_Master_Log_File). (Relay_Master_Log_File, Exec_Master_Log_Pos) in the master's
binary log corresponds to (Relay_Log_File, Relay_Log_Pos) in the relay log."
> What I think I'll end up doing is stopping both IO+SQL threads when I know
> Slave A is complete (up to date) with the master, then capture the master
> position, clone the database files, and restart both slaves. This way, I
> won't have to mess with the relay logs on Slave A.
This will work, too. I only wrote the above in case you need to start another slave
from a point when the first slave isn't caught up in the SQL thread. Maybe it's too
many options, and I'm just confusing you ;-) I bet a good diagram would help. Maybe
I'll make one and submit it for the manual.
> And I'll try out your checksum tool. I've been wanting something like that
> -- I've pretty much relied on record counts between my master and slaves to
> check consistency, and I know that can be misleading.
Cool. I hope it helps.
> On 5/8/07, Baron Schwartz <baron@stripped> wrote:
>> 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
>> > to the slave #2 (it is 44GB total, and would take over an hour to
>> > I have copied the (static) database from Slave #1 to Slave #2. How can
>> > now configure Slave #2 to process the pending relay-logs and bring
>> it up
>> > date?
>> > Obviously I would need to copy (and rename?) the relay logs, but what
>> > the master.info and relay-log.info files?
>> > Or in other words, can I use the show slave status information on Slave
>> > 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
>> thread running, but I guess that's off-topic. Anyway, what you need
>> to do
>> stop Slave A's SQL thread, look at Slave A's status, clone B from A, and
>> start Slave B from the master *at Slave A's Exec_Master file and
>> 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
>> the binlogs from Slave A over to it, but this is probably trickier. Not
>> 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
>> to be down for an hour. For some storage engines, you don't even have to
>> 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
>> Slave A has somehow gotten different data from the master? If you are
>> whether Slave A and B are really in sync with the master,
>> (a tool I wrote) might help you:
>> It's probably easiest for you to use the --replicate option to get a
>> checksum. (If you try it, let me know if that works well for you).