Tons and tons of relay logs on a master

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

and

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.info |
| 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?

EDITS TO REQUESTS

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

```
---- cnf request

[mysqld]
character_set_server = utf8

max_connections=200
max_user_connections=160
max_connect_errors=10000

userstat_running = 1

log_warnings
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=2

innodb_file_per_table

innodb_open_files=2048

innodb_additional_mem_pool_size=1M

innodb_buffer_pool_size=512M

innodb_log_buffer_size=1M

innodb_log_file_size=128M

innodb_autoextend_increment=16

innodb_flush_method=O_DIRECT

datadir=/var/lib/mysql/

tmpdir=/var/lib/mysql_ramdisk

server-id=2

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

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?

BROKEN REPLICATION

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

• MySQL was started with skip-slave-start enabled

• Someone/something ran STOP SLAVE;

• 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.

RECOMMENDATION

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

[mysqld]
relay_log_space_limit=4G

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

EPILOGUE

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

mysql -e"STOP SLAVE; CHANGE MASTER TO MASTER_HOST='';"
rm -f /var/lib/mysql/master.info

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

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

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE SQL_THREAD;

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

StackExchange Database Administrators Q#6983, answer score: 8