MySQL command for finding all MyISAM databases

Today most popular storage engine in latests MySQL databases is InnoDB. But if you have older databases, before the times when InnoDB becomes the king, you must somehow convert your data.

You can easy find which tables are using an older storage engine using:

SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema');

You can generate command sequence which will ALTER needed tables using following:

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' ENGINE = InnoDB;') FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema');

Changing „MyISAM“ with needed type to be converted is also possible (from „Aria“ e.t.c.).

Оставете коментар

This site uses Akismet to reduce spam. Learn how your comment data is processed.