How Mysql empties the slow query file _mysql

I. Overview

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

Test environment: MySQL 5.6.21

Second, the steps

Configure a slow query

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

Vim/etc/my.cnf

#slow_query

slow_query_log=1# Open slow query

slow_query_log_file=/var/lib/mysql/mysql-slow.log #设置慢查询路径

long_ query_time=0.01# Set Slow query time

The MySQL service will not take effect until it is configured to restart

Query slow query configuration is successful

Test Slow Query

Use Information_schema;

SELECT * from tables;

Statement execution time spent: 1.1 seconds, returned 314 rows of records;

To view the slow query file:cat Mysql-slow.log

Slow query log file records each day slow query record, each record is to start with time: The log contains login information, the query spent, the time of the lock, the number of rows returned, the number of records scanned, the execution of the statement.

Generate slow query log files online

1. Turn off global Slow query

Copy Code code as follows:

SET Global slow_query_log=0

Query whether to turn off global slow queries

Copy Code code as follows:

Show VARIABLES like '%query_log% ';

Set up a new slow query file

Copy Code code as follows:

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

Turn on Slow query

Copy Code code as follows:

SET Global slow_query_log=1;

Copy Code code as follows:

Show VARIABLES like '%query_log% ';

In the slow query path, you can see that a new slow query file has been generated.

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

At this time we can MV previous slow query file for archiving.

Restart the MySQL service

Service MySQL Restart

Copy Code code as follows:

Show VARIABLES like '%query_log% ';

After restarting the MySQL service, the slow log file becomes the previous file that was set in my.cnf, so if you want to restart the service, the slow log file is just set up Mysql-slow_ New.log you need to modify the global settings and then modify the My.cnf file, so that you can ensure that after the reboot file or the previous modified file.

Summarize

Configure slow query for the usual collection of poor performance statement is very helpful, there are many tools to analyze the slow query log, which Percona-toolkit is a very good analysis of slow query tools, if not used to understand.

The above is the entire content of this article, I hope to help you learn.