MySQL - how to alter tables from MyISAM into InnoDB?

For MySQL and MariaDB databases, it is recommended to use the InnoDB engine (or XtraDb for MariaDB). It is NOT recommended to use the MyISAM engine.

InnoDB:

• Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
• Support for transactions (giving you support for the ACID property)
• Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.
• InnoDB is more resistant to table corruption than MyISAM.
• Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
• MyISAM is stagnant; all future enhancements will be in InnoDB

InnoDB Limitations:

• No full text indexing (Below-5.6 mysql version)
• Cannot be compressed for fast, read-only

The InnoDB storage engine in MySQL.

MyISAM:

• Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
• Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
• Full-text indexing.
• Especially good for read-intensive (select) tables.

MyISAM Limitations:

• Table-level locking
• No foreign keys and cascading deletes/updates
• No transactional integrity (ACID compliance)
• No rollback abilities
• Row limit of 4,284,867,296 rows (232)
• Maximum of 64 indexes per row

The MyISAM storage engine in MySQL.

Before alter tables is neccessary to dump databases!
# mysqldump -u root -p  accounts > accounts.sql

Using following SQL query you can check engine type, in this case Accounts database.

MySQL> show table status from accounts;

 

Now you can see all tables which have to be altered. You can achieve this goal by following SQL commands.

MySQL> ALTER TABLE accounts.aliases ENGINE=InnoDB;
MySQL> ALTER TABLE accounts.domains ENGINE=InnoDB;
MySQL> ALTER TABLE accounts.metadata ENGINE=InnoDB;
MySQL> ALTER TABLE accounts.useraccess engine=InnoDB;
MySQL> ALTER TABLE accounts.users ENGINE=InnoDB;

All tables are now using InnoDB storage engine. You can check it by following SQL query.

MySQL> show table status from accounts;

 

In case of needed you can change storage engine to InnoDB on other databases such as groupware, anti-spam, directory cache, active sync, webclient cache. Procedure is very similar as on example above.