MYSQL5.7.9 tips for opening slow query logs _mysql

Use MySQL 5.7.9 as the monitoring database for Zabbix 2.4.7. Some time ago opened a slow query log, and later found that the slow query log expanded to 700M

See the last 100 most of them are 0.1 seconds later want to change, previously is the dynamic set global slow_query_log=1; Way of.

Then you want to use the configuration file directly/etc/my.cnf with slow query

# Remove Leading # and set to the amount's RAM for the most important data # cache in MySQL.
Start at% 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/mysql DataDir =/mysqldata/data Port = # server_id = ... socket =/tmp/mysql.sock # Remove Leading # t
o 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 = m sort_buffer_size = m read_rnd_buffer_size = M sql_mode=no_engine_substitution,strict_trans_tables Character_set_server=utf init_connect= ' Set NAMES utf ' Slow_query_log = Slow_query_log_file =/mysqldata/mysql_slow_ Query.log Long_query_time = Log_queries_not_using_indexes = On

Repeatedly started a good many times service mysqld restart

Mysql> Show variables like '%query% ';
+------------------------------+---------------------------------+
| variable_name | Value |
+------------------------------+---------------------------------+
| binlog_rows_query_log_events | Off |
| Ft_query_expansion_limit | |
| 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 other long_query_time get changed for 5 seconds.

Set up an error in dynamic mode

mysql> set global slow_query_log=1;
ERROR (HY000): File '/mysqldata/mysql_slow_query.log ' not found (Errcode:13-permission denied)

Yes I have deleted it, for silly not to create a?

I had to touch a

or an error?

Ps-ef |grep mysqld found a MySQL user who started MySQL.

Seems to be using MySQL users read slow query log user rights problem

below to introduce you to open MySQL slow query log

To view the configuration:

Query slow query time
show variables like "Long_query_time"; default 10s 
//View slow query configuration The status like
"%slow_queries%"; 
View slow Query log path

Modify configuration file

Add the following two words to the My.ini

Log-slow-queries = D:\wamp\mysql_slow_query.log
long_query_time=5

The first sentence uses to define the path of the slow query log (because it is windows, so it does not involve permission issues)

The second sentence used to define the number of seconds to search queries is slow query, I defined here is 5 seconds

Step Two: View the status of a slow query

Execute the following SQL statement to view the state of the MySQL slow query

Copy Code code as follows:

Show variables like '%slow% ';

The results are printed on the screen, such as whether to open the slow query, the number of seconds to slow the query, and the slow query log.

Step three: Perform a slow query operation

In fact, it is difficult to perform a meaningful slow query, because in your own test, even if the query has 200,000 data of the massive table, only need 0. A few seconds. We can use the following statement instead:

Copy Code code as follows:

SELECT sleep (10);

Step Fourth: View the number of slow queries

Look through the following SQL statements to see how many slow queries have been performed:

Show global status like '%slow% ';

MySQL Log configuration:

Note: These days files will not be generated until MySQL restarts # log all SQL statements

 Log=e:/mysqllog/mysql.log #记录数据库启动关闭信息, and error messages generated during the run log-error=e:/mysqllog/ Myerror.log # Records In addition to all SQL statements except the SELECT statement into the log, you can use to recover the data file Log-bin=e:/mysqllog/bin #记录查询慢的sql语句 log-slow-queries=e:/ Mysqllog/slow.log #慢查询时间 long_query_time=0.5