This section describes techniques for moving or copying some or all
InnoDB tables to a different server or instance.
For example, you might move an entire MySQL instance to a larger,
faster server; you might clone an entire MySQL instance to a new
replication slave server; you might copy individual tables to
another instance to develop and test an application, or to a data
warehouse server to produce reports.
InnoDB always stores database and
table names internally in lowercase. To move databases in a binary
format from Unix to Windows or from Windows to Unix, create all
databases and tables using lowercase names. A convenient way to
accomplish this is to add the following line to the
[mysqld] section of your
before creating any databases or tables:
Techniques for moving or copying
You can move an
InnoDB database simply by copying
all the relevant files listed under "Cold Backups" in
Section 14.21.1, “InnoDB Backup”.
InnoDB data and log files are binary-compatible
on all platforms having the same floating-point number format. If
the floating-point formats differ but you have not used
DOUBLE data types in your tables,
then the procedure is the same: simply copy the relevant files.
You can use a utility such as mysqldump to perform a logical backup, which produces a set of SQL statements that can be executed to reproduce the original database object definitions and table data for transfer to another SQL server. Using this method, it does not matter whether the formats differ or if your tables contain floating-point data.
To improve the performance of this method, disable
autocommit when importing data.
Perform a commit only after importing an entire table or segment of