Bypassed, Empty Relay Log We have a simple 1:1 Master-Slave replication configuration. The Master crashed, but we recovered and replication seems fine (confirmed by pt-heartbeat and SHOW SLAVE STATUS) but we've picked up one small, strange issue: there is an old, empty, and bypassed relay log hanging out on the Slave.

We are on relay.000725, and this one is relay.000724, so we're past it. What's more, it doesn't contain any actual queries. Just the meta data.

Can I delete this? What caused this?

&gt; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET
&gt; @@session.max_insert_delayed_threads=0*/; /*!50003 SET
&gt; @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER
&gt; /*!*/;
&gt; # at 4
&gt; #140704  3:36:04 server id 16  end_log_pos 107    Start: binlog v 4, server v 5.5.38 created 140704  3:36:04 BINLOG '
&gt; ZFm2Uw8QAAAAZwAAAGsAAAAAAAQANS41LjM4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
&gt; AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
&gt; '/*!*/;
&gt; # at 107
&gt; #140721  4:22:51 server id 14  end_log_pos 1073741944     Rotate to bin-log.006142  pos: 4
&gt; # at 148
&gt; #140704  3:36:04 server id 16  end_log_pos 187    Rotate to relay.000725  pos: 4 DELIMITER ;
&gt; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET
&gt; @@SESSION.PSEUDO_SLAVE_MODE=0*/;
You be able to delete it without issues. Why ?

Look for the file relay-log.index and display it

# cat relay-log.index


You will see a list of relay logs MySQL Replication needs and rotates. If relay.000724 is not in relay.index, you can delete relay.000724 without breaking anything.

Why is is still around ? It could be anything. Between setting up replication after an outrage, skipping SQL Errors, and just plain Starting and Stopping, along with a low expire_logs_days could leave one or more relay logs lingering.

In light of these things, if a relay log does not appear in relay.index, you can delete it.

Why is the relay log so empty ?

It has to do with the mechanism Replication uses to rotate to the next relay log. You will not see these annoying little files very often. The only time I usually see them is with one of the following:


when the SQL thread is broken
when the SQL thread is extremely busy with a long running query


In either case, the IO thread keeps collecting binlog events. Instead of triggering a rotation when the relay log reaches 1G or whatever max_binlog_size or max_relay_log_size is, two little relay logs with nothing in them mark the place where a rotation would have occurred.

UPDATE 2014-07-21 15:28 EDT

Since the relay-log.index both relay logs, there are one of two things you could do:


Leave it alone and let MySQL Replication rotate it away on its own
Reset all the relay logs back to relay-log.000001 with the following

Run STOP SLAVE;
Run SHOW SLAVE STATUS\G
Find Relay_Master_Log_File in the Status (say it is mysql-bin.001234)
Find Exec_Master_Log_File in the Status (say it is 123789456)
Run CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001234',MASTER_LOG_POS=123789456;
Run START SLAVE;