Converting mysql tables engines

Sometimes you may need to convert your mysql table engines.
One of the most common reason to convert tables to InnoDB is for better reliability and scalability

Quick Links:
Viewing Table Engines
Viewing Active Engines
Alter Table Command


Viewing Current Table Engines

To view your current mysql engines use the following command:

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'database_name';

This will show you a list similar to:

+------------------------+--------+
| TABLE_NAME | ENGINE |
+------------------------+--------+
| wp_commentmeta | InnoDB |
| wp_comments | InnoDB |
| wp_links | InnoDB |
| wp_options | InnoDB |
| wp_popularpostsdata | InnoDB |
| wp_popularpostssummary | InnoDB |
| wp_postmeta | InnoDB |
| wp_posts | InnoDB |
| wp_term_relationships | InnoDB |
| wp_term_taxonomy | InnoDB |
| wp_terms | InnoDB |
| wp_usermeta | InnoDB |
| wp_users | InnoDB |
+------------------------+--------+
13 rows in set (0.00 sec)


Checking Active Engines

Now we need to check that the engine is enabled

 

mysql> show engines;

+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                          | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO | NO | NO |
| CSV        | YES     | CSV storage engine                                         | NO | NO | NO |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO | NO | NO |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

Altering Table Engines
Now we can use and alter the following command.
This command will produce an output of all of the tables with a specific alter command needed to run to alert the engine:

Note: You need to change the following variables depending on your intended outcome:

  • ENGINE=InnoDB; (change to the engine you wish all tables to be changed to)
  • AND ENGINE = ‘MyISAM’ (change to the value of the current engine you wish to¬†edit
  • Change the name of the file you wish to store the commands in (alter_tables_database.sql)

Now run this from the command line to convert all MyISAM table engines to InnoDB:

 

mysql -e "SELECT concat('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'database_name(replace me)' AND ENGINE = 'MyISAM' AND TABLE_TYPE='BASE TABLE'" | tail -n+2 > alter_tables_database.sql

 

You should get an output of the file should look similar to similar to:

# cat alter_tables_database.sql

ALTER TABLE table_engine.wp_comments engine=InnoDB;
ALTER TABLE table_engine.wp_links engine=InnoDB;
ALTER TABLE table_engine.wp_options engine=InnoDB;
ALTER TABLE table_engine.wp_popularpostsdata engine=InnoDB;
ALTER TABLE table_engine.wp_popularpostssummary engine=InnoDB;
ALTER TABLE table_engine.wp_postmeta engine=InnoDB;
ALTER TABLE table_engine.wp_posts engine=InnoDB;
ALTER TABLE table_engine.wp_term_relationships engine=InnoDB;
ALTER TABLE table_engine.wp_term_taxonomy engine=InnoDB;
ALTER TABLE table_engine.wp_terms engine=InnoDB;
ALTER TABLE table_engine.wp_usermeta engine=InnoDB;
ALTER TABLE table_engine.wp_users engine=InnoDB;

Running Table Alter
You can now enter mysql and run all of these statements.
They will change each table 1 by one

You can simply copy and paste the commands found