From technotes.twosmallcoins.com
How to Convert MySQL from MyISAM to InnoDB Using a Script
Okay. So I need to convert MySQL database using the default MyISAM engine to the InnoDB engine. The command is simple:
ALTER TABLE table_name ENGINE = InnoDB;
However, this command needs to be done for EVERY table. Yes, it’s a pain in the rear… so here’s a way to do it by scripting.
Scripting a MySQL InnoDB Engine Conversion:
1) Backup your database. You should probably be doing this already. Now’s a good time to make sure that your backups ran.
2) Create the script. You’ll need the correct permissions to query the database. Here’s the command. Be sure to change as it fits.
mysql -p -e "show tables in
3) Run the script.
mysql --database=
Verify it by running this command in mysql:
mysql> show table status;
Discussion (If You’re Interested)
The script is simply a bunch of ALTER TABLE commands for each of your tables in the database of question.
The mysql -p -e “show tables in ” command gets a list of the tables. This list has a header that looks like “Tables_in_DATABASE”. The tail command drops that first header line. Now you have a clean list of tables. The "xargs -i echo" command creates the ALTER TABLE command, inserting the table name where the squiggly braces {} are placed. It’s now stored in alter_table.sql.
Step two simply runs the script that we stored in alter_table. Both mysql commands will prompt for a password, based on the -p flag.
