Mysql日志清理

背景

服务器运行一段时间之后，突然报磁盘已满了，查询了一下服务器磁盘后，发现mysql有一个文件占用了快20G

解决方法

1. 修改慢日志保存路径

#查询慢日志保存路径
mysql> show variables like '%slow%';
+---------------------------+-----------------------------+
| Variable_name             | Value                       |
+---------------------------+-----------------------------+
| log_slow_admin_statements | OFF                         |
| log_slow_slave_statements | OFF                         |
| slow_launch_time          | 2                           |
| slow_query_log            | ON                          |
| slow_query_log_file       | /var/lib/mysql/web-slow.log |
+---------------------------+-----------------------------+
5 rows in set (0.03 sec)

#关闭慢日志
mysql> set global slow_query_log=0;

#设置新保存路径
mysql> set global slow_query_log_file='/var/lib/mysql/web-slow-new.log';

#打开慢日志
mysql> set global slow_query_log=1;

#查询结果
mysql> show variables like '%slow%';
+---------------------------+---------------------------------+
| Variable_name             | Value                           |
+---------------------------+---------------------------------+
| log_slow_admin_statements | OFF                             |
| log_slow_slave_statements | OFF                             |
| slow_launch_time          | 2                               |
| slow_query_log            | ON                              |
| slow_query_log_file       | /var/lib/mysql/web-slow-new.log |
+---------------------------+---------------------------------+
5 rows in set (0.04 sec)

2. 删除原来的慢日志

rm /var/lib/mysql/web-slow.log

其他

#查询正在运行的sql语句
select * from information_schema.`PROCESSLIST` where info is not null;