This paper introduces the reason and solution of relay-log in one operation and maintenance practice.
Background: Today in the operation of a MySQL instance, found that the relay-log of its data directory has not been deleted for a long time, has accumulated dozens of relay-log. However, other examples of slave servers do not.
Phenomenon Analysis
Through the collected information, comprehensive analysis found that relay-log can not be automatically deleted and the following reasons. The instance was originally a slave: causing the presence of Relay-log and relay-log.index The instance is now not slave: because there is no io-thread, the Relay-log-purge does not work ( This is also why other slave instances do not, because Io-thread will do automatic rotate operations. Daily backups of this instance: the presence of Flush logs, which results in a daily relay-log that the instance is not configured Expire-logs-days: Flush purge is not done when logs Relay-log
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. In-depth Analysis
Incidentally also share under the MySQL internal logrotate mechanism Binary Log Rotate mechanism: Rotate: After each Binary log write completes, will determine whether the current file is more than Max_binlog_size, Automatically generates a Binlog file delete:expire-logs-days only when the instance is started and flush logs if the file access time is earlier than the set value, purge file Relay Log rotate Machine System: 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, if the relay-log is no longer needed, it is automatically deleted delete:expire-logs-days only when the instance is started and when the flush is logs, if File access time is earlier than set value, then purge file (with Binlog file) (Updated:expire-logs-days and Relaylog purge have no relationship)
PS: Therefore, it is recommended to configure expire-logs-days, otherwise there is a layer of security when our external script stops unexpectedly.
Therefore, it is recommended that when slave no longer used, through the reset slave to cancel the relaylog, so as not to appear relay-log accumulation.
--This article from: http://cenalulu.github.io/mysql/cannot-rotate-relaylog/
My personal situation is: Relay_log_purge parameter value is on, but relay log has accumulated a lot. SQL Process stopped:
Slave_io_running:yes
Slave_sql_running:no
The reason is that the master-slave data is inconsistent, from the inventory in a certain data, the main library inserts the record, when executing from the library is an error.
After I have dealt with the problem that the Lord has never been consistent, start slave. The next day found relay log automatically cleared, only two left.
It seems that the slave two processes are normal also affects whether the relay log can automatically clear AH.