MySQL5.7 Transfer Table space--Migration partition table

Mysql Transport Table Space--Copy the InnoDB partition table to another instance (ii)

Experimental environment: (All mysql5.7)

SOURCE Library: 192.168.2.200 mysql5.7.16 zhangdb under the emp_2 partition table

Target Library: 192.168.2.100 mysql5.7.18 Test (ZHANGDB's EMP table is imported into the target library's test schema)

--: Create the Test partition table emp_2 in the source database, and then import the data

MySQL [zhangdb]> CREATE TABLE emp_2 (

ID BIGINT unsigned not NULL auto_increment,

x VARCHAR (.) is not NULL,

Y VARCHAR (x) is not NULL,

PRIMARY KEY (ID)

)

PARTITION by RANGE COLUMNS (ID)

(

PARTITION p1 VALUES less THAN (1000),

PARTITION P2 VALUES Less THAN (2000),

PARTITION P3 VALUES less THAN (3000)

);

(then create the stored procedure, import the test data)

DELIMITER//

CREATE PROCEDURE Insert_batch ()

Begin

DECLARE num INT;

SET Num=1;

While Num <

IF (num%10000=0) Then

COMMIT;

END IF;

INSERT into Emp_2 VALUES (NULL, REPEAT (' X ', $), REPEAT (' Y ', 500));

SET num=num+1;

END while;

COMMIT;

END//

DELIMITER;

Mysql> Select Table_name,partition_name from information_schema.partitions where table_schema= ' zhangdb ';

+------------+----------------+

| table_name | Partition_name |

+------------+----------------+

| EMP | NULL |

| emp_2 | P1 |

| emp_2 | P2 |

| emp_2 | P3 |

+------------+----------------+

4 rows in Set (0.00 sec)

Mysql> Select COUNT (*) from emp_2 partition (P1);

+----------+

| COUNT (*) |

+----------+

| 999 |

+----------+

1 row in Set (0.00 sec)

Mysql> Select COUNT (*) from emp_2 partition (P2);

+----------+

| COUNT (*) |

+----------+

| 1000 |

+----------+

1 row in Set (0.00 sec)

Mysql> Select COUNT (*) from emp_2 partition (p3);

+----------+

| COUNT (*) |

+----------+

| 1000 |

+----------+

1 row in Set (0.00 sec)

As can be seen from the above, the Emp_2 partition table has been created and has 3 sub-partitions, each with a bit of data.

--: In the target database, create the structure of the emp_2 table, not the data (to view the SQL that created the table in the source library, using the show create Table Emp_2\g method)

MySQL [test]> CREATE TABLE ' emp_2 ' (

' ID ' bigint (unsigned) not NULL auto_increment,

' x ' varchar ($) Not NULL,

' Y ' varchar ($) Not NULL,

PRIMARY KEY (' id ')

) Engine=innodb auto_increment=3000 DEFAULT charset=utf8mb4

/*!50500 PARTITION by RANGE COLUMNS (ID)

(PARTITION p1 VALUES less THAN () ENGINE = InnoDB,

PARTITION p2 VALUES less THAN ENGINE = InnoDB,

PARTITION P3 VALUES less THAN ENGINE = InnoDB) */;

[email protected] test]# LL

-rw-r-----1 mysql mysql 98304 may 15:58 emp_2#p#p0.ibd

-rw-r-----1 mysql mysql 98304 may 15:58 emp_2#p#p1.ibd

-rw-r-----1 mysql mysql 98304 may 15:58 emp_2#p#p2.ibd

Attention:

※ Constraints, character sets and so on must also be consistent, it is recommended to use show create table T1; To get the SQL that created the table, or you will be prompted with a 1808 error when importing the table space on the new server.

--: On the target database, discard the table space of the partitioned table

MySQL [test]> ALTER TABLE emp_2 discard tablespace;

Query OK, 0 rows affected (0.12 sec)

[email protected] test]# ll---Look, the IDB files of the 3 partitions just now are gone.

-rw-r-----1 mysql mysql 8604 may 04:14 emp_2.frm

--: Run flush TABLES on the source database ... For EXPORT locks the table and generates a. CFG metadata file, and finally transfers the CFG and IBD files to the target database

Mysql> Flush tables emp_2 for export;

Query OK, 0 rows Affected (0.00 sec)

[Email protected] zhangdb]# SCP emp_2* [email protected]:/mysql/data/test/--file CP to target database

mysql> unlock tables; ---finally whether the lock on the table

--: Authorize the file in the target database, and then import the tablespace to see if the data is fully available

[Email protected] test]# chown mysql.mysql emp_2#*

MySQL [test]> ALTER TABLE emp_2 import tablespace;

Query OK, 0 rows affected (0.96 sec)

MySQL [test]> Select COUNT (*) from emp_2;

+----------+

| COUNT (*) |

+----------+

| 2999 |

+----------+

1 row in Set (0.63 sec)

From the above view, the partition table has been imported into the target database,

In addition, some sub-partitions can be imported into the target database, (often the entire partition table is very large, can only be used to import sub-partitions into the target database),

Some sub-partitions are imported to the target database by:

1, when creating the target table, only need to create the partition to be imported, such as: only created P2 p3 two partitions

CREATE TABLE ' emp_2 ' (

' ID ' bigint (unsigned) not NULL auto_increment,

' x ' varchar ($) Not NULL,

' Y ' varchar ($) Not NULL,

PRIMARY KEY (' id ')

) Engine=innodb auto_increment=3000 DEFAULT charset=utf8mb4

/*!50500 PARTITION by RANGE COLUMNS (ID)

(

PARTITION p2 VALUES less THAN ENGINE = InnoDB,

PARTITION P3 VALUES less THAN ENGINE = InnoDB) */

2, from the source depot CP to the target library files, of course, this is the two, there is no need for other partitions,

3, the other operation methods are the same.

MySQL5.7 Transfer Table space--Migration partition table