originally published on July 19, 2005 at newsforge.com

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. This lets you choose the low level table format that suits your application best.

I recently had the need to convert a production application from MyISAM format to InnoDB. Following is my no-hassle guide to perform these conversions.

Why use InnoDB?

Before getting to the details, why would you want to convert an existing MySQL database from MyISAM to InnoDB? The default storage engine in MySQL is an indexed sequential format, MyISAM. While the MyISAM format has low overhead and generally the fastest performance, 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 particular problem.

MySQL Storage Engines

MySQL 5.0 and higher offers nine storage engines and more are likely to be added in the future. The most commonly used are MyISAM, InnoDB, and Berkeley DB (BDB). Each storage engine offers special features and advantages. You can even use different formats for each table in your database, though it may be harder to manage a mixed format database. I prefer to keep all tables in a database using the same storage engine, but use different engines for different databases. For a complete list, see the official documentation

One of the applications I currently support is a course registration system using the ubiquitous LAMP architecture (Linux, Apache, MySQL, PHP). It was built with the default MyISAM table format. During peak usage, (about 100 simultaneous users), some of the records added to the system are 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, I did not consider it viable for such a heavily used application. What I needed was the row level locking and ACID transaction support of InnoDB.

No-hassle conversions

Here are the steps to convert a MySQL database from MyISAM to InnoDB:

1. Dump the database with mysqldump
2. Change TYPE=ISAM to TYPE=INNODB in dump file
3. Add entries to /etc/my.cnf and restart MySQL (if needed)
4. Load the database with mysql

There are other ways to perform a conversion, but these are easy to understand and have worked well for me.

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 mysqldump 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 your favorite text editor and change the table type to InnoDB. Make of copy of the dump file before editing 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 this SQL command to the beginning of the dump file (without the quotes): "SET AUTOCOMMIT = 0;" and add this SQL command to the end: "COMMIT;". 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. Without these commands, reloading a large database into InnoDB can take many hours.

Add entries to /etc/my.cnf and restart MySQL (if needed)

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. Also, 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 file(s). In this case, it will create a 100 MB 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 start up 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. The reason is it has to create the InnoDB data files (tablespaces), 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. Since the old MyISAM database still exists, we need to delete it. I prefer to login to the database and issue a "drop db1;" command to delete it. To reload it in InnoDB format, we simply have to feed the dump file back into mysql as follows:

mysql --user=user --password=password < db1.sql

Once that command completes, you 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 the database is converted to InnoDB, you can take advantage of the 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 overhead in terms of CPU, memory, and disk space. After conversion to InnoDB, the database in my application used triple the disk space compared to MyISAM. In addition, because multiple databases are stored in the same data file, backups and restores may be more complicated. See of the official MySQL documentation for more details on any topic.

This work is licensed under a Creative Commons Attribution-NonCommercial 2.5 License.