DBA之binlog文件管理

binlog文件管理

mysql binlog文件管理包括如下：

1 设置过期时间

mysql>set global expire_logs_days=1;

设置1天之前的binlog过期

2 查看binlog文件

mysql>>show binary logs; 

3 手动清理binlog文件

dwbdba_mgr@127.0.0.1:3316 [(none)]>help purge;
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }

The binary log is a set of files that contain information about data
modifications made by the MySQL server. The log consists of a set of
binary log files, plus an index file (see
http://dev.mysql.com/doc/refman/5.5/en/binary-log.html).

The PURGE BINARY LOGS statement deletes all the binary log files listed
in the log index file prior to the specified log file name or date.
BINARY and MASTER are synonyms. Deleted log files also are removed from
the list recorded in the index file, so that the given log file becomes
the first in the list.

This statement has no effect if the server was not started with the
--log-bin option to enable binary logging.

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

Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

如：

dwbdba_mgr@127.0.0.1:3316 [(none)]>PURGE BINARY LOGS TO     '3316-binlog.000087';           

 

3316-binlog.000087文件之前的binlog全部清理，3316-binlog.000087不会被清理

 

注意：不要直接删除磁盘上的binlog文件

4 开启binlog

主库必须开启binlog，过期时间一般设置为10天

从库可以考虑关闭binlog，或者设置为1天