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
replica server; you might copy individual tables to another instance
to develop and test an application, or to a data warehouse server to
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
A table that resides in a file-per-table tablespace can be imported from another MySQL server instance or from a backup using the Transportable Tablespace feature. See Section 126.96.36.199, “Importing InnoDB Tables”.
MySQL Enterprise Backup
The MySQL Enterprise Backup product lets you back up a running MySQL database with minimal disruption to operations while producing a consistent snapshot of the database. When MySQL Enterprise Backup is copying tables, reads and writes can continue. In addition, MySQL Enterprise Backup can create compressed backup files, and back up subsets of tables. In conjunction with the MySQL binary log, you can perform point-in-time recovery. MySQL Enterprise Backup is included as part of the MySQL Enterprise subscription.
For more details about MySQL Enterprise Backup, see Section 28.2, “MySQL Enterprise Backup Overview”.
Copying Data Files (Cold Backup Method)
You can move an
InnoDB database simply by copying
all the relevant files listed under "Cold Backups" in
Section 14.19.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.
When you move or copy file-per-table
files, the database directory name must be the same on the source
and destination systems. The table definition stored in the
InnoDB shared tablespace includes the database
name. The transaction IDs and log sequence numbers stored in the
tablespace files also differ between databases.
To move an
.ibd file and the associated table
from one database to another, use a
RENAME TABLE db1.tbl_name TO db2.tbl_name;
If you have a “clean” backup of an
.ibd file, you can restore it to the MySQL
installation from which it originated as follows:
The table must not have been dropped or truncated since you copied the
.ibdfile, because doing so changes the table ID stored inside the tablespace.
ALTER TABLEstatement to delete the current
ALTER TABLE tbl_name DISCARD TABLESPACE;
Copy the backup
.ibdfile to the proper database directory.
ALTER TABLEstatement to tell
InnoDBto use the new
.ibdfile for the table:
ALTER TABLE tbl_name IMPORT TABLESPACE;Note
ALTER TABLE ... IMPORT TABLESPACEfeature does not enforce foreign key constraints on imported data.
In this context, a “clean”
file backup is one for which the following requirements are
There are no uncommitted modifications by transactions in the
There are no unmerged insert buffer entries in the
Purge has removed all delete-marked index records from the
mysqld has flushed all modified pages of the
.ibdfile from the buffer pool to the file.
You can make a clean backup
.ibd file using the
Stop all activity from the mysqld server and commit all transactions.
SHOW ENGINE INNODB STATUSshows that there are no active transactions in the database, and the main thread status of
Waiting for server activity. Then you can make a copy of the
Another method for making a clean copy of an
.ibd file is to use the MySQL Enterprise Backup
Use MySQL Enterprise Backup to back up the
Start a second mysqld server on the backup and let it clean up the
.ibdfiles in the backup.
Restoring from a Logical Backup
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