How to Convert MyISAM to InnoDB in MySQL

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 Wordpress 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.

Instructions

1

Back up your database using your usual backup method, such as the mysqldump tool.

2

Open the command prompt / terminal window on your computer and navigate to your MySQL directory.

3

From the command prompt, stop your MySQL database process. For example, on Microsoft Windows you would type: "net stop mysql" and press "Enter."

4

Convert each table from MyISAM to InnoDB by executing the following SQL expression on your database in your command window:

mysql -u [USER_NAME] -p [DATABASE_NAME] ALTER TABLE TABLE_NAME ENGINE=INNODB;

Replace the values with those relevant to your database. Each table will be converted to InnoDB. If you have many tables to convert, it may be quicker to write a script to convert all tables in one batch.

5

From the command prompt, restart your MySQL database process. For example, on Microsoft Windows you would type: "net start mysql" and press "Enter." Check each table to confirm the conversion was successful before using your database.