How to enable INNODB in mysql

Question

When I execute a query in MySQL it returns an error saying that InnoDB is not enabled. When I clicked the storage engine, the InnoDB was disabled.

How do I enable InnoDB?

Ricker

Answer 1

You need to enable it in my.cnf file, then restart your server:

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#option_mysqld_innodb

Or you can load an InnoDB plugin during runtime:

https://docs.oracle.com/cd/E19078-01/mysql/mysql-refman-5.1/storage-engines.html#replacing-builtin-innodb

Bart

Do you also need to convert individual tables to use InnoDB if they were created when a different storage engine was default? – Lynelllynelle

Answer 2

I faced a similar situation where InnoDB got disabled after a mysql-server upgrade. The query "show engines" didn't display Innodb. Following this link fixed the issue for me.

    /etc/init.d/mysql stop

    cd /var/lib/mysql/

    ls ib_logfile*
    mv ib_logfile0 ib_logfile0.bak
    mv ib_logfile1 ib_logfile1.bak

    /etc/init.d/mysql restart

Nyberg

This fixed my issue finally! I spent hours going through a ton of different steps, but I couldn't get mysql to start and kept seeing errors related to Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) and removing these InnoDB log files was the fix! – Warmonger

This can also can cause total loss of peoples working database. So don't do this first, don't do rm, and look the the server error log, there are plenty of non-guesswork corrections to why InnoDB is disabled. – Hurlow

Answer 3

In my.ini (located in MySQL folder) put a # sign before 'skip-innodb' to disable this command. Then restart mysql. This will enable InnoDB engine.

Surbased

Answer 4

If your InnoDB gets disabled after a mysql-server upgrade what you have to do Initially is to set plugin-load of at server startup using

[mysqld]

plugin-load="myplugin_1=myplugin_1.so;myplugin_2=myplugin_2.so";

And then specify the pathname to the plugin_dir(plugin directory) it can be done by following changes in the my.cnf file

[mysqld]

ignore-builtin-innodb

plugin-load=innodb=ha_innodb_plugin.so

plugin_dir=/path/to/plugin/directory

Arvad