MySQL Change Table Engine from InnoDB to MyISAM

Every MySQL Database & Table is of either InnoDB or MyISAM storage engine. Each storage engine has their own features. For example, InnoDB has row-level locking while MyISAM has full table locking. InnoDB is great for transactional data while MyISAM is good for non-transactional data. So database developers choose InnoDB or MyISAM depending on their requirement. Sometimes you may need to change your database storage engine. In this article, we will learn how to change table engine from InnoDB to MyISAM in MySQL.

MySQL Change Table Engine from InnoDB to MyISAM

It is very easy to change storage engine from InnoDB to MyISAM. You can do this by logging into MySQL as administrator and running the following command.

mysql> SET storage_engine=MYISAM;

All tables that you create from now on, will have storage engine as MyISAM. However, this change in storage engine will last only till your MySQL session exists. Once you create a new session, the storage engine will revert back to InnoDB and you will have to run the above query again.

If you want to permanently change storage engine to MyISAM, open MySQL configuration file in a text editor.

$ sudo vi /etc/my.cnf

Add the following line to it.

default-storage-engine= MyISAM

If you see the following line in my.cnf file, remove it or comment it by adding # at its beginning.

default-storage-engine= InnoDB

Save and close the file. Restart MySQL Server to apply changes.

$ sudo service mysql restart

If you need to change storage engine of existing table, you can do so with the following command.

mysql> ALTER TABLE table_name ENGINE = MyISAM;

Depending on your requirement, you can change storage engine to InnoDB or MyISAM.

Also read:

How to Install Fonts in Ubuntu
How to Increment & Decrement Variables in Shell Script
How to Get Current Directory of Bash Script
Apache Commands Cheat Sheet
How to Clone Git Repository to Specific Folder