CODEWITHSUNDEEP
mysqlinnodbmyisam
Nikola
Nikola

Reputation: 133

migrate MyISAM table to InnoDB on live site

How should I convert MyISAM table to InnoDB on live working site in real time? Should I just change in phpmyadmin in operations menu "Storage Engine" to InnoDB? Would it lock all table during conversion?

Upvotes: 1

Views: 794

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53870

You will indeed get a write lock on the table. You will still be able to read from the table while the new table is being created and copied over.

From the documentation on ALTER TABLE:

While ALTER TABLE is executing, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. ...

The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to install a new version of the table .frm file, discard the old file, and clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads (and writes).

Upvotes: 2

akuzminsky
akuzminsky

Reputation: 2257

Check out pt-online-schema-change. See example in this answer https://dba.stackexchange.com/questions/60570/best-way-to-add-a-new-column-to-a-large-table-mysql-myisam/60576#60576

It will block a table for a second or so to rename two tables.

Upvotes: 1

blue
blue

Reputation: 1949

While it might work you'd better make a copy of your table and try the storage engine change operation on the copy.

That would also give you an estimate of time needed for the operation.

Even better would be if you do that on a copy of your application and make extensive checks whether it would work at all with the new storage engine.

Upvotes: 0

Related Questions