How to switch to INNODB database in mysql

MySQL support many storage engines. If you run the following command you can see all the storage engines supported by your mysql version.


show engines;

For the performance reasons MyISAM is the default storage engine.  In some cases, you might want to change this to different engine, like  InnoDB for transaction support etc.

There are couple of ways of doing it. I know three of them

1. Change my.cnf file.

MySQL reads from my.cnf to read the default configuration settings. This file is generally found in /etc/my.cnf or $USERHOME/.my.cnf in *nix/mac  or c: or installdirectory or windows directory in window. In my.cnf file have this configuration


[mysqld]
.........

.........
default-storage-engine = InnoDB

Also, have the following  lines or uncomment them if already present. Make sure the innodb_data_home_dir etc points to a valid location

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

2. SET storage_engine=INNODB

If you want your storage engine changed to something else for just the current session you can run the following command

SET storage_engine=InnoDB

3. Use engine=INNODB per table

You can change the engine per table like this

CREATE TABLE employee (id INT) ENGINE = InnoDB;

or if you have existing table and you want to change the engine

ALTER TABLE employee ENGINE = InnoDB;

Leave a Reply

Your email address will not be published. Required fields are marked *