Skip navigation links
MySQL 5.1 Reference Manual :: 13 Storage Engines :: 13.6 The InnoDB Storage Engine :: 13.6.7 Moving an InnoDB Database to Another Machine
13.6.8 The InnoDB Transaction Model and Locking »
Section Navigation      [Toggle]

13.6.7. Moving an InnoDB Database to Another Machine

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 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 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.6.6, “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 you use mysqldump to dump your tables on one machine and then import the dump files on the other machine, it does not matter whether the formats differ or your tables contain floating-point data.

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

Posted by Daniel Solin on January 13 2003 6:54am[Delete] [Edit]

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.

Posted by Steven Casey on August 15 2004 9:15pm[Delete] [Edit]

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.

Posted by Stephen Saine on June 26 2007 10:49pm[Delete] [Edit]

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

Posted by michael skramstad on October 26 2008 7:57am[Delete] [Edit]

I hope this solution saves you some frustration, as I spent several hours to get it to work. To free disk space on my primary server I moved non-clustered innodb databases to another server, which became an NFS server, and I created an NFS mount on the primary server (NFS client). Hour after hour I saw the following error in /var/log/syslog:

Oct 25 15:46:22 master mysqld[11434]: 081025 15:46:22 InnoDB: Operating system error number 13 in a file operation.
Oct 25 15:46:22 master mysqld[11434]: InnoDB: The error means mysqld does not have the access rights to
Oct 25 15:46:22 master mysqld[11434]: InnoDB: the directory.
Oct 25 15:46:22 master mysqld[11434]: InnoDB: File name ./ibdata1
Oct 25 15:46:22 master mysqld[11434]: InnoDB: File operation call: 'open'.
Oct 25 15:46:22 master mysqld[11434]: InnoDB: Cannot continue operation.
Oct 25 15:46:22 master mysqld_safe[11440]: ended
Oct 25 15:46:36 master /etc/init.d/mysql[11590]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf
ping' resulted in
Oct 25 15:46:36 master /etc/init.d/mysql[11590]: ^G/usr/bin/mysqladmin: connect to server at 'localhost' failed
Oct 25 15:46:36 master /etc/init.d/mysql[11590]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysq
ld.sock' (2)'
Oct 25 15:46:36 master /etc/init.d/mysql[11590]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock'
exists!

Since I use Ubuntu Hardy I had to add the new database location to apparmor's /etc/apparmor.d/usr.sbin.mysqld file. I did this on both the server and client. I also added the mysql user to the groups adm and admin on the NFS server.

After doing a chown -R mysql:adm on the new database location it finally worked.

http://www.mikeskramstad.com



Add your own comment.