Skip to main content

MySQL Log

confdefaultfor
log_outputFILETABLE,FILE,NONE
slow_query_logOFF
slow_query_log_file<HOSTNAME>-slow.log
general_logOFF
general_log_file<HOSTNAME>.log
sql_log_offOFF控制当前会话的 general log
long_query_time10慢查询 阀值
log_slow_admin_statementsOFF
log_queries_not_using_indexesOFFe.g [ALTER,ANALYZE,CHECK,OPTIMIZE,REPAIR] TABLE
log_slow_extraOFF在 FILE 输出中包含额外信息
select *
from performance_schema.global_variables
where VARIABLE_NAME in (
'log_output', 'slow_query_log', 'long_query_time', 'slow_query_log_file',
'log_queries_not_using_indexes', 'log_throttle_queries_not_using_indexes',
'general_log', 'general_log_file'
)
order by VARIABLE_NAME;
show variables like 'log_queries_not_using_indexes';


set global log_queries_not_using_indexes = 'ON'

--
set global general_log = 'ON';
select * from mysql.general_log
order by event_time desc;
head /var/lib/mysql/mysql/slow_log.CSV
wc -l /var/lib/mysql/mysql/slow_log.CSV

apk add mariadb-client
mysqldumpslow -t 10 slow.log

# percona-toolkit
curl -LO http://www.percona.com/get/pt-query-digest
chmod +x pt-query-digest

general log

slow log

varfor
log_slow_admin_statementsOFF
log_slow_extraOFF
log_slow_replica_statementsOFF
log_slow_slave_statementsOFF
slow_launch_time2
slow_query_logON
slow_query_log_file
long_query_time10
log_outputFILE
log_queries_not_using_indexes
show variables like '%slow%';
show variables like 'long_query_time';-- 默认 10s
show variables like 'log_output';-- 默认 FILE
show variables like 'log_queries_not_using_indexes';
show variables like 'slow_query_log_file';

-- slow_query_log_file 控制文件路径
set global log_output = 'FILE,TABLE';
set global slow_query_log='ON';
set global log_queries_not_using_indexes = 'ON';

set global long_query_time=3;
set session long_query_time=3;

-- 测试
select sleep(5);
select * from mysql.slow_log limit 2;


-- 排查其他
show processlist;
show engine innodb status;

flush logs;
flush slow logs;
truncate mysql.slow_log;

maintain

  • binlog_expire_logs_seconds 默认 30 天
  • binlog_expire_logs_auto_purge=ON
PURGE { BINARY | MASTER } LOGS {
TO 'log_name'
| BEFORE datetime_expr
}
FLUSH LOGS ;
FLUSH BINARY LOGS;

SHOW BINARY LOGS;
SHOW MASTER LOGS;
PURGE BINARY LOGS BEFORE now();
mysqladmin flush-logs