replication bin logs issue

[dayjah]

Hi,

We are in the process of setting my mysql replication on mysql ver
4.1.18. However we have noticed an odd bug that occurs every so often.

Every hour we issue flush logs to mysql so that we can move the bin
logs to our backup server. In our mysql logs we notice the following;

060526 12:00:02 [ERROR] Failed to open log (file
'/srv/backup/mysql/mysql-bin.000068', errno 2)

This doesn't happen on each flush. This is resulting in us losing an
hours bin logs at a time which then causes replication to break (rows
are inserted during this time and as they are not logged to the bin
logs they never make it to the slaves).

errno 2 is "No such file or directory" however the structure is correct
and this is confirmed by the fact that the bin log rotation/flushing
does work correctly most of the time.

Does any one have any ideas why this may be taking place?

Mike

[dayjah]

Okay so we worked this out.

If you ever see this type of problem you should watch out of the easy
mistake that we made.

We were issuing the flush logs to rotate the binary logs, mysql keeps
track of the binary logs that it has to rotate. If the file is
expecting to rotate is not there then it fails with the error in my
previous post. Our backup was moving the file as part of the whole
flush logs process.

Net effect was mysql hadn't moved the bin log (probably because some
transaction was taking place) by the time our process has moving it and
deleting it.

duh!

Mike

[Chander Ganesan]

> Okay so we worked this out.

If you ever see this type of problem you should watch out of the easy
mistake that we made.

We were issuing the flush logs to rotate the binary logs, mysql keeps
track of the binary logs that it has to rotate. If the file is
expecting to rotate is not there then it fails with the error in my
previous post. Our backup was moving the file as part of the whole
flush logs process.

Net effect was mysql hadn't moved the bin log (probably because some
transaction was taking place) by the time our process has moving it and
deleting it.

Hi Mike,

I'd recommend that you use MySQL's binary log handling tools (i.e.,
never remove the logs by hand or in a script, instead use the
appropriate 'PURGE MASTER' statements within the mysql client to do so.
This will ensure that MySQL knows that the log has been deleted, and
since it'll be deleting/removing the logs you shouldn't ever run into
issues where a log isn't available.

Ideally, your log rotation application would query the server to
determine the current log in use (show master logs), check all of the
slaves to ensure they are using the current log (to make sure you don't
break replication by deleting a log that a slave depends on), and only
then purge the logs that aren't in use ('PURGE MASTER LOGS TO
'log-bin.000012';).

If you need MySQL (or PostgreSQL) training, we offer a wide range of
both. (http://www.otg-nc.com)

--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
Phone: [phone]
duh!

Mike