Tag Archives: change default engine setting

Check the Default Engine of tables and database in MySQL

Check the Default Engine of tables and database in MySQL

Check the default engine in MySQL

mysql> select * from information_schema.engines;
OR
mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

Change default setting of Engine in ini file parameter

[mysqld]
# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

Or
Command line during starting:
--default-storage-engine=InnoDB

Check the table engine

mysql> SELECT TABLE_NAME, TABLE_TYPE, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'TEST';

+------------+------------+--------+
| TABLE_NAME | TABLE_TYPE | ENGINE |
+------------+------------+--------+
| test       | BASE TABLE | InnoDB |
+------------+------------+--------+

Change the table engine type

ALTER TABLE table_name ENGINE=InnoDB;

Show the engine status

SHOW ENGINE INNODB STATUS\G

Advertisements