What are on.number files in MySQL?

Question

I got an alert this evening that one of our MySQL cluster disks was over 80% full - upon checking there are lots of "on.number" files created in the last 48 hours. Can anyone tell me what these are and if they can be deleted?

They look like:

-rw-rw----   1 mysql mysql 1073742020 Feb 10 02:25 ON.002859
-rw-rw----   1 mysql mysql 1073741930 Feb 10 02:53 ON.002860
-rw-rw----   1 mysql mysql 1073742080 Feb 10 03:24 ON.002861
-rw-rw----   1 mysql mysql 1073742072 Feb 10 03:53 ON.002862
-rw-rw----   1 mysql mysql 1073742001 Feb 10 04:20 ON.002863
-rw-rw----   1 mysql mysql 1073742052 Feb 10 04:49 ON.002864
-rw-rw----   1 mysql mysql 1073741889 Feb 10 05:17 ON.002865

The most recent one looks like:

-rw-rw----   1 mysql mysql        540 Feb 10 22:18 ON.index

At this rate, the disk will fill in around 24 hours so I need to try and figure out what these are, why they are being created and if I can remove them?

Answer 1

They are binary logs. The system variable expire_log_days can be used to limit their growth.

They are used for replication and point in time recovery. Except for these functions they are not needed for the functioning of the database.

Answer 2

What the heck is going on to fill up 2GB of binlogs per hour!

Perhaps you have a dual master setup but both of the servers have the same server_id? (That's a mistake to fix.)

Perhaps you are doing lots of full-table UPDATEs with binlog_format=ROW? (That needs addressing for efficiency reasons.)

As for the ON prefix, that sounds like a typo somewhere, perhaps in my.cnf. Perhaps you thought some setting was an ON/OFF flag, but in reality, it was a file-prefix? (Not critical to fix.)