mysql中删除binlog的方法?mysql中如何删除binlog?

最新推荐文章于 2025-06-27 10:53:54 发布

weixin_33991727

最新推荐文章于 2025-06-27 10:53:54 发布

阅读量364

点赞数

CC 4.0 BY-SA版权

文章标签：操作系统数据库

原文链接：http://www.cnblogs.com/chuanzhang053/p/9336386.html

本文介绍MySQL中Binlog的清理方法，包括通过系统参数expire_logs_days自动删除旧Binlog，使用purge binary logs命令手动清理，及注意事项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

需求描述:

　　在mysql中如何删除binlog,因为随着数据库的运行,mysql中产生的binlog会越来越大,有可能把磁盘撑爆了,所以记录下删除

　　binlog的方法.

操作过程:

1.通过系统参数控制保留多久的binlog

在my.cnf中,加入以下的参数,重启实例

expire_logs_days = 3             #意思是保留3天的binlog;默认值是0,表示不自动删除.

备注:设置完该参数之后,当重启实例,或者刷新日志的时候,就会进行检查,然后删除3天之前的日志

2.测试在刷新日志的时候,触发删除

　　2.1查看当前系统中的二进制文件

[root@testvm data]# ls -ltr mysql-bin.*
-rw-r----- 1 mysql mysql 177 Jul 25 11:58 mysql-bin.000001
-rw-r----- 1 mysql mysql 201 Jul 25 12:17 mysql-bin.000002
-rw-r----- 1 mysql mysql 201 Jul 25 12:17 mysql-bin.000003
-rw-r----- 1 mysql mysql 201 Jul 25 12:17 mysql-bin.000004
-rw-r----- 1 mysql mysql 201 Jul 25 12:17 mysql-bin.000005
-rw-r----- 1 mysql mysql 154 Jul 25 12:17 mysql-bin.000006
-rw-r----- 1 mysql mysql 114 Jul 25 12:17 mysql-bin.index

　　2.2修改系统时间为3天之后

[root@testvm data]# date -s "2018-07-28 12:15:00"
Sat Jul 28 12:15:00 CST 2018
[root@testvm data]# clock -w

　　2.3执行flush logs刷新日志

[root@testvm data]# ls -ltr mysql-bin.*
-rw-r----- 1 mysql mysql 201 Jul 25 12:17 mysql-bin.000002   #1这个日志已经被删除了.
-rw-r----- 1 mysql mysql 201 Jul 25 12:17 mysql-bin.000003
-rw-r----- 1 mysql mysql 201 Jul 25 12:17 mysql-bin.000004
-rw-r----- 1 mysql mysql 201 Jul 25 12:17 mysql-bin.000005
-rw-r----- 1 mysql mysql 201 Jul 28 12:15 mysql-bin.000006
-rw-r----- 1 mysql mysql 154 Jul 28 12:15 mysql-bin.000007
-rw-r----- 1 mysql mysql 114 Jul 28 12:15 mysql-bin.index

　　2.4查看文件时间,执行flush logs操作

