a. 查询慢查询相关设置
mysql> show variables like 'slow_query%'; +---------------------+-------------------------------+ | Variable_name | Value | +---------------------+-------------------------------+ | slow_query_log | OFF | // 慢查询开启状态 | slow_query_log_file | /var/lib/mysql/local-slow.log | // 慢查询日志存放的位置 +---------------------+-------------------------------+ 2 rows in set (0.04 sec) mysql> show variables like 'long_query_time'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | // 查询超过多少秒才记录 +-----------------+-----------+ 1 row in set (0.00 sec)b. 开启慢查询， 设置记录时间
mysql> set global slow_query_log=on; Query OK, 0 rows affected (0.07 sec) mysql> set global long_query_time=1; Query OK, 0 rows affected (0.02 sec)c. 查看修改后的参数（慢查询时间修改后需要开启新的链接查看）
mysql> show variables like 'slow_query%'; +---------------------+-------------------------------+ | Variable_name | Value | +---------------------+-------------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/local-slow.log | +---------------------+-------------------------------+ 2 rows in set (0.00 sec) mysql> show variables like 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ 1 row in set (0.00 sec)d. 测试是否能够记录慢查询日志
mysql> select sleep(2); +----------+ | sleep(2) | +----------+ | 0 | +----------+ 1 row in set (2.00 sec)e.在终端查看
luslin@local:/var/lib$ sudo cat /var/lib/mysql/local-slow.log /usr/sbin/mysqld, Version: 8.0.22 (MySQL Community Server - GPL). started with: Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock Time Id Command Argument # Time: 2020-10-22T03:13:08.301400Z # User@Host: root[root] @ localhost [] Id: 31 # Query_time: 11.000270 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1 use mysql; SET timestamp=1603336377; select sleep(11); # Time: 2020-10-22T03:20:46.505885Z # User@Host: root[root] @ localhost [] Id: 41 # Query_time: 2.000241 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1 SET timestamp=1603336844; select sleep(2); # Time: 2020-10-22T03:23:06.579032Z # User@Host: root[root] @ localhost [] Id: 41 # Query_time: 2.000303 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1 SET timestamp=1603336984; select sleep(2);f. 为了方便测试， 将全部记录都加入慢查询中
mysql> show variables like'%log_queries_not_using_indexes%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF | +-------------------------------+-------+ 1 row in set (0.01 sec) mysql> set global log_queries_not_using_indexes=on; Query OK, 0 rows affected (0.00 sec) mysql> show variables like'%log_queries_not_using_indexes%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+ 1 row in set (0.00 sec)