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
0) Backup your database. You should probably be doing this already. Now’s a good time to make sure that your backups ran.
1) Create the script. You’ll need the correct permissions to query the database. Here’s the command. Be sure to change <DATABASE_NAME> as it fits.
Linux$ mysql -p -e "show tables in <DATABASE_NAME>;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql
2) Run the script.
Linux $ mysql --database=<DATABASE_NAME> -p < alter_table.sql
3) 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 <DATABASE_NAME>” 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.
Tags: innodb, myISAM, MySQL alter table