Scenario:
host a is the master
host b is a replication slave
host c is to become a second replication slave
there's no full dump from host a

Normally, to start a new slave, I'd restore a dump from host a, and
start slaving using the master data in that dump. In this situation,
however, running a full mysqldump on a would cause it to be unresponsive
for a while, and the app is depending on it (mostly MyISAM so can't run
the dump as a transaction).

I can temporarily make the front-end application not read from host b,
and while host b is not in use, run a full mysqldump there of the same
db, and restore that dump onto host c.

.... but how do I find the master data to start host c slaving with?

The dump file will have master data referring to host b's binlogs,
which are mostly empty because it's a replication slave. I need to
know what position in host a's binlogs to start host c slaving from.

One possibility I can think of:
- stop slave on host b
- run the dump on host b
- note its position in host a's binlogs using "show slave status"
- restore the dump on host c
- start c slaving using the binlog name and position from "show slave status"

Will that work?

Is there a way to do this *without* stopping replication on host b?
-- Cos

Hi

Ofer Inbar wrote:
> Scenario:
> host a is the master
> host b is a replication slave
> host c is to become a second replication slave
> there's no full dump from host a
[snip]
> One possibility I can think of:
> - stop slave on host b
> - run the dump on host b
> - note its position in host a's binlogs using "show slave status"
> - restore the dump on host c
> - start c slaving using the binlog name and position from "show slave status"
>
> Will that work?

Yes.

> Is there a way to do this *without* stopping replication on host b?

No, unless you have a snapshot-capable file system like LVM, and even then there
are caveats. Just be aware of which columns of SHOW SLAVE STATUS mean what --
there are three sets of binlog coordinates in that output. (There's a note on
the online manual that should make it clear).

Baron

Baron Schwartz <baron@xaprb.com> wrote:
> Ofer Inbar wrote:
> > host a is the master
> > host b is a replication slave
> > host c is to become a second replication slave
> > there's no full dump from host a

> >One possibility I can think of:
> > - stop slave on host b
> > - run the dump on host b
> > - note its position in host a's binlogs using "show slave status"
> > - restore the dump on host c
> > - start c slaving using the binlog name and position from "show slave status"
> >
> >Will that work?
>
> Yes.

For the benefit of future readers of the archive: yes, it worked easily.

> Just be aware of which columns of SHOW SLAVE STATUS
> mean what -- there are three sets of binlog coordinates in that output.
> (There's a note on the online manual that should make it clear).

The columns I used were:

Master_Log_File: binlog.000008
Read_Master_Log_Pos: 150484312

-- Cos