mysql的slow_log表_【转载】mysql 开启慢查询 清空slow_log日志或者slow_log表

slow log可以查看系统的sql的执行时间 以及详细的sql，当分析系统性能的时候可以用来参考。

我用到的是这些：

mysql> show variables like '%slow%';

+---------------------------+---------------------------------------------------------+

| Variable_name | Value |

+---------------------------+---------------------------------------------------------+

| log_slow_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| slow_launch_time | 2 |

| slow_query_log | OFF |

| slow_query_log_file | D:\mysql-5.6.16\data\Data-SERVER01-slow.log |

+---------------------------+---------------------------------------------------------+

5 rows in set (0.00 sec)

mysql> show variables like 'long%';

+-----------------+-----------+

| Variable_name | Value |

+-----------------+-----------+

| long_query_time | 10.000000 |

+-----------------+-----------+

1 row in set (0.00 sec)

打开slow log

set global slow_query_log = ON;

下面是转载的，更详细一些可以留作参考。

show variableslike '%log_output%';-- 默认是FILE

show variableslike '%quer%';

-- log_output 默认是FILE，表示慢查询日志输入至日志文件，可以通过set修改输出为TABLE

-- log_queries_not_using_indexes 默认是OFF，表示是否记录没有使用索引的查询

-- slow_query_log 默认是OFF，表示是否打开慢查询

-- long_query_time默认是 10.000000，表示记录超过时间的慢查询

-- 打开慢查询，记录查询时间超过5秒的慢查询，讲慢查询结果输出至slow_log表中

set global slow_query_log = ON;

SET GLOBAL long_query_time = 5;-- 10.000000

-- SET GLOBAL log_queries_not_using_indexes = ON;-- 是否打开看个人需要

set global log_output='TABLE';-- FILE

select * from mysql.slow_log order by start_time desc;

以下代码恢复慢查询的参数设置情况

SET global slow_query_log = OFF;

SET GLOBAL long_query_time = 10.000000;

SET GLOBAL log_queries_not_using_indexes = OFF;

SET global log_output='FILE';-- FILE

-- 清空慢查询日志

show variables like '%slow%';

+---------------------+------------------------------------------+

| Variable_name |Value |

+---------------------+------------------------------------------+

| log_slow_queries| ON |

| slow_launch_time| 2 |

| slow_query_log |ON |

|slow_query_log_file | /data/mysql/slow_queries_3306.log |

+---------------------+------------------------------------------+

4 rows in set(0.00 sec)

show variables like 'log_output%';

log_output   FILE

-- 关闭日志输出

set global slow_query_log=0;

show variables like '%slow%';

+---------------------+------------------------------------------+

| Variable_name |Value |

+---------------------+------------------------------------------+

| log_slow_queries| OFF |

| slow_launch_time| 2 |

| slow_query_log |OFF |

|slow_query_log_file | /data/mysql/slow_queries_3306.log |

+---------------------+------------------------------------------+

4 rows in set(0.00 sec)

set global slow_query_log_file='/data/mysql/new-slow.log';

Query OK, 0 rowsaffected (0.03 sec)

set global slow_query_log=1;

Query OK, 0 rowsaffected (0.01 sec)

show variables like '%slow%';

+---------------------+----------------------------------------------+

| Variable_name |Value |

+---------------------+----------------------------------------------+

| log_slow_queries| ON |

| slow_launch_time| 2 |

| slow_query_log |ON |

|slow_query_log_file | /data/mysql/new_slow.log |

+---------------------+----------------------------------------------+

4 rows in set(0.00 sec)

show variableslike '%log_output%';-- 目前是FILE

mysql> selectsleep(10) as a, 1 as b;

+---+---+

| a | b |

+---+---+

| 0 | 1 |

+---+---+

1 row in set(10.00 sec)

[mysql@xxx-xxx ~]$more /data/mysql/new-slow.log

6, backup the oldbig slow log file to other directory.

-- 清空slow_log表

select * from mysql.slow_log;

SET GLOBALslow_query_log = 'OFF';

ALTER TABLE mysql.slow_log RENAME mysql.slow_log_drop;

CREATE TABLE `slow_log` (

`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,

`user_host`mediumtext NOT NULL,

`query_time` time NOT NULL,

`lock_time`time NOT NULL,

`rows_sent`int(11) NOT NULL,

`rows_examined` int(11) NOT NULL,

`db`varchar(512) NOT NULL,

`last_insert_id` int(11) NOT NULL,

`insert_id`int(11) NOT NULL,

`server_id`int(10) unsigned NOT NULL,

`sql_text`mediumtext NOT NULL

) ENGINE=CSVDEFAULT CHARSET=utf8 COMMENT='Slow log';

SET GLOBAL slow_query_log = 'ON';

DROP TABLE mysql.slow_log_drop;