Sunday, July 16, 2006

MySQL Failed to open log file (InnoDB)

One needs to be very careful before deleting files as once deleted the files are gone for good. A friend of mine today called as MySQL was failing to start on his server. Earlier when "cleaning" his server, he had deleted binary log files without fully understanding their role. When I logged in to his server, I noticed the following errors:

060716 17:21:53  mysqld started
060716 17:21:53 InnoDB: Started; log sequence number 0 1736000119
/usr/sbin/mysqld: File './mysql-bin.000002' not found (Errcode: 2)
060716 17:21:53 [ERROR] Failed to open log (file './mysql-bin.000002', errno 2)
060716 17:21:53 [ERROR] Could not open log file
060716 17:21:53 [ERROR] Can't init tc log
060716 17:21:53 [ERROR] Aborting

060716 17:21:53 InnoDB: Starting shutdown...
060716 17:21:55 InnoDB: Shutdown completed; log sequence number 0 1736000119
060716 17:21:55 [Note] /usr/sbin/mysqld: Shutdown complete

060716 17:21:55 mysqld ended

I deleted the entries from mysql-bin.index and MySQL started without a problem.


Sheeri said...

I've had problems doing that because sometimes the ownership or permissions on the files changes -- try using PURGE MASTER LOGS to get rid of files that are older than a certain filename (or time). If you need to, you can create fake binary log files so the server can find & delete them.

Please tell your friend the best way to clean the files is to use "PURGE MASTER LOGS" on the mysql commandline.

Frankly Speaking! said...

Indeed, thanks for pointing that out.


nerkn said...

I checked /etc/init.d/mysqld and /usr/bin/mysql_safe for 2 hours but your entry did it in one minute.

Some one should write an automatic fix.

helper said...

delete the contents of the file /var/lib/mysql/mysql-bin-index.index (empty it) and start mysql /etc/init.d/mysql start
Mysql service will start without any problem

Anonymous said...

Adding the "expire_logs_days" environment variable to my.cnf seems to have done the job for me! Thanks, this blog did put me on the right track for getting rid of +600 MB of log files.


Anonymous said...

Old post, but still valid!


Vikash Bucha said...

saved me a few hours.

holgi said...

Helped me too at 2008-08-18. Thank you!

pam said...

hi, i have a similar problem.
I haven't delete files. can you help me?

090824 17:03:26 [Note] Plugin 'FEDERATED' is disabled.
090824 17:03:26 InnoDB: Started; log sequence number 0 46409
wampmysqld: File '' not found (Errcode: 2)
090824 17:03:26 [ERROR] Failed to open log (file '', errno 2)
090824 17:03:26 [ERROR] Could not open log file
090824 17:03:26 [ERROR] Can't init tc log
090824 17:03:26 [ERROR] Aborting

Grimnir said...

three years and all at same place...
clear index and start mysqld

echo '' >/var/db/mysql/mysql-bin.index

/usr/local/etc/rc.d/mysql-server start

Anonymous said...

thank you so much

shorawitz said...

I had a very similar problem, but I found that (some of) the mysqld-bin.###### files had changed ownership from mysql to root. chown'd them to mysql, and it came back to life!

hackerteamtest said...

Thanks so much