php小代码---从慢日志文件分离出日志记录并存放于excel文件中

php小代码---从慢日志文件分离出日志记录并存放于excel文件中

 
<?php
 
header("Content-type:text/html; charset=UTF-8");
ini_set(&#39;max_execution_time&#39;, &#39;10000&#39;);
ini_set(&#39;memory_limit&#39;,&#39;1024M&#39;);
 
function dealslowContent($content) {
    $data = array();
    preg_match(&#39;/#\sUser@Host:(.+)\n#\sThread_id/&#39;, $content, $match);
    $data[&#39;User@Host&#39;] = $match[1];
    preg_match(&#39;/#\sQuery_time:\s(.+)\sLock_time/&#39;, $content, $match);
    $data[&#39;Query_time&#39;] = $match[1];
    preg_match(&#39;/\sLock_time:\s(.+)\sRows_sent/&#39;, $content, $match);
    $data[&#39;Lock_time&#39;] = $match[1];
 
    preg_match(&#39;/\sRows_examined:\s(.+)\sRows_affected/&#39;, $content, $match);
    $data[&#39;Rows_examined&#39;] = $match[1];
    preg_match(&#39;/\sRows_read:\s(.+)\n#\sBytes_sent/&#39;, $content, $match);
    $data[&#39;Rows_read&#39;] = $match[1];
 
    preg_match(&#39;/timestamp=(\d+);\s/&#39;, $content, $match);
    $data[&#39;timestamp&#39;] = $match[1];
 
    preg_match(&#39;/#\sBytes_sent:\s(.+)\sSET/&#39;, $content, $match);
    $data[&#39;Bytes_sent&#39;] = $match[1];
 
    $sqlPos = strpos($content, &#39;;&#39;);
    $data[&#39;sql&#39;] = substr($content, $sqlPos + 1, strlen($content));
    return $data;
}
 
$slowData = array();
 
$handle = fopen(&#39;mysql-slow.log.2&#39;, &#39;r&#39;);
$content = &#39;&#39;;
$middlerCha = &#39;&#39;;
$identifier = &#39;# User@Host&#39;;
$identifierLength = strlen($identifier);
while (!feof($handle)) {
    $oneTakeCha = fread($handle, 1);
    if ($oneTakeCha === &#39;#&#39;) {
        $middlerCha = &#39;#&#39; . fread($handle, $identifierLength - 1);
        if ($middlerCha === $identifier) {
            if ($content != &#39;&#39;) {
                $slowData[] = dealslowContent($content);
                $content = $middlerCha;
            } else {
                $content = $middlerCha;
            }
        } else {
            $content.=$middlerCha;
        }
    } else {
        $content.= $oneTakeCha;
    }
}
$slowData[] = dealslowContent($content);
fclose($handle);
 
include &#39;libraries/PHPExcel.php&#39;;
include &#39;libraries/PHPExcel/IOFactory.php&#39;;
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;A&#39;)->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;B&#39;)->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;C&#39;)->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;D&#39;)->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;E&#39;)->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;F&#39;)->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;G&#39;)->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;H&#39;)->setWidth(15);
 
$titlearray = array(
    "主机信息" => &#39;User@Host&#39;, "查询时间" => &#39;Query_time&#39;, "锁表时间" => &#39;Lock_time&#39;
    , "查询时检查行数" => &#39;Rows_examined&#39;, "读取行数" => &#39;Rows_read&#39;, "时间" => &#39;timestamp&#39;,
    "发送字节数" => &#39;Bytes_sent&#39;, "执行的sql" => &#39;sql&#39;
);
 
$col = 0;
foreach ($titlearray as $key => $title) {
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $key);
    $col++;
}
 
$j = 2;
foreach ($slowData as $key => $sd) {
    $col = 0;
    foreach ($titlearray as $key2 => $title) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $j, $sd[$title]);
        $col++;
    }
    $j++;
}
 
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = IOFactory::createWriter($objPHPExcel, &#39;Excel5&#39;);
// Sending headers to force the user to download the file
header(&#39;Content-Type: application/vnd.ms-excel&#39;);
header(&#39;Content-Disposition: attachment;filename="slowsql.xls"&#39;);
header(&#39;Cache-Control: max-age=0&#39;);
$objWriter->save(&#39;php://output&#39;);