MySQL bin-log功能，bin-log清理及expire-logs-days参数详解

今天突然发现博客不能访问了，登录vps后发现磁盘/根目录可用空间为0：

root@antphp:/:# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/MyDebianVPS-root
                      9.3G  8.8G   0    100% /
tmpfs                 504M     0  504M   0% /lib/init/rw
udev                  493M   76K  493M   1% /dev
tmpfs                 504M     0  504M   0% /dev/shm
/dev/xvda1            228M   18M  199M   8% /boot
/dev/xvdb1            9.9G  2.7G  6.7G  29% /opt
来源：未来往事 https://www.fity.cn

经过排查发现是mysql bin-log占用了几个GB的空间，对于普通vps来说空间本来就几个GB，所以自然而然也就满了。于是本文呢就在这样的情况产生啦 [emot]grin[/emot]

看了上面内容，你可能会疑惑MySQL bin-log功能究竟有何作用？

1：数据恢复：如果你的数据库出问题了，而你之前有过备份，那么可以看日志文件，找出是哪个命令导致你的数据库出问题了，想办法挽回损失。
2：主从服务器之间同步数据：主服务器上所有的操作都在记录日志中，从服务器可以根据该日志来进行，以确保两个同步。
简单地说,这些MySQL var目录下的形如mysql-bin.000***的文件是MySQL的事务日志。
PS：缺点是将无法使数据库恢复先前的状态

这里我们查看到mysql-bin log占用了至少大于5GB的空间：

root@antphp:/usr/local/mysql/var# du -sh *
108M    antphp.err
4.0K    antphp.pid
144K    blog@002eyuboya@002ecom
1.1M    mysql
28K     mysql-bin.000001
1.1M    mysql-bin.000002
4.0K    mysql-bin.000003
4.0K    mysql-bin.000004
2.7M    mysql-bin.000005
1.1G    mysql-bin.000006
1.1G    mysql-bin.000007
1.1G    mysql-bin.000008
1.1G    mysql-bin.000009
1.1G    mysql-bin.000010
………………………………
来源：未来往事 https://www.fity.cn

PS：命令行模式查看：mysql> show binary logs;

MySQL bin-log状态查看/管理

通过查看mysql配置文件我们可以看到该mysql服务的bin-log是开启的状态：

root@antphp:/# cat /etc/my.cnf | grep log-bin
log-bin=mysql-bin
来源：未来往事 https://www.fity.cn

PS：
1、这一行注释，重启mysql服务即可关闭bin日志的记录
2、命令行模式查看：mysql> show global variables like '%bin%';

MySQL bin-log如何删除清理？

如果没有主从复制，可以通过reset master的方式，重置数据库日志，清除之前的日志文件：

reset master;

但是如果存在复制关系，应当通过PURGE的方式来清理bin日志，语法如下：

PURGE {MASTER | BINARY} LOGS TO 'log_name'
PURGE {MASTER | BINARY} LOGS BEFORE 'date'
来源：未来往事 https://www.fity.cn

用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。

例如：

PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2014-08-30 00:00:00';  //清理该日期之前的数据
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);  //清除3天前的
来源：未来往事 https://www.fity.cn

实例：

mysql> PURGE MASTER LOGS BEFORE '2014-08-30 00:00:00';
Query OK, 0 rows affected (0.05 sec)
来源：未来往事 https://www.fity.cn

再次查看bin-log，可以看到在2014年8月30日0点之前的bin-log已被清空(和上面列出的对比可以直观的看到 mysql-bin.000010之前的文件被清理了)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000013 |   2253251 |
| mysql-bin.000014 |    787801 |
| mysql-bin.000015 |     66744 |
| mysql-bin.000016 |     50939 |
| mysql-bin.000017 |    153046 |
| mysql-bin.000018 |     28678 |
| mysql-bin.000019 |   2123376 |
+------------------+-----------+
7 rows in set (0.00 sec)

再次查看磁盘空间使用情况：
root@antphp:/# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/MyDebianVPS-root
                      9.3G  3.4G  5.5G  38% /
tmpfs                 504M     0  504M   0% /lib/init/rw
udev                  493M   76K  493M   1% /dev
tmpfs                 504M     0  504M   0% /dev/shm
/dev/xvda1            228M   18M  199M   8% /boot
/dev/xvdb1            9.9G  2.7G  6.7G  29% /opt
来源：未来往事 https://www.fity.cn

MySQL bin-log相关参数：expire_logs_days

参数：expire_logs_days
说明：二进制日志自动删除/过期的天数。默认值为0,表示“没有自动删除”
例如：expire_logs_days = 5 //表示日志保留5天,超过5天则设置为过期的

PS：关于过期日志自动删除补充：
Q：在什么时间会删除过期日志？
A：每次进行 LOG flush的时会自动删除过期的日志。

Q：什么时间才能触发log flush，手册上的解释为：
A：1. 重启；
　   2. BINLOG文件大小达到参数max_binlog_size限制；
　   3. 手工执行命令。

有兴趣的小伙伴可以自行搜索下利用 MySQL bin-log 恢复数据表。

相关文章

• mongodb与mysql优缺点比较
• mysql replace
• sql查询过滤手机号码数据 mysql
• SQL去重统计
• SQL查询重复数据并列出重复数据