mysql查询日志 select记录 慢查询日志 debug调试 二进制日志文件删除与设置 mysqldebug
把以下php文件 放到任意可以运行的环境 打开即可设置
<?php
/*********************需要用root帐号登录************************/
$con = mysqli_connect('localhost','root','root') or die('mysql连接失败!');
/*****************************************************************************************/
;?>
<style>
table{border-spacing:0px;border-collapse: collapse ;}
td,th{border:1px solid #efefef;}
tr:nth-child(odd){background-color:#efefef;}tr:hover{background-color:#ffffdd;}
.nav {background-color:#35AADC;}
.nav a{display:inline-block;padding:10px;color:#FFFFFF;text-decoration:none;}
.caozuo a{text-decoration:none;color:Blue;margin-right:10px;}
.nav a:hover{
background-color:#2295c6;
}
#long_query_time{display:inline-block;}
#long_query_time #time{height:30px;width:70px;}
</style>
<div class="nav">
<a href="?">首页</a>
<a href="?type=sql_log">日志</a>
</div>
<?php
mysqli_query($con,'set names utf8') or die('设置网页编码失败!');
function getSize($size) {
if ($size >= 1073741824) {$size = round($size/1073741824*100)/100 .' GB';
} elseif ($size >= 1048576) {$size = round($size / 1048576 * 100) / 100 . ' MB';
} elseif ($size >= 1024) {$size = round($size / 1024 * 100) / 100 . ' KB';
} else {$size = $size . ' 字节';}
return $size;
}
$type="index";
if(isset($_GET['type'])){$type=$_GET['type'];}
?>
<?php
if($type=="sql_log"){
$sql="select * from mysql.general_log where command_type='Query' limit 100";
$result=mysqli_query($con,$sql);
if($result){
echo "<table>
<tr>
<th>序号</th>
<th style='min-width:170px;'>时间</th>
<th>thread_id</th>
<th>sql语句</th>
</tr>";
$i=1;
while($row = mysqli_fetch_assoc($result)) {
if(substr($row['user_host'],0,4)!='root'){//过滤掉root帐号的操作
echo "<tr>";
echo "<td>{$i}</td>";$i++;
echo "<td>{$row['event_time']}</td>";
//echo "<td>{$row['user_host']}</td>";
echo "<td style='color:#cacaca;'>{$row['thread_id']}</td>";
echo "<td><textarea style='width:1000px;height:40px;'>{$row['argument']}</textarea></td>";
echo "</tr>";
}
}
echo "</table>";
mysqli_real_query($con,"TRUNCATE mysql.general_log"); //显示完后清空日志
}
}
if($_GET['caozuo']=="reset master"){
if(!mysqli_real_query($con,"reset master")){echo $sql."<br>执行失败";}
}
;?>
<?php if($type=="index"){?>
<?php
if($_GET['caozuo']=="set_global_general_log_on"){ // 普通查询日志开启
$sql="set global general_log=on;";
if(!mysqli_real_query($con,$sql)){
echo $sql."<br>执行失败";
}
}
if($_GET['caozuo']=="set_global_general_log_off"){ //普通查询日志 关闭
$sql="set global general_log=off;";
if(!mysqli_real_query($con,$sql)){
echo $sql."<br>执行失败";
}
}
if($_GET['caozuo']=="set_global_log_output_file"){ //日志输出方式为 文件
$sql="set global log_output='file'";
if(!mysqli_real_query($con,$sql)){
echo $sql."<br>执行失败";
}
}
if($_GET['caozuo']=="set_global_log_output_table"){ //日志输出方式为 table
$sql="set global log_output='table'";
if(!mysqli_real_query($con,$sql)){
echo $sql."<br>执行失败";
}
}
if($_GET['caozuo']=="slow_query_log_on"){ //日志输出方式为 table
$sql="set global slow_query_log=ON";
if(!mysqli_real_query($con,$sql)){
echo $sql."<br>执行失败";
}
}
if($_GET['caozuo']=="slow_query_log_off"){ //日志输出方式为 table
$sql="set global slow_query_log=off";
if(!mysqli_real_query($con,$sql)){
echo $sql."<br>执行失败";
}
}
if(isset($_GET['long_query_time'])){ //设置慢查询时间值
$sql="SET GLOBAL long_query_time=".$_GET['long_query_time'];
if(!mysqli_real_query($con,$sql)){
echo $sql."<br>执行失败";
}else{
echo "<script type='text/javascript'>window.location.href='?'</script>";
}
}
?>
<table>
<tr>
<th>二进制日志文件名</th>
<th>文件大小(字节)</th>
<th>文件大小</th>
<th class="caozuo"><a href="?caozuo=reset master">全部删除</a></th>
</tr>
<?php
$sql="show binary logs"; //查看binlog文件列表
//$sql="show master status";
$result=mysqli_query($con,$sql);
if($result){
while($row = mysqli_fetch_assoc($result)) {
echo "<tr>";
echo "<td>{$row['Log_name']}</td>";
echo "<td>{$row['File_size']}</td>";
echo "<td>".getSize($row['File_size'])."</td>";
echo "</tr>";
}
}
mysqli_free_result($result);/*释放记录集*/
;?>
</table>
<br>
<table>
<tr>
<th>Variable_name(二进制日志配置信息)</th>
<th>Value
</th>
</tr>
<?php
$sql="show variables like 'log_bin%'";
$result=mysqli_query($con,$sql);
if($result){
while($row = mysqli_fetch_assoc($result)) {
echo "<tr>";
echo "<td>{$row['Variable_name']}</td>";
echo "<td>{$row['Value']}</td>";
echo "</tr>";
}
mysqli_free_result($result);/*释放记录集*/
}
;?>
</table>
<br>
<table>
<tr>
<th>MySQL查询日志 是否开启/路径</th>
<th>
</th>
<th>说明</th>
<th>操作</th>
</tr>
<?php
$caozuo1="<a href='?caozuo=set_global_general_log_on'>开启</a><a href='?caozuo=set_global_general_log_off'>关闭</a>"; // 查询日志开启与关闭
$caozuo2="<a href='?caozuo=set_global_log_output_file'>改为File</a><a href='?caozuo=set_global_log_output_table'>改为Table</a>"; //查询日志输出方式
$caozuo3="<a href='?caozuo=slow_query_log_on'>开启</a><a href='?caozuo=slow_query_log_off'>关闭</a>"; //慢查询日志的开启与关闭
$caozuo4.="<form id='long_query_time' method='get'><input id='time' type='text' name='long_query_time' value='0.000001'>秒<input type='submit' value='保存'></form>";
$sql="show variables where Variable_name like '%general_log%' or Variable_name like 'log_output%' or Variable_name like 'slow_query%' or Variable_name='long_query_time'";
$result=mysqli_query($con,$sql);
if($result){
while($row = mysqli_fetch_assoc($result)) {$caozuo="";$shuoming="";
if($row['Variable_name']=='general_log'){$shuoming="查询日志开启状态";$caozuo=$caozuo1;}
if($row['Variable_name']=='general_log_file'){$shuoming="日志路径";}
if($row['Variable_name']=='log_output'){$shuoming="日志输出方式";$caozuo=$caozuo2;}
if($row['Variable_name']=='slow_query_log'){$shuoming="慢查询日志开启状态";$caozuo=$caozuo3;}
if($row['Variable_name']=='slow_query_log_file'){$shuoming="慢查询日志路径";}
if($row['Variable_name']=='long_query_time'){$shuoming="时间值,单位(秒)";$caozuo=$caozuo4;}
echo "<tr>";
echo "<td>{$row['Variable_name']}</td>";
echo "<td>{$row['Value']}</td>";
echo "<td>{$shuoming}</td>";
echo "<td class='caozuo'>{$caozuo}</td>";
echo "</tr>";
}
mysqli_free_result($result);/*释放记录集*/
}
?>
</table>
<br>
<h4>说明</h4>
<p style="color:red;">
查看日志 需要把general_log开启,并把log_output设置为 Table 才可以查看!
</p>
<p style="color:red;">
每次刷新 显示完会自动清空查询日志表
</p>
<?php }?>