1、开启慢查询到slow_log表
set global slow_query_log = on;
set global long_query_time=1;
set global log_output='TABLE';
2、如何清空?
直接删除数据 delete from slow_log 会报异常
[SQL]delete from slow_log
[Err] 1556 - You can't use locks with log tables.
使用truncate清空
mysql -e 'use mysql; TRUNCATE TABLE slow_log;'
在删除之前可以备份到其他数据库
mysqldump --lock-tables=0 --no-create-info mysql slow_log -r /sqldata/temp/slow_log.sql
mysql -h 172.10.101.254 -uroot -p'root' -e 'use slow_log; source /sqldata /temp/slow_log.sql;'
echo -n > /sqldata/temp/slow_log.sql
mysql -e 'use mysql; TRUNCATE TABLE slow_log;'