{"id":3835,"date":"2025-12-18T18:31:00","date_gmt":"2025-12-18T17:31:00","guid":{"rendered":"https:\/\/kudzia.eu\/b\/?p=3835"},"modified":"2025-12-19T09:21:55","modified_gmt":"2025-12-19T08:21:55","slug":"decoder-for-mysql-8-4-row-level-binlogs","status":"publish","type":"post","link":"https:\/\/kudzia.eu\/b\/2025\/12\/decoder-for-mysql-8-4-row-level-binlogs\/","title":{"rendered":"decoder for MySQL 8.4 row-level binlogs"},"content":{"rendered":"\n<p>i&#8217;ve run into a problem &#8211; i had mysql 8.4 binlogs in row format generated with <em>binlog_rows_query_log_events =OFF<\/em>; i needed to see how exactly certain rows looked like before \/ after particular updates. problem was &#8211; those rows contained blobs and output from <em>mysqlbinlog -v <\/em>was a mess:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"403\" src=\"https:\/\/kudzia.eu\/b\/wp-content\/uploads\/2025\/12\/image-1024x403.png\" alt=\"\" class=\"wp-image-3836\" srcset=\"https:\/\/kudzia.eu\/b\/wp-content\/uploads\/2025\/12\/image-1024x403.png 1024w, https:\/\/kudzia.eu\/b\/wp-content\/uploads\/2025\/12\/image-300x118.png 300w, https:\/\/kudzia.eu\/b\/wp-content\/uploads\/2025\/12\/image-768x302.png 768w, https:\/\/kudzia.eu\/b\/wp-content\/uploads\/2025\/12\/image.png 1118w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>i needed to find a way to decode those \\x1f&#8230;. ; i wrote a nasty parser that can take &#8211; as stdin &#8211; output from <em>mysqlbinlog -v<\/em> and will print out blob lines decoded, presented as HEX.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p>usage: <em>mysqlbinlog -v mysql-bin.1445384 | php -f decode.php<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>&lt;?php\n\n        $f = fopen( 'php:\/\/stdin', 'r' );\n\n        $state = 0; \/\/ 0 - garbage, 1 - within ### UPDATE,\n        $buffer = '';\n        $prevLine = '';\n        while(feof($f)==false){\n                $l = fgets($f);\n                if ($l=='')continue;\n\n                if (str_starts_with($l,'### UPDATE')){\n                        $state = 1;\n                        $buffer = $prevLine.$l;\n                }else{\n                        if ($state == 1){\n                                if (str_starts_with($l,'###')){\n                                        $buffer .= $l;\n                                }else{\n                                        $state = 0;\n                                        handleBuffer($buffer);\n                                }\n                        }\n                }\n                $prevLine = $l;\n        }\n\n        function handleBuffer($b){\n                $lineFixed = \"\";\n\n                foreach(explode(\"\\n\",$b) as $line){\n                        if (strstr($line,\"\\\\\")!=false){ \/\/ maybe we should detect more?\n\n                                $equalPos = strpos($line,'=');\n                                $lineFixed.= substr($line,0,$equalPos+1);\n                                $tmp = substr($line, $equalPos+2,-1);\n\n                                $bin = \" \".bin2hex(unescapeMysqlBinlog($tmp)).\"\\n\";\n                                $lineFixed.=$bin;\n                                $worth=1;\n                        }else{\n                                $lineFixed.=\"$line\\n\";\n                        }\n\n                }\n\n                echo $lineFixed.\"\\n\\n\\n\";\n        }\n\n        function unescapeMysqlBinlog($escaped) {\n                $escaped = preg_replace_callback('\/\\\\\\\\x(&#91;0-9a-fA-F]{2})\/', function($m) { return chr(hexdec($m&#91;1])); }, $escaped);\n\n                \/\/ nasty, but good enough for my data\n                $escaped = str_replace(\"\\\\\\\\\",\"---backslash---\",$escaped);\n                $escaped = str_replace(&#91;\"\\\\'\",'\\f','\\n','\\\\t', '\\\\b',  '\\\\r', ], &#91; \"'\",chr(12),\"\\n\",\"\\t\", chr(0x08), \"\\r\" ], $escaped);\n                $escaped = str_replace(\"---backslash---\",\"\\\\\",$escaped);\n                return $escaped;\n        }<\/code><\/pre>\n\n\n\n<p>maybe it&#8217;ll help some poor soul who urgently needs to recover some values for some BLOBL columns from binlogs that lack original SQL statements.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>i&#8217;ve run into a problem &#8211; i had mysql 8.4 binlogs in row format generated with binlog_rows_query_log_events =OFF; i needed to see how exactly certain rows looked like before \/ after particular updates. problem was &#8211; those rows contained blobs and output from mysqlbinlog -v was a mess: i needed to find a way to [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[12],"class_list":["post-3835","post","type-post","status-publish","format-standard","hentry","category-tech","tag-mysql"],"_links":{"self":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts\/3835","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/comments?post=3835"}],"version-history":[{"count":2,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts\/3835\/revisions"}],"predecessor-version":[{"id":3838,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/posts\/3835\/revisions\/3838"}],"wp:attachment":[{"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/media?parent=3835"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/categories?post=3835"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kudzia.eu\/b\/wp-json\/wp\/v2\/tags?post=3835"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}