AmitBiswas.com

Converting from MyISAM to InnoDB in MySQL

November 10, 2010
Category: Database, MySQL | 1 Comment

MySQL logoRecently i had to convert a MySQL database from the MyISAM engine to the InnoDB engine. Although it looks like a complicated job but it is easier than you think.

First, some background information

One of the distinguishing features of MySQL is its ability to support multiple storage engines. Since each storage engine comes with its own set of features, strengths, and tradeoffs, the ability to use/switch between engines gives flexibility to the database designer.

InnoDB

According to MySQL 5.5 reference manual, the InnoDB is a transaction-safe (ACID compliant) storage engine that has commit, rollback, and crash-recovery capabilities. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys.
InnoDB is the default storage engine as of MySQL 5.5.5.

MyISAM

 According to MySQL 5.5 reference manual, MyISAM is the storage engine that is used the most in Web, data warehousing, and other application environments. MyISAM is supported in all MySQL configurations, and is the default storage engine prior to MySQL 5.5.5.

Why convert?

Although MyISAM has its own set of advantages over InnoDB, but one of the most common reasons to convert from MyISAM to InnoDB is its lack of advanced features like transactions, rollbacks, and row-level locking.  Another common reason is to maintain compatibility with existing databases in your application.

Converting from MyISAM engine to InnoDB engine in MySQL

Using ALTER TABLE

One way of doing this conversion is to use ALTER TABLE to change the database engine.

ALTER TABLE table_name ENGINE = InnoDB;

If the table is large, this may take some time and will consume fair amounts of CPU. You can speed up things slightly by arranging the database before converting it so that the primary key column is in order.

ALTER TABLE tablename ORDER BY 'primary_key_column';

So, to sum it up, here is what you do:

ALTER TABLE tablename ORDER BY 'primary_key_column';
ALTER TABLE table_name ENGINE = InnoDB;

Using mysqldump

Another quick-hack-hassle-free way of doing this is to dump the database into a .sql file, then edit the file and change the engine to InnoDB, then restore the dump to a database.

Use mysqldump to dump the MyISAM database:

mysqldump --user=username --password=password --add-drop-table --databases MyISAMdb >MyISAMdb.sql

This will create a dump named MyISAMdb.sql. Open this file in a text editor, look for table definitions and change the table type to InnoDB.

Example:

CREATE TABLE table_name (
E_ID int(10) unsigned NOT NULL auto_increment,
FIRST_NAME varchar(50) default NULL,
LAST_NAME varchar(50) default NULL,
PRIMARY KEY (E_ID)
) TYPE=ISAM;

Change TYPE=ISAM to TYPE=INNODB

CREATE TABLE table_name (
E_ID int(10) unsigned NOT NULL auto_increment,
FIRST_NAME varchar(50) default NULL,
LAST_NAME varchar(50) default NULL,
PRIMARY KEY (E_ID)
) TYPE=INNODB;

Now restore the dump

To increase the speed of restoring, add the SQL command SET AUTOCOMMIT = 0; to the beginning of the dump file, and add the COMMIT; command to the end. When Autocommit is on every insert statement is executed and written on the disk before starting with the next statement. This adds extra disk IO and slows down the whole process.

You can also disable keys before restoring and re-enable it again after the restoration. This will also speed up the process.

Comments

One Response to “Converting from MyISAM to InnoDB in MySQL”

  1. Vjt on January 25th, 2011 4:28 PM

    Thanks for some quality points there. I am kind of new to online , so I printed this off to put in my file, any better way to go about keeping track of it then printing?

Leave a Comment