Error reading MySQL 5.5 binary log using mysqlbinlog

Question

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

Answer 1

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.

Answer 2

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.