Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Previously, on every database created, I use:

mysql -u root -p
CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL ON dbname.* TO 'dbuser'@'localhost';

and then use the database without thinking about MyISAM or InnoDB

How to know storage engine used of a database?

share|improve this question
add comment

3 Answers

You can check per-table like this:

USE <database>;
SHOW TABLE STATUS\G

you'll get an output along these lines:

root@localhost/database> show table status\G
*************************** 1. row ***************************
           Name: tablename
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 101
 Avg_row_length: 70
    Data_length: 7070
Max_data_length: 19703248369745919
   Index_length: 2048
      Data_free: 0
 Auto_increment: 1004
    Create_time: 2009-12-07 20:15:53
    Update_time: 2010-11-10 21:55:01
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
share|improve this answer
    
any way you know of to do all tables in a DB (besides writing a script to SHOW TABLE for each table in the DB)? –  voretaq7 Mar 8 '11 at 17:15
2  
@voretaq7 the "SHOW TABLE STATUS" does show you all tables by default. –  Coops Mar 8 '11 at 17:21
    
cool -- I'm a postgres guy so all this MySQL and multiple storage engines stuff is strange to me :-) –  voretaq7 Mar 8 '11 at 17:38
add comment

Use the 'show engine' command to view the active default engine

Add default-storage-engine=InnoDB in [mysqld] section of the my.cnf file for the default engine to be active.

Use the 'show create table table_name' command to view default engine in the table.

share|improve this answer
add comment

This query lists all InnoDB tables and their databases in MySQL:

SELECT table_name, table_schema 
FROM information_schema.tables 
WHERE engine = 'InnoDB';

You can also list all tables and their storage engines:

SELECT table_name, table_schema, engine
FROM information_schema.tables;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.