Search code examples
mysqlterminalstorage-engines

See what storage engine MySQL database uses via terminal

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

Solution

  • 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';