See what storage engine MySQL database uses via terminal

Question

Is there a command in terminal for finding out what storage engine my MySQL database is using?

Answer 1

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.TABLES
WHERE
  TABLE_NAME='yourtable'
  AND TABLE_SCHEMA='yourdatabase';

Answer 2

SHOW ENGINES;

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