MySQL slow query file clearing tutorial

Configure slow query

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

Vim/etc/my. cnf

# Slow_query

Slow_query_log = 1 # enable slow query

Slow_query_log_file =/var/lib/mysql/mysql-slow.log # set the slow query path

Long_query_time = 0.01 # set the 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

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.

Mysql correctly and securely clears the online slow query log slow log


1. see the slow log status;

Mysql> show variables like '% slow % ';
+ --------------------- + ------------------------------------------ +
| Variable_name | Value |
+ --------------------- + ------------------------------------------ +
| Log_slow_queries | ON |
| Slow_launch_time | 2 |
| Slow_query_log | ON |
| Slow_query_log_file |/mysqllog/slow_log/slow_queries_3306.log |
+ --------------------- + ------------------------------------------ +
4 rows in set (0.00 sec)

2. stop the slow log server.

Mysql> set global slow_query_log = 0;
Query OK, 0 rows affected (0.27 sec)

Mysql> show variables like '% slow % ';
+ --------------------- + ------------------------------------------ +
| Variable_name | Value |
+ --------------------- + ------------------------------------------ +
| Log_slow_queries | OFF |
| Slow_launch_time | 2 |
| Slow_query_log | OFF |
| Slow_query_log_file |/mysqllog/slow_log/slow_queries_3306.log |
+ --------------------- + ------------------------------------------ +
4 rows in set (0.00 sec)

Mysql>
Mysql> show variables like '% slow %'; -- check slow log status
+ --------------------- + ------------------------------------------ +
| Variable_name | Value |
+ --------------------- + ------------------------------------------ +
| Log_slow_queries | OFF |
| Slow_launch_time | 2 |
| Slow_query_log | OFF |
| Slow_query_log_file |/mysqllog/slow_log/slow_queries_3306.log |
+ --------------------- + ------------------------------------------ +
4 rows in set (0.00 sec)

3, reset the new path of slow log

Mysql> set global slow_query_log_file = '/mysqllog/slow_log/slow_queries_3306_new.log ';
Query OK, 0 rows affected (0.03 sec)

4. start the slow log server

Mysql>
Mysql>
Mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.01 sec)


Mysql> show variables like '% slow % ';
+ --------------------- + ---------------------------------------------- +
| Variable_name | Value |
+ --------------------- + ---------------------------------------------- +
| Log_slow_queries | ON |
| Slow_launch_time | 2 |
| Slow_query_log | ON |
| Slow_query_log_file |/mysqllog/slow_log/slow_queries_3306_new.log |
+ --------------------- + ---------------------------------------------- +
4 rows in set (0.00 sec)

5. check the slow SQL in the new slow log file.

Mysql> select sleep (10) as a, 1 as B;
+ --- +
| A | B |
+ --- +
| 0 | 1 |
+ --- +
1 row in set (10.00 sec)

Mysql>
[Mysql @ xxx-xxx ~] $ More/mysqllog/slow_log/slow_queries_3306_new.log
......
Time Id Command Argument
# Time: 140213 6:44:24
# User @ Host: root [root] @ localhost []
# Query_time: 10.000365 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp = 1392273864;
Select sleep (10) as a, 1 as B;

6. backup the old big slow log file to other directory.

Mv/mysqllog/slow_log/slow_queries_3306.log/mysqlbackup/slow_log/slow_queries_3306.log.bak.20140213