MySQL Binlog log automatic cleanup and manual deletion

Source: Internet
Author: User
Tags log log

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 off 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  The number of days that x//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;  >'%log% ';  >Setglobal=ten;          


3. Manually clear the Binlog file

#/usr/local/mysql/bin/mysql-u root-p> PURGE MASTER LOGS before Date_sub(current_date,  (Ten 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 clear Binlog when it comes to the impact from MySQL description        

 
Appendix:
1.expire_logs_days English description
Where X is the number of days you ' 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

>{|  BINARY}' Log_name '>{|  BINARY}' 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 > ( Now ( Span class= "pun"),  INTERVAL 3 Day //cleared 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.

MySQL Binlog log automatic cleanup and manual deletion

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.