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