Go MySQL Empty Slow Query file

Overview

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

Test environment: MySQL 5.6.21

Steps

Configure Slow Query

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

Vim/etc/my.cnf

#slow_queryslow_query_log =1# turn on slow query Slow_query_log_file=/var/lib/mysql/mysql-slow.log #设置慢查询路径long_query_time =0.01 #设置慢查询时间

Restart MySQL service to take effect after configuration

Query slow query configuration is successful

Test Slow Query

Use information_schema;select * from tables;

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

View slow query files: Cat Mysql-slow.log

The slow query log file records every day slow query records, each record is a time: the beginning of the record contains the login information, the duration of the query, 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

SET Global slow_query_log=0

Query whether to turn off global slow query

SHOW VARIABLES like '%query_log% ';

Set up a new slow query file

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

Turn on Slow query

SET Global slow_query_log=1;

SHOW VARIABLES like '%query_log% ';

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

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

At this time we can archive the MV previous slow query file.

Restart MySQL Service

Service MySQL Restart

SHOW VARIABLES like '%query_log% ';

After restarting the MySQL service, the slow log file will be re-converted to the previous file set in My.cnf, so if you want to restart the service after the slow log file or just set the Mysql-slow_ New.log, you will need to modify the global settings while modifying the My.cnf file, which will ensure that the file after the reboot or the previously modified file.

Summarize

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

This article goes from:

Note: Pursuer.chen Blog: http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly to the link, otherwise reserves the right to hold responsibility. Welcome to the exchange of discussions

Go MySQL Empty Slow Query file