Example of restoring a data dictionary table with MySQL exception recovery

I have mentioned in previous articles that mysql data dictionary restoration (using tools to extract MySQL data dictionary directly, without SYS_FIELDS tables). The main data dictionary is as follows, this article mainly explains the significance of these data dictionaries to help you better understand the mysql restoration and processing ideas.
MySQL restoration 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 core tables for mysql recovery. It mainly records the table information of the database in InnoDB. It is written in the index_ids of InnoDB by default, 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 table NAME, but note that the record format is db/table. For example, xifenfei/zx_users indicates the zx_users table in xifenfei database.
ID: Number of the table
N_COLS: number of columns included in the table
The TYPE, MIX_ID, MIX_LEN, and CLUSTER_NAME columns are not described for database restoration.
SPACE: The identifier column of the tablespace. For example, if ibdata1 is SPACE 0 and ibdata2 is SPACE 1, each ibd file has its own tablespace identifier.

SYS_INDEXES
This is also one of the core tables for mysql recovery. It records the index information of InnoDB. By default, the index_ids of InnoDB is 3. Its main structure is as follows:
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 label column, SYS_TABLES.ID
ID: ID of the index in InnoDB. This is very important in restoration. You need to locate the specific file based on this ID.
NAME: NAME of the index of the PRIMARY table, which contains the index information of the PRIMARY and common columns. When restoring the PRIMARY table, select PRIMARY.
N_FIELDS: the number of columns in the table name index, which is not important during mysql recovery.
TYPE: This column is not used during restoration.
PAGE: equivalent to SYS_TABLES.SPACE
PAGE_NO: indicates the page number of the root page of each index. The following figure shows the structure of the page in the index.

 

SYS_COLUMNS
This table records the columns of tables in the database. It is stored in index_id 2. it is mainly used to determine the columns in the table to be restored. If you know the full column structure, this table is not required for MySQL restoration. Its 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 label column, SYS_TABLES.ID
POS: Position of the column in the table. The value starts from 0.
NAME: column NAME
MTYPE and PRTYPE: This is mainly used to record the type of columns. This problem occurs mainly because InnoDB was not initially designed for MySQL and is later designed to better support MySQL, therefore, there are two situations.
LEN: column length. note the encoding. For example, if the database is utf8 encoded and the varchar (10) is defined, the actual length is 30 because each character except English is encoded as 3 bytes.
PREC: column precision definition in some special types

SYS_FIELDS

Records the distribution of all index columns. It is stored in index_id 4. This table is not required for MySQL restoration. Its main structure is as follows:

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 flag, equivalent to SYS_INDEXES.ID
POS: Position of the column in the index, starting from 0
COL_NAME: column name
With the related tables and columns and MySQL-related recovery tools, you can recover from problems in InnoDB at the underlying level or by mistake.