MySQL innodb table using table space physical files to copy or migrate tables

MySQL InnoDB Engine table copies physical files for single-table or specified table replication, you can think of many ways to test 2 of them today:

• Modify the InnoDB engine's table to the MyISAM engine, and then copy the physical file
• Copy InnoDB tablespace files directly (provided that the stand-alone tablespace (by default, via show variables like ' innodb_file_per_table ' view) is copied

First, modify the engine

1. Create a table of the InnoDB engine and insert the test data;

Create TableTEST_TB (IDint Primary Key, C1varchar( -)) ENGINE=InnoDBDEFAULTCHARSET=UTF8;Insert  intoTest_tbSelect 1,'C1';Insert  intoTest_tbSelect 2,'C2';

2. Modifying the engine

Alter table test_tb engine=createtable test_tb\g

3. Copy the physical files to the target library

cd/data/mysql/mysql3307/Data/CD TestDBllCD. /testdb2/pwdllCP . /testdb/test_tb.* . ll

4. Modify Permissions

chown -R mysql:mysql.

5. View Results

The record is consistent with the source library.

6. Modify the table engine of the source library and the target library to InnoDB

Alter table testdb.test_tb engine=InnoDB; Alter table testdb2.test_tb engine=InnoDB;

II. copy. IDB Physical Tablespace File

1. Create a InnoDB table, in order to test the case of large table, I created a 800W record of the table, occupy 940M space

/*Create tables and stored procedures that quickly generate successive numbers*/--Build Table CREATE TABLE' TEST_TB2 ' (' ID ')int( One)DEFAULT NULL, ' AA 'varchar( -)DEFAULT NULL, ' BB 'varchar( -)DEFAULT NULL, ' CC 'varchar( -)DEFAULT NULL) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;--Create a processDELIMITER $$CREATE  PROCEDURE' Sp_test_tb2 ' (CNTINT )BEGIN    DECLAREIINT  DEFAULT 1; TRUNCATE TABLETEST_TB2; INSERT  intoTest_tb2SELECTConcat (I,'a'), concat (I,'b'), concat (I,'C')  ;  whileI<CNT doBEGIN        INSERT  intoTest_tb2SELECTId+I,concat (ID+I'a'), concat (ID+I'b'), concat (ID+I'C') fromTest_tb2WHEREId+I<=CNT; SETI=I*2; END; END  while;END$ $DELIMITER;--generate 8 million recordsCall SP_TEST_TB2 (8000000); Select Count(*) fromTEST_TB2;

2. Create the same table name in the target library

Mysql>  Usetestdb2;CREATE TABLE' TEST_TB2 ' (' ID ')int( One)DEFAULT NULL, ' AA 'varchar( -)DEFAULT NULL, ' BB 'varchar( -)DEFAULT NULL, ' CC 'varchar( -)DEFAULT NULL, ) ENGINE=InnoDBDEFAULTCHARSET=UTF8MB4;

3. Delete the table space for the target table

Alter table test_tb2 discard tablespace;

At this point the TEST_TB2 table of the target library is near the data definition file, and the tablespace file is deleted

4. Copy the IDB file from the Source library

5. Modify table Space file permissions

6. The target table imports Tablespace data (it takes a little time to record more)

Alter table test_tb2 import tablespace;

7. View the results of the import

The result is consistent with the source table

Tips:

The above 2 processing methods require the source table without write updates and so on, and need to flush tables data to the physical disk file. Therefore, it is recommended to lock the table or stop the business, to copy files and then restore the write operations.

MySQL InnoDB tables use tablespace physical files to copy or migrate tables