Use MySQL 5.7.9 as the monitoring database for Zabbix 2.4.7. Some time ago, the slow query log was turned on, and later found that the slow query log swelled to 700M
See the last 100 most of them are 0.1 seconds later want to change, formerly is the dynamic setting of set global slow_query_log=1; Way of.
And then want to use the configuration file/etc/my.cnf with slow query
7 [mysqld]
8
9 # Remove Leading # and set to the amount of RAM for the most important data
# Cache in MySQL. Start at 70% of all RAM for dedicated server, else 10%.
One innodb_buffer_pool_size = 128M
12
# Remove Leading # to turn on a very important data integrity option:logging
# Changes to the binary log between backups.
# Log_bin
16
These is commonly set, remove the # and set as required.
Basedir =/lanmp/mysql
DataDir =/mysqldata/data
Port = 3306
# server_id = .....
Socket =/tmp/mysql.sock
23
# Remove Leading # To set options mainly useful for reporting servers.
The server defaults is faster for transactions and fast selects.
# Adjust sizes as needed, experiment to find the optimal values.
Join_buffer_size = 128M
Sort_buffer_size = 2M
Read_rnd_buffer_size = 2M
30
Sql_mode=no_engine_substitution,strict_trans_tables
32
Character_set_server=utf8
init_connect= ' SET NAMES UTF8 '
Slow_query_log = 1
Slow_query_log_file =/mysqldata/mysql_slow_query.log
PNS Long_query_time = 5
Log_queries_not_using_indexes = on
Repeatedly started several 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 |
+------------------------------+---------------------------------+
Rows in Set (0.00 sec)
It's always off, and the rest of the long_query_time gets changed for 5 seconds.
Set up an error in a dynamic manner
mysql> set global slow_query_log=1;
ERROR (HY000): File '/mysqldata/mysql_slow_query.log ' not found (Errcode:13-permission denied)
I have already deleted it, for silly not to create a self?
I had to touch a
or an error?
Ps-ef |grep mysqld found a MySQL user who started MySQL.
It seems to be the user right to read the slow query log with a MySQL user.
MYSQL 5.7.9 turn on slow query log