一、问题说明
一个跑了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)