SDI of MySQL 8.0 Data Dictionary

Talking about the data dictionary changes from MySQL 5.7 to 8.0.23, of course, the original frm file is gone, and there is an extra mysql.ibd file. What has changed? It's just a replacement of the file name.

1. What is SDI

Serialized Dictionary Information refers to table structure metadata. MySQL 8.0 provides crash safety by serializing metadata when the metadata changes. Its output is in JSON (JavaScript Object Notation) format, called Serialized Dictionary Information (SDI).

For InnoDB tables, SDI is stored together with the data in the InnoDB user tablespace. For MyISAM and other storage engines, it is written to the .sdi file in the data directory.

Except for temporary tablespace and undo tablespace files, SDI exists in all InnoDB tablespace files. The SDI records in the InnoDB table space file only describe the tables and table space objects contained in the table space.

SDI data is updated through DDL operations on tables or checklists.

The MySQL server uses internal APIs accessed during DDL operations to create and maintain SDI records.

When the MySQL server is upgraded to a new version or version, the SDI data will not be updated.

Data recording method: For InnoDB, an SDI record requires an index page, and the default size is 16KB. But the actual SDI data is compressed to reduce storage space.

Partition table:
For partitioned InnoDB tables composed of multiple table spaces, SDI data is stored in the table space file of the first partition.

Note that this SDI is only a backup of metadata. It is not the metadata itself. The data dictionary completely exists in the InnoDB data dictionary table space.

2. SDI file content analysis

The existence of SDI data provides metadata redundancy. Then, if the data dictionary is not available, you can use the ibd2sdi tool to extract object metadata directly from the InnoDB tablespace file.

ibd2sdi is a utility for extracting serialized dictionary information (SDI) from InnoDB tablespace files, and the export format is JSON. All InnoDB tablespace files have SDI data.

2.ibd2sdi can

  • Run (*.ibd files) files on file-per-table tablespace files,
  • General table space files (*.ibd files).
  • System tablespace files (ibdata* files)
  • Data dictionary table space (mysql.ibd).
  • It does not support temporary tablespace or undo tablespace.
  • ibd2sdi can be used at runtime or when the server is offline. When performing SDI-related DDL operations, rollback operations, and undo log purge operations, there may be a short interval in which ibd2sdi fails to read the SDI data stored in the table space.

The current function of ibd2sqi is simply to extract dictionary information. The core is to extract the data part.

 

[[email protected] mysql]# ibd2sdi -v ibd2sdi Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL) [[email protected] emp]# ibd2sdi ./departments.ibd

image.png


For example:
1.sdi_version->dd_version:
upgrade from mysql8.0.19 to 8.0.23 version
2.created, last_altered: creation time is the last update time
3. What are the fields, length, engine file path, etc.

 

[[email protected] mysql]# ibd2sdi --skip-data ./db1/a1.ibd ["ibd2sdi", { "type": 1, "id": 1222 }, { "type": 2, "id": 142 }]

For the direct cat sdi file of the MyISAM engine:

image.png

3. Data recovery operation

1. MyISAM engine table recovery operation

image.png
 
##创建表 模拟数据mysql>CREATE TABLE `depart_myisam` (  `dept_no` char(4) COLLATE utf8mb4_bin NOT NULL,  `dept_name` varchar(40) COLLATE utf8mb4_bin NOT NULL,  PRIMARY KEY (`dept_no`)) ENGINE=MYISAM;mysql>INSERT INTO  depart_myisam(dept_no,dept_name)  VALUES('001','Beijing');##cp 数据文件之后mysql>DROP TABLE depart_myisam;##恢复操作,文件拷贝shell> cp depart_myisam_2389.sdi  /opt/data8.0/tmpshell> cp depart_myisam.*  /opt/data8.0/mysql/empshell>chown -R mysql.mysql  ./depart_myisam_2389.sdishell>chown -R mysql.mysql  ./depart_myisam.*##导入文件mysql>IMPORT TABLE FROM  '/opt/data8.0/tmp/depart_myisam_2389.sdi';

Remarks; The .sdi file can be placed in the directory named by the secure_file_priv system variable, in the data directory.
.MYD .MYI files are placed in the actual data directory

2. ibd recovery
Analyze the independent table space: the
following method simply uses the shell to manually create the table structure.

shell>ibd2sdi  --type=1 t1.ibd  --dump-file=/opt/script/json/t1.txtshell>cat t1.txt | grep -P 'name|column_type_utf8[" :]+\K[^"]+'

Remarks: table (type=1) and tablespace (type=2)

image.png

Note: When creating a new table, the PRIMARY information of the previous table must be required, and other indexes are not required.

image.png

Recovery operation:

#1.目标实例上,丢弃刚新创建的表的表空间mysql> ALTER TABLE t1 DISCARD TABLESPACE;#2.运行FLUSH TABLES…以暂停要导入的表。当表处于静默状态时,该表上只允许有只读事务。mysql> FLUSH TABLES t1 FOR EXPORT;##3.复制文件shell> cp /bak/world/t1.{ibd,cfg}  /datadir/worldshell>chown -R mysql.mysql /datadir/world/t1.*#4.使用UNLOCK TABLES来释放FLUSH TABLES获得的锁mysql> UNLOCK TABLES;##5.在目标实例上,导入表空间:mysql> ALTER TABLE t1 IMPORT TABLESPACE;

Remarks: If the original table structure is the best, it can only analyze the ibd file if it does not.
The ibd2sdi tool for table structure analysis is not as flexible as the mysql-utilities tool.
At present, it is necessary to perform secondary analysis through shell, jp or python.

to sum up

  • It can be said that the Serialized Dictionary Information (SDI) structure data dictionary looks very comfortable, and a lot of information is transparent, easy to understand and easy to read.
  • For the ibd2sdi tool, when the database is down and the system table structure cannot be extracted, the analysis is still more troublesome. So daily backup is very important. At present, there is no good way to find out how to locate the information indexed from ibd2sdi.

The improvement of technical capabilities stems from persistent efforts in obscurity every day!