MySQL binlog_format解析后筛选出指定SQL语句/支持远程自动解析，支持伪SQL转换，支持row、mixed、statement模式（原创）

2019-11-26 15:03:23   工作备份

  伪SQL,binlog,row   

  1. <?php
  2. header("Content-Type：text/html; charset=utf-8");
  4. class mysql_analysis
  5. {
  6.     protected $file;//binlog 解析后的伪sql文件
  8.     protected $mysqli;//MySQL连接句柄
  10.     protected $table;//表结构保存
  12.     protected $primaryKey;//表主键
  14.     protected $database;//当前数据库
  16.     protected $lines;//文件总行数
  18.     protected $outFile;//SQL写入文件
  20.     protected $key;//行数计数器，用于统计进度
  22.     protected $searchTable = '';//指定表筛选
  24.     protected $exceptTable = '';//排除指定表
  26.     protected $type;//操作类型
  28.     protected $startTime;//程序开始时间戳
  30.     protected $sqlNums = 0;//已解析出的SQL数量
  32.     protected $rollback = false;//回滚SQL
  34.     protected $rk = false;//insert去除主键
  36.     protected $mysqlbinlog;
  38.     protected $startDatetTime;
  40.     protected $stopDateTime;
  42.     public function __construct()
  43.     {
  44.         set_time_limit(0);//不限制脚本执行时间
  46.         $param_arr = getopt("m:h:u:d:p:",
  47.             ["port:", "output::", "help::", "table::", "type::", "except::", "rollback::", "rk::","start-datetime:","stop-datetime:","mysqlbinlog:"]);
  49.         $info = <<<EOF
  51.         example1(本地解析示例): php mysql_analysis.php -h"10.0.108.58" -ubee -dbee -pzxzdapp666 -maa.txt --output=aa.sql --table=wallet/user_role/jobs --except=wallet/user_role --port=3306
  53.         example2(远程解析示例):php mysql_analysis.php -h"10.0.108.58" -ubee -dbee-master -pzxzdapp666 --output=drop.sql  --start-datetime="2019-11-29 00:00:00" --stop-datetime="2019-11-29 17:00:00" --except=jobs --mysqlbinlog=/usr/local/mysql-5.7.25-macos10.14-x86_64/bin/mysqlbinlog
  55.             注：此脚本不指定-m情况下，需要执行shell_exec函数，请注意放行
  56.             -m 指定binlog解析出的分析文件(mysqlbinlog mysql-bin.* --base64-output=decode-rows -vv >> xxxx.txt 解析后的文件，目前支持row/statement模式，理论支持MIXED)，不指定-m则表示使用远程获取
  57.             -h 指定数据库连接地址
  58.             -u 指定用户名
  59.             -p 指定数据库密码
  60.             -d 指定数据库
  61.             --port= 指定数据库端口 可选
  62.             --mysqlbinlog 指定系统mysqlbinlog绝对地址（远程获取binlog时必选，指定-m时无需指定）
  63.             --output= 指定输出文件，不指定则自动生成一个文件（可选）
  64.             --table= 指定表,多表用/隔开，默认全库（可选）
  65.             --except= 排除指定表,多表用/隔开，默认无（可选）
  66.             --type= insert/updete/delete/alter/drop/create,多类型用/隔开 指定操作类型（可选）
  67.             --rollback 执行回滚操作，生成反向sql,row模式支持insert/delete/update，statement只支持回滚insert
  68.             --start-datetime= 用于没有指定-m参数情况下，远程获取binlog的起始时间（可选）
  69.             --stop-datetime= 用于没有指定-m参数情况下，远程获取binlog的结束时间 (可选)
  70.             --rk 去除insert语句主键 (可选，默认不去除)
  71.             --help 查看帮助
  72. EOF;
  74.         if (isset($param_arr['help'])) {
  75.             $this->log($info . "\n", true);
  76.         }
  78.         $this->log("\033[0;33;1m".$info . "\n");
  80.         if (isset($param_arr['mysqlbinlog']) && $param_arr['mysqlbinlog']) {
  81.             $this->mysqlbinlog = $param_arr['mysqlbinlog'];
  82.         }
  84.         if (!isset($param_arr['h'])) {
  85.             $this->log("请使用-h指定数据库连接地址", true);
  86.         }
  88.         if (!isset($param_arr['u'])) {
  89.             $this->log("请使用-u指定用户名", true);
  90.         }
  92.         if (!isset($param_arr['p'])) {
  93.             $this->log("请使用-p指定密码", true);
  94.         }
  96.         if (!isset($param_arr['d'])) {
  97.             $this->log("请使用-d指定数据库", true);
  98.         }
  100.         if (!isset($param_arr['output']) || (isset($param_arr['output']) && !$param_arr['output'])) {
  101.             $param_arr['output'] = "analysis-" . date("Y-m-d H:i:s") . ".sql";
  102.         }
  105.         if (isset($param_arr['rollback'])) {
  106.             //是否开启回滚SQL程序
  107.             $this->rollback = true;
  108.         }
  110.         if (isset($param_arr['rk'])) {
  111.             //是否去除主键
  112.             $this->rk = true;
  113.         }
  115.         //指定输出文件
  116.         $this->outFile = fopen($param_arr['output'], 'w+');
  118.         $this->log("SQL文件保存路径:\e[0;31;1m".$param_arr['output']);
  120.         $this->database = $param_arr['d'];
  122.         if (isset($param_arr['table']) && $param_arr['table']) {
  123.             //指定表
  124.             $this->searchTable = explode("/", strtolower($param_arr['table']));
  125.         }
  127.         if (isset($param_arr['except']) && $param_arr['except']) {
  128.             //排除指定表
  129.             $this->exceptTable = explode("/", strtolower($param_arr['except']));
  130.         }
  132.         if ($this->searchTable && $this->exceptTable) {
  133.             //同时设置了，指定表和排除表，取两数组差集
  134.             $this->searchTable = array_diff($this->searchTable, $this->exceptTable);
  135.         }
  137.         if (isset($param_arr['type'])) {
  138.             //指定操作类型（insert、update、delete）
  139.             $this->type = explode("/", strtoupper($param_arr['type']));
  140.         }
  142.         if (isset($param_arr['start-datetime']) && isset($param_arr['stop-datetime']) && $param_arr['start-datetime'] && $param_arr['stop-datetime']) {
  143.             //指定binlog 起始结束时间
  144.             $this->startDatetTime = $param_arr['start-datetime'];
  145.             $this->stopDateTime = $param_arr['stop-datetime'];
  146.         }
  148.         $this->file = isset($param_arr['m'])?$param_arr['m']:"";
  150.         $this->log("指定操作类型:\033[0;31;1m".($this->type?implode("/",$this->type):"全部"));
  152.         $this->log("指定表:\e[0;31;1m".($this->searchTable?implode("/",$this->searchTable):"全部"));
  154.         $this->log("排除指定表:\e[0;31;1m".($this->exceptTable?implode("/",$this->exceptTable):"无"));
  156.         $this->log("insert语句去除主键:\e[0;31;1m".($this->rk?"去除":"不去除"));
  158.         $this->log("是否生成回滚SQL:\e[0;31;1m".($this->rollback?"生成":"不生成"));
  160.         //连接数据库=》用于获取表结构
  161.         $this->mysqlConnect($param_arr['h'], $param_arr['u'], $param_arr['p'], $param_arr['d'],
  162.             isset($param_arr['port']) ? $param_arr['port'] : "3306");
  164.         if (!file_exists($this->file)) {
  165.             $this->log("文件不存在", true);
  166.         }
  168.         //获取所有行数
  169.         $this->getAllLines();
  171.     }
  173.     /**
  174.      * 连接数据库
  175.      * @param $host
  176.      * @param $user
  177.      * @param $password
  178.      * @param $dataBase
  179.      * @param int $port
  180.      */
  181.     public function mysqlConnect($host, $user, $password, $dataBase, $port = 3306)
  182.     {
  183.         $this->mysqli = new mysqli($host, $user, $password, $dataBase, $port);
  185.         if ($info = $this->mysqli->connect_errno) {
  186.             $this->log($this->mysqli->connect_error, true);
  187.         }
  189.         $this->getBinlog($host,$user,$password,$dataBase,$port);
  190.     }
  193.     /**
  194.      * 获取远程binlog
  195.      * @param $host
  196.      * @param $user
  197.      * @param $password
  198.      * @param $dataBase
  199.      * @param $port
  200.      */
  201.     public function getBinlog($host,$user,$password,$dataBase,$port){
  202.         if(!is_dir("result")){
  203.             mkdir("result");
  204.         }
  206.         if (!$this->file) {
  207.             if(!$this->mysqlbinlog){
  208.                 $this->log("请指定mysqlbinlog绝对地址",true);
  209.             }
  211.             $info = $this->mysqli->query("show global variables like '%binlog_format%';");
  213.             $binlog = $this->mysqli->query("show binary logs");
  215.             if ($info && ($info = $info->fetch_object()) && $binlog && $binInfo = $binlog->fetch_all()) {
  216.                 $mode = $info->Value;
  219.                 if(!isset($binInfo[0][0])){
  220.                     $this->log("读取远程binlog 失败，请尝试手动拉取binlog日志，并使用-m指定文件开始解析",true);
  221.                 }
  223.                 $time = time();
  225.                 $shell = $this->mysqlbinlog." --read-from-remote-server -h$host -u$user -p$password -P$port ".$binInfo[0][0];
  227.                 if (!in_array(strtolower($mode), array("mixed", "statement"))) {
  228.                     //row模式
  229.                     $shell.=" --base64-output=DECODE-ROWS -vv";
  230.                 }
  232.                 if($this->startDatetTime){
  233.                     $shell.=" --start-datetime=\"".strval($this->startDatetTime)."\"";
  234.                 }
  236.                 if($this->stopDateTime){
  237.                     $shell.=" --stop-datetime=\"".strval($this->stopDateTime)."\"";
  238.                 }
  240.                 $shell.=" --to-last-log --result-file=result/binlog.log";
  242.                 $this->log("开始获取远程binlog，起始log:".$binInfo[0][0]."\n指定开始时间:".($this->startDatetTime?$this->startDatetTime:"无")."\n指定结束时间:".($this->stopDateTime?$this->stopDateTime:"无"));
  244.                 shell_exec($shell);
  246.                 $this->log("binlog获取完成,耗时:".(time()-$time)."s");
  248.                 if(file_exists("result/binlog.log") && filemtime("result/binlog.log")>$time){
  249.                     $this->file = "result/binlog.log";
  250.                 }else{
  251.                     $this->log("读取远程binlog 失败，请尝试手动拉取binlog日志，并使用-m指定文件开始解析",true);
  252.                 }
  253.             }
  254.         }
  256.     }
  258.     /**
  259.      * 分析获取SQL结构体
  260.      * @return Generator
  261.      */
  262.     public function dealFile()
  263.     {
  264.         $inTable = false;
  265.         $sql = '';
  266.         $is_row = true;
  267.         $database = "";
  268.         $id = 0;
  270.         foreach ($this->readFile() as $n => $line) {
  271.             if ($is_row == true) {
  272.                 //row模式文件
  273.                 if (preg_match("/^#{3}\s+(INSERT INTO|UPDATE|DELETE FROM).*/", $line, $match)) {
  274.                     //匹配到SQL，开始拼接
  275.                     $sql = preg_replace("/^#{3}\s+/", '', $match[0]);
  276.                     $inTable = true;
  277.                 } else {
  278.                     if ($inTable && preg_match("/^(#{3}\s+|\s+|@\d+).*/", $line, $match)) {
  279.                         $sql .= preg_replace("/^#{3}\s+/", ' ', $line);
  280.                     } else {
  281.                         if ($inTable) {
  282.                             $sql = preg_replace("/\/\*(.*)+\*\//", '', $sql);
  284.                             yield "row" => $sql;
  286.                             $inTable = false;
  287.                         }
  289.                     }
  290.                 }
  291.             }
  293.             //非row模式
  294.             if (preg_match("/^\s*SET INSERT_ID=\s*(\d+)/i", $line, $match)) {
  295.                 $id = $match[1];
  296.             }
  298.             if (preg_match("/^(\s*use\s*[`a-zA-Z\-\._]+)/i", $line, $match)) {
  299.                 $database = preg_replace("/^(\s*use\s*)/", '', $match[0]);
  300.             }
  302.             if (preg_match("/^\s*(INSERT INTO|UPDATE|DELETE FROM|ALTER TABLE|DROP|CREATE).*/i", $line, $match)) {
  303.                 $is_row = false;
  304.                 $inTable = true;
  305.                 if (preg_match("/^\s*(INSERT INTO)\s+([`\-_a-zA-Z\.]+)/i", $line, $sqlInfo)) {
  307.                     //拼主键
  308.                     $primaryKey = $this->getPrimaryKey($database . "." . $sqlInfo[2]);
  310.                     if ($primaryKey && $id) {
  312.                         $sql = preg_replace("/^(\s*INSERT\sINTO\s[`_\-a-zA-Z]+\s*\()/i", "$1 `$primaryKey`,",
  313.                             $match[0]);
  315.                         $sql = preg_replace("/(values?\s*\()/i", "$1 $id,", $sql);
  317.                     }
  318.                 } else {
  319.                     $sql = preg_replace("/\s*(\/\*).*$/", '', $match[0]);//去除注释
  320.                 }
  322.             } else {
  323.                 if ($inTable && !$is_row && !preg_match("/^\/\*/", $line, $match)) {
  324.                     $sql .= $line;
  325.                 } else {
  326.                     if (!$is_row && $inTable) {
  327.                         //非row模式SQL直接输出，不需要再次进行处理
  328.                         if (preg_match("/(INSERT INTO|UPDATE|DELETE FROM|ALTER TABLE|DROP TABLE IF EXISTS|CREATE TABLE)\s+([`\-_a-zA-Z]+\.[`\-_a-zA-Z]+)/i",
  329.                                 $sql) || preg_match("/`" . $this->database . "`/",$sql)) {
  330.                             yield "statement" => $sql;
  331.                         }else{
  332.                             yield "statement" => preg_replace("/^(INSERT INTO|UPDATE|DELETE FROM|ALTER TABLE|CREATE TABLE|DROP TABLE IF EXISTS)\s*([`a-zA-Z_\-]*)\s*/i",
  333.                                 "$1 " . $database . ".$2 ", $sql);
  334.                         }
  337.                         $inTable = false;
  339.                         $is_row = true;
  340.                     }
  341.                 }
  342.             }
  343.         }
  344.     }
  346.     /**
  347.      * 根据row模式结构体生成SQL
  348.      */
  349.     public function analysisSql()
  350.     {
  351.         $this->log("正在解析binlog:".$this->file);
  353.         foreach ($this->dealFile() as $mode => $value) {
  354.             if (preg_match("/(INSERT INTO|UPDATE|DELETE FROM|ALTER TABLE|DROP TABLE IF EXISTS|CREATE TABLE)\s+([`\-_a-zA-Z\.]+)/i",
  355.                 $value, $match)) {
  356.                 $table = $match[2];
  358.                 if (preg_match("/(ALTER TABLE|DROP TABLE|CREATE TABLE)\s+([`\-_a-zA-Z\.]+)/i", $value)) {
  359.                     if (!preg_match("/`" . $this->database . "`/", $table)) {
  360.                         continue;
  361.                     }
  362.                 } else {
  363.                     $fields = $this->getTableFields($table);
  365.                     if (!$fields) {
  366.                         continue;
  367.                     }
  368.                 }
  370.                 $sql = '';
  372.                 if (preg_match("/^(INSERT INTO)\s+([_\-`a-zA-Z\.]+)/i", $value)) {
  373.                     if ($this->type && !in_array("INSERT", $this->type)) {
  374.                         continue;
  375.                     }
  377.                     if ($mode === "statement") {
  378.                         $this->out($mode, $value);
  379.                         continue;
  380.                     }
  381.                     //插入语句
  382.                     $val = "(" . implode(",", $fields) . ")";
  384.                     $sql = preg_replace("/^(INSERT INTO)\s+([_\-`a-zA-Z\.]+)\s+(SET)/", "$1 $2 $val", $value);
  386.                     if (preg_match_all("/@\d+=/", $sql, $match)) {
  387.                         foreach ($match[0] as $key => $v) {
  389.                             if ($key === 0) {
  390.                                 $sql = str_replace($v, 'values(', $sql);
  391.                             } elseif ($key == count($match[0]) - 1) {
  392.                                 $sql = preg_replace("/$v(.*)/", ",$1)", $sql);
  393.                             } else {
  394.                                 $sql = str_replace($v, ',', $sql);
  395.                             }
  396.                         }
  397.                     }
  399.                 } elseif (preg_match("/^(UPDATE)\s+([_\-`a-zA-Z\.]+)/i", $value)) {
  400.                     if ($this->type && !in_array("UPDATE", $this->type)) {
  401.                         continue;
  402.                     }
  404.                     if ($mode === "statement") {
  405.                         $this->out($mode, $value);
  406.                         continue;
  407.                     }
  408.                     //更新
  409.                     $where = explode(" SET", $value);
  411.                     foreach ($where as $k => $vv) {
  412.                         if (preg_match_all("/\s+@\d+/", $vv, $match)) {
  413.                             if ($k === 0) {
  414.                                 //SET语句
  415.                                 foreach ($match[0] as $key => $v) {
  416.                                     if ($key === 0) {
  417.                                         $sql = preg_replace("/$v=/", " " . $fields[trim($v)] . "=",
  418.                                             str_replace("WHERE", "SET", $vv));
  419.                                     } else {
  420.                                         $sql = preg_replace("/$v=/", "," . $fields[trim($v)] . "=", $sql);
  421.                                     }
  422.                                 }
  423.                             } else {
  424.                                 //where 替换@ 增加 AND
  425.                                 foreach ($match[0] as $key => $v) {
  426.                                     if ($key === 0) {
  427.                                         $sql .= preg_replace("/$v=/", " WHERE " . $fields[trim($v)] . "=", $vv);
  428.                                     } else {
  429.                                         $sql = preg_replace("/$v=/", " AND " . $fields[trim($v)] . "=", $sql);
  430.                                     }
  431.                                 }
  432.                             }
  433.                         }
  434.                     }
  436.                 } elseif (preg_match("/^(DELETE FROM)\s+([_\-`a-zA-Z\.]+)/i", $value)) {
  437.                     if ($this->type && !in_array("DELETE", $this->type)) {
  438.                         continue;
  439.                     }
  441.                     if ($mode === "statement") {
  442.                         $this->out($mode, $value);
  443.                         continue;
  444.                     }
  445.                     //删除
  446.                     if (preg_match_all("/\s+@\d+/", $value, $match)) {
  447.                         foreach ($match[0] as $key => $v) {
  448.                             if ($key === 0) {
  449.                                 $sql = preg_replace("/$v=/", " " . $fields[trim($v)] . "=", $value);
  450.                             } else {
  451.                                 $sql = preg_replace("/$v=/", " AND " . $fields[trim($v)] . "=", $sql);
  452.                             }
  453.                         }
  454.                     }
  455.                 } elseif (preg_match("/^(ALTER TABLE)\s+([_\-`a-zA-Z\.]+)/i", $value)) {
  456.                     //表结构更改
  457.                     if ($this->type && !in_array("ALTER", $this->type)) {
  458.                         continue;
  459.                     }
  461.                     $sql = $value;
  462.                 } elseif (preg_match("/^(DROP TABLE)\s+([_\-`a-zA-Z\.]+)\s*$/i", $value)) {
  463.                     //表结构更改
  464.                     if ($this->type && !in_array("DROP", $this->type)) {
  465.                         continue;
  466.                     }
  468.                     $sql = $value;
  469.                 } elseif (preg_match("/^(CREATE TABLE)\s+([_\-`a-zA-Z\.]+)/i", $value)) {
  470.                     //表结构更改
  471.                     if ($this->type && !in_array("CREATE", $this->type)) {
  472.                         continue;
  473.                     }
  475.                     $sql = $value;
  476.                 }
  478.                 if ($sql) {
  479.                     $this->out($mode, $sql);
  480.                 }
  481.             }
  482.         }
  483.     }
  485.     /**
  486.      * 写入SQL文件
  487.      */
  488.     public function out($mode, $sql)
  489.     {
  490.         $sql = preg_replace("/\n/", ' ', $sql);
  492.         if (!$this->rollback && $this->rk && preg_match("/^(INSERT INTO)\s+([_\-`a-zA-Z\.]+)/i", $sql, $match)) {
  493.             //去除主键
  494.             preg_match_all("/(?<=\()[^\)]+/", $sql, $field_value);
  496.             if (isset($field_value[0][0]) && isset($field_value[0][1])) {
  497.                 $field = explode(",", $field_value[0][0]);
  498.                 $value = explode(",", $field_value[0][1]);
  500.                 if (count($field) === count($value)) {
  502.                     $primaryKey = $this->getPrimaryKey($match[2]);
  504.                     foreach ($field as $key => $v) {
  505.                         if ($primaryKey && preg_match("/^\s*`$primaryKey/i", $v)) {
  506.                             unset($field[$key]);
  507.                             unset($value[$key]);
  508.                             break;
  509.                         }
  510.                     }
  512.                     $sql = "insert into ".$match[2]." (".implode(",",$field).") values(".implode(",",$value).")";
  513.                 }
  514.             }
  515.         }
  517.         if ($this->rollback) {
  518.             //回滚SQL
  519.             $sql = $this->backSql($mode, $sql);
  520.         }
  522.         if ($sql) {
  523.             $this->sqlNums++;
  525.             fwrite($this->outFile, $sql . ";" . PHP_EOL);
  526.         }
  527.     }
  529.     /**
  530.      * 生成回滚SQL
  531.      * @param $mode
  532.      * @param $sql
  533.      * @return string
  534.      */
  535.     public function backSql($mode, $sql)
  536.     {
  537.         $roll_sql = "";
  538.         if (preg_match("/^(INSERT INTO)\s+([_\-`a-zA-Z\.]+)/i", $sql, $match)) {
  539.             //insert 语句=》改为delete语句
  540.             preg_match_all("/(?<=\()[^\)]+/", $sql, $field_value);
  542.             if (isset($field_value[0][0]) && isset($field_value[0][1])) {
  543.                 $field = explode(",", $field_value[0][0]);
  544.                 $value = explode(",", $field_value[0][1]);
  546.                 if (count($field) !== count($value)) {
  547.                     return '';
  548.                 }
  550.                 $where = "where ";
  552.                 $primaryKey = $this->getPrimaryKey($match[2]);
  554.                 foreach ($field as $key => $v) {
  555.                     if ($primaryKey && preg_match("/^\s*`$primaryKey/i", $v)) {
  556.                         $where .= $v . "=" . $value[$key];
  557.                         break;
  558.                     } else {
  559.                         $where .= $v . "=" . $value[$key] . " AND ";
  560.                     }
  561.                 }
  563.                 $where = rtrim($where, " AND ");
  565.                 $roll_sql = "delete from " . $match[2] . " $where";
  566.             }
  567.         } elseif (preg_match("/^(UPDATE)\s+([_\-`a-zA-Z\.]+)/i", $sql, $match)) {
  568.             if ($mode == "row") {
  569.                 $set_p = stripos($sql, " set ");
  571.                 $where_p = strripos($sql, " where ");
  573.                 $set_string = preg_replace("/^\s*SET/i", " WHERE", substr($sql, $set_p, $where_p - $set_p - 1));
  574.                 $set_string = preg_replace("/,`/i", " AND `", $set_string);
  576.                 $where_string = preg_replace("/^\s*WHERE/i", " SET", substr($sql, $where_p));
  577.                 $where_string = preg_replace("/AND\s*`/i", ",`", $where_string);
  579.                 $roll_sql = $match[0] . $where_string . $set_string;
  580.             }
  582.         } elseif (preg_match("/^(DELETE FROM)\s+([_\-`a-zA-Z\.]+)/i", $sql, $match)) {
  584.             if ($mode === "row") {
  585.                 $where_p = stripos($sql, " where ");
  587.                 $where_string = preg_replace("/^\s*WHERE/i", "", substr($sql, $where_p));
  589.                 $array = preg_split("/\s+AND\s+/i", $where_string);
  591.                 $values = "values (";
  592.                 $fields = "(";
  593.                 array_map(function ($value) use (&$values, &$fields) {
  594.                     $filed = substr($value, 0, stripos($value, "="));
  595.                     $v = substr($value, stripos($value, "=") + 1);
  596.                     $values .= $v . ",";
  597.                     $fields .= $filed . ",";
  598.                 }, $array);
  600.                 $values = trim($values, ",") . ")";
  602.                 $fields = trim($fields, ",") . ")";
  605.                 $roll_sql = "insert into " . $match[2] . " " . $fields . " " . $values;
  606.             }
  607.         }
  609.         return $roll_sql;
  610.     }
  612.     /**
  613.      * 获取指定表结构
  614.      * @param $table
  615.      * @return mixed
  616.      */
  617.     public function getTableFields($table)
  618.     {
  620.         if (!preg_match("/`" . $this->database . "`/", $table)) {
  621.             return [];
  622.         }
  624.         $real_table = strtolower(preg_replace("/(" . $this->database . "|`|\.)/", '', $table));
  626.         if ($this->searchTable && !in_array($real_table, $this->searchTable)) {
  627.             //对特定表进行排查
  628.             return [];
  629.         }
  631.         if ($this->exceptTable && in_array($real_table, $this->exceptTable)) {
  632.             //对特定表进行排除
  633.             return [];
  634.         }
  636.         if (isset($this->table[$table])) {
  637.             return $this->table[$table];
  638.         }
  640.         if ($result = $this->mysqli->query("show FULL FIELDS from $table")) {
  641.             $result = $result->fetch_all();
  642.         } else {
  643.             return [];
  644.         }
  646.         foreach ($result as $k => $value) {
  647.             $this->table[$table]['@' . ($k + 1)] = "`" . $value[0] . "`";
  648.         }
  650.         return $this->table[$table];
  651.     }
  653.     /**
  654.      * 获取表主键
  655.      * @param $table
  656.      * @return string
  657.      */
  658.     public function getPrimaryKey($table)
  659.     {
  660.         if (!preg_match("/`" . $this->database . "`/", $table)) {
  661.             return '';
  662.         }
  664.         $real_table = strtolower(preg_replace("/(" . $this->database . "|`|\.)/", '', $table));
  666.         if ($this->searchTable && !in_array($real_table, $this->searchTable)) {
  667.             //对特定表进行排查
  668.             return '';
  669.         }
  671.         if ($this->exceptTable && in_array($real_table, $this->exceptTable)) {
  672.             //对特定表进行排除
  673.             return '';
  674.         }
  676.         if (isset($this->primaryKey[$table])) {
  677.             return $this->primaryKey[$table];
  678.         }
  680.         if ($result = $this->mysqli->query("show keys from $table where key_name='PRIMARY'")) {
  681.             $result = $result->fetch_all();
  683.             if (!empty($result)) {
  684.                 $this->primaryKey[$table] = $result[0][4];
  685.             }
  686.         } else {
  687.             return "";
  688.         }
  690.         return $this->primaryKey[$table];
  691.     }
  693.     /**
  694.      * 读取文件
  695.      * @param int $key
  696.      * @return Generator
  697.      */
  698.     public function readFile($key = 1)
  699.     {
  700.         $handle = fopen($this->file, 'r');
  702.         while (feof($handle) === false) {
  703.             $this->progress($key);
  704.             $key++;
  705.             yield fgets($handle);
  706.         }
  708.         fclose($handle);
  709.     }
  711.     /**
  712.      * 获取总行数
  713.      */
  714.     public function getAllLines()
  715.     {
  716.         $this->startTime = time();
  717.         $handle = fopen($this->file, 'r');
  718.         $line = 0;
  720.         $this->log("开始统计行数");
  721.         while (feof($handle) === false) {
  722.             fgets($handle);
  723.             $line++;
  724.         }
  726.         fclose($handle);
  728.         $this->lines = $line;
  730.         $this->log("统计完成，总行数:$line");
  731.     }
  733.     /**
  734.      * 日志
  735.      * @param $info
  736.      * @param bool $die
  737.      */
  738.     public function log($info, $die = false)
  739.     {
  740.         if ($die) {
  741.             echo "\033[0;31m\n提示:".$info."\033[0m".PHP_EOL;
  742.             exit("程序终止" . PHP_EOL);
  743.         }else{
  744.             echo "\033[0;32m$info\033[0m".PHP_EOL;
  745.         }
  746.     }
  748.     //进度条
  749.     public function progress($key)
  750.     {
  751.         $kb = memory_get_usage() / 1024;
  753.         $kb = round(($kb > 1024 ? $kb / 1024 : $kb), 2) . ($kb > 1024 ? "MB" : "KB");
  755.         $max_kb = memory_get_peak_usage() / 1024;
  757.         $max_kb = round(($max_kb > 1024 ? $max_kb / 1024 : $max_kb), 2) . ($max_kb > 1024 ? "MB" : "KB");
  759.         $time = time() - $this->startTime;
  761.         $time = $time > 60 ? (floor($time / 60) . "min" . ($time - floor($time / 60) * 60) . "s") : $time . "s";
  763.         printf("\033[1;33mprogress: [%-50s] %d%% Done;time:%s;sql nums:%d;Memory usage：%s;Memory max usage:%s\r\033[0m",
  764.             str_repeat('>', $key / ($this->lines) * 50), $key / ($this->lines) * 100, $time, $this->sqlNums, $kb,
  765.             $max_kb);
  766.     }
  768.     /**
  769.      * 销毁资源
  770.      */
  771.     public function __destruct()
  772.     {
  773.         // TODO: Implement __destruct() method.
  775.         if ($this->outFile) {
  776.             fclose($this->outFile);
  777.         }
  779.         if ($this->mysqli) {
  780.             $this->mysqli->close();
  781.         }
  783.         $this->log("\e[0;31;1m执行结束");
  784.     }
  785. }
  787. $mysql = new mysql_analysis();
  789. $mysql->analysisSql();
  791. unset($mysql);
  1. 文件格式（该脚本适用于分析此类格式文件）
    1. row模式
    2. #191121 11:21:22 server id 460911738  end_log_pos 6569752 CRC32 0x578dff18     Table_map: `bee`.`jobs` mapped to number 109
    3. # at 6569752
    4. #191121 11:21:22 server id 460911738  end_log_pos 6570489 CRC32 0xe2c32a78     Write_rows: table id 109 flags: STMT_END_F
    5. ### INSERT INTO `bee`.`jobs`
    6. ### SET
    7. ###   @1=515599 /* LONGINT meta=0 nullable=0 is_null=0 */
    8. ###   @2='default' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
    9. ###   @3='{"displayName":"App\x5c\x5cJobs\x5c\x5cOperationRecord","job":"Illuminate\x5c\x5cQueue\x5c\x5cCallQueuedHandler@call","maxTries":5,"timeout":null,"timeoutAt":null,"data":{"commandName":"App\x5c\x5cJobs\x5c\x5cOperationRecord","command":"O:24:\x5c"App\x5c\x5cJobs\x5c\x5cOperationRecord\x5c":13:{s:6:\x5c"\x5cu0000*\x5cu0000uri\x5c";s:11:\x5c"\x5c/api\x5c/notify\x5c";s:5:\x5c"\x5cu0000*\x5cu0000as\x5c";s:12:\x5c"normalNotify\x5c";s:7:\x5c"\x5cu0000*\x5cu0000uses\x5c";s:50:\x5c"App\x5c\x5cHttp\x5c\x5cControllers\x5c\x5cNotifyController@normalNotify\x5c";s:9:\x5c"\x5cu0000*\x5cu0000params\x5c";s:2:\x5c"[]\x5c";s:10:\x5c"\x5cu0000*\x5cu0000user_id\x5c";i:995;s:5:\x5c"tries\x5c";i:5;s:6:\x5c"\x5cu0000*\x5cu0000job\x5c";N;s:10:\x5c"connection\x5c";N;s:5:\x5c"queue\x5c";N;s:15:\x5c"chainConnection\x5c";N;s:10:\x5c"chainQueue\x5c";N;s:5:\x5c"delay\x5c";N;s:7:\x5c"chained\x5c";a:0:{}}"}}' /* LONGBLOB/LONGTEXT meta=4 nullable=0 is_null=0 */
    10. ###   @4=0 /* TINYINT meta=0 nullable=0 is_null=0 */
    11. ###   @5=NULL /* INT meta=0 nullable=1 is_null=1 */
    12. ###   @6=1574306482 /* INT meta=0 nullable=0 is_null=0 */
    13. ###   @7=1574306482 /* INT meta=0 nullable=0 is_null=0 */
    14. # at 6570489
    15. #191121 11:21:22 server id 460911738  end_log_pos 6570520 CRC32 0xcec70d2a     Xid = 38772244
    16. COMMIT/*!*/;
    17. # at 6570520
    statement模式
