MySQL: Dynamically turn on slow query log (Slow query log)

Objective

In development, high-performance programs also include high-performance queries, so optimizing SQL is also one of the necessary skills for programmers. To optimize, you must have slow logging to know which queries are slow, and then reverse the changes

Slow log setup mode

• Write file

• Write to Database

Practice how to: Write a file

Edit my.conf to modify Log_slow_queries's log address

$ cd/etc/mysql$ cat my.cnf |grep slow log_slow_queries=/data/logs/mysql/mysql-slow.log$ sudo/etc/init.d/mysql restart

Simple validation

$ mysql -uroot -pmysql> show variables like  '%slow_query_log% '; +-------- -------------+---------------------------------+| variable_name        | value                            |+---------------------+-------------------- -------------+| slow_query_log      | on                                | |  slow_query_log_file | /data/logs/mysql/mysql-slow.log |+---------------------+----------- ----------------------+2 rows in set  (0.00 sec) $ tail -f /data/logs/ Mysql/mysql-slow.log# time: 161110 23:20:22# [email proteCted]: root[root] @ localhost []# query_time: 3.007048  lock_time:  0.000000 rows_sent: 1  rows_examined: 0set timestamp=1478791222;select  sleep (3);

Mode two: write to the database

Check the current log output mode first

Mysql> Show variables like '%log_output% '; +---------------+-------+| variable_name | Value |+---------------+-------+| Log_output | FILE |+---------------+-------+1 row in Set (0.00 sec)

Set the output mode to File,table

mysql> set global log_output= ' file,table '; Query OK, 0 rows Affected (0.00 sec) mysql> Show variables like '%log_output% '; +---------------+------------+| variable_name | Value |+---------------+------------+| Log_output | File,table |+---------------+------------+1 row in Set (0.00 sec) mysql> Select COUNT (*) from mysql.slow_log;+-------- --+|        COUNT (*) |+----------+| 2 |+----------+1 row in Set (0.00 sec)

Note: log_output parameter sets the output of the log file, the optional value is TABLE, file, NONE; "Table" means that the setting log is recorded separately in the MySQL library's general_log and Slow_log tables; "File" means logging to the operating system of the file, "NONE" means to cancel the log records.

Resources

1. Http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html

Original address: MySQL: Dynamic Open Slow Query log (Slow query log)
Tags: mysql slow_log log log_output query

Smart recommendations

• AppArmor causes custom MySQL log issues
• In MySQL, a zero number equals any string
• MetaWeblog also manages blogs such as 51cto,csdn,sina,163,oschina,cnblogs
• PAC Automatic Proxy
• Free FQ Weapon: shadowsocks

MySQL: Dynamically turn on slow query log (Slow query log)