- Create a new table with the same schema as the old table (CREATE TABLE new SELECT * FROM OLD LIMIT 0)
- Convert the new table to InnoDB (ALTER TABLE new TYPE=INNODB)
- Add your primary key and ancillary indexes (ALTER TABLE new ADD PRIMARY KEY (...))
- Wherever your client code makes inserts into the old table, ALSO do a replace into the new one. Your application will be inserting rows into both tables.
- Incrementally copy data from the old table to the new one. (REPLACE INTO new SELECT * FROM OLD WHERE somekey>300 AND somekey<400)
- Update your application to read from the new table. You can stop updating the old table, too.
How to convert from MyISAM to InnoDB on a live system
Converting a large MySQL table from MyISAM to InnoDB format can be a lengthy process. Actually, there are many operations such as reporting that will read-lock your tables. The general solution is to slowly build yourself a new table.
No replies - reply