Why is MySQL writing hundreds of 125 byte binary log files?

Question

My MySQL Master (with no connected slaves right now) writes out a 125 byte file every minute or so:

-rw-rw---- 1 mysql mysql        125 2012-12-28 16:46 snapshot-mysql-v2-bin.004876
-rw-rw---- 1 mysql mysql        125 2012-12-28 16:45 snapshot-mysql-v2-bin.004875
-rw-rw---- 1 mysql mysql        125 2012-12-28 16:43 snapshot-mysql-v2-bin.004874
-rw-rw---- 1 mysql mysql        125 2012-12-28 16:41 snapshot-mysql-v2-bin.004873

while writing the actual binary log content to a file with a much lower number:

-rw-rw---- 1 mysql mysql  330755915 2012-12-28 16:48 snapshot-mysql-v2-bin.004472

(and when that file fills up, it goes to the next file).

In addition, MySQL is not writing the name of the actual file with content (004472 above) to the .index file, so when I connect a slave, it can't replicate until I edit the .index file by hand.

MySQL version is 5.1.41-3ubuntu12.10-log.

Any ideas?

Answer 1

The problem appears to have been that the /var/lib/mysql/ibdata1 file was locked and not writable by MySQL. I found a lot of entries to that effect in the mysql.err file, despite the fact that the MySQL server was functioning fine (and lots of rows were added to InnoDB tables).

To fix, I had to kill off all mysqld instances (mysqladmin shutdown was not enough to get them), and then I ran:

cp -a ibdata1 ibdatanew1

and I edited the my.cnf to point to the ibdatanew1 file. I started up MySQL again, reset the master, flushed the logs, and it's been fine for the past 6 hours.

I am still not clear how the ibdata1 file being locked would have enabled MySQL to continue running, but perhaps the ibdata1 issue was only cropping up with respect to writing the binary log? (Of note, I am used to see ibdata1 issues in the mysqld.log, not the mysql.err log).