mysql清理日志文件

一、问题说明

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

[root@mysql1 ~]# df -h文件系统 容量已用可用 已用% 挂载点devtmpfs 2.0G 02.0G0% /devtmpfs2.0G 02.0G0% /dev/shmtmpfs2.0G12M2.0G1% /runtmpfs2.0G 02.0G0% /sys/fs/cgroup/dev/mapper/centos-root17G17G0G100% //dev/sda11014M141M874M14% /boottmpfs394M 0394M0% /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> ｓｅｌｅｃｔ 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/my.cnf#添加如下一行配置expire_logs_days=10#重启Mysql服务systemctl restart mysqld或service mysqld restart3.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_indexesmysql> 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)