Bug #6178 unwanted relay-bin.xxx files after 'FLUSH LOGS' command
Submitted: 20 Oct 2004 13:27 Modified: 21 Oct 2004 22:35
Reporter: johan Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:4.0.18 OS:Microsoft Windows (Windows2000)
Assigned to: Lars Thalmann CPU Architecture:Any

[20 Oct 2004 13:27] johan
Unwanted relay-bin.xxx files after FLUSH LOGS command.

I switch a slave machine to become master.

If the new master still has the 'master.info' file in its mysql data directory, then the 'FLUSH LOGS' command on this master will have a side-effect: at each 'FLUSH LOGS' statement, a new <hostname>-relay-bin.nnn file will be created in the mysql data directory. 
This stops if you delete the 'master.info' file and restart the mysql master process.
The 'master.info' file is a left over from the time this machine was a slave.

In my case, the data directory ultimately contained more than 30000 useless <hostname>-relay-bin.nnn files on a machine that had been a slave once. The FLUSH LOGS statement, that is regularly done on the master to keep the binary log file from growing forever, stalled this PC for about 5 seconds at each invocation because of the huge number of relay files!!

How to repeat:
Switch a slave to become master without issuing a RESET SLAVE command in between. Then, on the new master, issue a FLUSH LOGS command.
You will see a new binary logfile, which is OK, but you will also see a new <hostname>-relay-bin.nnn file.

Suggested fix:
I start the master always with the 'skip-slave-start' option, so that it is NOT running as a slave although it HAS its slave information available in the master.info file. I suggest the <hostname>-relay-bin.nnn files should only be created if the mysql instance is an active, started, slave.

Issue a RESET SLAVE command on the master right after starting it.
The 'relay-log.info' and 'master.info' files will be deleted.
The mysql instance will still have the data from 'master.info' in its memory (you can see this by issuing 'SHOW SLAVE STATUS') but the FLUSH LOGS side-effect no longer occurs. After restarting mysql, the data in memory will be gone too.
[21 Oct 2004 22:13] Lars Thalmann
Hi Johan,

For having the FLUSH LOGS command not flushing the 
relay log, you must execute RESET SLAVE.

But, please look at option --bin-log-size for controlling
the size of your binlog.  Setting this option to 
a good value would make it unnecessary to execute
FLUSH LOGS as often as you do now.  Hopefully that will
make things easier for you.
[21 Oct 2004 22:35] Guilhem Bichot
Sorry, Lars meant max_binlog_size; which does:
 "Binary log will be rotated automatically when the size exceeds this \
value. Will also apply to relay logs if max_relay_log_size is 0. \
The minimum value for this variable is 4096."
(so better set max_relay_log_size to something else than 0).