MySQL open slow query log

1. Slow query parameter description

slow_query_log slow query on state
slow_query_log_file The location where the slow query log is stored (this directory needs the writable permission of the MySQL running account, and is generally set to the MySQL data storage directory)
long_query_time query be recorded before

2. Operation steps

1. View slow query parameters

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | OFF                              |
| slow_query_log_file       | /mysql/data/localhost-slow.log   |
+---------------------------+----------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

2. Setting method

Method 1: Global variable setting
1. Set slow_query_log global variable to "ON" state

mysql> set global slow_query_log='ON';

2. Set the slow query log path

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';

3. Set slow query time, set statistics if it exceeds 1s

mysql> set global long_query_time=1;

Method 2: Configuration file setting

1. Modify the configuration file my.cnf and add it under [mysqld]

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

2. Restart the mysql service to take effect

3. Verify the slow query configuration

1. Check the parameters after setting

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

2. Execute a slow query SQL

mysql> select sleep(2);

3. Check whether there is a record in the slow query log