MySQL Binlog log automatic cleanup and manual deletion

Transferred from: http://blog.csdn.net/atco/article/details/24259333

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 replication
# 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 10
Expire_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 logs 10 days ago, Appendix 2 for manual removal of purge MASTER logs usage and examples
> 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 a clear binlog when the effect on MySQL from the description

Appendix:
1.expire_logs_days English explanation
Where X is the number of days of your ' d ' like to keep them around. I would recommend, but this depends on how busy your MySQL server are and how fast these log files grow. Just Make sure it was longer than the slowest slave takes to replicate the data from your master.
Just A side note:you know that's you should does this anyway, and make sure your back up your MySQL database. The binary log can be used to recover the database in certain situations; So have a backup ensures that if your database server does crash and you'll be able to recover the data.

2.PURGE Master logs manual removal usage and examples, master and binary are synonyms
> 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); Clear 3 days ago Binlog log before, variable date argument can be ' 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.

Original reference:
My MySQL Binary Log files is taking up all my disk space!
Automatically cleans MySQL binlog logs with manually deleted settings

MySQL Binlog log automatic cleanup and manual deletion