This section describes techniques for moving or copying some or all
InnoDB tables to a different server. 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 server to
develop and test an application, or to a data warehouse server to
Techniques for moving or copying
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:
You can move an
InnoDB database simply by copying
all the relevant files listed under "Cold Backups" in
Section 14.19, “InnoDB Backup and Recovery”.
MyISAM data files,
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 mysqldump to dump your tables on one machine and then import the dump files on the other machine. Using this method, it does not matter whether the formats differ or if your tables contain floating-point data.
One way to increase the performance of this method is to switch off autocommit mode when importing data, assuming that the tablespace has enough space for the big rollback segment that the import transactions generate. Do the commit only after importing a whole table or a segment of a table.