x
dustball
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
 
Calendar

August 2008
12
3456789
10111213141516
17181920212223
24252627282930
31

June 2008
1234567
891011121314
15161718192021
22232425262728
2930

November 2007
123
45678910
11121314151617
18192021222324
252627282930


Older

Recent Visitors

August 20th
underground1986

August 19th
google
ozzie777

August 18th
hauntedwhisper
iliketiedye
google
Smurfy

August 17th
google

August 16th
google

August 15th
google
valbah

August 14th
askjesse