配置

配置方法一: 服务启动时

# vi /etc/my.cnf...log-output=TABLE,FILEgeneral-log=1slow-query-log=1# systemctl restart mysqld

log-output默认是FILE,还有个值是NONE,就不输出日志了.我这里演示的是表和日志文件都输出.

配置方法二: 运行时

mysql> set global log_output="table", global general_log=on, global slow_query_log=on;

查看

查看相关变量:

mysql> show variables like 'general%';+------------------+--------------------------+| Variable_name    | Value                    |+------------------+--------------------------+| general_log      | ON                       || general_log_file | /var/lib/mysql/srv00.log |+------------------+--------------------------+2 rows in set (0.00 sec)mysql> show variables like 'slow%';+---------------------+-------------------------------+| Variable_name       | Value                         |+---------------------+-------------------------------+| slow_launch_time    | 2                             || slow_query_log      | ON                            || slow_query_log_file | /var/lib/mysql/srv00-slow.log |+---------------------+-------------------------------+3 rows in set (0.00 sec)

相关日志文件:

general_log_file和slow_query_log_file变量指示的文件,可以按需要进行修改

日志相关的表:

mysql> select * from mysql.general_log;mysql> select * from mysql.slow_log;

维护

日志文件

mysql> set global general_log=off;mysql> \! mv /var/lib/mysql/srv00.log /var/lib/mysql/srv00.log.bakmysql> set global general_log=on;

或者:

mv /var/lib/mysql/srv00.log /var/lib/mysql/srv00.log.bakmv /var/lib/mysql/srv00-slow.log /var/lib/mysql/srv00-slow.log.bakmysqladmin flush-logs

修改日志表原理也是一样,先暂停,维护表,再启用

slow query log相关参数

mysql> show variables like 'long%';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.01 sec)

超过这个秒数的慢查询才记录

mysql> show variables like 'min%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| min_examined_row_limit | 0     |+------------------------+-------+1 row in set (0.00 sec)

返回记录数超过才记录

mysql> show variables like 'log_slow_admin%';+---------------------------+-------+| Variable_name             | Value |+---------------------------+-------+| log_slow_admin_statements | OFF   |+---------------------------+-------+1 row in set (0.00 sec)

是否记录管理类型的sql, 包括:ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, REPAIR TABLE.

mysql> show variables like '%not_using_indexes';+----------------------------------------+-------+| Variable_name                          | Value |+----------------------------------------+-------+| log_queries_not_using_indexes          | OFF   || log_throttle_queries_not_using_indexes | 0     |+----------------------------------------+-------+2 rows in set (0.01 sec)

没有使用索引的sql是否要记录,如果开启会产生很多记录,log_throttle_queries_not_using_indexes设置每分钟在此范围内只记录一次.

slow query log 分析

使用工具mysqldumpslow

熟悉oracle的可以认为mysqldumpslow是oracle的tkprof

总结

当然开启都会对服务器资源消耗.只在需要的时候开启,不用的时候关掉.

//END