MySQL5.7只剩下ibd文件时恢复实例，迁移ibd文件也可以使用

------------恢复内容开始------------

# 当有备份的时候，可以使用备份恢复，此处仅讨论只有.ibd文件时的恢复

下载测试库，下载地址如下

https://dev.mysql.com/doc/index-other.html

根据需求下载测试库，此处下载world库测试

解压并导入

source world.sql;//可以切到存放world.sql的路径下，在进入mysql 执行source，或者使用绝对路径

测试开始

1.创建worldbak库

3306 [world]>show create database world\G;
*************************** 1. row ***************************
Database: world
Create Database: CREATE DATABASE world /*!40100 DEFAULT CHARACTER SET utf8mb4 */
1 row in set (0.00 sec)
3306 [world]> CREATE DATABASE worldbak DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.00 sec)

2.导出world库中表的表结构

mysqldump -uroot -ppassw0rd -B world --no-data > /data/3306/data/a.sql

[root@localhost soft]# mysqldump -uroot -p -B  world --no-data >  /data/3306/data/a.sql
Enter password: 
[root@localhost soft]# less a.sql
a.sql: No such file or directory
[root@localhost soft]# less /data/3306/data/a
/data/3306/data/a: No such file or directory
[root@localhost soft]# less /data/3306/data/a.sql 
-- MySQL dump 10.13  Distrib 5.7.28, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: world
-- ------------------------------------------------------
-- Server version       5.7.28-log

/*!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 */;
/*!40101 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 */;
--
-- Current Database: `world`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `world` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `world`;

--
-- Table structure for table `city`
--

DROP TABLE IF EXISTS `city`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
!vi +19 /data/3306/data/a.sql
-- MySQL dump 10.13  Distrib 5.7.28, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: world
-- ------------------------------------------------------
-- Server version       5.7.28-log

/*!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 */;
/*!40101 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 */;

--
-- Current Database: `world`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `world` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `world`;

--
-- Table structure for table `city`
--
...skipping...
-- Server version       5.7.28-log

/*!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 */;
/*!40101 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 */;

--
-- Current Database: `world`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `world` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `world`;

--
-- Table structure for table `city`
--

DROP TABLE IF EXISTS `city`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `country`
--
DROP TABLE IF EXISTS `country`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `country` (
  `Code` char(3) NOT NULL DEFAULT '',
  `Name` char(52) NOT NULL DEFAULT '',
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` char(26) NOT NULL DEFAULT '',
  `SurfaceArea` decimal(10,2) NOT NULL DEFAULT '0.00',
  `IndepYear` smallint(6) DEFAULT NULL,
  `Population` int(11) NOT NULL DEFAULT '0',
  `LifeExpectancy` decimal(3,1) DEFAULT NULL,
  `GNP` decimal(10,2) DEFAULT NULL,
  `GNPOld` decimal(10,2) DEFAULT NULL,
  `LocalName` char(45) NOT NULL DEFAULT '',
  `GovernmentForm` char(45) NOT NULL DEFAULT '',
  `HeadOfState` char(60) DEFAULT NULL,
  `Capital` int(11) DEFAULT NULL,
  `Code2` char(2) NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `countrylanguage`
--

DROP TABLE IF EXISTS `countrylanguage`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `countrylanguage` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
  `Percentage` decimal(4,1) NOT NULL DEFAULT '0.0',
  PRIMARY KEY (`CountryCode`,`Language`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 */;
-- Dump completed on 2022-02-15  9:21:24
[root@localhost soft]#

### 导出表结构，然后查看是否已经将表结构导出，然后将该SQL语句中的world库名改为worldbak，然后导入该表结构

3306 [worldbak]>show tables;
+--------------------+
| Tables_in_worldbak |
+--------------------+
| city               |
| country            |
| countrylanguage    |
+--------------------+
3 rows in set (0.00 sec)

3306 [worldbak]>select count(*) from city;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

3306 [worldbak]>select count(*) from country;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

3306 [worldbak]>select count(*) from countrylanguage;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

3.删除表空间

select concat('alter table ',table_schema,'.',table_name,' discard tablespace;') from information_schema.tables where table_schema='worldbak' into outfile '/data/3306/data/discard.sql';

该报错是因为secure-file-priv没有配置，将该配置写入my.cnf文件中然后重新导入

vim /etc/my.cnf
secure-file-priv='/tmp'

3306 [worldbak]>select concat('alter table ',table_schema,'.',table_name,' discard tablespace;') from information_schema.tables where table_schema='worldbak' into outfile '/data/3306/data/discard.sql';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: worldbak

Query OK, 3 rows affected (0.01 sec)

source /data/3306/data/discard.sql;

3306 [worldbak]>source /data/3306/data/discard.sql;
Query OK, 0 rows affected (0.00 sec)

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ()
Query OK, 0 rows affected (0.00 sec)

该报错是因为删除表空间的时候，有库中的表有外检关系，所以设置参数跳过临时检查外键关系set foreign_key_checks=0

4.将IBD文件拷贝到worldbak目录下

5.导入表空间

select concat('alter table ',table_schema,'.',table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';

3306 [worldbak]>source /data/3306/data/discard.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

3306 [worldbak]>select concat('alter table ',table_schema,'.',table_name,' import tablespace;') from information_schema.tables where table_schema='worldbak' into outfile '/data/3306/data/import.sql';
Query OK, 3 rows affected (0.00 sec)

3306 [worldbak]>
3306 [worldbak]>source /data/3306/data/import.sql;
ERROR 1812 (HY000): Tablespace is missing for table `worldbak`.`city`.
ERROR 1812 (HY000): Tablespace is missing for table `worldbak`.`country`.
ERROR 1812 (HY000): Tablespace is missing for table `worldbak`.`countrylanguage`.