How to convert from MyISAM to InnoDB on a live system
Tags: mysql
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.

  1. Create a new table with the same schema as the old table (CREATE TABLE new SELECT * FROM OLD LIMIT 0)
  2. Convert the new table to InnoDB (ALTER TABLE new TYPE=INNODB)
  3. Add your primary key and ancillary indexes (ALTER TABLE new ADD PRIMARY KEY (...))
  4. 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.
  5. Incrementally copy data from the old table to the new one.  (REPLACE INTO new SELECT * FROM OLD WHERE somekey>300 AND somekey<400)
  6. Update your application to read from the new table.  You can stop updating the old table, too.

No replies - reply