SQL statement to extract MySQL binary logs in PHP

In the previous article, when extracting SQL statements from the log, I ran into a sentence of SQL that was not extracted across n rows. Later on the web search for two shells:

Sed-n '/^insert/,/;/p ' log.sql > Log_insert.sql

Sed-n '/^update/,/;/p ' log.sql > Log_update.sql

After the practice found that the two shells are flawed. This is a problem that needs to be solved, and today is the opportunity to use PHP to extract the cross-row SQL. Note that it is clear to note that the variable $action is the extracted SQL statement header, such as delete, INSERT, update, and so on, while the variable $table is the table name.

Later I will find time to write a shell version, try to use the most concise way to achieve, the dish itself is also very time.

W1<?PHP2 //need to use Mysqlbinlog to convert binary files to read clear text3 //45th row Gets the $sql variable for filtered clean SQL4 //Some binary log SQL will have 5 non-visible characters, some logs do not, temporarily do not know why, please open Filter_sql filter comments5 //The final SQL variable can be processed as long as the PROC_SQL function is implemented6 7 $action= ' INSERT ';8 $table= ' table ';9 Ten  One $fp=fopen(' Bin_log.txt ', ' rb+ '); A if(!$fp)Exit(' Err:file error '); - $delimiter= '/*!*/; '.CHR(0x0a); -  the // -  while(!feof($fp)) - { -     $sql= ' '; +     $str=fgets($fp, 8192); -     if(strstr($str,$action) &&strstr($str,$table)) +     { A         $sql=$str; at          -          while(!feof($fp)) -         { -             if(substr($sql,-7) = =$delimiter) -             { -                 $sql=substr($sql, 0,-7); in                  Break; -             } to              +             $str=fgets($fp, 8192); -             $sql.=$str; the         } *          $         if(feof($fp))Panax Notoginseng         { -             if(substr($sql,-7) = =$delimiter) the             { +                 $sql=substr($sql, 0,-7); A                  Break; the             } +         } -          $         $sql=fileter_sql ($sql); $Proc_sql ($sql); -     } - } the  - Wuyi  the fclose($fp); -  Wu functionFileter_sql ($sql) - { About     return $sql; $     //return substr ($sql, 0,-5); - } -  - functionProc_sql ($sql) A { +     Echo $sql,Php_eol,Php_eol,Php_eol; the}

SQL statement to extract MySQL binary logs in PHP