ERROR: Error in Log_event::read_log_event(): ‘Event too big’, data_len: 1635021669, event_type: 116
ERROR: Could not read entry at offset 56474241: Error in log format or read error.

The replication broke down cause the binlog file got corrupted. I have found two methods to try to recover some information:

1. MySQL command “show binlog events” – reading file line by line.
2. PHP script which reads the binary file and recovers the SQL statements. The big minus of the second method is that it doesn’t recover the information about autoincrements.

Method 1

#!/bin/bash

POS=56254262
FORCE_ITERATE=1
BINLOG_FILE="binlog.010569"
CLIENT_PATH="/usr/local/services/mysql/bin/mysql"
USR="root"
PSWD="YOUR_PASSWORD"
TEMP_ERROR="/tmp/binlog.err"
TEMP_OK="/tmp/binlog.dat"
LOG_ERR="/tmp/binlog.log"

if [ -f "$TEMP_ERROR" ]; then
    rm -v "$TEMP_ERROR"
fi
if [ -f "$TEMP_OK" ]; then
    rm -v "$TEMP_OK"
fi
if [ -f "$LOG_ERR" ]; then
    rm -v "$LOG_ERR"
fi

while true; do
    echo "show binlog events in  '$BINLOG_FILE' FROM $POS LIMIT  1 " | $CLIENT_PATH -u$USR --password="$PSWD" > $TEMP_OK 2>$TEMP_ERROR
    err_msg=`cat $TEMP_ERROR`
    is_err=$(echo "$err_msg" | grep -c "ERROR")
    if [ "$is_err" = "0" ]; then
        # echo "the position: $POS is sane"
        next_pos=`tail -n 1 "$TEMP_OK" | awk -F "\t" {'print $5'}`
        tail -n 1 $TEMP_OK
        # echo "next $next_pos"
        # overwrite the position
        if [ "$FORCE_ITERATE" -eq "1" ]; then
            let POS++
        else
            POS=$next_pos
        fi
    else
        echo "the position: $POS is insane." >> $LOG_ERR
        echo $err_msg >> $LOG_ERR
        let POS++
    fi
    # how to break? :)
    # if [ "$POS" -gt "56254599" ]; then
    #   break;
    # fi
done

Method 2

< ?PHP
error_reporting(1);ini_set("error_reporting",E_ALL);ini_set("display_errors", 1);
ini_set("memory_limit","4G");
set_time_limit ( 0);
$db = array('db1','db2','db3');
$db_allowed = array('db1');
$filename_save = "/storage_nfs/logbin.010569.save";
$filename = "/storage_nfs/logbin.010569";

$save = fopen($filename_save, "w");
$handle = fopen($filename, "rb");

function get_lastchar($_str, $_size, $_min)
{
    for($i = 0; $i < $_size; $i++) {         
       // get the current ASCII character representation of the current byte
       $asciiCharacter = $_str[$i];
       // get the base 10 value of the current characer
       //$base10value = ord($asciiCharacter);
       $hex = bin2hex($asciiCharacter);
       // now convert that byte from base 10 to base 2 (i.e 01001010...)
       // $base2representation = base_convert($base10value, 10, 2);
       // print the 0s and 1s
       if (strtolower($hex)=="a1" && $i>$_min){
           // print $i.' -- ';
           // $end_pos = $i;
           // print "|xd $i dx|";
           // print $_str;
           return $i;
           // break;
       }
    }
    return 0;
}

$iterator = 0;
$iterator2 = 0;
// $fsize= (8192);
$fsize= (8192 * 8);
$end_pos=0;
$remains = '';
while (!feof($handle)) {
    $contents =  fread($handle, $fsize);
    $size = $fsize;
    if ($remains) {
        $contents = $remains.$contents;
        $size = strlen($contents);
    }
    $remains = '';
    $SYSTEM_pos = 0;
    $SYSTEM_pos = strpos($contents, "std");
    print "\n";
    print "\n";
    $size = $fsize;
    while ( $end_pos = get_lastchar($contents, $size, $SYSTEM_pos) ) {
        // print "|".$end_pos."|";
        if ($end_pos==0)
            break;
        $len = ($end_pos-$SYSTEM_pos) - 2;
        // printf ("system pos: %d, end-pos %d", $SYSTEM_pos, $end_pos);
        $sql = substr($contents, $SYSTEM_pos,  $len);
        // clear SYSTEM_string
        $sql = substr($sql, strlen('std'));
        // clear SYSTEM or remaing word
        $first30 = substr($sql, 0, 30);
        if ( ($clear_pos  = strpos($sql, 'SYSTEM')) >0)
            $sql = substr($sql, ($clear_pos+ strlen('SYSTEM')));
        $first30 = substr($sql, 0, 30);
        
        $dbfound = 0;
        foreach($db as $database) {
            $db_pos = 0;
            $db_len = strlen($database);
            $db_pos = strpos(" " /* db can be first */ . strtolower($first30), $database);
            if ( $db_pos ) {
                // printf("\nDb is: %s \n", $database);
                if (in_array($database, $db_allowed)) {
                    fputs($save, 'use '.$database .' ;'."\n");
                }
                $dbfound=1;
                break;
            }
        }            
        if ($dbfound==0)
        {
            //error
        }
        
        $sql = substr($sql, $db_len+$db_pos);
        if ($sql[0]==".")
            $sql = substr($sql, 1);
        // printf("\nLen: %s SQL: %s \n---\n", $len, $sql);
        if (in_array($database, $db_allowed)){
            if (! stristr($sql, "replman"))
                fputs($save, $sql.' ;'."\n");
        }
        $contents = substr($contents, $end_pos);
        $SYSTEM_pos = strpos($contents, "std");
        $size = $size - $end_pos;
    }
    echo "\n ------ end of chunk, last position: $SYSTEM_pos ------- \n";    
    if ($SYSTEM_pos)
        $remains = $contents;
}
print "\n";
print ' < /pre>';
fclose($handle);
fclose($save);
?>