需求描述:
在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 fileDevice: fd00h/64768d Inode: 276736 Links: 1Access: (0640/-rw-r-----) Uid: ( 502/ mysql) Gid: ( 502/ mysql)Access: 2018-07-28 12:15:31.539000285 +0800Modify: 2018-07-25 12:17:39.528999883 +0800Change: 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]# dateSat 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 filemysqld: 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 filemysqld: 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 filemysqld: 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