Rechercher dans le manuel MySQL
15.6.1.2 Moving or Copying InnoDB Tables
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
replication slave server; you might copy individual tables to
another instance to develop and test an application, or to a data
warehouse server to produce reports.
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, 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
It is prohibited to start the server with a
lower_case_table_names
setting
that is different from the setting used when the server was
initialized.
Techniques for moving or copying InnoDB
tables
include:
Transportable Tablespaces
The transportable tablespaces feature uses
FLUSH
TABLES ... FOR EXPORT
to ready InnoDB
tables for copying from one server instance to another. To use this
feature, InnoDB
tables must be created with
innodb_file_per_table
set to
ON
so that each InnoDB
table
has its own tablespace. For usage information, see
Section 15.6.3.7, “Copying Tablespaces to Another Instance”.
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 30.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 15.17.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
FLOAT
or
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 .ibd
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
statement:
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
.ibd
file, because doing so changes the table ID stored inside the tablespace.Issue this
ALTER TABLE
statement to delete the current.ibd
file:Copy the backup
.ibd
file to the proper database directory.Issue this
ALTER TABLE
statement to tellInnoDB
to use the new.ibd
file for the table:NoteThe
ALTER TABLE ... IMPORT TABLESPACE
feature does not enforce foreign key constraints on imported data.
In this context, a “clean” .ibd
file backup is one for which the following requirements are
satisfied:
There are no uncommitted modifications by transactions in the
.ibd
file.There are no unmerged insert buffer entries in the
.ibd
file.Purge has removed all delete-marked index records from the
.ibd
file.mysqld has flushed all modified pages of the
.ibd
file from the buffer pool to the file.
You can make a clean backup .ibd
file using the
following method:
Stop all activity from the mysqld server and commit all transactions.
Wait until
SHOW ENGINE INNODB STATUS
shows that there are no active transactions in the database, and the main thread status ofInnoDB
isWaiting for server activity
. Then you can make a copy of the.ibd
file.
Another method for making a clean copy of an
.ibd
file is to use the MySQL Enterprise Backup
product:
Use MySQL Enterprise Backup to back up the
InnoDB
installation.Start a second mysqld server on the backup and let it clean up the
.ibd
files in the backup.
Export and Import (mysqldump)
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.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-innodb-migration.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.