Todd Harris, PhD

Facilitating scientific discovery at the intersection of genetics, genomics, bioinformatics, big data, cloud computing, and open science.

  • About

Migrating to RDS: Converting MyISAM to InnoDB

December 2, 2013 By Todd Harris Leave a Comment

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';

Share this:

  • Twitter
  • Facebook
  • LinkedIn

Filed Under: cloud Tagged With: AWS, innodb, mysql

Welcome!
My name is Todd Harris. A geneticist by training, I now work at the intersection of biology and computer science developing tools and systems to organize, visualize, and query large-scale genomic data across a variety of organisms.

I'm driven by the desire to accelerate the pace of scientific discovery and to improve the transparency and reproducibility of the scientific process.

Stay in touch!

Enter your address to receive notifications of new posts by email.

Join 1,296 other subscribers

Copyright © 2023 · Genesis Sample Theme on Genesis Framework · WordPress · Log in