Converting MyISAM tables over to InnoDB comes with several benefits such as row level locking, being able to commit and roll back transactions and using tractional logs for autorecovery.
While converting MyISAM over to InnoDB is usually straight forward, you must use caution:
- Always create a backup of the database before making any changes! - Confirm you are running MySQL 5.6.4 or above as that was when full-text indexing was introduced to InnoDB.
You can check to see what tables are using MyISAM in your database by running the following:
[root@db01 ~]# mysql mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS FROM information_schema.TABLES WHERE ENGINE = 'MyISAM' and TABLE_ROWS +--------------+-----------------------+--------+------------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | TABLE_ROWS | +--------------+-----------------------+--------+------------+ | mysql | db | MyISAM | 3 | | mysql | help_category | MyISAM | 39 | | mysql | help_keyword | MyISAM | 464 | | mysql | help_relation | MyISAM | 1028 | | mysql | help_topic | MyISAM | 508 | | mysql | proxies_priv | MyISAM | 2 | | mysql | user | MyISAM | 5 | | example01 | wp_commentmeta | MyISAM | 15 | | example01 | wp_comments | MyISAM | 72 | | example01 | wp_links | MyISAM | 11 | | example01 | wp_options | MyISAM | 224 | | example01 | wp_postmeta | MyISAM | 260 | | example01 | wp_posts | MyISAM | 277 | | example01 | wp_term_relationships | MyISAM | 178 | | example01 | wp_term_taxonomy | MyISAM | 62 | | example01 | wp_terms | MyISAM | 62 | | example01 | wp_usermeta | MyISAM | 22 | | example01 | wp_users | MyISAM | 1 | +--------------+-----------------------+--------+------------+
Alternatively, if you wanted to see MyISAM tables that have more than 5,000 rows, run:
mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS FROM information_schema.TABLES WHERE ENGINE = 'myISAM' and TABLE_ROWS > 5000; +--------------+---------------------+--------+------------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | TABLE_ROWS | +--------------+---------------------+--------+------------+ | example02 | employees | MyISAM | 80257 | | example02 | templates | MyISAM | 155341 | | example02 | zipcodes | MyISAM | 33178 | | example02 | providers | MyISAM | 34833 | | example02 | portal | MyISAM | 25184 | +--------------+---------------------+--------+------------+
Converting the tables is pretty simple, but keep in mind that this will lock the table while it is running. Make sure you backup your database BEFORE converting any tables! Things can go wrong at the worst times, so be sure to have a solid roll back plan before proceeding!
Convert the desired table from MyISAM to InnoDB by running:
[root@db01 ~]# mysql mysql> use example02; mysql> ALTER TABLE providers ENGINE=InnoDB;