# at 37849
#191122 14:43:10 server id 1  end_log_pos 37849 CRC32 0x3ade880b     Intvar
SET INSERT_ID=483677/*!*/;
#191122 14:43:10 server id 1  end_log_pos 38598 CRC32 0x9f3d299e     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1574404990/*!*/;
insert into `operation_record` (`uri`, `as`, `uses`, `data`, `comment`, `user_id`, `updated_at`, `created_at`) values ('/api/auth/login', 'login', 'App\\Http\\Controllers\\AuthController@index', '{\"code\":\"855353\",\"phone\":\"13708082028\",\"latitude\":\"30.662541\",\"location\":\"\\u56db\\u5ddd\\u7701\\u6210\\u90fd\\u5e02\\u6210\\u534e\\u533a\\u6210\\u534e\\u5927\\u9053\\u65b0\\u9e3f\\u8def84\\u53f7\\u9760\\u8fd1\\u56db\\u5ddd\\u5929\\u5e9c\\u94f6\\u884c(\\u6210\\u90fd\\u6210\\u534e\\u652f\\u884c)\",\"model\":\"OPPO PACM00\",\"version\":\"Android 9\",\"longitude\":\"104.095424\"}', '接口登录校验', 0, '2019-11-22 14:43:10', '2019-11-22 14:43:10')
/*!*/;
# at 38598
#191122 14:43:10 server id 1  end_log_pos 38629 CRC32 0x4081ef04     Xid = 797
COMMIT/*!*/;
# at 38629
#191122 14:43:10 server id 1  end_log_pos 38694 CRC32 0x7237e64e     Anonymous_GTID    last_committed=89    sequence_number=90    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 38694
#191122 14:43:10 server id 1  end_log_pos 38785 CRC32 0x3b89e911     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1574404990/*!*/;
BEGIN
/*!*/;
# at 38785
#191122 14:43:10 server id 1  end_log_pos 38909 CRC32 0x52ca6fab     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1574404990/*!*/;
delete from `jobs` where `id` = 515606
/*!*/;
# at 38909
#191122 14:43:10 server id 1  end_log_pos 38940 CRC32 0xaf4f0661     Xid = 801
COMMIT/*!*/;
# at 38940

执行方式:php mysql_analysis.php -h10.0.108.58 -ubee -dbee -pzxzdapp666 -mbinlog.txt --output=all.sql --table=wallet/user_role/jobs --except=wallet/user_role --port=3306

张书贤

User Image

标签

ajax跨域 API设计 coding exception freeswitch freeswitch，多网卡 frp，局域网穿透，端口转发 Git自动部署 go,浏览器调用，模拟按键操作 jwt jwt方法 laravel monit,centos7,进程监控 mysql时间函数 nginx,小程序 nginx，跨域 python小计 RESTful 设计规范 tar恢复备份，新硬盘 个人常用函数 主从同步 二步验证 伪SQL,binlog,row 元组 内存优化 列表 多网卡 字符串 正则 笛卡尔积 订阅，发布 队列任务，laravel
热门文章
友情链接