只有库文件，如何恢复有全文索引的表–上

只有库文件，如何恢复有全文索引的表–上

1、环境准备

这是测试准备的环境，如果是独立恢复，那么只有某一个数据库的目录.

1.1、表结构

[root@mydb db01]# mysql -e 'show create table db01.t1\G'
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int NOT NULL,
`name` varchar(200) DEFAULT NULL,
`name2` varchar(210) DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `idx_t1` (`name`),
FULLTEXT KEY `idx_t2` (`name2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

[root@mydb db01]# mysql -e 'show create table db01.t2\G'
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int NOT NULL,
`name` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `idx_t1` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


1.2、文件清单

[root@mydb db01]# ll
total 3392
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_00000000000001f8_index_1.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_00000000000001f8_index_2.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_00000000000001f8_index_3.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_00000000000001f8_index_4.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_00000000000001f8_index_5.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_00000000000001f8_index_6.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:34 fts_00000000000004b3_00000000000001ff_index_1.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:34 fts_00000000000004b3_00000000000001ff_index_2.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:34 fts_00000000000004b3_00000000000001ff_index_3.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:34 fts_00000000000004b3_00000000000001ff_index_4.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:34 fts_00000000000004b3_00000000000001ff_index_5.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:34 fts_00000000000004b3_00000000000001ff_index_6.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_being_deleted_cache.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_being_deleted.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_config.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_deleted_cache.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_deleted.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_0000000000000208_index_1.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_0000000000000208_index_2.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_0000000000000208_index_3.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_0000000000000208_index_4.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_0000000000000208_index_5.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_0000000000000208_index_6.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_being_deleted_cache.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_being_deleted.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_config.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_deleted_cache.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_deleted.ibd
-rw-r----- 1 mysql mysql 131072 Jul 16 21:37 t1.ibd
-rw-r----- 1 mysql mysql 131072 Jul 16 21:46 t2.ibd


2、基础知识导入

2.1、全文索引文件

如果一张表只有一个全文索引，那么它有以下文件，

-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_0000000000000208_index_1.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_0000000000000208_index_2.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_0000000000000208_index_3.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_0000000000000208_index_4.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_0000000000000208_index_5.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:46 fts_00000000000004c6_0000000000000208_index_6.ibd

-rw-r----- 1 mysql mysql 131072 Jul 16 21:46 t2.ibd


如果一张表有多个全文索引，那么它的文件清单是如下：。每一个全文索引文件还会增加index_1-6

-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_00000000000001f8_index_1.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_00000000000001f8_index_2.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_00000000000001f8_index_3.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_00000000000001f8_index_4.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_00000000000001f8_index_5.ibd
-rw-r----- 1 mysql mysql 114688 Jul 16 21:33 fts_00000000000004b3_00000000000001f8_index_6.ibd

-rw-r----- 1 mysql mysql 131072 Jul 16 21:37 t1.ibd


2.2、举例换算

举例文件：fts_00000000000004b3_00000000000001f8_index_1.ibd

4b3是16进制，换算成10进制是1203

1f8是16进制，换算成10进制是504

2.3、表ID

[root@mydb db01]# mysql -e 'SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_INDEXES WHERE table_id=1203;'
+----------+------------------+----------+-------+
| index_id | name | table_id | space |
+----------+------------------+----------+-------+
| 497 | PRIMARY | 1203 | 139 |
| 504 | idx_t1 | 1203 | 139 |
| 511 | idx_t2 | 1203 | 139 |
| 503 | FTS_DOC_ID_INDEX | 1203 | 139 |
+----------+------------------+----------+-------+


2.4、索引ID

[root@mydb db01]# mysql -e 'SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_INDEXES WHERE index_id=504;'
+----------+--------+----------+-------+
| index_id | name | table_id | space |
+----------+--------+----------+-------+
| 504 | idx_t1 | 1203 | 139 |
+----------+--------+----------+-------+


3、后记

如果只有数据库目录文件是无法查询这些信息。那么如何把全文索引文件和表文件对应起来呢？如何恢复表结构呢？然后恢复数据呢？下次恢复课再讲。