MySQL cleanup tips for binary logs

Transferred from: http://blog.csdn.net/cdefg198/article/details/7063524

1: Binary Log

The binary log records all DDL (data definition Language) statements and DML (data manipulation language) statements, but does not record statements that include data queries. Statements are saved as "events," which describe the process of changing the data, which plays an extremely important role in recovering data during a disaster.

2: Location and format of the log

When started with the-log-bin[=file_name] option, Mysqld writes the SQL command that contains all the updated data to the log file. If the file_name value is not given, the default name is the hostname followed by _bin, if the file name is given, but the path is not included, the files are written by default to the directory specified by the parameter DataDir (data directory)

3: Read the log

Since the log is stored in binary mode and cannot be read directly, it needs to be viewed with the Mysqlbinlog tool, with the following syntax:

#mysqlbinlog Log_file

4: Deletion of logs

For more busy OLTP systems, because of the large daily production log, these logs can be a waste of disk space if they are not cleaned up for a long time, so regular deletion of logs is an important part of the DBA's maintenance of the MySQL database, and several common ways to delete logs are described below.

(1):

Perform "Reset Master;" command, the command will delete all binary logs, the new log number starts with "000001" and the command is as follows

Mysql>reset master;

(2):

Execute the "Purge Master logs to ' mysql-bin.***** '" command, which will delete all logs before the "* * * *" number, and all logs numbered before "mysql-bin.000001" are removed in the following

Mysql>purge master logs to ' mysql-bin.000015;

Found in the results, all logs before number 000015 have been deleted

(3):

Execute the "purge master logs before ' yyyy-mm-dd hh24:min:ss '" command, which deletes all logs that were generated before the date "Yyyy-mm-dd Hh24:mi:ss", and the dates in the following are removed in the " 2010-05-22 01:00:00 "all logs before

Mysql>purge master logs before ' 2010-05-22 01:00:00 ';

(4):

Set the parameter-expire_logs_days=# (days), this parameter means to set the expiration date of the log, and the log will be automatically deleted after the specified number of days, which will help reduce the amount of DBA administration log.

#vi/etc/my.cnf

[Mysqld]

--expire_logs_days=3

In this way, 3 days before the log will be deleted, the system automatically deleted

MySQL cleanup tips for binary logs