December 30, 2008

MySQL migration: MyISAM to InnoDB

"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." linux.com

1 comment:

  1. Now, you can do a simple

    ALTER TABLE table_name ENGINE=InnoDB;

    http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html

    ReplyDelete