mysql慢查询日记详解数据库

mysql慢查询日记

#必须写到mysqld 注意给/tmp/showslowmysql.log 775权限 要写入权限
[mysqld]
#开启慢查询日记
slow_query_log = 1
#设置日记路径
slow_query_log_file = /tmp/showslowmysql.log
#超过1秒 就代表慢查询记录到日志
long_query_time=1
 
 
 
mysql> show variables like '%query%';
+------------------------------+------------------------+
| Variable_name | Value |
+------------------------------+------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 1.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | /tmp/showslowmysql.log |
+------------------------------+------------------------+
13 rows in set

运行mysql语句select sleep(1);

然后查看慢查询日志 cat /tmp/showslowmysql.log
# Time: 170516 14:46:47
# [email protected]: root[root] @ [122.224.247.131] Id: 67
# Query_time: 1.000352 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1494917207;
select sleep(1);