How to set/change the default storage engine in MySQL

The storage engines are responsible for the storage and retrieval of all data stored in MySQL.

Storage engines available for MySQL are
1) MyISAM (used by default)
2) InnoDB (Supports transactions, row-level locking, and foreign keys)
3) Heap (Hash based, stored in memory, useful for temporary tables)

The ENGINES table in database information_schema provides the default storage engine being used.

MyISAM is the default storage engine.

The default storage engine can be changed. Once the default storage engine type is changed, any new table created will have the new storage engine.However, tables already created will continue to have the old storage engine.

To change it to INNODB storage engine,

1) Add the following line in mysql configuration file /etc/my.cnf under [mysqld] section

[mysqld]
default-storage-engine=InnoDB 

2) Restart mysql
   # service mysqld restart

3) Check if the default storage engine is set as INNODB, from engines table in information_schema database

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from ENGINES\G;
*************************** 1. row ***************************
      ENGINE: MRG_MYISAM
     SUPPORT: YES
     COMMENT: Collection of identical MyISAM tables
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO
*************************** 2. row ***************************
      ENGINE: CSV
     SUPPORT: YES
     COMMENT: CSV storage engine
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO
*************************** 3. row ***************************
      ENGINE: MyISAM
     SUPPORT: YES
     COMMENT: Default engine as of MySQL 3.23 with great performance
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO
*************************** 4. row ***************************
      ENGINE: InnoDB
     SUPPORT: DEFAULT
     COMMENT: Supports transactions, row-level locking, and foreign keys
TRANSACTIONS: YES
          XA: YES
  SAVEPOINTS: YES
*************************** 5. row ***************************
      ENGINE: MEMORY
     SUPPORT: YES
     COMMENT: Hash based, stored in memory, useful for temporary tables
TRANSACTIONS: NO
          XA: NO
  SAVEPOINTS: NO
5 rows in set (0.00 sec)


How to alter the storage engine set for a table?

Tables can be created with different storage engines. All tables in a database need not have the same storage engine. Also, the storage engine for a table too can be altered after creation.


To convert a table from one storage engine to another, use an ALTER TABLE statement that indicates the new engine

ALTER TABLE <tablename> ENGINE = <storage engine name>;

Eg:

Say, I wish to change the storage engine for table dg_cities in database schema sampledata.

1) First let me identify the storage engine used by table dg_cities in database sampledata

mysql> select TABLE_NAME,TABLE_SCHEMA, ENGINE from information_schema.tables where TABLE_NAME like '%dg_cities%'\G;
*************************** 1. row ***************************
  TABLE_NAME: dg_cities
TABLE_SCHEMA: sampledata
      ENGINE: MyISAM
1 row in set (0.00 sec)

2) Altering the storage engine for table dg_cities in database sampledata , from MyISAM to InnoDB

mysql> use sampledata;

mysql> ALTER TABLE dg_cities ENGINE = InnoDB;
Query OK, 1056 rows affected (0.17 sec)
Records: 1056  Duplicates: 0  Warnings: 0

mysql> select TABLE_NAME,TABLE_SCHEMA, ENGINE from information_schema.tables where TABLE_NAME like '%dg_cities%'\G;
*************************** 1. row ***************************
  TABLE_NAME: dg_cities
TABLE_SCHEMA: sampledata
      ENGINE: InnoDB