Mysql deletes binlog logs and restores data.

The binlog function is enabled for mysql in the home decoration. The advantage of enabling this function is that it facilitates data recovery in the future, but the disadvantage is that log files are growing rapidly and will soon occupy the disk space. Therefore, we need to regularly or manually delete large log files. The methods for deleting log files are as follows: 1. Set the Log retention period (expire_lo ).

The binlog function is enabled for mysql in the home decoration. The advantage of enabling this function is that it facilitates data recovery in the future, but the disadvantage is that log files are growing rapidly and will soon occupy the disk space. Therefore, we need to regularly or manually delete large log files. The methods for deleting log files are as follows: 1. Set the Log retention period (expire_lo ).

The binlog function is enabled for mysql in the home decoration. The advantage of enabling this function is that it facilitates data recovery in the future, but the disadvantage is that log files are growing rapidly and will soon occupy the disk space.
Therefore, we need to regularly or manually delete large log files.
The methods for deleting log files are as follows:

I,Set Log Retention PeriodExpire_logs_daysAutomatic deletion

#View the number of days for saving the current log:Show? Variables? Like? '% Expire_logs_days ';;#The default value is0, That isLogsIf it does not expire, you can set global parameters to make it take effect temporarily:Set?GlobalExpire_logs_days = 7;#Set to retain only7DaysBINLOG,Next restartMysqlThis parameter will fail by default, so you needMy. cnfSet inExpire_logs_days? = 7

Ii. manually delete BINLOG? (Purge? Binary? Logs)

Others:

#Deletes all binary logs listed in the log index before the specified log or date. These logs are also recorded in the log index file

PURGE? {MASTER? |? BINARY }? LOGS? TO ?" Log_name"

PURGE? {MASTER? |? BINARY }? LOGS? BEFORE ?" Date "#For example:

PURGE? MASTER? LOGS? TO ?" Mysql-bin.010 '; PURGE? MASTER? LOGS? BEFORE ?" 2008-06-22:00 ′;

PURGE? MASTER? LOGS? BEFORE? DATE_SUB (? NOW (),? INTERVAL? 3? DAY );

#View the list of current log files:Show? Binary? Logs;

The purpose of using binlog logs is to facilitate data recovery, so we should not only delete the logs, but also use logs to restore data.

UseBinlogRecover data:

Use the mysqlbinlog command to restore logs

Mysqlbinlog? -D? Test? /Root/mysql/mysql-bin.000001 | mysql-uroot? -Ppassword

※Appendix

#1AboutMysqlbinlogInMysql? 5.0For more information, see: official materials.#2If you do not knowLog-binYou can use the following command to view the file location:Mysql-uroot? -Ppassword? -E? 'Show? BINLOG? EVENTS? \ G'#3When you only need to restore some records, you can use the time point or location point to locate them. For example:Mysqlbinlog? -Start-date = "2005-04-20? 9:01:00 "-stop-date =" 2005-04-20? 10:00:01 ″/Var/Log/mysql/bin.123456 | mysql-uroot? -Ppassword

#Indicates restoration.4Month20Morning9Point10Click the data of this period of time.Mysqlbinlog? -Start-position = "368301"-stop-position = "368312 ″/Var/Log/mysql/bin.123456 | mysql-uroot? -Pmypwd

#Indicates redo.368301To368312Operations between locations. (Location points can be used onlyMysqlbinlogSeeAt? XxxNode)Mysqlbinlog? -Start-date = "2005-04-20? 9:55:00 "-stop-date =" 2005-04-20? 10:05:00 ″/Var/Log/mysql/bin.123456?> /Tmp/mysql_restore. SQL

#Similarly, if we just want to view the operation for a certain period of time, just export it to a file.

Original article address: mysql deletes binlog logs and restores data. Thank you for sharing it with the original author.