In the previous article, I talked about the recovery of the MySQL data dictionary ( using the tool to directly extract the MySQL data dictionary , lacking the SYS_FIELDS table).There are several main data dictionaries.In this article, I mainly explain the meaning of these data dictionaries.For everyone to have a deeper understanding of mysql recovery processing ideas

   MySQL restore dictionary table

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| SYS_COLUMNS    | 
| SYS_FIELDS     | 
| SYS_INDEXES    | 
| SYS_TABLES     | 
+----------------+
4 rows in set (0.00 sec)

   SYS_TABLES

   This table is one of the most core tables restored by mysql, mainly to record the information of the database table in InnoDB. It is written by default in InnoDB where index_ids is 1, and its root page is on page 8.Its main column structure is:

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: As the name implies, it is the name of the table, but note that the format of his record is db/table, for example: xifenfei/zx_users, represented as the zx_users table in the xifenfei database

   ID: the number of the table

   N_COLS: The number of columns contained in the table

   The TYPE, MIX_ID, MIX_LEN and CLUSTER_NAME columns have no meaning for database recovery and will not be described

   SPACE: The label column of the table space.For example: ibdata1 is SPACE 0, ibdata2 is SPACE 1, each ibd file has its own table space label.

   SYS_INDEXES

   This is also one of the most core tables restored by mysql.It mainly records the index information of InnoDB.By default, the index_ids of InnoDB is 3, and its main structure is:

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: table identification column, SYS_TABLES.ID

   ID: It is the index number in InnoDB.This is very important in recovery.When recovering, you need to locate specific files based on this

   NAME: The name of the index of the main table, there are PRIMARY and ordinary column index information, generally we choose PRIMARY when recovering

   N_FIELDS: The table name index contains the number of columns, which is not important in mysql recovery

   TYPE: This column cannot be used during recovery, so no explanation

   PAGE: Same purpose as SYS_TABLES.SPACE

   PAGE_NO: It is marked as the page number of the root page of each index.The page structure in the index is shown in the following figure

SYS_COLUMNS

   This table mainly records the status of the columns of the table in the database.It is stored in index_id 2.It is mainly used to determine the status of the columns that need to be restored.If you know the complete column structure, the table is not necessary for MySQL recovery.The main structure is:

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: table identification column, SYS_TABLES.ID

   POS: The position in the table where the column is located, the value starts from 0

   NAME: the name of the column

   MTYPE and PRTYPE: Mainly to record the types of columns.This type of problem occurs mainly because InnoDB was not originally designed for MySQL, and later to better support MySQL, so two situations occurred.

   LEN: The length of the column.This requires attention to the encoding.For example, the database is encoded in utf8, and the defined varchar(10) is actually displayed as 30, because each character except English is encoded as 3 bytes.

   PREC: In some special types, the accuracy of the column definition

   SYS_FIELDS

   Record the distribution information of all index columns.It is stored in index_id 4.This table is not necessary for MySQL recovery.Its main structure is:

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: a sign of index, equivalent to SYS_INDEXES.ID

   POS: The position listed in the index, starting from 0

   COL_NAME: the name of the column

   Through the above-mentioned related tables and columns, and combined with MySQL-related recovery tools, you can provide recovery processing when there is a problem in InnoDB from the bottom, or when it is misoperation.