This article mainly introduces the method of opening slow query log in MySQL. The editor thinks it is quite good. Now I will share it with you and give it as a reference. Let’s follow the editor to take a look, I hope it can help everyone.
1.1 Introduction
Turning on the slow query log allows MySQL to record queries that exceed the specified time. By locating and analyzing performance bottlenecks, the database can be better optimized. system performance.
1.2 Log in to the database to view
[root@localhost lib]# mysql –uroot
Because there is no password set, if you have a password, enter the password in mysql –uroot –p
1.2.1 Enter MySql to check whether slow query is enabled
##
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)
1.2.2 Check the slow query timeout
mysql> show variables like 'long%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec)
1.3 Modification method 1: (not recommended)
Method 1: Advantages: Enable slow query temporarily, no restart required Database Disadvantages: MySql slow query fails after restarting Recommendation: According to business needs, it is recommended to use the second type, and the first one can be used temporarily By default, the value of slow_query_log is OFF, indicating slow query The log is disabled and can be enabled by setting the value of slow_query_log, as shown below: Whether to enable the slow query log, 1 means on, 0 means off.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)
mysql> set global slow_query_log=1; Query OK, 0 rows affected (0.11 sec)
1.3.2 Check 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 don’t know the my.cnf path, you can use find to find itRemarks: My MySQL is The compiled 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 /application/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 ###（一般都是这里）
1.4.1.1 Modification
[root@localhost log]# vim /etc/my.cnf
slow_query_log =1 slow_query_log_file=/application/mysql/data/localhost-slow.log long_query_time = 1
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 Check the slow query log
[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.sock Time 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.sock Time 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.sock Time 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: 0 SET timestamp=1496615820; select sleep(2);
1.5. 1.3 Check how many slow queries there are through MySQL commands
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, search and analyze SQL, it is obviously a laborious task. MySQL provides the log analysis tool mysqldumpslowRelated recommendations:MySQL slow query log related Configuration and use
The difference between normal and slow query logs in MySQL
Introduction to MySQL slow query operation for Mysql optimization
The above is the detailed content of Detailed explanation of how to enable slow query log in MySQL. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
