命令开启数据库慢查询

命令开启数据库慢查询只是暂时的，重启数据库就还原了。
如果要永久设置，需要修改my.cnf中对应的参数之后重启数据库生效。

#记录执行时间超过一秒的

show variables like "%long%";  
set GLOBAL long_query_time=1;

#开启慢查询记录

show variables like "%slow%"; 
set GLOBAL slow_query_log='ON';

#把mysql慢查询记录到数据库表里面、默认是FILE

show variables like "%log_output%";  
set GLOBAL log_output='TABLE';  

#记录没有使用索引的

show variables like "%log_queries_not_using_indexes%"; 
set GLOBAL log_queries_not_using_indexes='ON';  

#修改文件

set GLOBAL slow_query_log_file='/var/lib/mysql/slow.log';

#慢查询数量

show global status like '%slow%';

#快速还原

SET global slow_query_log = OFF;
SET GLOBAL long_query_time = 10.000000;
SET GLOBAL log_queries_not_using_indexes = OFF;
SET global log_output='FILE';-- FILE

#清空slow_log表

SET GLOBALslow_query_log = 'OFF';
ALTER TABLE mysql.slow_log RENAME mysql.slow_log_drop;

CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';

SET GLOBAL slow_query_log = 'ON';
DROP TABLE mysql.slow_log_drop;