How To Migrate From MyISAM to InnoDB in MySQL Safely and Easily
By on February 19th, 2012

Since the time that I have run this site, I have had my fights with keeping the website running smoothly. However, over time, I have identified several areas we need to improve on and one of them has been MySQL.

The problem I have had with MySQL is whether to use the MyISAM engine or the InnoDB engine. I have more recently tilted towards using InnoDB more often than not because of the advantages it has for a high traffic website. Though, I won’t go over the advantages in detail here, I might ask you to read my article on why we switched to InnoDB.

Nevertheless, here is a quick and easy tutorial on how to switch from a MyISAM engine to a InnoDB engine safely and easily. You can run these queries on the command prompt or use a GUI like PHP MyAdmin as well. However, you must note that the data in your MyISAM table might not be similar to your new table due to frequent updates and you might want to plan downtime so that you don’t lose data or have to sync the tables again.

So without further ado, here are the steps:

Create a Replica Table

The first step is to create a replica table of the one you want to switch the engines for, for that run the query given below. Make sure to replace the table names as appropriate.

CREATE TABLE new_table LIKE old_table;

Once you have run this query you’ll have a new table which has the same schema as the older table and you are already on your way.

Drop FULLTEXT Indexes in New Table

The biggest difference, if you measure it that way is that InnoDB tables do not support FULLTEXT indexes. So before we do anything else you will have to drop the FULLTEXT indexes from the table you just created. To do that follow the steps given below.

Run the query:

SHOW INDEX from new_table where index_type=’FULLTEXT’;

Drop individual indexes from the query (rename indexname for the index you want to drop):

ALTER TABLE new_table DROP INDEX indexname;

Once you have done that, you are now ready to move your MyISAM table to the InnoDB engine. So lets get ahead with it.

Moving MyISAM Table to InnoDB Engine

The next move is to update the Engine of your MySQL table to InnoDB. For that, you will have to run the following query:

ALTER TABLE new_table ENGINE = InnoDB;

That’s it. You now have the new table in InnoDB format, now you just need to move the data.

Move Data From Old Table to New Table

You will need to move the data from the old table to the new table. To do that, run the following query:

INSERT new_table SELECT * FROM old_table;

And the data is finally there. The last but not the least step is to switch between your older MyISAM table to your new InnoDB table.

Rename Old Table to Backup and New Table to Old

Run the following queries to rename your tables:

Rename Table old_table TO old_table_backup;

Rename Table new_table TO old_table;

That’s it. You have now successfully migrated your MyISAM table to the InnoDB engine while having a backup which you can quickly switch to by renaming the backup table again.

Tags: , ,
Author: Keith Dsouza Google Profile for Keith Dsouza
I am the editor-in-chief and owner of Techie Buzz. I love coding and have contributed to several open source projects in the past. You can know more about me and my projects by visiting my Personal Website. I am also a social networking enthusiast and can be found active on twitter, you can follow Keith on twitter @keithdsouza. You can click on my name to visit my Google+ profile.

Keith Dsouza has written and can be contacted at keith@techie-buzz.com.
  • http://www.wrock.org/ sandeep

    Thanks for tips and Is this really increase site speed? any kind of error in this process ?

  • santhosh

    After the data has been migrated. Shouldn’t the indexes be created again ?

    • http://keithdsouza.com Keith Dsouza

      @santhosh – The indexes will also be switched along with the table, you do not need to recreate them

 
Copyright 2006-2012 Techie Buzz. All Rights Reserved. Our content may not be reproduced on other websites. Content Delivery by MaxCDN