前提：
用的innodb引擎且有以下配置：
innodb_file_per_table=1
源数据库与目标数据库的innodb_page_size一样
假设要恢复的数据库为shengye
1 用mysqldump备份 源数据库表结构信息tbl.sql
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`id` smallint NOT NULL AUTO_INCREMENT,
`book_id` int NOT NULL,
`name` varchar(300) NOT NULL,
`image` varchar(150) DEFAULT NULL,
`type` tinyint DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=743 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Table structure for table `options` */
DROP TABLE IF EXISTS `options`;
CREATE TABLE `options` (
`id` smallint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`code` varchar(10) NOT NULL COMMENT '编码',
`name` varchar(50) DEFAULT NULL COMMENT '名称',
`type` varchar(10) DEFAULT NULL COMMENT '类型',
PRIMARY KEY (`id`),
KEY `type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Table structure for table `parameter` */
DROP TABLE IF EXISTS `parameter`;
CREATE TABLE `parameter` (
`parameter_id` smallint unsigned NOT NULL COMMENT 'id',
`parameter_code` varchar(30) NOT NULL COMMENT '编码',
`parameter_name` varchar(50) NOT NULL COMMENT '名称',
`parameter_type` tinyint unsigned NOT NULL COMMENT '类别',
`property_id` smallint unsigned NOT NULL COMMENT '资产id',
`parameter_unit` varchar(30) NOT NULL COMMENT '单位',
`parameter_description` varchar(400) DEFAULT NULL COMMENT '描述',
`parameter_creation_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
`parameter_creator` varchar(80) DEFAULT NULL COMMENT '创建人',
PRIMARY KEY (`parameter_id`),
KEY `fr_key_property` (`property_id`),
CONSTRAINT `fr_key_property` FOREIGN KEY (`property_id`) REFERENCES `property` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='参数';
/*Table structure for table `property` */
DROP TABLE IF EXISTS `property`;
CREATE TABLE `property` (
`id` smallint unsigned NOT NULL AUTO_INCREMENT COMMENT '资产',
`asset_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '资产编码',
`asset_name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '资产名称',
`purchase_price` decimal(10,0) DEFAULT NULL COMMENT '购置价格',
`purchase_time` datetime DEFAULT NULL COMMENT '采购日期',
`description` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '描述',
`pic_url` varchar(200) DEFAULT NULL COMMENT '图片地址',
`principal_id` smallint unsigned NOT NULL COMMENT '负责人id',
`is_critical` varchar(100) DEFAULT NULL COMMENT '关键性(下拉)',
`state` varchar(100) DEFAULT NULL COMMENT '状态',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='资产';
2 从源数据库上生成丢弃表空间语句
先禁用外键约束：
SET FOREIGN_KEY_CHECKS=0;
执行SELECT CONCAT('alter table ', TABLE_NAME, ' discard tablespace;') FROM information_schema.tables WHERE table_schema = 'shengye';
得到sql:
ALTER TABLE book DISCARD TABLESPACE;
ALTER TABLE OPTIONS DISCARD TABLESPACE;
ALTER TABLE parameter DISCARD TABLESPACE;
ALTER TABLE property DISCARD TABLESPACE;
3 切换到目标数据库，并创建shengye源数据库。导入1中的tbl.sql, 再导入2中的丢弃表空间语句
4 将源数据库shengye的ibd文件，复制目标数据库shengye中
5 导入表空间
ALTER TABLE book IMPORT TABLESPACE;
ALTER TABLE OPTIONS IMPORT TABLESPACE;
ALTER TABLE parameter IMPORT TABLESPACE;
ALTER TABLE property IMPORT TABLESPACE;
6 备份成功
