MyISAM to InnoDB Engine Conversion

We are doing lots of MyISAM to InnoDB migrations in our production environment and since the engine conversion needs to be done for each table, its good to generate a script to do so when you have huge number of databases each having several tables. Here is the quick script to generate script for MyISAM to InnoDB engine conversion.

mysql -u <user> -p -e "SELECT concat('ALTER TABLE \`',TABLE_SCHEMA,'\`.\`',TABLE_NAME,'\` ENGINE=InnoDB;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA in ('database1', 'database2', 'databaseN') AND ENGINE = 'MyISAM' AND TABLE_TYPE='BASE TABLE'" | tail -n+2 > alter.sql

Once the SQL script is generated, all you need to do is run the sql file to your database server.

$ mysql -u <user> -p < alter.sql

Note that while InnoDB is generally the better engine than MyISAM and MySQL has InnoDB as default engine since 5.5, MyISAM has its own benefits and you should make performance analysis in preferably a test environment while converting the engine type.


Read more...