MySQL学习笔记-手动数据备份恢复（ibd文件）

最新推荐文章于 2025-03-30 03:32:54 发布

鱼听禅

最新推荐文章于 2025-03-30 03:32:54 发布

阅读量3.2k

点赞数

分类专栏：数据库操作文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/u010839204/article/details/125429068

版权

数据库操作专栏收录该内容

6 篇文章

订阅专栏

1.备份的数据文件

数据文件默认路径为：C:\ProgramData\MySQL\MySQL Server 8.0\Data

需要保存对应数据下对应数据库的所有“.ibd”文件

2.删除旧的数据（取消表与idb文件的关联）：、

//指令为 alter table table1 discard tablespace;

//以下声明是用于配置mysql数据，以在数据表有数据关联的情况下仍然可以取消关联
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

alter table pgx.aboutlist discard tablespace;
alter table pgx.channellist discard tablespace;
alter table pgx.customer discard tablespace;
alter table pgx.eventlog discard tablespace;
alter table pgx.genotypelist discard tablespace;
alter table pgx.hospital discard tablespace;
alter table pgx.item discard tablespace;
alter table pgx.itempackage discard tablespace;
alter table pgx.itemtypelist discard tablespace;
alter table pgx.medicinesuggested discard tablespace;
alter table pgx.packageaboutlist discard tablespace;
alter table pgx.record_about discard tablespace;
alter table pgx.record_data discard tablespace;
alter table pgx.record_file discard tablespace;
alter table pgx.record_item discard tablespace;
alter table pgx.record_itempackage discard tablespace;
alter table pgx.record_layout discard tablespace;
alter table pgx.record_log discard tablespace;
alter table pgx.record_packageabout discard tablespace;
alter table pgx.record_subitem discard tablespace;
alter table pgx.relatedgenelist discard tablespace;
alter table pgx.result_combination discard tablespace;
alter table pgx.result_starcells discard tablespace;
alter table pgx.result_suggests discard tablespace;
alter table pgx.resultslist discard tablespace;
alter table pgx.rslist discard tablespace;
alter table pgx.sampletypelist discard tablespace;
alter table pgx.subitem discard tablespace;
alter table pgx.subitemtypelist discard tablespace;
alter table pgx.user discard tablespace;


//以下是重新配置相关参数
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

注意：删除表时需要表存在，若有表不存在时执行，会报“Incorrect key file for table”错误，需要重新创建表，然后重新删除数据。

3.将备份的所有idb文件拷贝到目标替换的数据库文件夹内

4.重新关联idb文件

//使用的指令为：alter table table1 import tablespace;

alter table pgx.aboutlist import tablespace;
alter table pgx.channellist import tablespace;
alter table pgx.customer import tablespace;
alter table pgx.eventlog import tablespace;
alter table pgx.genotypelist import tablespace;
alter table pgx.hospital import tablespace;
alter table pgx.item import tablespace;
alter table pgx.itempackage import tablespace;
alter table pgx.itemtypelist import tablespace;
alter table pgx.medicinesuggested import tablespace;
alter table pgx.packageaboutlist import tablespace;
alter table pgx.record_about import tablespace;
alter table pgx.record_data import tablespace;
alter table pgx.record_file import tablespace;
alter table pgx.record_item import tablespace;
alter table pgx.record_itempackage import tablespace;
alter table pgx.record_layout import tablespace;
alter table pgx.record_log import tablespace;
alter table pgx.record_packageabout import tablespace;
alter table pgx.record_subitem import tablespace;
alter table pgx.relatedgenelist import tablespace;
alter table pgx.result_combination import tablespace;
alter table pgx.result_starcells import tablespace;
alter table pgx.result_suggests import tablespace;
alter table pgx.resultslist import tablespace;
alter table pgx.rslist import tablespace;
alter table pgx.sampletypelist import tablespace;
alter table pgx.subitem import tablespace;
alter table pgx.subitemtypelist import tablespace;
alter table pgx.user import tablespace;