删除MySQL二进制日志命令与例子详解

下面我总结了四种删除mysql二进制日志的方法,分别为reset master PURGE 设置日志过期expire_log_day及linux中的rm命令来删除,具体操作方法都有例子说明有兴趣的可参考一下.

方法一,删除全部二进制日志:

在mysql上执行reset master命令,那么就清除了所有的mysql-bin.*日志,并且以后日志文件名从mysql-bin.000001开始.

reset master

删除部分日志,代码如下:

PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE

PURGE MASTER LOGS TO 'mysql-bin.******'命令，是将'******'编号之前的所有日志

进行删除.

PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令，是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除

方法二,或者使用PURGE

PURGE {MASTER | BINARY} LOGS TO ‘log_name’

PURGE {MASTER | BINARY} LOG

查看日志,代码如下:

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)

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

1. mysql>purgebinarylogsto'mysql-bin.000018';
2. QueryOK,0rowsaffected(0.08sec)
4. mysql>showbinarylogs;
5. +------------------+-----------+
6. |Log_name|File_size|
7. +------------------+-----------+
8. |mysql-bin.000018|117|
9. |mysql-bin.000019|285300|
10. |mysql-bin.000020|181229|
11. |mysql-bin.000021|98|
12. +------------------+-----------+
13. 4rowsinset(0.00sec)

查看日志,代码如下:

1. mysql>showbinlogevents;
2. +------------------+-----+-------------+-----------+-------------+---------------------------------------+
3. |Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
4. +------------------+-----+-------------+-----------+-www.111cn.net------------+---------------------------------------+
5. |mysql-bin.000018|4|Format_desc|1|98|Serverver:5.0.45-log,Binlogver:4|
6. |mysql-bin.000018|98|Stop|1|117||
7. +------------------+-----+-------------+-----------+-------------+---------------------------------------+
8. 2rowsinset(0.01sec) //phpfensi.com

方法三,设置日志过期时间,修改my.cnf,代码如下:expire_log_day=5,这里设置保存5天的日志,超过5天的日志会被自动删除.

方法四,如果是linux系统可以使用rm删除,删除二进制日志,启动服务,代码如下:

# c -rf /mybinlog/*

# netstat -tnlp 查看启动的mysql进程号

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2523/mysql

# kill 2523,注意,如果在备份之前mysql服务是开启的,重启是不成功的,需要把这个进程先kill掉.

# service mysqld restart