Shulou(Shulou.com)05/31 Report--

This article mainly introduces the method of automatic cleaning and manual deletion of mysql binlog logs. In daily operation, I believe that many people have doubts about the methods of automatic cleaning and manual deletion of mysql binlog logs. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "automatic cleaning of mysql binlog logs and manual deletion". Next, please follow the editor to study!

Description:

When the master and slave of mysql database is opened, a large number of files such as mysql-bin.00000* log will be generated, which will consume a lot of your hard disk space.

Mysql-bin.000001

Mysql-bin.000002

Mysql-bin.000003

Mysql-bin.000004

Mysql-bin.000005

...

There are three solutions:

1. Close mysql master and slave, close binlog

two。 Enable mysql master and slave and set expire_logs_days

3. Manually clear the binlog file, all before > PURGE MASTER LOGS TO 'MySQL-bin.010';-' MySQL-bin.010' are cleared

Achieve:

1. Close mysql master and slave, close binlog

MySQL installed by LNMP one-click installation package opens log files by default. If data operations are more frequent, a large number of logs will be generated. Mysql-bin.0000*-like files will be generated under / usr/local/mysql / var/, and they will generally be dozens of MB to several GB, and even eat up the entire hard disk space, never causing mysql to fail to start or report errors, such as feedback from vps Forum users.

How to turn off the logging function of MySQL:

Delete the log:

Execute: / usr/local/mysql/bin/mysql-u root-p

Enter the password to log in and then execute: reset master

Modify the / etc/my.cnf file to find

Log-bin=mysql-bin

Binlog_format=mixed

Precede these two lines with #, comment them out, and then execute / etc/init.d/mysql restart.

two。 Restart mysql, enable mysql master and slave, and set expire_logs_days

# vim / etc/my.cnf / / modify the number of days for expire_logs_days,x to be deleted automatically. Generally, set x to a short point, such as 10

Expire_logs_days = the number of days that the x / / binary log is automatically deleted. The default value is 0, which means "no automatic deletion"

This method requires a restart of mysql, with an English description of expire_logs_days in the appendix

Of course, you can not restart mysql, turn on mysql master and slave, and directly set expire_logs_days in mysql.

> 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 10 DAY); / / Delete MySQL binlog logs from 10 days ago. Appendix 2 shows the usage and examples of manual deletion of PURGE MASTER LOGS.

> 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 explains the impact of clearing binlog on slave mysql

Appendix:

English description of 1.expire_logs_days

Where X is the number of days you'd like to keep them around. I would recommend 10, but this depends on how busy your MySQL server is and how fast these log files grow. Just make sure it is longer than the slowest slave takes to replicate the data from your master.

Just a side note: You know that you should do this anyway, but make sure you back up your mysql database. The binary log can be used to recover the database in certain situations; so having a backup ensures that if your database server does crash, you will be able to recover the data.

2.PURGE MASTER LOGS manually deletes 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 specified log or log index before the date. These logs are also deleted from the list recorded in the log index file, so that the given log becomes the first.

Example:

> PURGE MASTER LOGS TO 'MySQL-bin.010'; / / clear the MySQL-bin.010 log

> PURGE MASTER LOGS BEFORE '2008-06-22 1300 PURGE MASTER LOGS BEFORE; / / clear the binlog log before 2008-06-22 before 13:00:00

> PURGE MASTER LOGS BEFORE DATE_SUB (NOW (), INTERVAL 3 DAY); / / clear the BEFORE of the binlog log 3 days ago. The date argument of the variable can be in 'YYYY-MM-DD hh:mm:ss' format.

3. Impact on slave mysql when clearing binlog

If you have an active secondary server that is currently reading one of the logs you are trying to delete, this statement will not work but will fail with an error. However, if the secondary server is resting and you happen to clean up one of the logs it wants to read, the secondary server cannot be replicated when it starts. This statement runs safely when the secondary server is replicating. You don't need to stop them.

At this point, the study of "automatic cleaning of mysql binlog logs and manual deletion" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.