MySQL Binlog log automatic cleanup and manual deletion

Description
When you turn on MySQL database master and slave, there will be a lot of files such as mysql-bin.00000* log, which will consume your hard disk space.
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
...
There are three solutions: 1. Turn off MySQL master and slave, turn off binlog;2. Turn on MySQL master and slave, set expire_logs_days;3. Manually clear Binlog files,> PURGE MASTER logs to ' mysql-bin.010 ′;

Realize:
1. Turn off MySQL master and slave, close Binlog

# VIM/ETC/MY.CNF//Comment out log-bin,binlog_format# Replication Master Server (default) # Binary logging is required for replica tion# log-bin=mysql-bin# binary Logging format-mixed recommended# binlog_format=mixed

Then restart the database

2. Restart MySQL, turn on MySQL master and slave, set expire_logs_days

# VIM/ETC/MY.CNF//Modify Expire_logs_days,x is the number of days automatically deleted, typically set X to a short point, such as 10expire_logs_days = x//The number of days the binary log is automatically deleted. The default value is 0, which means "No automatic deletion"

This method requires a restart of MySQL, appendix for the English instructions for Expire_logs_days

Of course, you can not restart MySQL, turn on MySQL master, directly in MySQL set expire_logs_days

> Show binary logs;> show variables like '%log% ';> set global expire_logs_days = 10;

3. Manually clear the Binlog file

#/usr/local/mysql/bin/mysql-u root-p> PURGE MASTER LOGS before Date_sub (current_date, INTERVAL Day); Delete MySQL binlog log 10 days ago, Appendix 2 for manual removal of purge master logs usage and example > show MASTER logs;

You can also reset master to delete all Binlog files:

# /usr/local/mysql/bin/mysql -u root -p> reset master;  // Appendix 3 has an explanation of the impact from MySQL when clearing Binlog 

> PURGE {MASTER | BINARY} LOGS to ' Log_name ' > PURGE {MASTER | BINARY} LOGS before ' date '

Deletes all binary logs in the log index before the specified log or date. These logs will also be deleted from the list in the log index file to the MySQL Bin-log log, so that the given log becomes the first one.

Instance:

> purge master logs to  ' mysql-bin.010 ';   //clear MySQL-bin.010 Log >  purge master logs before  ' 2008-06-22 13:00:00 ';    //clear 2008-06-22  13:00:00 before Binlog log > purge master logs before date_sub ( now ( ),  Interval 3 day);   //cleared 3 days ago Binlog log before, the date argument of a variable can be in the ' yyyy-mm-dd hh:mm:ss ' format. 

3. When Binlog is cleared, the effect on MySQL
If you have an active secondary server that is currently reading one of the logs that you are trying to delete, this statement will not work, but will fail with an error. However, if the secondary server is inactive and you happen to have cleaned one of the logs that it wants to read, the secondary server cannot replicate after it is started. This statement is safe to run when the secondary server is replicating. You do not need to stop them.

This article is from the "Intelligent Future _XFICC" blog, please be sure to keep this source http://xficc.blog.51cto.com/1189288/1616693

MySQL Binlog log automatic cleanup and manual deletion