Sunday, 4 September 2011

How to determine type of mysql database

To determine the storage engine being used by a table, you can use show table status. The Engine field in the results will show the database engine for the table. Alternately, you can select the engine field from information_schema.tables.

To get the type per database:
mysql -u root -p'<password>' -Bse 'select distinct table_schema, engine from information_schema.tables'
For a specific table use:
select engine from information_schema.tables where table_schema = 'schema_name' and table_name = 'table_name'
You can change between storage engines using alter table:
alter table the_table engine = InnoDB;
Where, of course, you can specify any available storage engine.

Possibly Related Posts

No comments:

Post a Comment