CODEWITHSUNDEEP
mysql
Andrew
Andrew

Reputation: 238727

How to change the storage engine type on MySQL?

I would like to use InnoDB as the storage engine on all my tables and databases. Is there a command I can run to change the type of my current tables to use InnoDB instead of MyISAM?

Also, is there a way to set this as the default so I don't have to do this again?

Upvotes: 12

Views: 17096

Answers (4)

amar pathak
amar pathak

Reputation: 11

Today in latest versions of MySQL DB, by default InnoDB comes as default DB Engine. We can check by : (in CMD)

mysql> show engines;

or

mysql> select * from information_schema.engines;

Upvotes: 0

ClearCrescendo
ClearCrescendo

Reputation: 1155

If you use are using the mysql workbench there is a script menu option to change the engine of all tables.

Select Plugins > Utilities > Change storage engine of all all tables.

Upvotes: 2

Pascal Thivent
Pascal Thivent

Reputation: 570365

To change the storage engine of an existing table:

ALTER TABLE my_table ENGINE = InnoDB;

To set InnoDB as default:

Normally, this is MyISAM, but you can change it by using the --default-storage-engine or --default-table-type server startup option, or by setting the default-storage-engine or default-table-type option in the my.cnf configuration file.

Upvotes: 20

Matt
Matt

Reputation: 44058

The following article will tell you how to set the default storage engine, how to explicitly use a specific engine during a CREATE TABLE command, and how to change the storage engine of existing tables:

http://dev.mysql.com/doc/refman/5.1/en/storage-engine-setting.html

Upvotes: 5

Related Questions