473,703 Members | 2,394 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

replication bin logs issue

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

May 26 '06 #1
2 2661
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

May 30 '06 #2
> 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: 877-258-8987/919-463-0999
duh!

Mike


Jun 5 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5101
by: RYAN | last post by:
Hi All, I am new to Oracle and I have some question that I hope that someone out there can help me. I have heard the Oracle Rman can do replication of Archives logs. In this way, one can setup some sort or Oracle Instances replication accross 2 machine at different sites. 1. Does amyone know how is the performance of this method of replication as
2
1595
by: Ian Wyld | last post by:
Hello All We intend to replicate a database in order to have it as a near immediate standby in case of the failure of the main server. Is this the best solution for disaster recovery? We are currently testing our replication plan. When we set up the subscriber, a snapshot of the publisher is created and this takes hours. Is there a better way?
1
1540
by: Vincento Harris | last post by:
Is there any problem with implementating some kind of replication and backing up transaction logs at the same time? SQL Server 2000 Standard Edition The server is configured to back up log files regularly, with the presence of a(New) Disaster recovery server and with an inability for standby(Standard Edition)I am planning to implement some kind of replication hopefully still backing up the transaction logs to enable a point in time...
1
4351
by: GB | last post by:
Hi, I'm trying to find an easy, efficient and safe site-to-site backup/failover solution. The idea is to have anytime a remote copy of 99.99% of the data. Productive db -> VPN (internet) -> Backup site source destination
1
2401
by: Andy K | last post by:
Hi , Finally i was able to setup my replication process between two databases . For those who were not following my story , i was trying to setup a replication between two databases on different linux server (RH 7.3) for UDB 7.x . I did the capture with a : asnccp mong warm
1
1831
by: Andrew Chanter | last post by:
I developed 3 different replicated database applications in MS Access for 3 different corporate clients in Access 97 several years ago to enable data to be shared across wide area networks. I observed 2 issues that plagued all 3 systems. 1. the database files continually became corrupt during synchronization. Most times this was easily fixed via a repair and compact, but the frequency with which this occurred was of concern, and...
2
2395
by: Simon Windsor | last post by:
Hi I am used to using Oracle (15 years) and MySQL(5 years), but I am planning to move an existing application from MySQL to Postgres. The reasons are very simple,
3
4546
by: Gert van der Kooij | last post by:
Hi, Our SQL Replication is between DB2 databases on Windows servers. I'm searching for the document which tells me how to migrate our SQL Replication environment from V8 to V9 (we also need to migrate from V7 to V8 but that's fully described so that's no problem). The PDF 'Migrating to Replication Version 9' doesn't contain a description about migrating SQL Replication, only Q replication. I found some links to PDF manuals which should...
2
7234
by: tech101 | last post by:
Can I get the master (or slaves) to automatically remove the binary logs once they are processed by all slaves? It says in the mysql manual : If you are using replication, you should not delete old binary log files until you are sure that no slave still needs to use them. For example, if your slaves never run more than three days behind, once a day you can execute mysqladmin flush-logs on the master and then remove any logs that are...
0
8750
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8662
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9111
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
6585
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5922
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4421
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4679
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3114
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2058
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.