正确清理mysql-bin

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

1. 背景

机器磁盘满导致mysql连接不上删除部分日志错误将mysql-bin.0050* 都删除重启mysql失败

$ service mysqld start
Starting MySQL.......... ERROR! The server quit without updating PID file (/data/mysqldata/gzqc249-243-214-g249hqzltcs1.pid).

2. 解决方法

MySQL 数据存放的绝对路径

$ grep datadir /etc/my.cnf
datadir = /data/mysqldata

先备份 /data/mysqldata/mysql-bin.index 然后清空 /data/mysqldata/mysql-bin.index。

启动mysql:

service mysqld start

3. 原因

mysql-bin是数据库的操作日志文件mysql-bin.index中记录的是文件已经删除mysql-bin.index内容需要清空。
在这里插入图片描述

4. 正确清理binlog

4.1 设置过期删除时间

查看当前到期时间

> show variables like 'expire_logs_days'; 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 30    |
+------------------+-------+

将到期时间由30天设置为10天。

方法一编辑配置文件 vim /etc/my.cnf

expire_logs_days = 10  //二进制日志自动删除的天数。

此方法需重启mysql服务

方法二无需重启mysql服务

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

MariaDB [vss_stock_option_svr_multi]> show variables like 'expire_logs_days'; 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 10    |
+------------------+-------+
1 row in set (0.00 sec)

4.1 手动删除binlog文件

PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);   //删除1天前的MySQL binlog日志

删除1天前的MySQL binlog日志

> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.002338 | 1073742660 |
| mysql-bin.002339 | 1073742551 |
| mysql-bin.002340 | 1073742466 |
| mysql-bin.002341 | 1073742538 |
| mysql-bin.002342 | 1073742770 |
| mysql-bin.002343 | 1073742574 |
| mysql-bin.002344 | 1073742084 |
| mysql-bin.002345 | 1073742809 |
| mysql-bin.002346 | 1073742195 |
| mysql-bin.002347 | 1074775919 |
| mysql-bin.002348 | 1073742659 |
| mysql-bin.002349 | 1073742546 |
| mysql-bin.002350 | 1073741966 |
| mysql-bin.002351 | 1073741891 |
| mysql-bin.002352 | 1073742204 |
| mysql-bin.002353 | 1073989969 |
| mysql-bin.002354 | 1073742107 |
| mysql-bin.002355 | 1073743414 |
| mysql-bin.002356 | 1073742527 |
| mysql-bin.002357 | 1073742335 |
| mysql-bin.002358 | 1073742868 |
| mysql-bin.002359 | 1073742808 |
| mysql-bin.002360 | 1073742326 |
| mysql-bin.002361 | 1073742219 |
| mysql-bin.002362 | 1073742028 |
| mysql-bin.002363 | 1073742256 |
| mysql-bin.002364 | 1073742147 |
| mysql-bin.002365 | 1073742558 |
| mysql-bin.002366 | 1073742303 |
| mysql-bin.002367 | 1073742538 |
| mysql-bin.002368 | 1073741929 |
| mysql-bin.002369 | 1073742054 |
| mysql-bin.002370 | 1073742387 |
| mysql-bin.002371 |  894966812 |
+------------------+------------+
34 rows in set (0.00 sec)

> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY); 
Query OK, 0 rows affected (0.01 sec)

MariaDB [vss_stock_option_svr_multi]> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.002359 | 1073742808 |
| mysql-bin.002360 | 1073742326 |
| mysql-bin.002361 | 1073742219 |
| mysql-bin.002362 | 1073742028 |
| mysql-bin.002363 | 1073742256 |
| mysql-bin.002364 | 1073742147 |
| mysql-bin.002365 | 1073742558 |
| mysql-bin.002366 | 1073742303 |
| mysql-bin.002367 | 1073742538 |
| mysql-bin.002368 | 1073741929 |
| mysql-bin.002369 | 1073742054 |
| mysql-bin.002370 | 1073742387 |
| mysql-bin.002371 |  894966812 |
+------------------+------------+
13 rows in set (0.00 sec)

操作说明