Migrating to RDS: Converting MyISAM to InnoDB

If you want to leverage the RDS service on AWS, you’ll receive maximum benefit by converting MyISAM tables to InnoDB. Here’s a distillation of a useful approach outlined Another woblag on the Interweb


// Create a backup of your database
mysqldump -u USER -p MYSQLDB | gzip -c > /mnt/backups/mysqldb.sql.gz


// Log in to your mysql instance and dump a .sql to convert tables in batch
mysql> select concat('ALTER TABLE `',table_schema,'`.`',table_name,'` ENGINE=InnoDB;') from information_schema.tables where table_schema='mydb' and ENGINE='MyISAM' into outfile '/tmp/InnoBatchConvert.sql'
mysql> quit
shell> mysql -u root -p < /tmp/InnoBatchConvert.sql


// Confirm tables have been converted to InnoDB
mysql> select table_name, engine from information_schema.tables where table_schema = 'mydb';