Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having some trouble with a replication setup I'm using. It has been working fine for months, and then over the weekend, the slave stopped reading updates from the masters binlog with an error Got fatal error 1236 from master when reading data from binary log: 'bogus data in log event'

When trying to read the relevant binary log using mysqlbinlog, I get the error shown below;

[root@slglcd-01] # mysqlbinlog ibm-pr-slglcd-01.000075 > /dev/null
ERROR: Error in Log_event::read_log_event(): 'Event too small', data_len: 0, event_type: 0
ERROR: Could not read entry at offset 1828: Error in log format or read error.
[root@slglcd-01] #

Frustrating! I have tried using various combinations of --start-position and --offset to try and get past the bad data, and nothing seems to be working...

What I'm after is a way of skipping (or removing) this error from within the binary log, creating a new binary log without the offending item, so that I can continue my replication.

I'm not worried about loosing the statement, this is a syslog collection database, and one missing row isn't going to hurt.

What I can't do is re-create the slave from the master, as the master uses the BLACKHOLE engine, and therefore has no real data stored...

If the worst comes to the worst, I will have to start from the next binary log in the sequence and loose the data left in the offending log.

Thanks in advance, Dave

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

Since the binary log on the Master is corrupt, there is nothing else you can do. My condolences. Just skip to the next binlog:

STOP SLAVE;
CHANGE MASTER TO master_log_file='ibm-pr-slglcd-01.000076',master_log_pos=107;
START SLAVE;

Of course, 107 is the start position for MySQL 5.5 binlogs. If the Master is 5.1 (or 5.0.95), use 106. If the Master is prior to 5.1, use 98.

If Master BinLog Corruption is frequent, you may need to consider using a smaller size for the binary logs on the Master (perhaps 128M instead of the default 1G):

[mysqld]
max_binlog_size=128M

This will not stop corruption, but lowering the size it will minimize data loss from 1G to 128M.

share|improve this answer
    
This is what I think I'm going to have to do :( –  Dave Rix Jul 3 '12 at 7:40
    
Makes me think that having smaller binary logs would be beneficial as I wouldn't loose so much if it happened again. Hasn't happened to me before though :( –  Dave Rix Jul 3 '12 at 7:41
    
Fortunately, this data is only syslog and nothing more important!!! –  Dave Rix Jul 3 '12 at 7:42
add comment

I had a very similar problem once.

As you already mentioned the problem is to find a correct --start-position.

I solved this by looking into the corrupt binlog-file with a hexeditor / xxd (to find some blocks zeroed out at the corrupt position in my case), skipping this obviously broken part of the binlog-file by looking for the next part where some useful SQL statement were visible in the hexdump and the binlog was possibly intact again.

To identify a correct binlog-header in the Hexdump I looked into the developer docs at http://forge.mysql.com/wiki/MySQL_Internals_Binary_Log.

Basically a Header starts with a timestamp (4 Bytes), A Type-Filed (1 Byte) and the Master-Server-Replication-ID (4 Bytes).

The Server-ID was relatively easy to identify in my case, so I subtracted 5 from what I thought was the position of the Server-ID and entered this value as start-position to mysqlbinlog - and it was happy.

Setting this as new master-pos on the slave finally got the replication running again.

Yes, this is "dirty" and you lose a couple of statements, but as your are already aware of this, maybe this helps in your case.

share|improve this answer
    
Thanks @Dietmar, but I've already tried skipping forward but with no luck, mysqlbinlog will not even read past the offending data. If you set the start-pos to any number, it will start with the first 'correct' location after that and read on. But in this case it won't... :( –  Dave Rix Jul 3 '12 at 7:40
    
We've run into a similar problem with one of our databases. The binlog has an entry whose size field is 00 00 00 00 and the next log pos is pointing back to an older entry. After this the binlog is just zeros for about 32kb and then the next entry starts off cleanly. If I use the byte offset of the next entry as the start-position, mysqlbinlog works just fine. Were you able to find a root cause for this? –  kashyap Dec 28 '12 at 4:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.