10bet网址
MySQL 8.0 Reference Manual
相关的文档10bet官方网站 Download this Manual
PDF (US Ltr)- 41.9Mb
PDF (A4)- 42.0Mb
Man Pages (TGZ)- 266.3Kb
Man Pages (Zip)- 376.0Kb
Info (Gzip)- 4.0Mb
Info (Zip)- 4.0Mb
Excerpts from this Manual

15.6.1.4 Moving or Copying InnoDB Tables

This section describes techniques for moving or copying some or allInnoDBtables 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 produce reports.

On Windows,InnoDBalways 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 yourmy.cnformy.inifile before creating any databases or tables:

[mysqld] lower_case_table_names=1
Note

It is prohibited to start the server with alower_case_table_namessetting that is different from the setting used when the server was initialized.

Techniques for moving or copyingInnoDBtables include:

Importing Tables

A table that resides in a file-per-table tablespace can be imported from another MySQL server instance or from a backup using theTransportable Tablespacefeature. SeeSection 15.6.1.3, “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, seeSection 30.2, “MySQL Enterprise Backup Overview”.

Copying Data Files (Cold Backup Method)

You can move anInnoDBdatabase simply by copying all the relevant files listed under "Cold Backups" inSection 15.18.1, “InnoDB Backup”.

InnoDBdata 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 usedFLOATorDOUBLEdata types in your tables, then the procedure is the same: simply copy the relevant files.

When you move or copy file-per-table.ibdfiles, the database directory name must be the same on the source and destination systems. The table definition stored in theInnoDBshared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.

To move an.ibdfile and the associated table from one database to another, use aRENAME TABLEstatement:

RENAME TABLEdb1.tbl_nameTOdb2.tbl_name;

If you have acleanbackup of an.ibdfile, you can restore it to the MySQL installation from which it originated as follows:

  1. 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.

  2. Issue thisALTER TABLEstatement to delete the current.ibdfile:

    ALTER TABLEtbl_nameDISCARD TABLESPACE;
  3. Copy the backup.ibdfile to the proper database directory.

  4. Issue thisALTER TABLEstatement to tellInnoDBto use the new.ibdfile for the table:

    ALTER TABLEtbl_nameIMPORT TABLESPACE;
    Note

    TheALTER TABLE ... IMPORT TABLESPACEfeature does not enforce foreign key constraints on imported data.

In this context, aclean.ibd文件备份以下要求ments are satisfied:

  • There are no uncommitted modifications by transactions in the.ibdfile.

  • There are no unmerged insert buffer entries in the.ibdfile.

  • Purge has removed all delete-marked index records from the.ibdfile.

  • mysqldhas flushed all modified pages of the.ibdfile from the buffer pool to the file.

You can make a clean backup.ibdfile using the following method:

  1. Stop all activity from themysqldserver and commit all transactions.

  2. Wait untilSHOW ENGINE INNODB STATUSshows that there are no active transactions in the database, and the main thread status ofInnoDBisWaiting for server activity. Then you can make a copy of the.ibdfile.

Another method for making a clean copy of an.ibdfile is to use the MySQL Enterprise Backup product:

  1. Use MySQL Enterprise Backup to back up theInnoDBinstallation.

  2. Start a secondmysqldserver on the backup and let it clean up the.ibdfiles in the backup.

Restoring from a Logical Backup

You can use a utility such asmysqldumpto 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, disableautocommitwhen importing data. Perform a commit only after importing an entire table or segment of a table.