How to upgrade the engine of your databases in MySQL

Overview

Upgrading your MySQL on linux is fairly easy as long as you follow the proper steps
This exemple will take the change to InnoDB as exemple

Backup your database

The first step is to backup Your environment including the my.cnf usually located in /etc

cp /etc/my.cnf /etc/my.cnf.yyymmdd
service mysql stop
cd /backup
cp -Rp /var/lib/mysql .
service mysql start

To backup your databases you can also use my ksh script from one of my posts from my blog

Upgrade each database

the second step is to run the following command for each instance except mysql and the other system databases.

echo 'SHOW TABLES;' \
| mysql -uroot --password=your_password -D your_database \
| awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
| column -t \
| mysql -uroot --password=your_password -D your_database 

Check all the tables in the database

The last step is to validate that all your tables for each database are now InnoDB.
I prefer the second option that is much easier to run

mysql -uroot --password=your_password -D your_database

show table engine;

or

You can use mysqlcheck to check all tables on the database.

mysqlcheck -c your_database  -uroot  -pyour_password

Set InnoDB as the default engine

If you want to set innodb as the default engine you can do it by adding the following parameter in you my.cnf in the [mysqld] section

default-storage-engine=innodb