Copying InnoDB tables

[tim55]

When using MyISAM tables, all the data and structure files are contained in the one directory - c:\mysql\data\mydatabase1, for instance. This means that to copy the files from one PC to another is easy.

However, if using InnoDB tables, you have the ibdata1, ib_logfile0 etc. in the c:\mysql\data directory, which presumably contain data for all the tables in all the subdirectories.

As a developer, I often want to copy databases and data from clients so that I can work on them directly, but having combined data files would appear to make this impossible, since my overall database will be different from theirs.

Have I missunderstood how the ibdata1, ib_logfile0 etc. work, or is there a simple way to copy InnoDB data files? I have found reference to this on the MySQL site and forums, but nothing that addresses this particular issue.

Thanks.

 

[TonyGroves]

Use mysqldump, from the shell command line. It generates a text file containing the SQL statements necessary to recreate and populate the database with the selected tables. To reduce the file size a bit, you could pipe the mysqldump output to a compression program.