MYSQL STORAGE ENGINE STATUS CHECK

MYSQL STORAGE ENGINE COMMANDS

1. How to check Global storage engine :

SHOW GLOBAL VARIABLES LIKE 'storage_engine';

2. How to check all table status/engine for Particular database :

SHOW TABLE STATUS FROM 'DBNAME';

3. How to Find out InnoDB tables of Particular database :

SELECT TABLE_NAME FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'DBNAME' AND engine = 'InnoDB';

4 Check Table Status with Condition :

SHOW TABLE STATUS FROM 'DBNAME' WHERE ENGINE like 'MyISAM'

5 Modify or Update ENGINE of particular table in MYSQL

ALTER TABLE tablename ENGINE = InnoDB;

6 Check data tables rows,size & engine details of selected/multiple database in MYSQL

SELECT    engine,
        count(*)    as    TABLES,
        concat(round(sum(table_rows)/1000000,2),'M')    rows,
        concat(round(sum(data_length)/(1024*1024*1024),2),'G')    DATA,
        concat(round(sum(index_length)/(1024*1024*1024),2),'G')    idx,
        concat(round(sum(data_length+index_length)/
                        (1024*1024*1024),2),'G')    total_size,
        round(sum(index_length)/sum(data_length),2)    idxfrac
    FROM    information_schema.TABLES
WHERE    table_schema       in
                        ('billing_new')
GROUP    BY    engine
ORDER    BY    sum(data_length+index_length)    DESC
LIMIT    10;