MySQL clears the slow query file _ MySQL

This chapter describes how to generate a new slow query file online when the slow query file is large. Test Environment: mysql5.6.21 step 1 configure slow query default my. cnf file in the etc directory vimetcmy. cnf # slow_queryslow_query_log1 # enable slow query slo overview

This chapter mainly describes how to generate a new slow query file online when the slow query file is large.

Test Environment: mysql 5.6.21

Procedure

Configure slow query

The default my. cnf file is in the/etc/directory.

Vim/etc/my. cnf # slow_queryslow_query_log = 1 # enable slow query slow_query_log_file =/var/lib/mysql/mysql-slow.log # set slow query path long_query_time = 0.01 # set slow query time

The configuration takes effect only after the mysql service is restarted.

Query whether the slow query configuration is successful

Test slow query

Use information_schema;

Select * from tables;

The statement execution time took 1.1 seconds and 314 rows of records were returned;

Viewing slow query files: cat mysql-slow.log

The slow query log file records the slow query records every day. each record starts with Time:. it records the logon information, query Time, and lock Time, number of returned rows, number of scanned records, and executed statements.

Generate slow query log files online

1. disable global slow query

SET global slow_query_log=0

Whether to disable global slow query

Show variables like '% query_log % ';

Set a new slow query file

SET global slow_query_log_file='/var/lib/mysql/mysql-slow_new.log'

Enable slow query

Enable slow query

SET global slow_query_log=1;SHOW VARIABLES LIKE '%query_log%';

A new slow query file is generated in the slow query path.

The slow query information is recorded in the new log file,

At this time, we can archive the slow query files before mv.

Restart mysql service

Service mysql restart

Show variables like '% query_log % ';

After the mysql service is restarted, the slow log file will be changed to the previous one in my. cnf file, so if you want to restart the service after the slow log file or just set the mysql-slow_new.log, you need to modify the global settings while modifying my. cnf file to ensure that the file is modified after restart.

Summary

Configuring slow queries is helpful for collecting statements with poor performance at ordinary times, and many tools are dedicated to analyzing slow query logs. percona-toolkit is a good tool for analyzing slow queries, if you are not using it, you can check it out.

The above is the content of MySQL to clear the slow query file _ MySQL. For more information, please follow the PHP Chinese network (www.php1.cn )!