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?

Sling

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

Impassable

SHOW CREATE TABLE is how I do it. – Stowers

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.

Directorate

Answer 3

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, ENGINE
from information_schema.TABLES
where TABLE_SCHEMA='yourdbname'

Contralto

what if engine column for some tables is InnoDB and null for others? what does null mean in this case? – Naranjo

Answer 4

This is a longer solution but it can be useful if you want to learn something about information_schema

mysql> select table_name,engine from information_schema.tables where table_name
= 'table_name' and table_schema = 'db_name';

Obliterate

what if engine column for some tables is InnoDB and null for others? what does null mean in this case? – Naranjo

Answer 5

You can use this command:

mysql -u[user] -p -D[database] -e "show table status\G"| egrep "(Index|Data)_length" | awk 'BEGIN { rsum = 0 } { rsum += $2 } END { print rsum }'

Cheyney

Answer 6

SHOW TABLE STATUS WHERE Name = 'user_tbl'

Phenyl

Answer 7

mysql -u[user] -p -D[database] -e "show table status\G" | egrep "(Engine|Name)"

This will list all the tables and their corresponding engine. Good to get an overview of everything!

It's a modified answer from @yago-riveiro where he showed how to get the size of the tables, rather than the engines in use. Also, it's better to have an explanation on what a command does.

Bots