Sangco – 程序员之家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_hostmediumtext NOT NULL,
query_time time NOT NULL,
lock_timetime NOT NULL,
rows_sentint(11) NOT NULL,
rows_examined int(11) NOT NULL,
dbvarchar(512) NOT NULL,
last_insert_id int(11) NOT NULL,
insert_idint(11) NOT NULL,
server_idint(10) unsigned NOT NULL,
sql_textmediumtext NOT NULL
) ENGINE=CSVDEFAULT CHARSET=utf8 COMMENT=’Slow log’;
SET GLOBAL slow_query_log = ‘ON’;
DROP TABLE mysql.slow_log_drop;
————————————————
版权声明：本文为CSDN博主「zhonglijun_05」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zhonglijun_05/article/details/53333545
