Enable slow query log_mysql

Using MYSQL579 as the monitoring database of ZABBIX247, slow query logs were enabled some time ago. later, it was found that slow query logs expanded to 100 MB, and the last logs were mostly 01 seconds, previously, setglobalslo was dynamically set to use MYSQL 5.7.9 as the monitoring database of ZABBIX 2.4.7. some time ago, the slow query log was enabled, and later it was found that the slow query log expanded to 700 MB.

Check that most of the last 100 items were modified after 0.1 seconds. in the past, set global slow_query_log = 1; method was set dynamically.

Then I want to directly use the configuration file/etc/my. cnf for slow query.

7 [mysqld]
8
9 # Remove leading # and set to the amount of RAM for the most important data
10 # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
11 innodb_buffer_pool_size = 128 M
12
13 # Remove leading # to turn on a very important data integrity option: logging
14 # changes to the binary log between backups.
15 # log_bin
16
17 # These are commonly set, remove the # and set as required.
18 basedir =/LANMP/mysql
19 datadir =/MYSQLDATA/data
20 port = 3306
21 # server_id = .....
22 socket =/tmp/mysql. sock
23
24 # Remove leading # to set options mainly useful for reporting servers.
25 # The server defaults are faster for transactions and fast SELECTs.
26 # Adjust sizes as needed, experiment to find the optimal values.
27 join_buffer_size = 128 M
28 sort_buffer_size = 2 M
29 read_rnd_buffer_size = 2 M
30
31 SQL _mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES
32
33 character_set_server = utf8
34 init_connect = 'set NAMES utf8'
35 slow_query_log = 1
36 slow_query_log_file =/MYSQLDATA/mysql_slow_query.log
37 long_query_time = 5
38 log_queries_not_using_indexes = ON

Multiple times of service mysqld restart

Mysql> show variables like '% query % ';
+ ------------------------------ + --------------------------------- +
| Variable_name | Value |
+ ------------------------------ + --------------------------------- +
| Binlog_rows_query_log_events | OFF |
| Ft_query_expansion_limit | 20 |
| Have_query_cache | YES |
| Long_query_time | 5.000000 |
| Query_alloc_block_size | 8192 |
| Query_cache_limit | 1048576 |
| Query_cache_min_res_unit | 4096 |
| Query_cache_size | 1048576 |
| Query_cache_type | OFF |
| Query_cache_wlock_invalidate | OFF |
| Query_prealloc_size | 8192 |
| Slow_query_log | ON |
| Slow_query_log_file |/MYSQLDATA/mysql_slow_query.log |
+ ------------------------------ + --------------------------------- +
13 rows in set (0.00 sec)

It is always OFF, and the other LONG_QUERY_TIME is changed for 5 seconds.

Set an error dynamically

Mysql> set global slow_query_log = 1;
ERROR 29 (HY000): File '/MYSQLDATA/mysql_slow_query.log' not found (Errcode: 13-Permission denied)

Yes, I have deleted it. why don't I create one myself?

You have to touch one by yourself.

Or error?

Ps-ef | grep mysqld found that another mysql User started mysql

It seems that the permission for reading slow query logs by mysql users is incorrect.