Mysql binlogs exists, show binary logs is empty

Question

I am working on a replication setup and have turned on bin-logs. I have moved my mysql datadir to a new location as well as the binlog path

From my.cnf

datadir = /vol/data/mysql
log-bin=/vol/data/mysql/mysql-bin
binlog_format=mixed

When I start mysqld and check the status:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000315 | 106 | | |
+------------------+----------+--------------+------------------+

When I check the contents of /vol/data/mysql, I can see a lot of bin logs, but specifically, I can see this file was just created:

mysql-bin.000313
mysql-bin.000314
mysql-bin.000315
mysql-bin.index

If I tail the index file:

$ sudo tail mysql-bin.index
/vol/data/mysql/mysql-bin.000313
/vol/data/mysql/mysql-bin.000314
/vol/data/mysql/mysql-bin.000315

In mysql, there are no binary logs at all.

mysql> SHOW BINARY LOGS;
Empty set (0.00 sec)

This means that purging binary logs in mysql has no affect. I checked the permissions of the directory and files and all belong to mysql user and group. Why can't mysql see and/or purge those binary logs?

mysql> PURGE BINARY LOGS BEFORE NOW();
ERROR 1373 (HY000): Target log not found in binlog index

• RolandoMySQLDBA: If you run FLUSH LOGS; does it generate mysql-bin.000316 ?
• Michael - sqlbot: What does SELECT @@LOG_BIN_INDEX; return? Is that the same path and name as the "mysql-bin.index" file you're examining?
• Jage: @Michael-sqlbot thanks, I received an error with that query. mysql> SELECT @@LOG_BIN_INDEX; ERROR 1193 (HY000): Unknown system variable 'LOG_BIN_INDEX'
• Jage: @RolandoMySQLDBA yes it did generate 000316.
• Michael - sqlbot: Apologies, the @@LOG_BIN_INDEX global system variable wasn't introduced until MySQL 5.6.4.

Answer 1

You may need to try something a little unorthodox

service mysql stop

Open mysql-bin.index in vi

Change the contents to this

./mysql-bin.000313
./mysql-bin.000314
./mysql-bin.000315

Save mysql-bin.index

service mysql start

Login to MySQL and run

SHOW BINARY LOGS;
SHOW MASTER STATUS;

This stunt worked a few times for me.

Give it a Try !!!

• Jage: thanks for the suggestion here. The key here was that when I went to edit that file, the very first line was blank. All the files were listed starting on the second line. Removing that blank line fixed the problem.

Answer 2

in my case there was an empty line in the beginning of the index file. After removing the blank line it worked fine.