MySQL 慢查询日志配置

查看慢查询配置文件

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

开启慢查询日志

mysql>set global slow_query_log = on;

查看：

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.03 sec)

设置日志文件路径

mysql> set global slow_query_log_file = '/var/log/mysql/mysql-slow.log';
Query OK, 0 rows affected (0.04 sec)

若 mysql 采用的 docker 集群方式，则 log 文件在容器中

若 mysql 是单节点物理机中安装的，记得/var/log 下的 mysql 文件夹要赋予 mysql 属组,否则不会生成 mysql-slow.log 文件

chown mysql:mysql /var/log/mysql/

记录没用使用索引查找的 SQL

mysql>set global log_queries_not_using_indexes = on; 

记录插叙语句超过一定时间的 SQL (秒)

mysql>set global long_query_time  = 0.1; 

查看：

mysql> show variables like '%quer%';
+----------------------------------------+-------------------------------+
| Variable_name                          | Value                         |
+----------------------------------------+-------------------------------+
| binlog_rows_query_log_events           | OFF                           |
| ft_query_expansion_limit               | 20                            |
| ft_query_extra_word_chars              | OFF                           |
| have_query_cache                       | YES                           |
| log_queries_not_using_indexes          | ON                            |
| log_throttle_queries_not_using_indexes | 0                             |
| long_query_time                        | 10.000000                     |
| query_alloc_block_size                 | 8192                          |
| query_cache_limit                      | 1048576                       |
| query_cache_min_res_unit               | 4096                          |
| query_cache_size                       | 16777216                      |
| query_cache_strip_comments             | OFF                           |
| query_cache_type                       | OFF                           |
| query_cache_wlock_invalidate           | OFF                           |
| query_prealloc_size                    | 8192                          |
| slow_query_log                         | ON                            |
| slow_query_log_always_write_time       | 10.000000                     |
| slow_query_log_file                    | /var/log/mysql/mysql-slow.log |
| slow_query_log_use_global_control      |                               |
| wsrep_reject_queries                   | NONE                          |
| wsrep_sst_donor_rejects_queries        | OFF                           |
+----------------------------------------+-------------------------------+
21 rows in set (0.04 sec)

参考：
https://blog.csdn.net/qq_33652147/article/details/85787812
https://www.cnblogs.com/jiqing9006/p/9098181.html