Converting from MyISAM engine to InnoDB engine in MySQL 5.5

Converting from MyISAM engine to InnoDB engine.

MySQL is a relational database management system, or RDBMS, used for storing small and large quantities of data. It is commonly used as the back-end database to a website. Websites based on content management systems such as Word press or Drupal use databases to store and manage their content.

Tables within a MySQL database are stored using a particular storage engine format. MySQL used to default to using MyISAM but now uses InnoDB instead. InnoDB is more efficient and faster than MyISAM.

  

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.

                  Conversion Methods 

 Method-I:=

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;

For converting all tables in all database you need to use below SQL statement and script.

SELECT 

CONCAT('ALTER TABLE ',table_schema,'.',table_name,

' ORDER BY primary_key_column; 

ALTER TABLE ',table_schema,'.',table_name,' engine=InnoDB;')

FROM information_schema.tables 

WHERE TABLE_SCHEMA='my_database' 

AND ENGINE = 'MyISAM';

Method-II:=

Using MySQL dump

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.

$$**************************Posted by Shishir Tekade****************************$$