How does MySQL enable slow log query? Does mysql enable log query?

How does MySQL enable slow log query? Does mysql enable log query?

1.1 Introduction

Enabling slow query logs allows MySQL to record statements that have been queried for more than the specified time. By locating the analysis performance bottleneck, we can better optimize the performance of the database system.

1.2 log on to the database and view

[root@localhost lib]# mysql –uroot

Because no password is set, the password is connected to mysql-uroot-p.

1.2.1 check whether slow query is enabled for MySql Query

mysql> show variables like 'slow_query%';+---------------------+--------------------------------------------+| Variable_name    | Value                   |+---------------------+--------------------------------------------+| slow_query_log   | OFF                    || slow_query_log_file | /application/mysql/data/localhost-slow.log |+---------------------+--------------------------------------------+2 rows in set (0.00 sec)

Parameter description:

1. Slow_query_log slow query enabled status OFF disabled ON Enabled
2. Slow_query_log_file: location where slow query logs are stored (this directory requires the write permission of the MySQL Running Account, which is generally set to the MySQL data storage directory)

1.2.2 viewing slow query timeout

mysql> show variables like 'long%';+-----------------+-----------+| Variable_name  | Value   |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)

The number of seconds after long_query_time is queried. The default value of 10 seconds is changed to 1 second.

1.3 Method 1: (not recommended)

Method 1: advantage: temporarily enable slow query without restarting the database. disadvantage: Slow query upon MySql restart is invalid.

Recommendation: The second type is recommended based on business needs. The first type can be used temporarily.

By default, the slow_query_log value is OFF, indicating that the slow query log is disabled. You can enable it by setting the slow_query_log value, as shown in the following figure: whether to enable the slow query log; 1 indicates that it is enabled, 0 indicates disabled.

1.3.1 check whether slow query is enabled

mysql> show variables like '%slow_query_log%';+---------------------+--------------------------------------------+| Variable_name    | Value                   |+---------------------+--------------------------------------------+| slow_query_log   | OFF                    || slow_query_log_file | /application/mysql/data/localhost-slow.log |+---------------------+--------------------------------------------+2 rows in set (0.01 sec)

Modify the input statement (invalid after restart. It is recommended that the modification take effect permanently in/etc/my. cnf)

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

1.3.2 view again

mysql> show variables like '%slow_query_log%';+---------------------+--------------------------------------------+| Variable_name    | Value                   |+---------------------+--------------------------------------------+| slow_query_log   | ON                     || slow_query_log_file | /application/mysql/data/localhost-slow.log |+---------------------+--------------------------------------------+2 rows in set (0.00 sec)

1.4 Modification Method 2: (recommended)

Modify MySql slow query. Many people do not know my. cnf path. You can use find to find

Note: my MySQL compilation path is/etc/my. cnf (usually here)

[Root @ localhost log] # find/-type f-name "my. cnf "/application/mysql-5.5.51/mysql-test/suite/rpl/my. cnf/applications/mysql-5.5.51/mysql-test/suite/federated/my. cnf/application/mysql-5.5.51/mysql-5.5.51-linux2.6-x86_64/mysql-test/suite/rpl/my. cnf/application/mysql-5.5.51/mysql-5.5.51-linux2.6-x86_64/mysql-test/suite/federated/my. cnf/etc/my. cnf ### (usually here)

1.4.1.1 Modification

[root@localhost log]# vim /etc/my.cnf

Find [mysqld] and add

slow_query_log =1slow_query_log_file=/application/mysql/data/localhost-slow.loglong_query_time = 1

Parameter description:

1. The slow_query_log slow query is enabled. The status 1 is enabled.
2. Slow_query_log_file: location where slow query logs are stored
3. The number of seconds after long_query_time is queried. The default value of 10 seconds is changed to 1 second.

Restart MySQL after modification

1.5 view and Test

1.5.1.1 insert a test slow Query

mysql> select sleep(2);+----------+| sleep(2) |+----------+|    0 |+----------+1 row in set (2.00 sec)

1.5.1.2 view slow query logs

[root@localhost data]# cat /application/mysql/data/localhost-slow.log/application/mysql/bin/mysqld, Version: 5.5.51-log (MySQL Community Server (GPL)). started with:Tcp port: 3306 Unix socket: /tmp/mysql.sockTime         Id Command  Argument/application/mysql/bin/mysqld, Version: 5.5.51-log (MySQL Community Server (GPL)). started with:Tcp port: 3306 Unix socket: /tmp/mysql.sockTime         Id Command  Argument/application/mysql/bin/mysqld, Version: 5.5.51-log (MySQL Community Server (GPL)). started with:Tcp port: 3306 Unix socket: /tmp/mysql.sockTime         Id Command  Argument# Time: 170605 6:37:00# User@Host: root[root] @ localhost []# Query_time: 2.000835 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp=1496615820;select sleep(2);

1.5.1.3 run the MySQL command to view slow queries

mysql> show global status like '%Slow_queries%';+---------------+-------+| Variable_name | Value |+---------------+-------+| Slow_queries | 1   |+---------------+-------+1 row in set (0.00 sec)

1.6 log analysis tool mysqldumpslow

In the production environment, if you want to manually analyze logs and search and analyze SQL statements, it is obviously a physical activity. MySQL provides the log analysis tool mysqldumpslow.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.