Move MySQL tables and data from storage engine MyISAM to InnoDB

Question

This question is probably for MySQL experts and admins that have done this sort of migration before.

I have 17 MySQL tables, triggers and stored procedures on MyISAM storage engine. These tables have around 8 MiB data combined. Since I am moving the application and database to Amazon EC2 and RDS I was wondering what are the key considerations when migrating tables from MyISAM to InnoDB.

The steps that I will be going through are pretty much as follows.

1. System Tables on AWS RDS has to be on MyISAM so no contest on that.
2. Create 17 tables with the same structure as they exist on MyISAM and build them in RDS InnoDB.
3. Export Data from the MyISAM tables and import them in to the new tables created in RDS on InnoDB.
4. Create the Triggers, Stored Procedures and Functions in the InnoDB database.

When I ask about key considerations of a migration like this across MySQL storage engines I want to know from the point of view of experienced admins if something explicitly needs to be taken care of at the DB level during the data migration etc so that the DB behaves the way it should and there are no glitches. I am worried mainly because I read that InnoDB and MyISAM's way of buffering, I/O, CPU and Memory usage are different.

I will happily accept your 2 cents on this process.

Thanks.

*Update*

innodb_buffer_pool_size - {DBInstanceClassMemory*3/4} This is what it is set to right now.

I am looking for other parameters as well. Let me know if you want to see values set for any other specific parameters.

Answer 1

There are many differences between MyISAM and InnoDB but the main points that you should be aware of before the migration., 1. Data backups cannot be done by simply copying over files as in MyISAM 2. InnoDB does not work in an optimized way when run with default options, you will have to configure and tune according to your needs,. 3. InnoDB does not have compressed indexes like MyISAM so its going to take more space 4. InnoDB automatically appends primary key columns to secondary keys,. so make sure primary key columns are not large.,

Apart from that migration should be easy,. 1. Create tables with similar structures as the MyISAM but only with the engine changed to InnoDB. 2. Dump the data from the MyISAM table 3. Import the data in primary key order in the InnoDB engine wrapping the import between START TRANSACTION and COMMIT., this is the fastest way to load data,. 4. Create the stored procedures and trigger.,. but they have nothing to do with the storage engine,.

You can let me know if you need any help with the InnoDB configuration,.

Answer 2

If you want to convert your current tables to INNODB, here's the easy way:

ALTER TABLE t1 ENGINE = InnoDB;

See the MySQL doc.

Doing this on your own servers before you go to the cloud is a great idea - don't change two parameters at the same time!

There are lot of things that differ in the InnoDB engine, but I'm sure a few minutes with your favorite search engine will show you that.

Answer 3

The migration process itself is very easy. Dump will record all necessary objects that need to be reconstructed. You'll only need to remove (or change, if default is not what you want), storage engine clauses. What you may have to worry about is that applications written for MyISAM may assume there is never need to commit transactions. So anyway, few years ago I got away with this:

mysqldump --all-databases | perl -pwe 's/\) TYPE=MyISAM/\)/' >dump.sql
mysql <dump.sql

plus adding commits and rollbacks where necessary.