MySQL database server has tbe capability to use different database storage engines. The database storage engine is what actually do the work of storing and retrieving data from the underlying database tables.

The storage engines work like a modular code which can be plugged into MySQL. Among the stroage enginers MyISAM is the one, used most widely and also the default storage engine for MySQL. However it do lack some high level functionalities which are requirement for any enterprise level database server e.g. it do not have proper support for transaction or foreign key.

This is where InnoDB storage engine comes into play, InnDB is a trasanction safe storage engine with support for foreign keys along with commit, rollbak and crash recovery capabilties.

InnoDB is a product of Innobase Oy, which a Finnish company now owned by Oracle. MySQL has a licensing agreement with Innobase Oy which allows it to provide the InnoDB.

As we have covered some InnoDb background lets take a look at main ‘how to’ topic , i.e. how we can check if the MySQL server we are running has InnDB support enabled or not.

There is a MySQL system variable have_inndb and its value can be checked to see if the support enabled or not. You can do this by using following command :


mysqladmin variables | grep have_innodb

If this returns YES then it means that the InnoDB support is enabled. If you have ‘–skip-innodb’ in your /etc/my.cnf MySQL configuration file then InnoDB engine would be disabled. And in that case you can comment out this option to enable InnoDB. It is enabled by default and unless there is some related disable configuration added to my.cnf configuration, there should be no issues.

Also if you would like to use InnoDB as the default storage engine, you can do that by using below cofiguration in /etc/my.cnf :

default-storage_engine=InnoDB

But make sure to do your homework before setting the storage engine, it do have performance benefits over MyISAM once its configured properly, but do have higher administrative cost. InnoDB can be configured to have per table innodb file, which do help in isolating any individual database corruptions. I will cover that in some other post soon.

 

Tags: , , , , ,

4 Comments on How to check if innodb is enabled in Mysql server

  1. Brad says:

    # mysqladmin -udbuser -p varitables | grep have_innodb
    Enter password:
    mysqladmin: Unknown command: ‘varitables’

  2. Sachin says:

    :~/Desktop$ mysqladmin -u root -p -variables | grep have_innodb
    mysqladmin: unknown option ‘-a’

    What should I do now?

Leave a Reply to Brad Cancel reply