MySQL binlog log deletion and limit size

Phenomenon: Web site access more and more slow, and finally inaccessible, after the check found that the disk is full. Careful inquiry down to confirm is because the binlog of MySQL too much occupied space.

Analysis process and solution: Usually this problem should be logged in to the server to check the disk, memory and process use, through the top, df–h and free–m to check, found that disk space is full. Further through the du–sh to the directory can be checked, found that the MySQL binlog occupy too much space. The way to clean the Binlog is as follows:

1 set log retention time long expire_logs_days automatic deletion
To view the current log save days:

The code is as follows	Copy Code
Show variables like '%expire_logs_days% ';

This default is 0, that is, logs, you can set the global parameters to enable him to take effect temporarily:
Set global expire_logs_days=7;
Set to keep only 7 days binlog, next restart mysql This parameter defaults will fail, so you need to set up in MY.CNF
Expire_logs_days = 7

2) manually delete binlog (purge binary logs)

Used to delete all binary logs that are listed in the log index before the specified log or date. These logs will also be recorded from the log index file

The code is as follows	Copy Code
PURGE {MASTER | BINARY} LOGS to ' Log_name ' PURGE {MASTER | BINARY} LOGS before ' date '

For example:

The code is as follows	Copy Code
PURGE MASTER LOGS to ' mysql-bin.010 '; PURGE MASTER LOGS before ' 2008-06-22 13:00:00 '; PURGE MASTER LOGS before Date_sub (now (), INTERVAL 3 day);

Add

Cleanup Log steps

1. Find log files

The code is as follows	Copy Code
Mysql> show binary logs; +----------------+-----------+ | Log_name | File_size | +----------------+-----------+ | ablelee.000001 | 150462942 | |       ablelee.000002 | 125 | |       ablelee.000003 | 106 | +----------------+-----------+

2. Delete Bin-log (before removing ablelee.000003 without ablelee.000003)

The code is as follows	Copy Code
Mysql> purge binary logs to ' ablelee.000003 '; Query OK, 0 rows affected (0.16 sec)

3. Query results (there is only one record now.)

The code is as follows

Copy Code

Mysql> Show Binlog EVENTSG 1. Row *************************** log_name:ablelee.000003 Pos:4 Event_type:format_desc Server_id:1 end_log_pos:106 Info:server Ver:5.1.26-rc-log, Binlog ver:4 1 row in Set (0.01 sec) (ABLELEE.000001 and ablelee.000002 have been deleted) Mysql> show binary logs; +----------------+-----------+ | Log_name | File_size | +----------------+-----------+ |       ablelee.000003 | 106 | +----------------+-----------+ 1 row in Set (0.00 sec)

(Use of other formatting removed!)

The code is as follows	Copy Code
PURGE {MASTER | BINARY} LOGS to ' Log_name ' PURGE {MASTER | BINARY} LOGS before ' date '

Used to delete all binary logs that are listed in the log index before the specified log or date. The logs are also deleted from the list in the log index file, which is the first of the given logs.
For example:

The code is as follows	Copy Code
PURGE MASTER LOGS to ' mysql-bin.010 '; PURGE MASTER LOGS before ' 2008-06-22 13:00:00 ';

Clear the Binlog 3 days ago.

The code is as follows	Copy Code
PURGE MASTER LOGS before Date_sub (now (), INTERVAL 3 day);

The date argument for the before variable can be in ' yyyy-mm-dd hh:mm:ss ' format. Master and binary are synonyms.
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 clean up 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.
To clean up the log, follow these steps:

1. On each subordinate server, use Show SLAVE status to check which log it is reading.
2. Use show master logs to obtain a series of logs on the primary server.
3. Determine the oldest log in all subordinate servers. This is the target log. If all the subordinate servers are updated, this is the last log on the list.

4. Make a backup of all the logs that you will delete. (This step is optional, but recommended.) ）

5. Clean up all the logs, but not the target log