decoder for MySQL 8.4 row-level binlogs

i’ve run into a problem – 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 – those rows contained blobs and output from mysqlbinlog -v was a mess:

i needed to find a way to decode those \x1f…. ; i wrote a nasty parser that can take – as stdin – output from mysqlbinlog -v and will print out blob lines decoded, presented as HEX.

usage: mysqlbinlog -v mysql-bin.1445384 | php -f decode.php

<?php

        $f = fopen( 'php://stdin', 'r' );

        $state = 0; // 0 - garbage, 1 - within ### UPDATE,
        $buffer = '';
        $prevLine = '';
        while(feof($f)==false){
                $l = fgets($f);
                if ($l=='')continue;

                if (str_starts_with($l,'### UPDATE')){
                        $state = 1;
                        $buffer = $prevLine.$l;
                }else{
                        if ($state == 1){
                                if (str_starts_with($l,'###')){
                                        $buffer .= $l;
                                }else{
                                        $state = 0;
                                        handleBuffer($buffer);
                                }
                        }
                }
                $prevLine = $l;
        }

        function handleBuffer($b){
                $lineFixed = "";

                foreach(explode("\n",$b) as $line){
                        if (strstr($line,"\\")!=false){ // maybe we should detect more?

                                $equalPos = strpos($line,'=');
                                $lineFixed.= substr($line,0,$equalPos+1);
                                $tmp = substr($line, $equalPos+2,-1);

                                $bin = " ".bin2hex(unescapeMysqlBinlog($tmp))."\n";
                                $lineFixed.=$bin;
                                $worth=1;
                        }else{
                                $lineFixed.="$line\n";
                        }

                }

                echo $lineFixed."\n\n\n";
        }

        function unescapeMysqlBinlog($escaped) {
                $escaped = preg_replace_callback('/\\\\x([0-9a-fA-F]{2})/', function($m) { return chr(hexdec($m[1])); }, $escaped);

                // nasty, but good enough for my data
                $escaped = str_replace("\\\\","---backslash---",$escaped);
                $escaped = str_replace(["\\'",'\f','\n','\\t', '\\b',  '\\r', ], [ "'",chr(12),"\n","\t", chr(0x08), "\r" ], $escaped);
                $escaped = str_replace("---backslash---","\\",$escaped);
                return $escaped;
        }

maybe it’ll help some poor soul who urgently needs to recover some values for some BLOBL columns from binlogs that lack original SQL statements.