MySQL Clear Relay-log file method detailed

Today in the MySQL data directory of the local found many similar hostname-relay-bin.0000* files, the file is generally in the MySQL slave instance exists. The main purpose is to record the information of master-slave synchronization, which will normally be deleted automatically.

This machine is not configured with Master, slave, and the source is really not clear. Since it is used on the slave, it can be relieved to delete. Delete the log file on the master instance with reset master, use the command for the slave instance:

The code is as follows	Copy Code
Reset Slave

Relay log cannot be automatically deleted

After comprehensive analysis, the following causes were found.

• This instance was originally a slave-------leading to the existence of Relay-log and Relay-log.index
• This instance is not currently slave-------because Io-thread is not present, Relay-log-purge does not work (this is not the case for other slave instances because Io-thread does an automatic rotate operation).
• Daily backup of this instance-------Flush logs, resulting in the generation of one relay-log per day
• This instance is not configured expire-logs-days------cause flush logs and do not do relay-log cleanup

In short, if an instance is slave before it is deactivated (stop slave) and the expire-logs-days is not configured, Relay-log accumulation will occur.

Incidentally also share with everyone under the MySQL internal logrotate mechanism

Binary Log rotate mechanism:

rotate: After each binary log write is completed, will determine whether the current file is more than Max_binlog_size, if more than automatically generate a Binlog file
Delete:expire-logs-days is only judged when the instance is started and flush logs, if the file access time is earlier than the set value, purge file

Relay Log rotate mechanism:

rotate: After each event from master, determine whether the current file exceeds max_relay_log_size if more than automatically generate a new relay-log-file
delete:purge-relay-log When SQL thread executes one event at a time, it is automatically deleted if the relay-log is no longer needed
Delete:expire-logs-days Only when the instance is started and flush logs, if the file access time is earlier than the set value, then purge file (same as Binlog file) (Updated:expire-logs-days and R Elaylog's purge does not matter)

PS: Therefore, it is recommended to configure expire-logs-days, otherwise there is a layer of security when our external script stops unexpectedly.

It is therefore recommended that when slave is no longer in use, the reset slave is used to cancel Relaylog