mysql innodb 分区表_如何恢复mysql 单个innodb 分区表

Recover orphaned InnoDB partition tablespaces in MySQL

1. 创建同名，同结构 表

CREATE TABLE `employees` (

`id` int(11) NOT NULL,

`fname` varchar(30) DEFAULT NULL,

`lname` varchar(30) DEFAULT NULL,

`hired` date NOT NULL DEFAULT '1970-01-01',

`separated` date NOT NULL DEFAULT '9999-12-31',

`job_code` int(11) NOT NULL,

`store_id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

PARTITION BY RANGE (store_id)

(PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB,

PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB,

PARTITION p3 VALUES LESS THAN (21) ENGINE = InnoDB)；

2.创建冗余非分区表：

CREATE TABLE t1_t LIKE t1;

#移除分区

ALTER TABLE t1_t REMOVE PARTITIONING;

#discard tablespace：

ALTER TABLE t1_t DISCARD TABLESPACE;

#拷贝待回复的ibd 分区文件到mysql datadir 目录下，并修改为冗余分区表名的ibd 文件；

copy  t1#P#p0.ibd  as  t1_t.ibd  into the MySQL data directory

#导入到冗余表空间中：

ALTER TABLE t1_t IMPORT TABLESPACE;

#将普通冗余分区表交换到分区表中：

ALTER TABLE t2 EXCHANGE PARTITION pxsp1 WITH TABLE t2_t;

on MySQL 5.7.5, I created the same t2 table above, DISCARD TABLESPACE ,

copy the partition tablespaces from my test 5.6 instance and IMPORT TABLESPACE  And done! ??

ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;