MySQL migration: 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:

Dump the database with mysqldump

The first step is to dump the existing database using the mysqldump utility. The dump provides a complete backup of the database in case something goes wrong, and is also used to restore it later in the InnoDB format. Make sure the application is not in use while performing the conversion.

Here is syntax I use:

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

Change the user and password as needed for your database. The --add-drop-table option generates the SQL instructions to create all the tables. Change the name db1 to the name of your database. The output of the dump is an ASCII file with SQL commands to rebuild the database from scratch. The output is redirected and stored in file db1.sql.

Change TYPE=ISAM to TYPE=INNODB

The second step is to edit the db1.sql dump file with a text editor and change the table type to InnoDB. Make of copy of the dump file before you edit it in case you need to restore it later. Here is a sample table definition:


CREATE TABLE audience_def (
AUDIENCE_NO int(10) unsigned NOT NULL auto_increment,
DESCRIPTION varchar(150) default NULL,
STATUS varchar(10) default NULL,
PRIMARY KEY (AUDIENCE_NO)
) TYPE=ISAM;


For each table definition in the dump file, change the TYPE=ISAM to
TYPE=INNODB. If your database is very large, the dump file may be too large to fit in your text editor. If so, you can use a batch editor like sed to make the changes.

To vastly increase the speed of the reload, add the SQL command SET AUTOCOMMIT = 0; to the beginning of the dump file, and add the COMMIT; command to the end. By default, autocommit is on, meaning that each and every insert command in the dump file will be treated as a separate transaction and written to disk before the next one is started. If you don’t add these commands, reloading a large database into InnoDB can take many hours.

Add entries to/etc/my.cnf and restart MySQL

If MySQL is already configured to support InnoDB on your system, skip this step. Some distributions come with MySQL packages that are not configured to use the InnoDB storage engine, and some may require an extra package to be installed to support InnoDB. Check the documentation of your distribution to be sure.

A few extra entries are needed in the MySQL configuration file,/etc/my.cnf, to support InnoDB. For a basic configuration, add these settings under the [mysqld] group of settings in/etc/my.cnf:


[mysqld]
# InnoDB settings
innodb_data_home_dir =/var/lib/mysql/
innodb_data_file_path = ibdata1:100M:autoextend
set-variable = innodb_buffer_pool_size=100M
set-variable = innodb_additional_mem_pool_size=10M
innodb_flush_log_at_trx_commit=1


The innodb_data_home_dir setting defines the location where InnoDB should create data files. The innodb_data_file_path setting defines the name of the data files. In this case, it will create a 100MB data file called ibdata1 and will extend the size as needed. A data file in InnoDB parlance is a tablespace.

Next, restart the MySQL service. To see all the startup messages, you may want to start it from the command line instead of using the normal startup script. The first time you start MySQL with InnoDB support, it will take a lot longer to start, because it has to create the InnoDB data files (tablespaces) and transaction log files and initialize everything. After the first successful start, future restarts happen quickly.

After a successful start, you should see files with names like these in your/var/lib/mysql directory:


admin@linux01 [/var/lib/mysql]$ ls -l
-rw-rw---- 1 mysql mysql 104857600 Jul 4 11:13 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Jul 4 11:13 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jul 4 11:13 ib_logfile1


The data file is ibdata1. The transaction log files are ib_logfile0 and ib_logfile1.

Load the database with mysql

The final step is to drop the old database and reload it using the dump file. I prefer to log in to the database and issue a drop db1; command to delete it. To reload it in InnoDB format, simply feed the dump file back into MySQL with the command
mysql --user=user --password=password.

Once that command completes, you should have a fully functional InnoDB database. Your application should not require any changes to work with InnoDB because the storage engine is isolated from the application code.

SQL wrap up

Now that your database is converted to InnoDB, you can take advantage of advanced features in your application. There were two places in my PHP application where the SQL insert statements needed to be executed atomically to ensure the data remained consistent. In those two places, I wrapped the SQL statements with the commands to start and stop a transaction. I added the SQL command BEGIN; where I wanted the transaction to start, and COMMIT; where I wanted the transaction to end. All the complicated details of the transaction are handled by InnoDB.

Using the InnoDB storage engine in MySQL is relatively easy, but it does come with a price. The extra features in InnoDB require more resources in terms of CPU, memory, and disk space. After conversion to InnoDB, the database in my application used triple the disk space it did as MyISAM. In addition, because multiple databases are stored in the same data file, backups and restores may be more complicated.