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;
Tags: How to switch to INNODB database in mysql, innodb, myisam, storage engines
Recent Comments