7.5.8 Moving an InnoDB Database to Another Machine

On Windows InnoDB stores the database names and table names internally always in lower case. To move databases in a binary format from Unix to Windows or from Windows to Unix you should have all table and database names in lower case. A convenient way to accomplish this is to add on Unix the line

set-variable=lower_case_table_names=1

to the [mysqld] section of your `my.cnf' before you start creating your tables. On Windows the setting 1 is the default.

InnoDB data and log files are binary-compatible on all platforms if the floating-point number format on the machines is the same. You can move an InnoDB database simply by copying all the relevant files, which we already listed in the previous section on backing up a database. If the floating-point formats on the machines are different but you have not used FLOAT or DOUBLE datatypes in your tables then the procedure is the same: just copy the relevant files. If the formats are different and your tables contain floating-point data, you have to use `mysqldump' and `mysqlimport' to move those tables.

A performance tip is to switch off auto-commit mode when you import data into your database, assuming your tablespace has enough space for the big rollback segment the big import transaction will generate. Do the commit only after importing a whole table or a segment of a table.

User Comments

When moving a database between two machines where the floating-point values are not compatible, this solution can be easier (depending on the number and complexity of your tables). If you have a very large database with just a few FLOAT and/or DOUBLE columns, it is definitely worth to consider:

1. On the source machine, convert any DOUBLE and/or FLOAT column(s) to CHAR(50) (adjust the length of the string to your needs).
2. Shut down the MySQL server on the source machine.
3. Copy the binary files to the target machine.
4. Start the MySQL server on the target machine.
5. On the target machine, convert the column(s) in question back to FLOAT and/or DOUBLE.

Add your own comment.