MySQL異常恢複恢複資料字典表的例子

在以前的文章中說過mysql的資料字典的恢複(使用工具直接抽取MySQL資料字典,缺少SYS_FIELDS表),主要的資料字典有一下幾個，在本文中主要對這些資料字典的意義進行一些講解，為大家更深一步瞭解mysql恢複處理思路
MySQL恢複字典表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| SYS_COLUMNS    |
| SYS_FIELDS     |
| SYS_INDEXES    |
| SYS_TABLES     |
+----------------+
4 rows in set (0.00 sec)
SYS_TABLES
這個表是mysql恢複的最核心的表之一，主要是記錄資料庫在InnoDB中表的資訊。它預設寫在InnoDB的index_ids為1的裡面，它的根頁在8號page上，他的主要列結構為：
mysql> desc SYS_TABLES;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| NAME         | varchar(255)        | NO   | PRI |         |       |
| ID           | bigint(20) unsigned | NO   |     | 0       |       |
| N_COLS       | int(10)             | YES  |     | NULL    |       |
| TYPE         | int(10) unsigned    | YES  |     | NULL    |       |
| MIX_ID       | bigint(20) unsigned | YES  |     | NULL    |       |
| MIX_LEN      | int(10) unsigned    | YES  |     | NULL    |       |
| CLUSTER_NAME | varchar(255)        | YES  |     | NULL    |       |
| SPACE        | int(10) unsigned    | YES  |     | NULL    |       |
+--------------+---------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
NAME:顧名思義,就是表的名字,但是注意他記錄的格式是db/table,例如:xifenfei/zx_users,表示為xifenfei資料庫中的zx_users表
ID:表的編號
N_COLS:表一共包含的列的數量
TYPE, MIX_ID, MIX_LEN 和 CLUSTER_NAME列,對於資料庫恢複無任何意義不做描述
SPACE:資料表空間的標示列. 例如： ibdata1 是 SPACE 0, ibdata2 是 SPACE 1, 每一個 ibd 檔案都有自己的資料表空間標示.

SYS_INDEXES
這個也是mysql恢複的最核心表之一,主要是記錄InnoDB的index資訊,它預設InnoDB的index_ids為3的裡面，他的主要結構為：
mysql> desc SYS_INDEXES;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| TABLE_ID | bigint(20) unsigned | NO   | PRI | 0       |       |
| ID       | bigint(20) unsigned | NO   | PRI | 0       |       |
| NAME     | varchar(120)        | YES  |     | NULL    |       |
| N_FIELDS | int(10) unsigned    | YES  |     | NULL    |       |
| TYPE     | int(10) unsigned    | YES  |     | NULL    |       |
| SPACE    | int(10) unsigned    | YES  |     | NULL    |       |
| PAGE_NO  | int(10) unsigned    | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
TABLE_ID:表標示列,為SYS_TABLES.ID
ID:為InnoDB中的index的編號,這個在恢複中非常重要,恢複之時需要根據這個去定位具體的檔案
NAME:主要表的index的名字,有PRIMARY 和 普通列的index資訊,一般恢複之時我們選擇PRIMARY
N_FIELDS:表名index包含列的數量,在mysql恢複中不重要
TYPE:恢複之中使用不到該列,不做說明
PAGE:用途等同SYS_TABLES.SPACE
PAGE_NO:標示為每個index的root page的page號,關於index中的page結構如下圖所示

 

SYS_COLUMNS
這個表主要記錄資料庫中表的列的情況,它儲存在index_id 2中.主要用它來確定需要恢複表的列的情況,如果你知道完全的列結構,該表不是MySQL恢複所必須的,它的主要結構為：

mysql> desc SYS_COLUMNS;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| TABLE_ID | bigint(20) unsigned | NO   | PRI | NULL    |       |
| POS      | int(10) unsigned    | NO   | PRI | NULL    |       |
| NAME     | varchar(255)        | YES  |     | NULL    |       |
| MTYPE    | int(10) unsigned    | YES  |     | NULL    |       |
| PRTYPE   | int(10) unsigned    | YES  |     | NULL    |       |
| LEN      | int(10) unsigned    | YES  |     | NULL    |       |
| PREC     | int(10) unsigned    | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

TABLE_ID:表標示列,為SYS_TABLES.ID
POS:該列所在表中的位置,該值從0開始
NAME:列的名字
MTYPE 和 PRTYPE:主要是為了記錄列的類型,出現此類問題主要是由於InnoDB最初並不是為MySQL而設計,到了後面為更好支援MySQL,因此出現了兩種情況.
LEN:列的長度.這個需要注意編碼,比如資料庫是utf8編碼,定義的varchar(10),實際該處長度顯示為30,因為每個除英文外的字元編碼為3個byte.
PREC:有些特殊類型中,列的精確度定義

SYS_FIELDS

記錄所有index的列的分布資訊,它儲存在index_id 4中,該表不是MySQL恢複所必須的,它的主要結構為:

mysql> desc SYS_FIELDS;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| INDEX_ID | bigint(20) unsigned | NO   | PRI | NULL    |       |
| POS      | int(10) unsigned    | NO   | PRI | NULL    |       |
| COL_NAME | varchar(255)        | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
INDEX_ID:index的標誌,等同SYS_INDEXES.ID
POS:列在index中的位置,從0開始
COL_NAME:列的名稱
通過上述相關表和列,然後結合MySQL相關恢複工具,就可以從底層在InnoDB出現問題,或者誤操作之時提供恢複處理.