MySQL support many storage engines. If you run the following command you can see all the storage engines supported by your mysql version.
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
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;