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.