Tons and tons of relay logs on a master

Question

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

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)

Answer 1

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.

Answer 2

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?

Answer 3

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.

Answer 4

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.