Mysql Unknown table engine ‘InnoDB’ when using LOCK TABLES

Today I got an error when I try to backup all the databases from mysql. Surprisingly this error does not contain any database name and so I could not identify which database causing this issue. So that I start backing up each database separately and identify the database causing the issue.

Then I start to googling, is one of my interesting regular jobs.. revieing many blog post.. most them saying that to “#skip-innodb” in /etc/my.cnf will solve the issue. But didn’t work for me 🙁

Later I came to know that, this mysql server does not having “INNODB” storage engine enabled.. Shocked.. that moment and did some deep drive to error logs at data directory(/var/lib/mysq/asd.err). It’s appears that mysql showing InnoDB warning when it’s started and this engine support has been turned off due to the error.

Solution : just look at the mysql errors found in data directory. When I open the error file I saw that innodb log file (innodb_log_file_size) was modified recently at run-time and hence got error.

See the log below,

#vi /var/lib/mysql/www.asd.com.err

120912 09:07:43 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
120912 09:07:44 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120912 9:07:44 InnoDB: Initializing buffer pool, size = 2.0G
120912 9:07:44 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 20971520 bytes!
120912 9:07:44 [ERROR] Plugin 'InnoDB' init function returned error.
120912 9:07:44 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
120912 9:07:44 [ERROR] Unknown/unsupported table type: Innodb
120912 9:07:44 [ERROR] Aborting

Fix : delete or move the “ib_logfile0 and ib_logfile1” and restart the mysql service.

#rm -rf /var/lib/mysql/ib_logfile*