A common practice among DBAs and developers is to copy table data and .frm files from the data dictionary. They often set up batch jobs to automate the recovery of these tables. This capability is also utilized in disaster recovery scenarios, where individuals well-versed in .frm files can reconstruct their metadata as needed.
In MySQL 8.0, the information is presented within serialized objects within the dictionary. In the case of InnoDB tablespaces, this information is incorporated into the tablespace itself, creating a fusion of metadata and data primarily to enhance performance. MySQL writes a .sdi file to accommodate the serialized dictionary information for storage engines that lack support for this functionality.
Purpose of .sdi files
Serialized dictionary information (SDI) files store serialized metadata about various database objects, such as tables, indexes, and other schema-related details. This serialized data is typically stored in a compact JSON format.
Storage engine compatibility
Different MySQL storage engines handle .sdi files in various ways.
InnoDB
One of the most widely used storage engines stores .sdi data within its tablespace files. This helps improve the performance of metadata retrieval and management for InnoDB tables.
The existence of SDI data offers redundancy in terms of metadata. For instance, in cases where the data dictionary becomes inaccessible, object metadata can be directly extracted from InnoDB tablespace files using the ibd2sdi tool.
The utility ibd2sdi is compatible with file-per-table tablespace files (.ibd files), general tablespace files (.ibd files), system tablespace files (ibdata* files), and the data dictionary tablespace (mysql.ibd). However, it is not intended for use with temporary or undo tablespaces.
Let’s test it with InnoDB using the ibd2sdi tool.
1 2 3 4 5 6 7 8 9 10 11 | [root@db3-node test]# ibd2sdi transaction_processing_flag.ibd -d transaction_processing_flag.sdi [root@db3-node test]# ls -lrth | grep transaction_processing_flag -rw-r-----. 1 mysql mysql 18M Apr 15 10:39 transaction_processing_flag.ibd -rw-r-----. 1 mysql mysql 160K Apr 15 10:46 transaction_processing_flag_new.ibd -rw-------. 1 root root 27K Sep 18 09:05 transaction_processing_flag.sdi [root@db3-node test]# |
Other storage engines
Some other storage engines in MySQL may store .sdi data in separate .sdi files created in the database directory for a specific table. The exact handling can vary depending on the storage engine.
I’ve generated a MyISAM table to illustrate what .sdi files appear like.
1 2 | [root@db3-node test]# ibd2sdi transaction_processing_flag.ibd -d transaction_processing_flag.sdi [root@db3-node test]# ls -lrth | grep transaction_processing_flag |
We can see that it has.sdi file generated
1 2 3 4 5 6 7 8 9 | [root@db3-node test]# ls -lrth | grep test -rw-r-----. 1 mysql mysql 2.8K Sep 18 08:15 test_table_714.sdi -rw-r-----. 1 mysql mysql 1.0K Sep 18 08:15 test_table.MYI -rw-r-----. 1 mysql mysql 0 Sep 18 08:15 test_table.MYD [root@db3-node test]# |
RocksDb also supports SDI.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | mysql> create database test_rocksdb; Query OK, 1 row affected (0.01 sec) mysql> use test_rocksdb; Database changed mysql> create table test (id int) engine=rocksdb; Query OK, 0 rows affected (0.02 sec) mysql> q Bye [root@mysql-test mysql]# cd test_rocksdb [root@mysql-test test_rocksdb]# ls -lrth total 4.0K -rw-r-----. 1 mysql mysql 1.7K Sep 29 19:41 test_394.sdi [root@mysql-test test_rocksdb]# |
Updating .sdi files
.sdi files are typically updated when certain database operations are performed, such as Data Definition Language (DDL) operations on tables or using the “CHECK TABLE FOR UPGRADE” command. These operations can trigger changes to the metadata, and .sdi files are updated to reflect these changes.
SDI data does not receive updates while upgrading the MySQL server to a new release or version.
Whenever you change a table using an engine that requires an SDI file, a new file is created with a distinct OID (Object ID) number, effectively replacing any previous file.
Note: In the case of the InnoDB storage engine, SDI is updated and stored inside the IBD files when certain database operations are performed, such as Data Definition Language (DDL).
Parsed the .sdi using JSON parser (jq parser)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 | [root@db3-node test]# cat test_table_715.sdi |jq { "mysqld_version_id": 80034, "dd_version": 80023, "sdi_version": 80019, "dd_object_type": "Table", "dd_object": { "name": "test_table", "mysql_version_id": 80034, "created": 20230918081536, "last_altered": 20230918081536, "hidden": 1, "options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;", "columns": [ { "name": "id", "type": 4, "is_nullable": false, "is_zerofill": false, "is_unsigned": true, "is_auto_increment": true, "is_virtual": false, "hidden": 1, "ordinal_position": 1, "char_length": 10, "numeric_precision": 10, "numeric_scale": 0, "numeric_scale_null": false, "datetime_precision": 0, "datetime_precision_null": 1, "has_no_default": false, "default_value_null": false, "srs_id_null": true, "srs_id": 0, "default_value": "AAAAAA==", "default_value_utf8_null": true, "default_value_utf8": "", "default_option": "", "update_option": "", "comment": "", "generation_expression": "", "generation_expression_utf8": "", "options": "interval_count=0;", "se_private_data": "", "engine_attribute": "", "secondary_engine_attribute": "", "column_key": 2, "column_type_utf8": "int unsigned", "elements": [], "collation_id": 8, "is_explicit_collation": false }, { "name": "name", "type": 16, "is_nullable": true, "is_zerofill": false, "is_unsigned": false, "is_auto_increment": false, "is_virtual": false, "hidden": 1, "ordinal_position": 2, "char_length": 20, "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": "interval_count=0;", "se_private_data": "", "engine_attribute": "", "secondary_engine_attribute": "", "column_key": 1, "column_type_utf8": "varchar(20)", "elements": [], "collation_id": 8, "is_explicit_collation": false } ], "schema_ref": "test", "se_private_id": 18446744073709552000, "engine": "MyISAM", "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": false, "is_generated": false, "ordinal_position": 1, "comment": "", "options": "flags=0;", "se_private_data": "", "type": 1, "algorithm": 2, "is_algorithm_explicit": false, "is_visible": true, "engine": "MyISAM", "engine_attribute": "", "secondary_engine_attribute": "", "elements": [ { "ordinal_position": 1, "length": 4, "order": 2, "hidden": false, "column_opx": 0 } ] } ], "foreign_keys": [], "check_constraints": [], "partitions": [], "collation_id": 8 } } |
sdi2ddl – Convert SDI JSON to SHOW CREATE TABLE statement
One of my coworkers has created a tool called sdi2ddl, which enables the conversion of MySQL SDI into a SHOW CREATE TABLE statement. MySQL 8 introduced a shift away from conventional .frm files, opting instead for a transactional data dictionary stored within the mysql.ibd file. There are instances when retrieving the Data Definition Language (DDL) from a .ibd file becomes essential, as this capability facilitates the potential recovery of a table through DISCARD/IMPORT TABLESPACE procedures.
Caution: The sdi2ddl tool/script is not for production and should be handled carefully at your risk.
Another blog post explains how Percona XtraBackup depends on SDI to perform rollback and undo operations during the preparation phase: 20X Faster Backup Preparation With Percona XtraBackup 8.0.33-28.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
