mysql bin删不了_Mysql binlog 无法删除

最新推荐文章于 2023-04-03 21:38:48 发布

verwandte

最新推荐文章于 2023-04-03 21:38:48 发布

阅读量727

点赞数

文章标签： mysql bin删不了

本文链接：https://blog.csdn.net/weixin_30430517/article/details/113587223

版权

1.版本

1)操作系统

cat /etc/issue

CentOS release 6.6 (Final)

Kernel \r on an \m

cat /proc/version

Linux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC) ) #1 SMP Wed Oct 15 04:27:16 UTC 2014

2)mysql数据库版本

MySQL --version

MySQL Ver 14.14 Distrib 5.6.26, for linux-glibc2.5 (x86_64) using EditLine wrapper

2. 问题描述

2.1 发现问题

今天研发跟我反映他们有一套测试库，mysql 的binlog删除不了(他使用的是purge命令做日志删除)。登录发现binlog 占了将近70个G，保存了最近一周的日志：

ls -rlt 3306-bin*

-rw-rw---- 1 mysql mysql 1074477734 Jan 9 19:55 3306-bin.000049

-rw-rw---- 1 mysql mysql 1074023126 Jan 9 21:15 3306-bin.000050

......

-rw-rw---- 1 mysql mysql 859308032 Jan 16 19:49 3306-bin.000107

-rw-rw---- 1 mysql mysql 2478 Jan 17 09:19 3306-bin.index查看数据库的expire_logs_days参数，发现是7，然后我设置该参数为1，并进行 flush logs操作(正常情况下，会删除1天以前的所有binlog)

mysql> set global expire_logs_days=1;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'expire_logs_days';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| expire_logs_days | 1 |

+------------------+-------+

1 row in set (0.00 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.00 sec)但是执行后我发现，结果并未像我预想的那样，所有的binlog都还在，这是怎么回事？难道这些binlog没有记录在该实例中？查看了3306-bin.index文件，发现这些binlog文件都是记录在 binlog 的index 文件中的，然后在实例上执行showbinary logs; 发现报如下错误：

mysql> show binary logs;

ERROR 1381 (HY000): You are not using binary logging报错说我没有使用 binlog，但是我查看参数，该实例确实是打开了binlog：

mysql> show variables like '%log_bin%';

+---------------------------------+------------------------------------------+

| Variable_name | Value |

+---------------------------------+------------------------------------------+

| log_bin | ON |

| log_bin_basename | /data/mysql/mysql3306/log/3306-bin |

| log_bin_index | /data/mysql/mysql3306/log/3306-bin.index |

| log_bin_trust_function_creators | ON |

| log_bin_use_v1_row_events | OFF |

| sql_log_bin | ON |

+---------------------------------+------------------------------------------+

6 rows in set (0.00 sec)看看error 日志有没有什么信息：

2018-01-16 19:15:41 6886 [Warning] Disk is full writing '/data/mysql/mysql3306/log/3306-bin.000107' (Errcode: 28 - No space left on device). Waiting for someone to free space...

2018-01-16 19:15:41 6886 [Warning] Retry in 60 secs. Message reprinted in 600 secs

2018-01-16 19:15:44 6886 [ERROR] Error writing file '/data/mysql/mysql3306/log/slow3306.log' (errno: 1 - No space left on device)

2018-01-16 19:25:41 6886 [Warning] Disk is full writing '/data/mysql/mysql3306/log/3306-bin.000107' (Errcode: 28 - No space left on device). Waiting for someone to free space...

2018-01-16 19:25:41 6886 [Warning] Retry in 60 secs. Message reprinted in 600 secs

2018-01-16 19:35:41 6886 [Warning] Disk is full writing '/data/mysql/mysql3306/log/3306-bin.000107' (Errcode: 28 - No space left on device). Waiting for someone to free space...

2018-01-16 19:35:41 6886 [Warning] Retry in 60 secs. Message reprinted in 600 secs

2018-01-16 19:36:41 6886 [Warning] Disk is full writing '/data/mysql/mysql3306/log/3306-bin.000107' (Errcode: 28 - No space left on device). Waiting for someone to free space...

2018-01-16 19:36:41 6886 [Warning] Retry in 60 secs. Message reprinted in 600 secs

2018-01-16 19:46:41 6886 [Warning] Disk is full writing '/data/mysql/mysql3306/log/3306-bin.000107' (Errcode: 28 - No space left on device). Waiting for someone to free space...

