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

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 !!!

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.