除了将数据库对象的元数据存储在数据目录中,mysql还支持以序列化的格式进行存储。即序列化数据目录信息(SDI),SDI是表和表空间对象的序列化元数据。
innodb将SDI数据存储在自己的表空间中;ndbcluster将SDI数据存储在ndb目录中;其它存储引擎将SDI数据存储在.SDI文件中。
SDI数据以json格式生成。
SDI数据存在于innodb的所有表空间中,临时表空间和undo表空间除外。表空间中的SDI记录只是用来描述表空间中的表和表空间对象。
SDI数据跟着ddl操作或check table for upgrade操作更新。升级mysql server到新的版本不会更新SDI数据。
SDI数据的存在提供了元数据冗余。例如,如果数据字典不可用,可以从表空间文件中提取字典对象元数据。可以使用工具ibd2sdi直接从innodb的表空间文件中抽取出元数据信息。
在表空间文件中包含SDI数据会增加表空间文件的大小。对于innodb,一条SDI记录需要一个索引页,默认大小为 16KB。不过,SDI数据在存储时会进行压缩,以减少存储空间占用。不同的存储引擎,处理.SDI文件的方式也不同。
对于分区的innodb表,有多个表空间组成,SDI数据存储在第一个分区表空间文件中。
mysql server使用内部的api创建和维护SDI记录。
import table命令根据.SDI文件中信息导入myisam表。
ibd2sdi工具
ibd2sdi工具可以应用于独立表空间文件(*.ibd)、通用表空间文件(*.ibd)、系统表空间文件(*.ibdata),以及数据目录表空间(mysql.ibd)
ibd2sdi可以在线、离线使用。在ddl、回滚、以及undo日志purge操作时执行ibd2sdi可能会有短暂的读不到sdi数据。
ibd2sdi从指定的表空间执行未提交的SDI读取。不会访问重做日志和撤销日志。
帮助查看
$ ibd2sdi --help ibd2sdi Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL) Copyright (c) 2015, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Usage: ibd2sdi [-v] [-c <strict-check>] [-d <dump file name>] [-n] filename1 [filenames] See http://dev.mysql.com/doc/refman/8.0/en/ibd2sdi.html for usage hints. -h, --help Display this help and exit. -v, --version Display version information and exit. -d, --dump-file=name Dump the tablespace SDI into the file passed by user. Without the filename, it will default to stdout -s, --skip-data Skip retrieving data from SDI records. Retrieve only id and type. -i, --id=# Retrieve the SDI record matching the id passed by user. -t, --type=# Retrieve the SDI records matching the type passed by user. -c, --strict-check=name Specify the strict checksum algorithm by the user. Allowed values are innodb, crc32, none. -n, --no-check Ignore the checksum verification. -p, --pretty Pretty format the SDI output.If false, SDI would be not human readable but it will be of less size (Defaults to on; use --skip-pretty to disable.) Variables (--variable-name=value) and boolean options {FALSE|TRUE} Value (after reading options) --------------------------------- ---------------------------------------- dump-file (No default value) skip-data FALSE id 0 type 0 strict-check crc32 no-check FALSE pretty TRUE
ibd2sdi支持多文件的表空间,比如系统表空间,但同一时刻只能在一个表空间上执行。比如:
ibd2sdi ibdata1 ibdata2
测试
>create database abce; Query OK, 1 row affected (0.00 sec) >use abce; Database changed >CREATE TABLE t1 (c VARCHAR(16) NOT NULL); Query OK, 0 rows affected (0.02 sec) >CREATE TABLE t2 (c VARCHAR(16) NOT NULL DEFAULT "myabc"); Query OK, 0 rows affected (0.01 sec) # cd abce/ # ls -ltr total 224 -rw-r----- 1 mysql mysql 114688 Oct 17 17:13 t1.ibd -rw-r----- 1 mysql mysql 114688 Oct 17 17:13 t2.ibd # ibd2sdi -d t1.sdi t1.ibd # ls -ltr total 236 -rw-r----- 1 mysql mysql 114688 Oct 17 17:13 t1.ibd -rw-r----- 1 mysql mysql 114688 Oct 17 17:13 t2.ibd -rw------- 1 root root 9501 Oct 17 17:14 t1.sdi
可以看到列c的default_value是非空的,实际上两个表中都对c列做了填充:
# ibd2sdi t1.ibd | grep -m1 '\"default_value\"' | cut -b34- | sed -e s/,// "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAA=" # ibd2sdi t2.ibd | grep -m1 '\"default_value\"' | cut -b34- | sed -e s/,// "BW15YWJjAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAA="
可以使用json解释器工具jq来解析ibd2sdi的输出结果:
# cat t2.sdi |jq [ "ibd2sdi", { "type": 1, "id": 697, "object": { "mysqld_version_id": 80030, "dd_version": 80023, "sdi_version": 80019, "dd_object_type": "Table", "dd_object": { "name": "t2", "mysql_version_id": 80030, "created": 20231017091332, "last_altered": 20231017091332, "hidden": 1, "options": "avg_row_length=0;encrypt_type=N;explicit_encryption=0;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;", "columns": [ { "name": "c", "type": 16, "is_nullable": false, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": false, "is_virtual": false, "hidden": 1, "ordinal_position": 1, "char_length": 64, "numeric_precision": 0, "numeric_scale": 0, "numeric_scale_null": true, "datetime_precision": 0, "datetime_precision_null": 1, "has_no_default": false, "default_value_null": false, "srs_id_null": true, "srs_id": 0, "default_value": "BW15YWJjAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAA=", "default_value_utf8_null": false, "default_value_utf8": "myabc", "default_option": "", "update_option": "", "comment": "", "generation_expression": "", "generation_expression_utf8": "", "options": "interval_count=0;", "se_private_data": "table_id=1355;", "engine_attribute": "", "secondary_engine_attribute": "", "column_key": 1, "column_type_utf8": "varchar(16)", "elements": [], "collation_id": 255, "is_explicit_collation": false }, { "name": "DB_ROW_ID", "type": 10, "is_nullable": false, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": false, "is_virtual": false, "hidden": 2, "ordinal_position": 2, "char_length": 6, "numeric_precision": 0, "numeric_scale": 0, "numeric_scale_null": true, "datetime_precision": 0, "datetime_precision_null": 1, "has_no_default": false, "default_value_null": true, "srs_id_null": true, "srs_id": 0, "default_value": "", "default_value_utf8_null": true, "default_value_utf8": "", "default_option": "", "update_option": "", "comment": "", "generation_expression": "", "generation_expression_utf8": "", "options": "", "se_private_data": "table_id=1355;", "engine_attribute": "", "secondary_engine_attribute": "", "column_key": 1, "column_type_utf8": "", "elements": [], "collation_id": 63, "is_explicit_collation": false }, { "name": "DB_TRX_ID", "type": 10, "is_nullable": false, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": false, "is_virtual": false, "hidden": 2, "ordinal_position": 3, "char_length": 6, "numeric_precision": 0, "numeric_scale": 0, "numeric_scale_null": true, "datetime_precision": 0, "datetime_precision_null": 1, "has_no_default": false, "default_value_null": true, "srs_id_null": true, "srs_id": 0, "default_value": "", "default_value_utf8_null": true, "default_value_utf8": "", "default_option": "", "update_option": "", "comment": "", "generation_expression": "", "generation_expression_utf8": "", "options": "", "se_private_data": "table_id=1355;", "engine_attribute": "", "secondary_engine_attribute": "", "column_key": 1, "column_type_utf8": "", "elements": [], "collation_id": 63, "is_explicit_collation": false }, { "name": "DB_ROLL_PTR", "type": 9, "is_nullable": false, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": false, "is_virtual": false, "hidden": 2, "ordinal_position": 4, "char_length": 7, "numeric_precision": 0, "numeric_scale": 0, "numeric_scale_null": true, "datetime_precision": 0, "datetime_precision_null": 1, "has_no_default": false, "default_value_null": true, "srs_id_null": true, "srs_id": 0, "default_value": "", "default_value_utf8_null": true, "default_value_utf8": "", "default_option": "", "update_option": "", "comment": "", "generation_expression": "", "generation_expression_utf8": "", "options": "", "se_private_data": "table_id=1355;", "engine_attribute": "", "secondary_engine_attribute": "", "column_key": 1, "column_type_utf8": "", "elements": [], "collation_id": 63, "is_explicit_collation": false } ], "schema_ref": "abce", "se_private_id": 1355, "engine": "InnoDB", "last_checked_for_upgrade_version_id": 0, "comment": "", "se_private_data": "", "engine_attribute": "", "secondary_engine_attribute": "", "row_format": 2, "partition_type": 0, "partition_expression": "", "partition_expression_utf8": "", "default_partitioning": 0, "subpartition_type": 0, "subpartition_expression": "", "subpartition_expression_utf8": "", "default_subpartitioning": 0, "indexes": [ { "name": "PRIMARY", "hidden": true, "is_generated": false, "ordinal_position": 1, "comment": "", "options": "", "se_private_data": "id=797;root=4;space_id=292;table_id=1355;trx_id=622891;", "type": 2, "algorithm": 2, "is_algorithm_explicit": false, "is_visible": true, "engine": "InnoDB", "engine_attribute": "", "secondary_engine_attribute": "", "elements": [ { "ordinal_position": 1, "length": 4294967295, "order": 2, "hidden": true, "column_opx": 1 }, { "ordinal_position": 2, "length": 4294967295, "order": 2, "hidden": true, "column_opx": 2 }, { "ordinal_position": 3, "length": 4294967295, "order": 2, "hidden": true, "column_opx": 3 }, { "ordinal_position": 4, "length": 4294967295, "order": 2, "hidden": true, "column_opx": 0 } ], "tablespace_ref": "abce/t2" } ], "foreign_keys": [], "check_constraints": [], "partitions": [], "collation_id": 255 } } }, { "type": 2, "id": 297, "object": { "mysqld_version_id": 80030, "dd_version": 80023, "sdi_version": 80019, "dd_object_type": "Tablespace", "dd_object": { "name": "abce/t2", "comment": "", "options": "autoextend_size=0;encryption=N;", "se_private_data": "flags=16417;id=292;server_version=80030;space_version=1;state=normal;", "engine": "InnoDB", "engine_attribute": "", "files": [ { "ordinal_position": 1, "filename": "./abce/t2.ibd", "se_private_data": "id=292;" } ] } } } ]
输出结果的可读性仍然是很差的。
好在有人开发了工具sdi2ddl,可以将结果显示成show create table语句的效果。不过,不建议在生产环境使用该工具。
下载主页:
https://github.com/altmannmarcelo/sdi2ddl
- Information Dictionary Serialized MySQL SDIinformation dictionary serialized mysql dictionary present mysql 12599 initialization dictionary mysql8 failed mysql configuration information for information_schema information内存mysql5 serialized sdi shared_memory serialized pipeline message 串口 功能mcu sdi mdin高清380 sdi