Thursday, July 21 2005 @ 02:58 AM EDT Contributed by: glosser
NewsForge explores the configurability of MySQL with this tutorial on coverting from MyISAM to InnoDB.
The MySQL database is unique in that it offers multiple storage engines. The SQL parser and front end interfaces are separate from the storage engines, so you can choose among nine low-level table formats the one that suits your application best. I recently needed to convert a production application from the default indexed sequential format, MyISAM, to InnoDB. Here's my no-hassle guide to performing the conversion.
Why would you want to convert an existing MySQL database from MyISAM to InnoDB? While the MyISAM format has low overhead and generally the fastest performance among MySQL storage engines, it does not have advanced features like transactions, rollbacks, and row-level locking. InnoDB has these features and is also fully ACID-compliant (atomicity, consistency, isolation, and durability). ACID compliance is one of the touchstones of high-end database systems. I needed these features to solve my problem.
One of the applications I inherited is a course registration system using the ubiquitous LAMP architecture (Linux, Apache, MySQL, PHP) and the default MyISAM table format. During peak usage (about 100 simultaneous users) some of the records added to the system were incorrectly linked with other records in related tables. The problem was that no locking was done on the database, leading to some SQL insert commands being executed out of order. While the MyISAM engine provides table locking to simulate transactions, that wasn't good enough for such a heavily used application. I needed the row-level locking and ACID transaction support of InnoDB. Here are the steps I took to convert a MySQL database from MyISAM to InnoDB:
