Convert all tables to myisam -- mysql linux

Backup software like Zmanda recommends all  database to be either Myisam or Innodb.
For older versions of mysql MyISAM is faster.
Having innodb and myisam will need to allocate resources for both in my.cnf
So its better to have myisam only..

Howto convert all innodb to myisam ?

Imp:  Make backup of DB before attempting

Step 1:

Delete all foreign key constraints . We dont need them in myisam.



Run in shell

mysql -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';') FROM information_schema.table_constraints where constraint_type='FOREIGN KEY';" >  del-foriegn-keys

mysql -A < del-foriegn-keys





Step 2|:

Convert All Innodb to Myisam

Run in shell

mysql -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') FROM information_schema.tables WHERE engine ='InnoDB';" >  innodb-to-myisam

mysql -A < innodb-to-myisam