Tip of the Day — MyISAM vs Innodb Portability
You can copy the three files (table_name.frm, table_name.MYI, and table_name.MYD) that form a MyISAM table from one server to another and just add them to the appropriate database directory and they will “appear” in the new database. However, with Innodb this is not possible. To copy Innodb databases you either need to dump the database/tables involved and re-import or copy the entire dataset and tablespace files from one server to another.
One caveat about the MyISAM table copy, you should lock the table while copying it to keep the copy from being corrupt due to changes to the file during the copy operation. Also, check and make sure your owner/permissions are the same after the copy.
2 Comments so far
Leave a reply
Is this true even with individual files per innodb table? I have not personally tried it that way.
Michael
Yup, it’s even true when innodb_file_per_table is enabled. However you can import and export individual tables to a server, if you need to restore them versus restore an entire database.
http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html