Tips for enabling slow log query in MYSQL5.7.9 _ MySQL

This article describes how to enable slow log query in MYSQL5.7.9. For more information, see 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.

# Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at % of total RAM for dedicated server, else %.innodb_buffer_pool_size = M# Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin# These are commonly set, remove the # and set as required.basedir = /LANMP/mysqldatadir = /MYSQLDATA/dataport = # server_id = .....socket = /tmp/mysql.sock# Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.join_buffer_size = Msort_buffer_size = Mread_rnd_buffer_size = Msql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLEScharacter_set_server=utfinit_connect='SET NAMES utf'slow_query_log = slow_query_log_file = /MYSQLDATA/mysql_slow_query.loglong_query_time = 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.

The code is as follows:

Show variables like '% slow % ';

The execution result prints information such as whether to enable slow query, slow query seconds, and slow query logs on the screen.

Step 3: perform a slow query operation

In fact, it is difficult to execute a meaningful slow query, because during the test, it takes only a few seconds to query the sea scale with 0.2 million pieces of data. We can use the following statement instead:

The code is as follows:

Select sleep (10 );

Step 4: view the number of slow queries

Run the following SQL statement to check the number of slow queries that have been executed:

Show global status like '% slow % ';

Mysql log configuration:

Note: These files are generated only when mysql is restarted. # All SQL statements are recorded.

Log = E:/mysqllog/mysql. log # records the database startup and shutdown information and the error information generated during running. log-error = E:/mysqllog/myerror. log # records all SQL statements except the select statement into the log, which can be used to restore the data file log-bin = E: /mysqllog/bin # Records slow query SQL statement log-slow-queries = E:/mysqllog/slow. log # slow query time long_query_time = 0.5