- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 13.2 The InnoDB Storage Engine
- 13.2.1 InnoDB Overview
- 13.2.2 InnoDB Contact Information
- 13.2.3 InnoDB Configuration
- 13.2.4 InnoDB Startup Options and System Variables
- 13.2.5 Creating the InnoDB Tablespace
- 13.2.6 Creating and Using InnoDB Tables
- 13.2.7 Adding and Removing InnoDB Data and Log Files
- 13.2.8 Backing Up and Recovering an InnoDB Database
- 13.2.9 Moving an InnoDB Database to Another Machine
- 13.2.10 InnoDB Transaction Model and Locking
- 13.2.11 InnoDB Performance Tuning Tips
- 13.2.12 Implementation of Multi-Versioning
- 13.2.13 InnoDB Table and Index Structures
- 13.2.14 InnoDB File Space Management and Disk I/O
- 13.2.15 InnoDB Error Handling
- 13.2.16 Restrictions on InnoDB Tables
- 13.2.17 InnoDB Troubleshooting
On Windows, 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, you should
have all table and database names in lowercase. A convenient way
to accomplish this is to add the following line to the
[mysqld] section of your
my.cnf or my.ini file
before creating any databases or tables:
[mysqld] lower_case_table_names=1
Like MyISAM data files,
InnoDB data and log files are binary-compatible
on all platforms having the same floating-point number format. You
can move an InnoDB database simply by copying
all the relevant files listed in Section 13.2.8, “Backing Up and Recovering an InnoDB Database”.
If the floating-point formats differ but you have not used
FLOAT or DOUBLE data types
in your tables, then the procedure is the same: simply copy the
relevant files. If the formats differ and your tables contain
floating-point data, you must use mysqldump to
dump your tables on one machine and then import the dump files on
the other machine.
One way to increase performance 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.
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.
It would be really nice to have an example of the syntax or the steps necessary to move the database, instead of sending everyone all over the documentation to try to find it.
FYI -- If you are moving binary copies of InnoDB tables from Ubuntu to Mac OSX or Windows, make sure the lower_case_table_names=0 flag is set otherwise you get "ERROR 1146 (42S02): Table ‘db.table’ doesn’t exist"
http://stephensaine.com/?p=5
Add your own comment.