Converting live MyISAM table to InnoDB I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of converting the table's storage engine from MyISAM -> InnoDB. Below is what I'm planning to do based on information I could gather from other posts. Please tell me if I'm missing anything.


mysqldump -u -p --lock-tables=false mydb tracking &gt; tracking.sql - from what I understand this should not lock "tracking" table
Rename table name in the sql file to tracking_new
mysql -u -p mydb &lt; tracking.sql
ALTER TABLE mydb.tracking_new ENGINE=InnoDB;
Insert new rows to "tracking_new" table that were written to the original table while performing dump

SELECT @last_id := tracking.id FROM mydb.tracking_new ORDER BY tracking.id DESC LIMIT 1;    
INSERT INTO mydb.tracking_new
SELECT * FROM mydb.tracking WHERE tracking.id &gt; @last_id;



Rename tables

RENAME TABLE 
    mydb.tracking TO mydb.tracking_old, 
    mydb.tracking_new TO mydb.tracking;
Assuming you don't have any triggers on that table, the simplest way to do this without locking the table is to use pt-online-schema-change.

Make sure you have enough extra disk space to accommodate this change. The table will be larger when you convert it to InnoDB, and you need to maintain both copies of the table during the conversion. For example, if your table is 100 GB make sure you have at least 200+ GB of free disk space.

You should test this out in a non-production environment first to make sure it works for you and to find out exactly how much disk space you need.

The syntax for your case is quite simple.

First do a dry run to verify that it will work:

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=tracking --dry-run


Then execute it:

pt-online-schema-change --alter "ENGINE=InnoDB" D=mydb,t=tracking --execute