See what storage engine MySQL database uses via terminal

This is available in a few places.

From the SHOW CREATE TABLE output.

mysql> SHOW CREATE TABLE guestbook.Guestbook;+-----------+-------------------------------------------+| Table     | Create Table                                                                                                                                                                   |+-----------+-------------------------------------------+| Guestbook | CREATE TABLE `Guestbook` (  `NAME` varchar(128) NOT NULL DEFAULT '',  `MESSAGE` text NOT NULL,  `TIMESTAMP` varchar(24) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 |+-----------+-------------------------------------------+1 row in set (0.00 sec)

From information_schema

You may also find it in information_schema.TABLES if you want to query the engines of multiple tables.

SELECT ENGINE FROM information_schema.TABLESWHERE  TABLE_NAME='yourtable'  AND TABLE_SCHEMA='yourdatabase';

SHOW ENGINES;

return the engines your MySQL database support and tell you which is the default one if not otherwise specified at creation time.

A database on MySQL can use multiple storage engines, so you'll have to check per-table. Simplest is to do

show create table yourtable;

and see what the 'engine' line at the end of the DDL statement is. e.g. engine=InnoDB, engine=MyISAM, etc...

If you want to check all the tables in your DB:

select TABLE_NAME, ENGINEfrom information_schema.TABLESwhere TABLE_SCHEMA='yourdbname'