Change innodb to myisam in mysql and separate single innodb file to multi files by table

I leave this blog for a long time because of health reason. Now I am back and hope that I can keep on with it.

Today I would like to talk about how to change a mysql database from using innodb engine to myisam. It started when I first use mysql database years ago. I knew nothing about it except that I needed a database. My first database use myisam engine and everything worked fine. Later, I think, mysql changed the default engine to use innodb. Since then my new database were all in innodb. Innodb provides more transaction control and management features, which for my application is really not necessary. I use innodb simply because it is the default and I didn't have the knowledge to concern about it. Until recently, I need to duplicate one database to another and found that innodb stores all databases into one single file. This makes duplicating database in file system level impossible.

So I went deeper into the documentation and search the internet to find a method to duplicate my databases.

All you need is your own mysql installation and your own databases in innodb. You also need sufficient space which is double the space of your database. Here it goes:

1. First step is of course to start up your mysql and your sql client.
2. If you have foreign key constraint in your database tables then you have to drop them because myisam does not support it. Use "alter table" command to drop all foreign key.
3. Use "alter table engine=myisam;" to change your table one-by-one to use myisam engine.
4. Check the folder that stores your myisam databases and you will find *.frm, *.myd, *.myi files. frm is the table definition, myd is the data and myi is the index. * is your table name. Then you can duplicate, copy your database (tables) to anywhere you like.
5. If you want to remain in innodb engine but use separate files for each table, innodb has an option that allows you to do so. Shut down your mysql, change the my.ini or my.cnf to include "innodb_file_per_table=1". Start your mysql again.
6. Use "alter table engine=innodb;" to change back to innodb engine. Use "alter table add constraint ...." to add back the foriegn keys.

I am not sure if the product of Step 5 and 6 can be copy/move in file system level. I understand in innodb, the frm file also stores where about of the data and index. So copy/move them to another folder may not work.

You may ask why didn't I use sqldump to duplicate databases? I haven't try it. But when I think of turn my multi-million rows table into a sql text file, I would rather hunt for another method.