安全删除MySql二进制日志

安全删除MySql二进制日志

mysql>SHOW MASTER LOGS;

此命令显示目前二进制日志的数目。

然后mysql> PURGE MASTER LOGS TO ‘binary-log.xxx’;

除了命令中给出的日志之后的外，其他的二进制日志全部删除。

具体如下:PURGE MASTER LOGS PURGE { MASTER|BINARY } LOGS TO ‘log_name’PURGE { MASTER|BINARY } LOGS BEFORE ‘date’ Deletes all the binary logs listed in the log index that are strictly prior to the specified log or date. The logs also are removed from this list recorded in the log index file, so that the given log now becomes the first. Example: PURGE MASTER LOGS TO ‘mysql-bin.010′;

PURGE MASTER LOGS BEFORE ‘2003-04-02 22:46:26′;

 The BEFORE variant is available in MySQL 4.1;

 its date argument can be in ‘YYYY-MM-DD hh:mm:ss’ format. MASTER and BINARY are synonyms, though BINARY can be used only as of MySQL 4.1.1. If you have an active slave that is currently reading one of the logs you are trying to delete, this command does nothing and fails with an error. However, if you have a dormant slave, and happen to purge one of the logs it wants to read, the slave will be unable to replicate once it comes up. The command is safe to run while slaves are replicating. You do not need to stop them. You must first check all the slaves with SHOW SLAVE STATUS to see which log they are reading, then do a listing of the logs on the master with SHOW MASTER LOGS, find the earliest log among all the slaves (if all the slaves are up to date, this will be the last log on the list), backup all the logs you are about to delete (optional) and purge up to the target log.

MySQL二进制日志
   

MySQL二进制日志是以事务安全的方式包含更新日志中可用的所有信息。它包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。

运行服务器时若启用二进制日志则性能大约慢1%。但是，二进制日志的好处，即用于恢复并允许设置复制超过了这个小小的性能损失。

mysqld选项来控制二进制日志

--binlog-do-db=db_name

将db_name更新记录到二进制日志中

--binlog-ignore-db=db_name

不将db_name更新保存到二进制日志中

MySQL二进制日志占据硬盘空间。要想释放空间，应随时清空。操作方法是删掉不再使用的二进制日志，例如进行完全备份时：

shell> mysqldump --single-transaction --flush-logs --master-data=2

           --all-databases --delete-master-logs > backup_sunday_1_PM.sql

注释：如果你的服务器为复制主服务器，用mysqldump --delete-master-logs删掉MySQL二进制日志很危险，因为从服务器可能还没有完全处理该二进制日志的内容。