MySQL migration: MyISAM to InnoDB
By Keith Winston on July 18, 2005 (8:00:00 AM)
Share Print Comments
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
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,
[mysqld]
# InnoDB settings
innodb_data_home_dir =
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
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.
Share Print Comments
Related Links
Last 5 articles by this author:
Testing Web application security using Google's ratproxy Jul 29, 2008
Multifunction copiers in a Linux network Mar 13, 2008
Apache authentication and authorization using LDAP Oct 31, 2007
30 days with JFS Sep 14, 2007
Using squidGuard for content filtering Mar 01, 2007
Sponsored links:
Best deals: Technology
Comments
on MySQL migration: MyISAM to InnoDB
Note: Comments are owned by the poster. We are not responsible for their content.
Thanks
Posted by: Anonymous Coward on July 19, 2005 04:07 AM
Very good article. Looks like something I may someday need.
#
additional issues
Posted by: Anonymous Coward on July 19, 2005 02:49 PM
i. there's a COMMIT. where's the ROLLBACK?
ii. how about handling foreign keys?
not nitpicking. just honestly asking how
you handled them, if you did.
#
Re:additional issues
Posted by: Anonymous Coward on July 19, 2005 08:25 PM
i. COMMIT and ROLLBACK always come in pairs. The former would be pointless without the later.
ii. Yes, they're there. That's one of the points for using InnoDB.
Not to nitpick, just honestly asking why you couldn't be bothered to look on the MySQL site before implying FUD.
#
Re:additional issues
Posted by: Anonymous Coward on July 20, 2005 10:11 AM
i was asking about the author's experience
with respect to using mysql and his
corresponding code changes. that's the point of
the article and not strictly mysql usage,
which i could easily have looked up in the their
site.
the COMMIT/ROLLBACK pair's impact on the code
is the point. not their existence.
same with how he handled foerign keys in
the code.
the problem with you mysql churchgoers
is that you identify anything short of an
obvious question as FUD against mysql,
which is not.
go eat some shit, wiseass.
#
Re:additional issues
Posted by: Keith Winston on July 20, 2005 07:42 PM
I considered going into more detail about the changes in the PHP code, but wanted to limit the article to the MySQL conversion mechanics. Otherwise, it would have made the article way too long.
In my case, I added ROLLBACK to each "or die()" statement when calling MySQL from PHP. In my testing, InnoDB will do a rollback automatically if the script fails anywhere between the BEGIN/START and COMMIT, but it is always good form to be explicit.
Since MyISAM doesn't handle foreign keys, the application code already dealt with cascade deletes. It would be more efficient and safer to let InnoDB handle that, but it was beyond the scope of my initial conversion. I may go back and make those changes in the future. Again, I didn't go into foreign keys to keep the article succinct.
Good questions.
#
Overly complex method?
Posted by: ulric on July 19, 2005 06:41 PM
The easy way to convert a myisam table to innodb is:
alter table mytable type=innodb;
Google for "mysql convert myisam to innodb". Or Click here.
#
Rather incomplete
Posted by: Anonymous Coward on July 19, 2005 10:00 PM
Hi,
I'm sorry to say so, but your article is rather incomplete. Someone already said that only one ALTER statement would do the conversion. Your method, though has the benefit of having a backup. Also, as someone said, you forgot to talk about the rollback, that is as important as the commit. In my opinion, an introduction to transactions would have been welcomed.
I think it is incomplete since you failed to enumerate all the benefits of InnoDB and explaining the consequences of such a change.
InnoDB use an innovative lock mechanism, while MyISAM uses table locks. It also implements all four levels of isolation and referential integrity. You can get much better concurrency with InnoDB, since it user row-level locks and a version-control-like mechanism (called MVCC to make sure no action is uselessly stopped by a lock. Of course, this increases the CPU overhead. There are also other differences (indexes, for example).
For the question of disk usage, I am curious to see where you got your numbers? Directly from file sizes? I hope not.
Backups more difficult? Not really, as you can still use mysqldump just like you did with MyISAM tables, but you can't do hot backups without the software sold by InnoDB, and its, in my opinion, reasonably priced (by this, I mean that if you have the volume to need hot backups absolutely, the 1300USD$ for the perpetual licence won't scare you off).
Also, you should have warned the readers that they may loose functionnality when switching to InnoDB. Count()s will be slower and full-text indexing is not supported in InnoDB. One must make sure this will not affect the design or performance ot the application.
#
Question about MyISAM to InnoDB with FULLTEXT
Posted by: ppowell777 on July 19, 2005 10:56 PM
Although I totally jive with the migration from MyISAM to InnoDB, I can see a potential problem with such a migration.
Consider this scenario: I have an application with its own built-in search engine that employs the MyISAM table's native FULLTEXT index types for keyword searches. Conversion of the tables from MyISAM to InnoDB would cause fatal MySQL errors as long as the FULLTEXT indexes are in place, however, removing them would also cause fatal errors with the SELECT MATCH () AGAINST () queries that employ the FULLTEXT indexes.
What would you do in such a situation like this if it were asked to ensure ACID integrity of the table structures via conversion to InnoDB in light of this roadblock?
Thanx
Phil
#
Re:Question about MyISAM to InnoDB with FULLTEXT
Posted by: Keith Winston on July 20, 2005 07:08 PM
AFAIK, you would have to leave the tables that use the FULLTEXT feature as MyISAM. You could convert the other tables to InnoDB. The only other solution I can think of is to move the text search into your application code which would be difficult and probably slower. Each storage engine has advantages and disadvantages. Good luck!
#
Good article, but ...
Posted by: Anonymous Coward on July 24, 2005 04:30 PM
It's a good article, but it shouldn't have been necessary. MySQL has far too many options for its backend; and InnoDB should be the default, because it's the only one that gives you a real database.
There's some really good software in MySQL, but the defaults suck, in general. Somebody who picks the defaults at installation time should get a real database, and as strict compliance with standard SQL as is possible with MySQL.
#
MySQL migration: MyISAM to InnoDB
Posted by: Anonymous [ip: 61.247.255.57] on September 05, 2007 12:53 PM
Types Of MYSQl
#
MySQL migration: MyISAM to InnoDB
Posted by: scruffyboo on September 24, 2007 09:13 AM
Tremendously useful guide, just what i was looking for, thank you!
We have a drupal intranet serving just under 6000 people with about 100 authors, and MyISAM just don't cut the mustard
Thanks again.
#
MySQL migration: MyISAM to InnoDB
Posted by: Anonymous [ip: 203.200.48.59] on December 01, 2007 05:23 PM
Thanks a lot
#
MySQL migration: MyISAM to InnoDB
Posted by: Anonymous [ip: 192.168.11.130] on January 17, 2008 09:06 AM
I have followed this steps and I does not have encountered any errors, but when I dump again the migrated database (w/ type=InnoDB) then, when I was checking the dumped file the type I saw again was type=MyISAM instead of TYPE=InnoDB..... Please help me, what's should I do?...
#
MySQL migration: MyISAM to InnoDB
Posted by: Anonymous [ip: 85.5.150.27] on February 17, 2008 08:13 PM
thank you! did the trick for me. save me a lot of time.
#
MySQL migration: MyISAM to InnoDB
Posted by: Anonymous [ip: 85.221.230.230] on February 22, 2008 12:10 PM
However it's helpful guide please consider the fact, that your foreign keys declarations will be lost. MyISAM ignores F-keys declarations upon creation of tables and if you haven't original database schema, you're probably lost. That's pity.
#
MySQL migration: MyISAM to InnoDB
Posted by: Anonymous [ip: 72.10.194.1] on March 04, 2008 07:23 PM
I'm going to try it - but like phil above - i have a full text index used for searches. I think instead of this I will be moving the fulltext search to another table.
#
1 comment:
I don't think I understood any of that.
(Anonymous IP)
Post a Comment