1

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
  • If you run FLUSH LOGS; does it generate mysql-bin.000316 ? – RolandoMySQLDBA Dec 25 '14 at 17:37
  • What does SELECT @@LOG_BIN_INDEX; return? Is that the same path and name as the "mysql-bin.index" file you're examining? – Michael - sqlbot Dec 25 '14 at 21:13
  • @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 Dec 26 '14 at 12:14
  • @RolandoMySQLDBA yes it did generate 000316. – Jage Dec 26 '14 at 12:14
  • Apologies, the @@LOG_BIN_INDEX global system variable wasn't introduced until MySQL 5.6.4. – Michael - sqlbot Dec 26 '14 at 15:18
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 !!!

  • 1
    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. – Jage Dec 26 '14 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.