mysql关闭与删除bin-log日志详解

mysql中如果我们开启bin-log日志全在mysql目录发现大量的mysql-bin.000001,mysql-bin.000002等这些文章,如果多了会发现占很大的空间,下面我来介绍mysql-bin日志关闭与删除方法.

关闭bin-log日志:

当开启bin-log日志时,会生成很多mysql-bin.0000*类似的文件,而且可能会占用很大的硬盘空间,对于自己的测试机器或硬盘比较紧张的vps,而又不需要做master,slave的配置,完全可以关闭日志功能.

关闭操作很简单,找到配置文件my.cnf,对于linux,一般默认在/etc目录下,打开此文件,使用井号(#)注释掉如下两个配置项目即可,代码如下:

log-bin=mysql-bin

binlog_format=mixed

例,vi /etc/my.cnf 或my.cnf的路径,把其中的log-bin=mysql-bin修改为#log-bin=mysql-bin,重启mysql即可生效.

配置修改好后需要重启mysqld服务才能生效,可能在重启时候会收到一个错误,“ERROR 1186 (HY000): Binlog closed,cannot RESET MASTER”,解决办法是先登入到mysql命令行中执行 reset master 即可。reset master的用途就是删除先前所以的bin-log日志文件。所以在master、slave配置环境中慎用此命令。

删除bin-log日志

1.查找当前有哪些二进制日志文件,代码如下:

1. mysql>showbinarylogs;
2. +------------------+-----------+
3. |Log_name|File_size|
4. +------------------+-----------+
5. |mysql-bin.000001|1357315|
6. |mysql-bin.000002|117|
7. |mysql-bin.000003|404002|
8. |mysql-bin.000004|2050722|
9. |mysql-bin.000005|139103|
10. |mysql-bin.000006|46702|
11. |mysql-bin.000007|117|
12. |mysql-bin.000008|98|
13. |mysql-bin.000009|117|
14. |mysql-bin.000010|1254|
15. |mysql-bin.000011|117|
16. |mysql-bin.000012|29394942|
17. |mysql-bin.000013|422100|
18. |mysql-bin.000014|117|
19. |mysql-bin.000015|117|
20. |mysql-bin.000016|98|
21. |mysql-bin.000017|117|
22. |mysql-bin.000018|117|
23. |mysql-bin.000019|285300|
24. |mysql-bin.000020|181229|
25. |mysql-bin.000021|98|
26. +------------------+-----------+
27. 21rowsinset(0.03sec)

2.删除bin-log(删除mysql-bin.000018之前的所有二进制日志文件),代码如下:

1. mysql>purgebinarylogsto'mysql-bin.000018';
2. QueryOK,0rowsaffected(0.08sec)
3. mysql>showbinarylogs;
4. +------------------+-----------+
5. |Log_name|File_size|
6. +------------------+-----------+
7. |mysql-bin.000018|117|
8. |mysql-bin.000019|285300|
9. |mysql-bin.000020|181229|
10. |mysql-bin.000021|98|
11. +------------------+-----------+
12. 4rowsinset(0.00sec)
13. mysql>showbinlogevents;
14. +------------------+-----+-------------+-----------+-------------+---------------------------------------+
15. |Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
16. +------------------+-----+-------------+-----------+-------------+---------------------------------------+
17. |mysql-bin.000018|4|Format_desc|1|98|Serverver:5.0.45-log,Binlogver:4|
18. |mysql-bin.000018|98|Stop|1|117||--phpfensi.com
19. +------------------+-----+-------------+-----------+-------------+---------------------------------------+
20. 2rowsinset(0.01sec)