从MySQL数据库binlog中读取数据

1. 执行SQL查看Binlog日志文件名：

show binary logs;

举个例子

2.去官网(https://dev.mysql.com/downloads/installer/)下载一个MySQL客户端

导出原始格式的binlog
进入mysql/bin目录

 mysqlbinlog -u{数据库用户名} -p{数据库密码} -h{数据库地址} --read-from-remote-server --raw mysql-bin.XXX

举个例子
mysqlbinlog -u{数据库用户名} -p{数据库密码} -h{数据库地址} --read-from-remote-server --raw mysql-bin.001204
就拿到了

3.格式化binlog

mysqlbinlog -vv --base64-output=decode-rows mysql-bin.XXX > XXX.sql

举个例子

得到的数据大概长这样子

4.把binlog按事务切割，只保留匹配到关键字的事务

$file) {    $handle = fopen($file,'r');    $rows = [];    $i = 0;    $dir = './'.$file.'_dir';    !file_exists($dir) AND mkdir($dir);    while (!feof($handle) && $row = fgets($handle)){        $rows = [];        $rowWeight = 0;        if (strpos($row,'BEGIN') !== false){            $row = trim($row);            do{                $rows []= $row;                $row = fgets($handle);                $row = trim($row);                foreach ($weightList as $weightItem) {                    list($wStr, $w) = $weightItem;                    if (strpos($row,$wStr) !== false){                        $rowWeight += $w;                    }                }// 自由发挥//                if ($rowWeight && preg_match("/###   @1=(.*) \/\* INT meta=0 nullable=0 is_null=0 /",$row, $matches)){//                    if (!in_array($matches[1], $ids)){//                        $rowWeight = -99;//                    }//                }            }while(!feof($handle) && strpos($row,'COMMIT;') === false);            $rows []= $row;            if ($rowWeight >= $minWeight){                file_put_contents("{$dir}/{$i}.sql",implode("\r\n",$rows));                $i++;            }        }    }    fclose($handle);}

5.按实际需要处理数据和表头进行组装

举个例子
我要把原来的数据和更新后的数据都去重了写入两个文本里面

{  "id": "11863",  "shop_id": "NULL",  "platform_id": "5",  "type": "2",  "name": "'字体：always in my heart'",  "translate": "'handwriting'",  "filter": "''",  "no_translation": "0",  "user_id": "915",  "create_time": "'2022-03-24 11:14:32'"}{  "id": "8",  "shop_id": "0",  "platform_id": "4",  "type": "1",  "name": "'日期'",  "translate": "'Date'",  "filter": "''",  "no_translation": "0",  "user_id": "3",  "create_time": "'2021-08-05 14:25:19'"}