Convert MySQL database from MyISAM to InnoDB

In MySQL, you have a command to convert a table from MyISAM engine to InnoDB engine.

ALTER TABLE table_name ENGINE = InnoDB;
However, this command needs to be done for EVERY table, so hereís a way to do it by scripting if you want to do the entire database 🙂

1) Backup your database
2) Create the script (change DATABASE_NAME by yours):

mysql -p -e "show tables in <DATABASE_NAME>;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql

3) Check the file alter_table.sql to see if it looks correct
4) Run the script:

mysql –database= -p < alter_table.sql
5) Verify:

mysql> show table status;

Share this post: on Twitter on Facebook on Google+