[root@testvm data]# stat mysql-bin.000002
  File: `mysql-bin.000002'
  Size: 201       	Blocks: 8          IO Block: 4096   regular file
Device: fd00h/64768d	Inode: 276736      Links: 1
Access: (0640/-rw-r-----)  Uid: (  502/   mysql)   Gid: (  502/   mysql)
Access: 2018-07-28 12:15:31.539000285 +0800
Modify: 2018-07-25 12:17:39.528999883 +0800
Change: 2018-07-25 12:17:39.528999883 +0800
[root@testvm data]# ls -ltr mysql-bin.*
-rw-r----- 1 mysql mysql 201 Jul 25 12:17 mysql-bin.000002
-rw-r----- 1 mysql mysql 201 Jul 25 12:17 mysql-bin.000003
-rw-r----- 1 mysql mysql 201 Jul 25 12:17 mysql-bin.000004
-rw-r----- 1 mysql mysql 201 Jul 25 12:17 mysql-bin.000005
-rw-r----- 1 mysql mysql 201 Jul 28 12:15 mysql-bin.000006
-rw-r----- 1 mysql mysql 201 Jul 28 12:17 mysql-bin.000007
-rw-r----- 1 mysql mysql 154 Jul 28 12:17 mysql-bin.000008   #这个生成时间减去标记为黄色的时间必须大于3天,黄色对应的文件才会被删除.
-rw-r----- 1 mysql mysql 133 Jul 28 12:17 mysql-bin.index
[root@testvm data]# date
Sat Jul 28 12:18:08 CST 2018
[root@testvm data]# ls -ltr mysql-bin.*
-rw-r----- 1 mysql mysql 201 Jul 28 12:15 mysql-bin.000006
-rw-r----- 1 mysql mysql 201 Jul 28 12:17 mysql-bin.000007
-rw-r----- 1 mysql mysql 201 Jul 28 12:18 mysql-bin.000008
-rw-r----- 1 mysql mysql 154 Jul 28 12:18 mysql-bin.000009    #18分执行的时候,3天前的17分的文件就都被删除了.
-rw-r----- 1 mysql mysql  76 Jul 28 12:18 mysql-bin.index

备注:经过测试,就是,当刷新日志时(写满日志文件或者手动执行flush logs操作),在这个时间3天之前的binlog文件都会被删除.即执行刷新日志时,减去3天,binlog的时间小于这个的都会被删除.同时呢,当系统启动的时候也会执行flush logs操作,也会触发这个删除binlog的动作.

3.通过puge binary logs命令来进行删除

　　3.1查看当前binlog的信息

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |       201 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |       201 |
| mysql-bin.000010 |       201 |
| mysql-bin.000011 |       201 |
| mysql-bin.000012 |       201 |
| mysql-bin.000013 |       201 |
| mysql-bin.000014 |       201 |
| mysql-bin.000015 |       201 |
| mysql-bin.000016 |       201 |
| mysql-bin.000017 |       201 |
| mysql-bin.000018 |       201 |
| mysql-bin.000019 |       201 |
| mysql-bin.000020 |       201 |
| mysql-bin.000021 |       154 |
+------------------+-----------+
16 rows in set (0.00 sec)

　　3.2purge删除binlog

mysql> purge binary logs to 'mysql-bin.000017';
Query OK, 0 rows affected (0.01 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000017 |       201 |
| mysql-bin.000018 |       201 |
| mysql-bin.000019 |       201 |
| mysql-bin.000020 |       201 |
| mysql-bin.000021 |       154 |
+------------------+-----------+
5 rows in set (0.00 sec)

备注:通过查询结果可以知道,在17之前的日志都被清除了.不包括17本身.切记!

4.切记不能在操作系统上直接删除binlog文件,虽然释放了空间,但是在Index中,还是有记录.

[root@testvm data]# ls -ltr mysql-bin.*
-rw-r----- 1 mysql mysql 201 Jul 28 12:42 mysql-bin.000017
-rw-r----- 1 mysql mysql 201 Jul 28 12:42 mysql-bin.000018
-rw-r----- 1 mysql mysql 201 Jul 28 12:42 mysql-bin.000019
-rw-r----- 1 mysql mysql 201 Jul 28 12:42 mysql-bin.000020
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000021
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000022
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000023
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000024
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000025
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000026
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000027
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000028
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000029
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000030
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000031
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000032
-rw-r----- 1 mysql mysql 154 Jul 28 12:45 mysql-bin.000033
-rw-r----- 1 mysql mysql 323 Jul 28 12:45 mysql-bin.index
[root@testvm data]# rm -f mysql-bin.000017 mysql-bin.000018 mysql-bin.000019 mysql-bin.000020
[root@testvm data]# ls -ltr mysql-bin.*
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000021
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000022
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000023
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000024
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000025
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000026
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000027
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000028
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000029
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000030
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000031
-rw-r----- 1 mysql mysql 201 Jul 28 12:45 mysql-bin.000032
-rw-r----- 1 mysql mysql 154 Jul 28 12:45 mysql-bin.000033
-rw-r----- 1 mysql mysql 323 Jul 28 12:45 mysql-bin.index

在mysql中查询binlog信息

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000017 |         0 |
| mysql-bin.000018 |         0 |
| mysql-bin.000019 |         0 |
| mysql-bin.000020 |         0 |
| mysql-bin.000021 |       201 |
| mysql-bin.000022 |       201 |
| mysql-bin.000023 |       201 |
| mysql-bin.000024 |       201 |
| mysql-bin.000025 |       201 |
| mysql-bin.000026 |       201 |
| mysql-bin.000027 |       201 |
| mysql-bin.000028 |       201 |
| mysql-bin.000029 |       201 |
| mysql-bin.000030 |       201 |
| mysql-bin.000031 |       201 |
| mysql-bin.000032 |       201 |
| mysql-bin.000033 |       154 |
+------------------+-----------+
17 rows in set (0.00 sec)

备注:还记录了文件的名字,但是文件的大小是0byte.

查看index中的记录信息

[root@testvm data]# cat mysql-bin.index 
./mysql-bin.000017
./mysql-bin.000018
./mysql-bin.000019
./mysql-bin.000020
./mysql-bin.000021
./mysql-bin.000022
./mysql-bin.000023
./mysql-bin.000024
./mysql-bin.000025
./mysql-bin.000026
./mysql-bin.000027
./mysql-bin.000028
./mysql-bin.000029
./mysql-bin.000030
./mysql-bin.000031
./mysql-bin.000032
./mysql-bin.000033
[root@testvm data]#

备注:index中还是记录该信息,认为还是存在的.

重新启动的时候,会检查文件是否存在,如果不在会报错:

mysqld: File './mysql-bin.000017' not found (Errcode: 2 - No such file or directory)
2018-07-28T04:49:02.619409Z 0 [ERROR] Failed to open log (file './mysql-bin.000017', errno 2)
2018-07-28T04:49:02.619416Z 0 [ERROR] Could not open log file
mysqld: File './mysql-bin.000018' not found (Errcode: 2 - No such file or directory)
2018-07-28T04:49:02.619426Z 0 [ERROR] Failed to open log (file './mysql-bin.000018', errno 2)
2018-07-28T04:49:02.619428Z 0 [ERROR] Could not open log file
mysqld: File './mysql-bin.000019' not found (Errcode: 2 - No such file or directory)
2018-07-28T04:49:02.619436Z 0 [ERROR] Failed to open log (file './mysql-bin.000019', errno 2)
2018-07-28T04:49:02.619438Z 0 [ERROR] Could not open log file
mysqld: File './mysql-bin.000020' not found (Errcode: 2 - No such file or directory)
2018-07-28T04:49:02.619445Z 0 [ERROR] Failed to open log (file './mysql-bin.000020', errno 2)
2018-07-28T04:49:02.619447Z 0 [ERROR] Could not open log file

备注:遇到这种问题,直接通过purge binary logs删除就行了.同时也会更新index文件

切记!!不要从操作系统上直接删除binlog!!!

文档创建时间:2018年7月19日16:02:00

转载于:https://www.cnblogs.com/chuanzhang053/p/9336386.html