How can I check MySQL engine type for a specific database?

Question

How can I check MySQL engine type for a specific database via mysql query?

Answer 1

In MySQL, databases don't have an engine type; tables have engine types. The Friendly Manual even explicitly states:

It is important to remember that you are not restricted to using the same storage engine for an entire server or schema: you can use a different storage engine for each table in your schema.

You can query the information_schema database (substitute your database name and table name in the example):

SELECT `ENGINE` FROM `information_schema`.`TABLES`   WHERE `TABLE_SCHEMA`='your_database_name' AND `TABLE_NAME`='your_table_name'; 

You can also get the value of the global environment variable storage_engine - which is only used as a default when a table is created without an engine specified, it does not affect the server in any other way:

SHOW GLOBAL VARIABLES LIKE 'storage_engine' 

Answer 2

Databases do not have engines. Tables have. You can run e.g. SHOW TABLE STATUS:

SHOW TABLE STATUS FROM mydatabase 

Available engines can be found with SHOW ENGINES.

---

Tip: if you are the using the official command-line client rather than GUI tools you might want to use the \G command (not to be confused with lowercase \g):

Send the current statement to the server to be executed and display the result using vertical format.

... which turns this:

mysql> SHOW TABLE STATUS; +----------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+----------- ---+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----- ------------------------------------------------------+ | Name                             | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_leng th | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comm ent                                                   | +----------------------------------+--------+---------+------------+------+----------------+-------------+-----------------+----------- ---+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+----- ------------------------------------------------------+ | canal                            | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |  0 |  27262976 |              1 | 2015-04-10 11:07:01 | NULL        | NULL       | utf8_general_ci   |     NULL |                | 

… into this:

mysql> SHOW TABLE STATUS\G *************************** 1. row ***************************            Name: canal          Engine: InnoDB         Version: 10      Row_format: Compact            Rows: 0  Avg_row_length: 0     Data_length: 16384 Max_data_length: 0    Index_length: 0       Data_free: 27262976  Auto_increment: 1     Create_time: 2015-04-10 11:07:01     Update_time: NULL      Check_time: NULL       Collation: utf8_general_ci        Checksum: NULL  Create_options:         Comment: *************************** 2. row ***************************            Name: cliente          Engine: InnoDB […]