Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a master that has 298 relay bin files as recent as today, going back well 298 days.

There is no relay-log definitions in the .cnf


mysql> show variables like '%relay%';
| Variable_name                   | Value          |
| innodb_overwrite_relay_log_info | OFF            |
| max_relay_log_size              | 0              |
| relay_log                       |                |
| relay_log_index                 |                |
| relay_log_info_file             | |
| relay_log_purge                 | ON             |
| relay_log_space_limit           | 0              |

Reset slave clears them out, but then they just start getting regenerated.

Any idea what's causing this? How to stop it?


General critiques of the cnf are welcome but let's keep the OP topic in mind.

---- cnf request

character_set_server = utf8


userstat_running = 1













log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/mysql.index

key_buffer_size = 800M

preload_buffer_size = 256K

max_allowed_packet = 8M
table_cache = 512
sort_buffer_size = 8M
join_buffer_size = 8M

read_buffer_size = 2M
read_rnd_buffer_size = 2M
thread_cache_size = 32
query_cache_size = 32M
query_cache_limit = 16M

myisam_sort_buffer_size = 2000M

tmp_table_size = 64M
max_heap_table_size = 64M

---- now for the cli requests

mysql> show slave status\G
Empty set (0.00 sec)

mysql> show master status;
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| awesome-bin.xxxxxxx | yyyyyyyy |              |                  |
1 row in set (0.00 sec)

---- version

mysql> select version();
| version()          |
| 5.1.47-rel11.1-log |
1 row in set (0.00 sec)
share|improve this question
Please post MySQL version and my.cnf entries if possible. – dabest1 Oct 20 '11 at 0:38

If a Master has relay logs, then the Master must also be a Slave in the midst of some Replication topology (i.e., Master/Master, Daisy-Chained Replication)

What could cause relay logs to grow like this?


MySQL Replication is broken when the IO Thread or SQL thread dies under these SCENARIOS:

  • SCENARIO #1 : When the IO Thread and SQL thread are off, one of two things happended
  • SCENARIO #2 : When the IO thread dies
    • nothing can pile up the relay logs
    • The SQL thread processes all SQL commands in the relay logs or until a SQL error occurs
  • SCENARIO #3 : When the SQL thread dies
    • SQL error occurred processing a SQL command
    • Running SHOW SLAVE STATUS\G shows you the Last_Errno and Last Error
    • IO Thread continued collecting SQL commands from the Master, making relay logs grow

It is SITUATION #3 that's the problem. When the SQL thread dies due to a SQL error, there is no built-in mechanism in MySQL Replication that triggers the IO thread to disconnect.


The only decent way to control the growth of relay logs is to set the limit on it


Setting relay_log_space_limit places a cap of 4G.

When a relay log is completely processed

  • it is rotated out
  • the SQL thread starts working on the next relay log
  • the I/O thread starts loading SQL from the Master from the last place it left of from, as long as there is enough freespace on the disk


If the Master used to be a Slave and it does not need to be anymore, simple disable it.

rm -f /var/lib/mysql/

If the Master is a Slave, go correct the SQL error.

I would suggest this if the SQL error is in the way:

SET GLOBAL sql_slave_skip_counter = 1;

then run SHOW SLAVE STATUS\G every minute to see if the relay logs get processed and rotated.

share|improve this answer

Without seeing your my.cnf, it's impossible to answer this question, but I would also suggest posting your SHOW SLAVE STATUS\G output - is it possible that your slave is actually incredibly far behind? That would keep the relay logs around. Is the SQL Slave thread running?

share|improve this answer

Could it be that my.cnf file is mis-configured and master binary logs are named as relay logs?

Or maybe your master has hard coded replication settings in my.cnf file, which are picked up at MySQL instance restart.

EDIT: Did you mask the actual binlog filename in show master status output? I'm asking because the setting in my.cnf does not match the binlog name. If so, could you provide the actual filename, and the output of show slave status like Aaron mentioned? So far other than name mismatch for bin-log, nothing stands out in your my.cnf file.

share|improve this answer

Execute the RESET SLAVE command. It will clean the relays logs and regenerates a new one. But, It will not use the new one. You can check by running later the FLUSH LOGS command, the server will not create a second relay log.

share|improve this answer
Can you expand your answer? It is not clear what you mean. – Max Vernon Mar 13 '13 at 16:56

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.