ERROR: Error in Log_event::read_log_event(): ‘Event too big’, data_len: 1635021669, event_type: 116
\nERROR: Could not read entry at offset 56474241: Error in log format or read error.<\/p>\n
The replication broke down cause the binlog file got corrupted. I have found two methods to try to recover some information:<\/p>\n
1. MySQL command “show binlog events” – reading file line by line.
\n2. 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.<\/p>\n
Method 1<\/strong><\/p>\n Method 2<\/strong><\/p>\n 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/674"}],"collection":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=674"}],"version-history":[{"count":11,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/674\/revisions"}],"predecessor-version":[{"id":797,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/674\/revisions\/797"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=674"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=674"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=674"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\r\n#!\/bin\/bash\r\n\r\nPOS=56254262\r\nFORCE_ITERATE=1\r\nBINLOG_FILE=\"binlog.010569\"\r\nCLIENT_PATH=\"\/usr\/local\/services\/mysql\/bin\/mysql\"\r\nUSR=\"root\"\r\nPSWD=\"YOUR_PASSWORD\"\r\nTEMP_ERROR=\"\/tmp\/binlog.err\"\r\nTEMP_OK=\"\/tmp\/binlog.dat\"\r\nLOG_ERR=\"\/tmp\/binlog.log\"\r\n\r\nif [ -f \"$TEMP_ERROR\" ]; then\r\n rm -v \"$TEMP_ERROR\"\r\nfi\r\nif [ -f \"$TEMP_OK\" ]; then\r\n rm -v \"$TEMP_OK\"\r\nfi\r\nif [ -f \"$LOG_ERR\" ]; then\r\n rm -v \"$LOG_ERR\"\r\nfi\r\n\r\nwhile true; do\r\n echo \"show binlog events in '$BINLOG_FILE' FROM $POS LIMIT 1 \" | $CLIENT_PATH -u$USR --password=\"$PSWD\" > $TEMP_OK 2>$TEMP_ERROR\r\n err_msg=`cat $TEMP_ERROR`\r\n is_err=$(echo \"$err_msg\" | grep -c \"ERROR\")\r\n if [ \"$is_err\" = \"0\" ]; then\r\n # echo \"the position: $POS is sane\"\r\n next_pos=`tail -n 1 \"$TEMP_OK\" | awk -F \"\\t\" {'print $5'}`\r\n tail -n 1 $TEMP_OK\r\n # echo \"next $next_pos\"\r\n # overwrite the position\r\n if [ \"$FORCE_ITERATE\" -eq \"1\" ]; then\r\n let POS++\r\n else\r\n POS=$next_pos\r\n fi\r\n else\r\n echo \"the position: $POS is insane.\" >> $LOG_ERR\r\n echo $err_msg >> $LOG_ERR\r\n let POS++\r\n fi\r\n # how to break? :)\r\n # if [ \"$POS\" -gt \"56254599\" ]; then\r\n # break;\r\n # fi\r\ndone\r\n<\/pre>\n\r\n\r\n< ?PHP\r\nerror_reporting(1);ini_set(\"error_reporting\",E_ALL);ini_set(\"display_errors\", 1);\r\nini_set(\"memory_limit\",\"4G\");\r\nset_time_limit ( 0);\r\n$db = array('db1','db2','db3');\r\n$db_allowed = array('db1');\r\n$filename_save = \"\/storage_nfs\/logbin.010569.save\";\r\n$filename = \"\/storage_nfs\/logbin.010569\";\r\n\r\n$save = fopen($filename_save, \"w\");\r\n$handle = fopen($filename, \"rb\");\r\n\r\nfunction get_lastchar($_str, $_size, $_min)\r\n{\r\n for($i = 0; $i < $_size; $i++) { \r\n \/\/ get the current ASCII character representation of the current byte\r\n $asciiCharacter = $_str[$i];\r\n \/\/ get the base 10 value of the current characer\r\n \/\/$base10value = ord($asciiCharacter);\r\n $hex = bin2hex($asciiCharacter);\r\n \/\/ now convert that byte from base 10 to base 2 (i.e 01001010...)\r\n \/\/ $base2representation = base_convert($base10value, 10, 2);\r\n \/\/ print the 0s and 1s\r\n if (strtolower($hex)==\"a1\" && $i>$_min){\r\n \/\/ print $i.' -- ';\r\n \/\/ $end_pos = $i;\r\n \/\/ print \"|xd $i dx|\";\r\n \/\/ print $_str;\r\n return $i;\r\n \/\/ break;\r\n }\r\n }\r\n return 0;\r\n}\r\n\r\n$iterator = 0;\r\n$iterator2 = 0;\r\n\/\/ $fsize= (8192);\r\n$fsize= (8192 * 8);\r\n$end_pos=0;\r\n$remains = '';\r\nwhile (!feof($handle)) {\r\n $contents = fread($handle, $fsize);\r\n $size = $fsize;\r\n if ($remains) {\r\n $contents = $remains.$contents;\r\n $size = strlen($contents);\r\n }\r\n $remains = '';\r\n $SYSTEM_pos = 0;\r\n $SYSTEM_pos = strpos($contents, \"std\");\r\n print \"\\n\";\r\n print \"\\n\";\r\n $size = $fsize;\r\n while ( $end_pos = get_lastchar($contents, $size, $SYSTEM_pos) ) {\r\n \/\/ print \"|\".$end_pos.\"|\";\r\n if ($end_pos==0)\r\n break;\r\n $len = ($end_pos-$SYSTEM_pos) - 2;\r\n \/\/ printf (\"system pos: %d, end-pos %d\", $SYSTEM_pos, $end_pos);\r\n $sql = substr($contents, $SYSTEM_pos, $len);\r\n \/\/ clear SYSTEM_string\r\n $sql = substr($sql, strlen('std'));\r\n \/\/ clear SYSTEM or remaing word\r\n $first30 = substr($sql, 0, 30);\r\n if ( ($clear_pos = strpos($sql, 'SYSTEM')) >0)\r\n $sql = substr($sql, ($clear_pos+ strlen('SYSTEM')));\r\n $first30 = substr($sql, 0, 30);\r\n \r\n $dbfound = 0;\r\n foreach($db as $database) {\r\n $db_pos = 0;\r\n $db_len = strlen($database);\r\n $db_pos = strpos(\" \" \/* db can be first *\/ . strtolower($first30), $database);\r\n if ( $db_pos ) {\r\n \/\/ printf(\"\\nDb is: %s \\n\", $database);\r\n if (in_array($database, $db_allowed)) {\r\n fputs($save, 'use '.$database .' ;'.\"\\n\");\r\n }\r\n $dbfound=1;\r\n break;\r\n }\r\n } \r\n if ($dbfound==0)\r\n {\r\n \/\/error\r\n }\r\n \r\n $sql = substr($sql, $db_len+$db_pos);\r\n if ($sql[0]==\".\")\r\n $sql = substr($sql, 1);\r\n \/\/ printf(\"\\nLen: %s SQL: %s \\n---\\n\", $len, $sql);\r\n if (in_array($database, $db_allowed)){\r\n if (! stristr($sql, \"replman\"))\r\n fputs($save, $sql.' ;'.\"\\n\");\r\n }\r\n $contents = substr($contents, $end_pos);\r\n $SYSTEM_pos = strpos($contents, \"std\");\r\n $size = $size - $end_pos;\r\n }\r\n echo \"\\n ------ end of chunk, last position: $SYSTEM_pos ------- \\n\"; \r\n if ($SYSTEM_pos)\r\n $remains = $contents;\r\n}\r\nprint \"\\n\";\r\nprint ' < \/pre>';\r\nfclose($handle);\r\nfclose($save);\r\n?>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"