Batch converting MySQL table engines

This article details how to perform batch conversions of MySQL tables between storage engines like MyISAM and InnoDB. While you can alter individual tables, these commands are useful for migrating many tables at once.

Before making changes, you might want to record the current engine for all your tables. This generates a file containing ALTER TABLE commands that can be used later to convert the tables back to their original engines. The following command generates this file, excluding system databases.

mysql -Bse 'SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," ENGINE=",Engine,";") FROM information_schema.tables WHERE table_schema NOT IN("mysql","information_schema","performance_schema");' > tableconvert.sql

To restore your tables using the file created above, you can simply execute the SQL commands contained within it.

mysql < tableconvert.sql

One common task is to convert all MyISAM tables to InnoDB. This can be beneficial for performance and reliability, especially on busy servers, as InnoDB supports row-level locking [from previous discussion]. You can generate and execute ALTER TABLE commands for all tables currently using the MyISAM engine that are not in the system databases.

mysql -Bse 'SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," ENGINE=InnoDB;") FROM information_schema.tables WHERE table_schema NOT IN ("mysql","information_schema","performance_schema") AND Engine = "MyISAM";' | mysql -Bs

Please be aware that if any tables have features not supported by InnoDB, the conversion for those specific tables will fail with a warning. For example, in MySQL versions before 5.6, tables with a FULLTEXT index will cause an error during conversion to InnoDB. A potential issue with the command above is that it might stop executing if it encounters a failure, leaving subsequent tables unconverted.

A more robust method for converting all MyISAM tables to InnoDB is available. This command will continue processing tables even if an individual table fails to convert, and it provides enough information to help you identify which specific tables did not convert successfully.

mysql -Bse 'SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," ENGINE=InnoDB;") FROM information_schema.tables WHERE table_schema NOT IN ("mysql","information_schema","performance_schema") AND Engine = "MyISAM";' | while read -r i ; do echo $i ; mysql -e "$i" ; done

Conversely, you may need to convert all InnoDB tables to MyISAM. Similar to the MyISAM to InnoDB conversion, you can generate and execute the necessary ALTER TABLE commands for tables currently using the InnoDB engine outside of system databases.

mysql -Bse 'SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," ENGINE=MyISAM;") FROM information_schema.tables WHERE table_schema NOT IN ("mysql","information_schema","performance_schema") AND Engine = "InnoDB";' | mysql -Bs

Note that converting tables from InnoDB to MyISAM can also fail if the tables use features not supported by MyISAM. A common example is that MyISAM does not support foreign key constraints; any tables with these will fail to convert to MyISAM.