2018-01-16 19:46:41 6886 [Warning] Retry in 60 secs. Message reprinted in 600 secs

2018-01-16 19:49:41 6886 [ERROR] An error occured during flushing cache to file. 'binlog_error_action' is set to 'IGNORE_ERROR'. Hence turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.我们看到昨天下午17点/data 目录下磁盘满了，然后刷数据到磁盘的操作失败，并且binlog_error_action参数使用的默认值(IGNORE_ERROR)。所以自动关闭了binlog(所以我们上面执行 show binary logs时报错说你没有使用binlog)。

3.解决方案

解决方案其实上面error log中已经给我们，我们需要重启实例，来重新启用binlog，然后再删除binlog(purge 或者 flush logs)。当然做这些操作时我们数据目录磁盘是不能被占满的，需要有一定的空间(可以通过删除或者清空一些无关紧要的文件来释放一些空间)，否则数据库实例可能停不掉(因为没有空间写日志)

重启实例后我们执行 show binary logs; ，我们看到此时能够正常看到当前实例下所有未被删除的binlog

mysql> show binary logs;

+-----------------+------------+

| Log_name | File_size |

+-----------------+------------+

| 3306-bin.000054 | 1073745018 |

| 3306-bin.000055 | 1073741931 |

| 3306-bin.000056 | 1073742629 |

| 3306-bin.000057 | 1073742391 |

| 3306-bin.000058 | 1073741974 |

| 3306-bin.000059 | 1073742197 |

| 3306-bin.000060 | 1074293707 |

| 3306-bin.000061 | 1074318524 |

| 3306-bin.000062 | 1074196194 |

| 3306-bin.000063 | 1073957987 |

| 3306-bin.000064 | 1074045715 |

| 3306-bin.000065 | 1074456116 |

| 3306-bin.000066 | 1074352906 |

| 3306-bin.000067 | 1073745429 |

| 3306-bin.000068 | 1089211613 |

| 3306-bin.000069 | 3264774138 |

| 3306-bin.000070 | 1073743716 |

| 3306-bin.000071 | 1074132057 |

| 3306-bin.000072 | 1074296041 |

| 3306-bin.000073 | 1073865674 |

| 3306-bin.000074 | 1074128306 |

| 3306-bin.000075 | 1693579796 |

| 3306-bin.000076 | 1073918285 |

| 3306-bin.000077 | 1074052487 |

| 3306-bin.000078 | 1074118960 |

| 3306-bin.000079 | 1074426059 |

| 3306-bin.000080 | 1092942370 |

| 3306-bin.000081 | 1073817857 |

| 3306-bin.000082 | 1074191946 |

| 3306-bin.000083 | 1074310368 |

| 3306-bin.000084 | 1073810038 |

| 3306-bin.000085 | 1074093671 |

| 3306-bin.000086 | 1074359248 |

| 3306-bin.000087 | 1073741978 |

| 3306-bin.000088 | 1074433324 |

| 3306-bin.000089 | 1073839559 |

| 3306-bin.000090 | 1074001883 |

| 3306-bin.000091 | 1074115966 |

| 3306-bin.000092 | 1074141865 |

| 3306-bin.000093 | 1074028550 |

| 3306-bin.000094 | 1074057515 |

| 3306-bin.000095 | 1074384113 |

| 3306-bin.000096 | 1073786087 |

| 3306-bin.000097 | 1074062101 |

| 3306-bin.000098 | 1074039304 |

| 3306-bin.000099 | 1073871663 |

| 3306-bin.000100 | 1074377569 |

| 3306-bin.000101 | 1367597993 |

| 3306-bin.000102 | 3076703855 |

| 3306-bin.000103 | 2959674341 |

| 3306-bin.000104 | 2960788209 |

| 3306-bin.000105 | 2960786610 |

| 3306-bin.000106 | 1073742076 |

| 3306-bin.000107 | 859308032 |

| 3306-bin.000108 | 120 |

+-----------------+------------+执行 purge logs 或者 flush logs(要先设置expire_logs_days参数)命令删除日志

verwandte

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql bin删不了_Mysql binlog 无法删除

1.版本1)操作系统cat /etc/issueCentOS release 6.6 (Final)Kernel \r on an \mcat /proc/versionLinux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7...
复制链接

扫一扫