RE: Replication Stopping at position before it started????

Stopping mysql will flush the current binlog. FLUSH LOGS also does this. This leaves the position in a convenient location -- start of next next file.

I would expect this sequence to work:

Stop master
Dump data
(start master)
copy date to slave
start slave
CHANGE MASTER ... to start of 'next binlog'.

Stopping the master before dumping is important because of un-flushed data sitting in various caches. Stopping clients to the master is probably not sufficient. FLUSH TABLES WITH READLOCK "should" be as good as stopping the master, but I don't trust it.

> -----Original Message-----
> From: Jesse [mailto:jlc@msdlg.com]
> Sent: Wednesday, October 10, 2007 11:02 AM
> To: Rick James; Nicklas Westerlund
> Cc: replication@lists.mysql.com
> Subject: Re: Replication Stopping at position before it started????
>
> Problem is that when the slave simply stopped like it did,
> and I attempted
> to re-start it, I would almost always get an error stating
> that it can't add
> some detail record because of a foreign key violation. In
> other words, it's
> trying to add a child record without the parent record being
> there. So, it
> would appear to be trying to start at a different place all-together.
>
> When I reset, what I do is backup the master, restore the
> data on the slave,
> re-set the log file name and position, and re-start the
> slave. This works
> for a while, then suddenly stops for no apparent reason.
>
> Regarding shutting down the master before re-booting. No, I didn't
> explicitly stop it, but when you re-start a windows machine,
> I believe it
> stops all of the services anyway. I don't know this for
> sure, but it would
> make sense that it would. I've never had to explicitly stop
> and start the
> master server before. Plus, this is the first time since I
> started the
> replication stuff that I rebooted the server (it was due to a
> Microsoft
> Update that I had to re-boot anyway). I had this problem fro
> the get-go,
> and had not restarted the master server at all.
>
> Jesse
>
> ----- Original Message -----
> From: "Rick James" <rjames@yahoo-inc.com>
> To: "Jesse" <jlc@msdlg.com>; "Nicklas Westerlund"
> <nwesterlund@estalea.com>
> Cc: <replication@lists.mysql.com>
> Sent: Wednesday, October 10, 2007 12:43 PM
> Subject: RE: Replication Stopping at position before it started????
>
>
> In normal situations, you never need to change the logfile name or
> position. The slave would notice that it is at the end of file, and
> move on to the start of the next file.
>
> I'm assuming you shut down mysql (master) when you "re-booted"; an
> abrupt power failure can cause confusion in the binlog positions.
>
> > -----Original Message-----
> > From: Jesse [mailto:jlc@msdlg.com]
> > Sent: Wednesday, October 10, 2007 5:23 AM
> > To: Nicklas Westerlund
> > Cc: replication@lists.mysql.com
> > Subject: Re: Replication Stopping at position before it started????
> >
> > > Perhaps you set master_log_pos to 235 on mysql-bin.000002 ?
> >
> > Nope. When I re-set the slave last night, I executed the
> > following command:
> > CHANGE MASTER TO
> > MASTER_LOG_FILE='mysql-bin.000003',
> > MASTER_LOG_POS=235;
> >
> > I know for sure that I did this, because I had re-booted the
> > master server
> > (due to an update), and noticed that the log file name had
> > changed, so I was
> > sure to change it before I re-started the slave.
> >
> > Jesse
> >
> >
> > --
> > MySQL Replication Mailing List
> > For list archives: http://lists.mysql.com/replication
> > To unsubscribe:
> > http://lists.mysql.com/replication?unsub=rjames@yahoo-inc.com
> >
> >
>
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:
> http://lists.mysql.com/replication?unsub=jlc@msdlg.com
>
>
>

-- 
MySQL Replication Mailing List
For list archives: 
http://lists.mysql.com/replication
To unsubscribe:    
http://lists.mysql.com/replication?unsub=lists@pantek.com