删除轮换的 MySQL 二进制日志是否安全？

Is it safe to delete rotated MySQL binary logs?

提问by Milan Babu?kov

I have a MySQL server with binary logging active. Once a day logs file is "rotated", i.e. MySQL seems to stop writing to it and creates and new log file. For example, I currently have these files in /var/lib/mysql

我有一个启用二进制日志记录的 MySQL 服务器。一天日志文件被“轮换”一次，即 MySQL 似乎停止写入并创建新的日志文件。例如，我目前在 /var/lib/mysql 中有这些文件

-rw-rw---- 1 mysql mysql 10485760 Jun  7 09:26 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jun  7 09:26 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jun  2 15:20 ib_logfile1
-rw-rw---- 1 mysql mysql  1916844 Jun  6 09:20 mybinlog.000004
-rw-rw---- 1 mysql mysql 61112500 Jun  7 09:26 mybinlog.000005
-rw-rw---- 1 mysql mysql 15609789 Jun  7 13:57 mybinlog.000006
-rw-rw---- 1 mysql mysql       54 Jun  7 09:26 mybinlog.index

and mybinlog.000006 is growing.

并且 mybinlog.000006 正在增长。

Can I simply take mybinlog.000004 and mybinlog.000005, zip them up and transfer to another server, or I need to do something else before?

我可以简单地将 mybinlog.000004 和 mybinlog.000005 压缩并传输到另一台服务器，或者我之前需要做其他事情吗？

What info is stored in mybinlog.index? Only the info about the latest binary log?

mybinlog.index 中存储了哪些信息？只有有关最新二进制日志的信息？

UPDATE:I understand I can delete the logs with PURGE BINARY LOGS which updates mybinlog.index file. However, I need to transfer logs to another computer before deleting them (I test if backup is valid on another machine). To reduce the transfer size, I wish to bzip2 the files. What will PURGE BINARY LOGS do if log files are not "there" anymore?

更新：我知道我可以使用更新 mybinlog.index 文件的 PURGE BINARY LOGS 删除日志。但是，我需要在删除日志之前将日志传输到另一台计算机（我测试备份在另一台机器上是否有效）。为了减少传输大小，我希望 bzip2 文件。如果日志文件不再“存在”，那么 PURGE BINARY LOGS 会做什么？

采纳答案by Milan Babu?kov

I finally found the answer on MySQL website. In case somebody needs this information:

我终于在 MySQL 网站上找到了答案。如果有人需要这些信息：

Prior to MySQL 5.0.60, PURGE BINARY LOGS TO and PURGE BINARY LOGS BEFORE did not behave in the same way (and neither one behaved correctly) when binary log files listed in the .index file had been removed from the system by some other means (such as using rm on Linux). Beginning with MySQL 5.0.60, both variants of the statement fail with an error in such cases. (Bug#18199, Bug#18453) To handle such errors, edit the .index file (which is a simple text file) manually to ensure that it lists only the binary log files that are actually present, then run again the PURGE BINARY LOGS statement that failed.

在 MySQL 5.0.60 之前，当 .index 文件中列出的二进制日志文件已通过其他方式从系统中删除时，PURGE BINARY LOGS TO 和 PURGE BINARY LOGS BEFORE 的行为方式不同（并且两者都没有正确行为） （例如在 Linux 上使用 rm）。从 MySQL 5.0.60 开始，在这种情况下，语句的两种变体都会失败并显示错误。(Bug#18199, Bug#18453) 要处理此类错误，请手动编辑 .index 文件（这是一个简单的文本文件）以确保它仅列出实际存在的二进制日志文件，然后再次运行 PURGE BINARY LOGS失败的声明。

This means I should edit .index file manually and everything will be fine. What's interesting is that .index file is a regular textual file. I didn't even notice that until now.

这意味着我应该手动编辑 .index 文件，一切都会好起来的。有趣的是 .index 文件是一个普通的文本文件。直到现在我才注意到这一点。

回答by titanoboa

You can delete old binary logs. Instead of deleting them directly, it is safer to use the MySQL-statement PURGE BINARY LOGSwhich also updates your mybinlog.indexfile. This file stores which filenames have been used for binary logging, see

您可以删除旧的二进制日志。与其直接删除它们，不如使用PURGE BINARY LOGS同样更新mybinlog.index文件的 MySQL 语句更安全。此文件存储哪些文件名已用于二进制日志记录，请参阅

http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html

http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html

Further, you can configure your MySQL-Server to delete old binary logs automatically. Set the variables max_binlog_sizeand expire_logs_daysin your server configuration to appropriate values.

此外，您可以将 MySQL-Server 配置为自动删除旧的二进制日志。在您的服务器配置中将变量max_binlog_size和设置expire_logs_days为适当的值。

The ibdataand ib_logfilefiles have nothing to do with binary logging. They are used by the innodb storage engine. Do not be mistaken by the fact that they do not seem to grow: If you have innodb-tables on your server, these files are important and deleting them may result in loss of data. You can read more about InnoDB in the docs:

在ibdata与ib_logfile文件无关二进制日志功能。它们由 innodb 存储引擎使用。不要误以为它们似乎没有增长：如果您的服务器上有 innodb 表，这些文件很重要，删除它们可能会导致数据丢失。您可以在文档中阅读有关 InnoDB 的更多信息：

http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html

回答by zloctb

mysql> PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY; 

DELETE ALL bin files before 3 DAYS!

在 3 天前删除所有 bin 文件！

or

或者

PURGE MASTER LOGS BEFORE '2010-10-08 00:00:00';

回答by Prasanna k Ram

mysql-bin.index usually carry all the .bin files. If u have removed some files pls edit the .index to reflect what are all files available. If u have removed all .bin files remove empty the .index file. This will solve u r problem.

mysql-bin.index 通常携带所有 .bin 文件。如果您删除了一些文件，请编辑 .index 以反映所有可用文件的内容。如果您删除了所有 .bin 文件，请删除空的 .index 文件。这将解决您的问题。