MySQL binlog secure deletion

MySQL binlog secure deletion

Introduction:

If you have set up master-slave synchronization for MySQL or enabled log-bin for the database, a large number of log files will be generated in the data directory of your database over time.

Shell> ll/usr/local/mysql/data/# as follows
-Rw ----. 1 mysql 63278 September 11 02:03 mysql-bin.000001
-Rw ----. 1 mysql 1096670 September 11 02:03 mysql-bin.000002
-Rw ----. 1 mysql 133 September 11 02:04 mysql-bin.000003
-Rw ----. 1 mysql 133 September 12 02:00 mysql-bin.000004
-Rw ----. 1 mysql 271 September 12 18:25 mysql-bin.000005
-Rw ----. 1 mysql 133 September 12 18:55 mysql-bin.000006
-Rw ----. 1 mysql 133 September 12 19:48 mysql-bin.000007
-Rw ----. 1 mysql 133 September 12 21:21 mysql-bin.000008
-Rw ----. 1 mysql 321 November 18 17:30 mysql-bin.000009

 

# Because my test environment is used, the file size is very small. If these files are not processed in the production environment, your disk may even pop up.

The following describes several solutions:

~~> If your database has not set up a service such as master-slave synchronization and binlog is not used, you can delete or comment out the lines below the/etc/my. cnf file.

Log-bin = mysql-bin

~~> If you need such a file, we recommend that you add the following lines to the main configuration file/etc/my. cnf to limit the time when the binlog log file exists and automatically delete it when it expires.

Expire_logs_days = 7 # retain log files for only 7 days

~~> If you directly obtain a server whose binlog has affected the disk space, run the following command to clear it:

Mysql> reset master; # Clear all binlog files

 

~~> If you get the server, mysql also performs master-slave synchronization, the above command is not suitable for you, because too many binlog files are deleted, it will cause data not to be synchronized

Mysql> show slave status \ G # first, you must check the binlog read from the database on slave.
Master_Log_File: mysql-bin.000009 # we can see that it is a mysql-bin.000009

Mysql> purge master logs to 'mysql-bin.000009 '; # We can execute this command in the master to delete the binlog file before the mysql-bin.000009

Mysql> purge master logs before '2017-11-18 00:00:00 '; # Delete the binlog file before this date

This article permanently updates the link address: