定时删除日志文件(Mysql删除日志文件恢复磁盘空间及相关优化)

一、问题说明

  一个跑了3年的管理系统,年后突然有人报错说登录的时候提示无用户,于是排查发现Web端连接数据库异常,于是查数据库的服务器发现磁盘空间满了,而占用最大的就是Mysql数据库的日志目录,于是清理了Mysql的日志目录恢复了正常,处理过程还原模拟如下:

[root@mysql1 ~]# df -h
文件系统                 容量  已用  可用 已用% 挂载点
devtmpfs                 2.0G     0  2.0G    0% /dev
tmpfs                    2.0G     0  2.0G    0% /dev/shm
tmpfs                    2.0G   12M  2.0G    1% /run
tmpfs                    2.0G     0  2.0G    0% /sys/fs/cgroup
/dev/mapper/centos-root   17G  17G   0G   100% /
/dev/sda1               1014M  141M  874M   14% /boot
tmpfs                    394M     0  394M    0% /run/user/0

二、删除多余的日志文件

2.1、确认日志文件并删除

2.1.1、登陆Mysql并 查看当前使用的日志文件。

mysql>  show binary logs;
 ---------------------- ----------- 
| Log_name             | File_size |
 ---------------------- ----------- 
| master-rs-bin.000001 |      1236 |
| master-rs-bin.000002 |       177 |
| master-rs-bin.000003 |       177 |
| master-rs-bin.000004 |       177 |
| master-rs-bin.000005 |       177 |
| master-rs-bin.000006 |       177 |
| master-rs-bin.000007 |       177 |
| master-rs-bin.000008 |       177 |
| master-rs-bin.000009 |       177 |
| master-rs-bin.000010 |       177 |
| master-rs-bin.000011 |       177 |
| master-rs-bin.000012 |       177 |
| master-rs-bin.000013 |       177 |
| master-rs-bin.000014 |       177 |
| master-rs-bin.000015 |       177 |
| master-rs-bin.000016 |       154 |
 ---------------------- ----------- 
16 rows in set (0.00 sec)

2.1.2、查看Mysql当前正在使用的日志文件名称

mysql> show master status;        #确认当前使用的日志名称为master-rs-bin.000016
 ---------------------- ---------- -------------- ------------------ ------------------- 
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 ---------------------- ---------- -------------- ------------------ ------------------- 
| master-rs-bin.000016 |      154 | rongshu      |                  |                   |
 ---------------------- ---------- -------------- ------------------ ------------------- 
1 row in set (0.00 sec)

2.1.3、删除日志文件

  我们确认Mysql当前使用的日志文件名称为master-rs-bin.000016,所以在删除时应避免删除master-rs-bin.000016文件。

mysql>  purge binary logs to 'master-rs-bin.000016';  #删除除master-rs-bin.000016文件以外的日志文件;
Query OK, 0 rows affected (0.00 sec)

mysql> show binary logs;   #重新查看只剩下一个master-rs-bin.000016文件了;
 ---------------------- ----------- 
| Log_name             | File_size |
 ---------------------- ----------- 
| master-rs-bin.000016 |       154 |
 ---------------------- ----------- 
1 row in set (0.00 sec)

2.2、慢查询日志文件删除

  在删除了日志文件之后查看了一下日志文件的目录,发现还有一个mysql1-slow.log文件居然有19G的大小,由于系统跑了很长时间了,果断删除文件,操作如下。

2.2.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/mysql1-slow.log |
 --------------------------- -------------------------------- 
5 rows in set (0.00 sec)

2.2.2、关闭慢查询日志

mysql> set global slow_query_log=0;   #关闭慢查询
Query OK, 0 rows affected (0.00 sec)

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            | OFF                            |
| slow_query_log_file       | /var/lib/mysql/mysql1-slow.log |
 --------------------------- -------------------------------- 
5 rows in set (0.01 sec)

2.2.3、指定新的慢查询日志文件路径

mysql> set global slow_query_log_file='/var/lib/mysql/mysql1-new-slow.log.log';  #修改文件
Query OK, 0 rows affected (0.00 sec)

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            | OFF                                    |
| slow_query_log_file       | /var/lib/mysql/mysql1-new-slow.log.log |
 --------------------------- ---------------------------------------- 
5 rows in set (0.00 sec)

2.2.4、开启慢查询日志并确认正常

mysql> set global slow_query_log=1;   #设置开启
Query OK, 0 rows affected (0.01 sec)

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/mysql1-new-slow.log.log |
 --------------------------- ---------------------------------------- 
5 rows in set (0.00 sec)

#模拟一个慢查询语句确认日志读写正常
mysql> select sleep(5) as a, 1 as b;

2.2.5、删除之前的慢查询文件

  注意:如果有其他空间足够的服务器,请在删除之前备份,本例为未备份直接删除。

[root@mysql1 ~]# cd /var/lib/mysql/
[root@mysql1 mysql]# rm -rf mysql1-slow.log

三、系统的优化

3.1、配置Mysql日志文件定时清理

3.1.1、方法一:无需重启,Mysql服务的配置方式

#查询当前保留的天数,默认为0即不自动删除;
mysql> show variables like '%expire%';
 -------------------------------- ------- 
| Variable_name                  | Value |
 -------------------------------- ------- 
| disconnect_on_expired_password | ON    |
| expire_logs_days               | 0     |
 -------------------------------- ------- 
2 rows in set (0.00 sec)

#设置自动删除时间为30天,具体日志要保留的时间需以所在环境的要求决定;
mysql> set global expire_logs_days = 30;
Query OK, 0 rows affected (0.00 sec)

#查询确认修改正常
mysql> show variables like '%expire%';
 -------------------------------- ------- 
| Variable_name                  | Value |
 -------------------------------- ------- 
| disconnect_on_expired_password | ON    |
| expire_logs_days               | 30    |
 -------------------------------- ------- 
2 rows in set (0.00 sec)

3.1.2、方法二:需重启,修改Mysql服务配置文件的方式

[root@mysql1 mysql]# vi /etc/myf
#添加如下一行配置
expire_logs_days=10

#重启Mysql服务
systemctl restart mysqld

service mysqld restart

3.2、关闭未走索引查询语句的慢查询日志

#查询log_queries_not_using_indexes的当前状态是否关闭
mysql> show variables like '%indexes%';
 ---------------------------------------- ------- 
| Variable_name                          | Value |
 ---------------------------------------- ------- 
| log_queries_not_using_indexes          | ON    |
| log_throttle_queries_not_using_indexes | 0     |
 ---------------------------------------- ------- 
2 rows in set (0.00 sec)

#关闭log_queries_not_using_indexes
mysql> set global log_queries_not_using_indexes = 'off';
Query OK, 0 rows affected (0.00 sec)

#确认已关闭
mysql> show variables like '%indexes%';
 ---------------------------------------- ------- 
| Variable_name                          | Value |
 ---------------------------------------- ------- 
| log_queries_not_using_indexes          | OFF   |
| log_throttle_queries_not_using_indexes | 0     |
 ---------------------------------------- ------- 
2 rows in set (0.00 sec)