Mysql Engine Swap MyISam to InnoDB and InnoDB to MyISam

To convert all tables in a database from InnoDB to MyISAM, run the MySQL following command, replacing db_name with the database name in question:


mysql -e "SELECT concat('ALTER TABLE ', TABLE_NAME,' ENGINE=MYISAM;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'"

Then, after stopping MySQL, you want to get the ibdata* and ib_logfiles out of the way:

Code:
/etc/init.d/mysql stop
mkdir -p /root/innodb.bak
mv ib* /root/innodb.bak/
/etc/init.d/mysql start

Now you've got MySQL started up with the tables using MyISAM, and it's time to get them converted back to InnoDB, fingers crossed (again replace db_name with your database name):

mysql -e "SELECT concat('ALTER TABLE ', TABLE_NAME,' ENGINE=InnoDB;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'MyISAM'"