MySQL command for finding all MyISAM databases, and convert them to innodb

FreeBSD Jan 27, 2021

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.).

Tags

Jordan Ostreff

2G/3G/4G/NSA-5G/SA-5G Mobile Packet Core, Telco Clouds, Ericsson CEE, OpenStack, NFV, Orchestration, SDN, Containerisation, Networking IPv4/IPv6, Industrial Computer Networks, Mathematics, Engineering

Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.
Share with Me via Nextcloud