MySQL slave server not removing old relay binlogs

Question

I have a MySQL server with slave replication on another host. Today I stumbled across the high disk usage of the slave host and invastigated what takes up all the space. As it turns out this space is occupied by the slaves relay logs.

I tried to turn the expire_logs_days variable down and restarted the MySQL daemon but the reported disk space stays the same. I could't really find anything exept that FLUSH LOGS should clear old logs. I tried that with no result.

Is there any way I can reduce the disk space that the relay logs take?

Answer 1

The expire_logs_days setting controls binary logging, not relay logging.

Normally, the relay logs are purged when the slave has finished applying the data from them. This can be changed with relay-log-purge, but it's default is 1.

If your slave is simply behind, it will continue to accumulate relay logs by design. You might want to consider setting relay-log-space-limit to prevent out of disk space problems. This setting provides a disk space limit for the IO thread to use for storing relay logs.

Answer 2

Make sure you haven't set relay_log_purge=0 in your configuration. I had set this option while troubleshooting a replication issue and forgot to remove it. Later we found the logs disk was filling up due to the relay logs not being purged after being applied.

You can check and set the state of this variable dynamically like this:

> SELECT @@global.relay_log_purge;
+--------------------------+
| @@global.relay_log_purge |
+--------------------------+
|                        0 |
+--------------------------+

> SET @@global.relay_log_purge = 1;
Query OK, 0 rows affected (0.04 sec)

> SELECT @@global.relay_log_purge;
+--------------------------+
| @@global.relay_log_purge |
+--------------------------+
|                        1 |
+--------------------------+