Convert MySQL DB from InnoDB to MyISAM and vice versa

[cvrle77]

To convert MySQL database from InnoDB to MyISAM, use these 3 simple console commands

# navigate to tmp folder 

cd /tmp

# change username for db username and db_name for database name you are converting


mysql -u [U]username[/U] -p -e "SHOW TABLES IN [U]db_name[/U];" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=[B][COLOR=#ff0000]MYISAM[/COLOR][/B];" > alter_table.sql
 

# run queries

mysql -u [U]username[/U] -p [U]db_name[/U] < alter_table.sql

To change MyISAM to InnoDB, instead of MYISAM word with INNODB, rest of the procedure is same.

* parts starting with # are just comments

 

[Tango]

You can use phpmyadmin to alter tables as well

Click on the table you want to change storage engine on, then Operations, then in the drop down box select InnoDB.
If the table is huge the page may time out but the query will still be running, don't run another query until you are sure it has completed.