MySQL 5.1.6以上版本动态开启慢查询日志

在slow_query_log (注意log_slow_querys参数已经废弃)值为ON的情况下(默认为OFF),当一条SQL语句执行的时间超过了long_query_time 预设的时间(默认为10s,同时精确到微秒)时,默认(log_output值为FIFL时)就会把这种慢查询记录到:slow_query_log_file值所指定的文件中。
mysql> select @@global.log_output;
+---------------------+
| @@global.log_output |
+---------------------+
| FILE |
+---------------------+
1 row in set (0.00 sec)
注意在MySQL5.1就开始支持把慢查询的日志记录放到mysq.slow_log中,但需要设置log_output变量值为TABLE:
mysql> set @@global.log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)
mysql> desc mysql.slow_log;
+----------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+-------------------+-----------------------------+
| start_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user_host | mediumtext | NO | | NULL | |
| query_time | time | NO | | NULL | |
| lock_time | time | NO | | NULL | |
| rows_sent | int(11) | NO | | NULL | |
| rows_examined | int(11) | NO | | NULL | |
| db | varchar(512) | NO | | NULL | |
| last_insert_id | int(11) | NO | | NULL | |
| insert_id | int(11) | NO | | NULL | |
| server_id | int(10) unsigned | NO | | NULL | |
| sql_text | mediumtext | NO | | NULL | |
+----------------+------------------+------+-----+-------------------+-----------------------------+
11 rows in set (0.01 sec)
注意,文件记录和写表记录只会有一种生效。

修改之前:
mysql> show variables like '%slow%';
+---------------------+------------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------------+
| log_slow_queries | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql-5.1.36/var/localhost-slow.log |
+---------------------+------------------------------------------------+
mysql>
4 rows in set (0.00 sec)
注意这个时候localhost-slow.log文件是不存在的:
mysql> system cat /usr/local/mysql-5.1.36/var/localhost-slow.log
cat: /usr/local/mysql-5.1.36/var/localhost-slow.log: No such file or directory
修改slow_query_log的方法:
mysql> warnings;
Show warnings enabled.
mysql> set @@global.log_slow_queries=ON;
Query OK, 0 rows affected, 1 warning (0.01 sec)
Warning (Code 1287): The syntax '@@log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '@@slow_query_log' instead
注意已经不再使用log_slow_queries参数,用slow_query_log替代,这样修改slow_query_log时,log_slow_queries也会被隐性地修改:
mysql> set @@global.slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)
修改之后:
mysql> show variables like '%slow%';
+---------------------+------------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------------+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql-5.1.36/var/localhost-slow.log |
+---------------------+------------------------------------------------+
4 rows in set (0.00 sec)
这时localhost-slow.log文件已经建立:
mysql> system cat /usr/local/mysql-5.1.36/var/localhost-slow.log
/usr/local/mysql-5.1.36/libexec/mysqld, Version: 5.1.36-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument

分析时我们可以直接查询慢查询日志,也可以通过mysqldumpslow命令(推荐)来解析这个文件:
# mysqldumpslow localhost-slow.log
Reading mysql slow query log from localhost-slow.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts

我们可以通过sleep函数来做简单的测试:
如:select sleep(11);
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值