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_logs_days to automatically delete

 
 

  
  
# View the current Log Retention days:
  
  
 
  
  
Show variables like '% expire_logs_days % ';
  
  
 
  
  
# The default value is 0, indicating that logs does not expire. You can set a global parameter to make it take effect temporarily:
  
  
 
  
  
Set global expire_logs_days = 7;
  
  
 
  
  
# If BINLOG is set to be retained for only 7 days, the parameter "mysql restart" will fail by default. Therefore, you need to set it in my. cnf.
  
  
 
  
  
Expire_logs_days = 7
 
 

Ii. manually delete BINLOG (purge binary logs)

 
 

  
  
# Delete 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'
  
  
 
  
  
# Example:
  
  
 
  
  
Purge master logs to 'mysql-bin.010 ′;
  
  
Purge master logs before '2017-06-22 13:00:00 ′;
  
  
Purge master logs before DATE_SUB (NOW (), INTERVAL 3 DAY );
  
  
 
 
 

Others:

 
 

  
  
View the current log file list: 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.

Use binlog to restore data:

Use the mysqlbinlog command to restore logs

 
 

  
  
Mysql BINLOG-d test/root/mysql/mysql-bin.000001 | mysql-uroot-ppassword
  
  
 
  
  
※Appendix
  
  
 
  
  
#1. For how to use mysqlbinlog on mysql 5.0, refer to: official materials
  
  
 
  
  
#2. If you do not know the location of the files stored in log-bin, run the following command:
  
  
 
  
  
Mysql-uroot-ppassword-e 'show binlog events \ G'
  
  
 
  
  
#3. When you only need to restore some records, you can use the time point or location point to locate them. For example:
  
  
 
  
  
Mysqlbinlog -- start-date = "9:01:00" -- stop-date = "10:00:01"/var/log/mysql/bin.123456 | mysql-uroot-ppassword [code]
  
  
 
  
  
# It means to restore data from AM to AM on January 1, April 20.
  
  
 
  
  
Mysqlbinlog -- start-position = "368301" -- stop-position = "368312"/var/log/mysql/bin.123456 | mysql-uroot-pmypwd
  
  
 
  
  
# It means to redo the operation between 368301 and 368312. The location point can be the at xxx node displayed in mysqlbinlog)
  
  
 
  
  
Mysqlbinlog -- start-date = "9:55:00" -- stop-date = "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.
 
 

 

This article from the "small Cui's growth path" blog, please be sure to keep this source http://cyr520.blog.51cto.com/714067/957041