Secure Mysql binlog deletion and mysqlbinlog Deletion

Secure Mysql binlog deletion and mysqlbinlog 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/# The following-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 set/etc/my. delete or comment out log-bin = mysql-bin in the line below the cnf File

~~> If you need such a file, we suggest you in the main configuration file/etc/my. add the following lines to cnf to limit the time for binlog log files to exist. expire_logs_days = 7 # Only log files for 7 days are retained.

~~> If you directly obtain a server whose binlog has affected disk space, you will use the following command to clear 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, this will cause data not to be synchronized to mysql> show slave status \ G # first, you need to check on slave where to read the binlog from the database Master_Log_File: mysql-bin.000009 # We can see the mysql-bin.000009mysql> purge master logs to 'mysql-bin.000009'; # We can execute this command in the master, remove the binlog file to the mysql-bin.000009 before mysql> purge master logs before '2017-11-18 00:00:00 '; # Delete the binlog file before this date