硬盘占满 MySQL 清理 binlog 日志

MySQL服务主机硬盘占满，MySQL自动清理binlog日志

1.问题原因

MySQL服务器硬盘占满，经排查发现数据库目录下binlog所致。

[root@SZ-P-L-DB-11 ~]# ls -hl /data/mysql/data/ | grep master-bin | head -n 5
-rw-r----- 1 mysql mysql  177 Jun 13  2018 master-bin.000001
-rw-r----- 1 mysql mysql 1.8K Jun 15  2018 master-bin.000002
-rw-r----- 1 mysql mysql 1.1G Jun 15  2018 master-bin.000003
-rw-r----- 1 mysql mysql 1.1G Jun 15  2018 master-bin.000004
-rw-r----- 1 mysql mysql 1.1G Jun 15  2018 master-bin.000005


[root@SZ-P-L-DB-11 ~]# ls -hl /data/mysql/data/ | grep master-bin | wc -l 
348

2.硬盘空间使用率

清理binlogs前

数据硬盘分区，已使用空间98%，可用空间9.9.G

[root@SZ-P-L-DB-11 ~]# df -hl /data
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-data  403G  393G  9.9G  98% /data

清理binlogs后

数据硬盘分区，已使用空间14%，可用空间348G

[root@SZ-P-L-DB-11 ~]# df -hl /data
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-data  403G   56G  348G  14% /data

3.查询binlogs信息

清理binlogs前

mysql> show binary logs;
+-------------------+------------+
| Log_name          | File_size  |
+-------------------+------------+
| master-bin.000001 | 1075501457 |
| master-bin.000002 | 1074568830 |
...
| master-bin.000376 |  742715572 |
+-------------------+------------+
376 rows in set (0.04 sec)

清理binlogs后

mysql>  show binary logs;
+-------------------+------------+
| Log_name          | File_size  |
+-------------------+------------+
| master-bin.000342 | 1073742218 |
| master-bin.000343 | 1073742066 |
...
| master-bin.000377 |       3449 |
+-------------------+------------+
36 rows in set (0.00 sec)

4.查看binlogs过期时间设置

配置前

值默认是0天

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

配置后

设置保留15天

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 15    |
+------------------+-------+
1 row in set (0.00 sec)

5.设置binlogs过期时间

设置保留15天，超出的日志自动删除。

mysql> set global expire_logs_days=15;
Query OK, 0 rows affected (0.00 sec)

6.手动触发执行清理

设置之后不会立即清除，先手动执行。

mysql> flush logs;
Query OK, 0 rows affected (11.07 sec)