MySQL supports several different types of Table Engines also known as "Table Types". A database can have its tables being a mix of different table engine types or all of the same type. Here is more information on each of the different types of table engines that MySQL offers:
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html
There's pros and cons between this two table engines, but that's not what I wanted to discuss here.
Sometimes, when you work with MySql database, later on you relize that you using the wrong table engines for some or all tables, if it's only 1-10 tables then it's easy, but how if your database contains 30-50 tables? and only some of them using different table engine? That is tricky.
Well you can always back up the database and restore them to the correct table engines, but that will cost you more time, more over if you have huge database.
So, how to do it? First of all, you have to familiarize yourself to phpmyadmin, coz it's easiest way to do it.
It's actually 3 step of work
1. Make sure you already select the correct database
2. Select list all table with MyISAM table engine using this query
SELECT CONCAT('ALTER TABLE ',table_name,' engine=InnoDB;')
FROM information_schema.tables
WHERE engine = 'MyISAM';
The query result will looks like:
ALTER TABLE users engine=InnoDB;
ALTER TABLE users_group engine=InnoDB;
ALTER TABLE users_images engine=InnoDB;
....
The query will list all table with MyISAM engine tables, while doing so, it also create queries to alter those tables. The output should be lists of ALTER queries with tables name on it, now you have all queries needed to convert only MyISAM tables engine.
3. Copy all query output it produce and run those query at once ... tada... all MyISAM tables on your database has been converted to InnoDB.
Now the safest way to do it is using this step:
1. Backup your database, always backup your database!
2. If you have enough space on your harddrive copy those database to database_backup, make sure you have the same user privilege as your production database
3. Run those query above, check if all tables converted successfully
4. If something goes wrong, rename database_backup to database, make sure you have the same user privilege or restore database from backup
If you needed to convert from InnoDB to MyISAM, do the same thing, just switch 'InnoDB' and 'MyISAM'
I have done this several times with 100% success
Good Luck
