… or what I should keep in mind in case of disaster
To retrieve and maintain in SQL format the definition of all tables in a database, is a best practice that we all should adopt. To have that under version control is also another best practice to keep in mind.
While doing that may seem redundant, it can become a life saver in several situations. From the need to review what has historically changed in a table, to knowing who changed what and why… to when you need to recover your data and have your beloved MySQL instance not start…
But let’s be honest, only a few do the right thing, and even fewer keep that information up to date. Given that’s the case, what can we do when we have the need to discover/recover the table structure?
From the beginning, MySQL has used some external files to describe its internal structure.
For instance, if I have a schema named windmills and a table named wmillAUTOINC1, on the file system I will see this:
-rw-r-----. 1 mysql mysql 8838 Mar 14 2018 wmillAUTOINC1.frm
-rw-r-----. 1 mysql mysql 131072 Mar 14 2018 wmillAUTOINC1.ibd
The ibd file contains the data, while the frm file contains the structure information.
Putting aside ANY discussion about if this is safe, if it’s transactional and more… when we’ve experienced some major crash and data corruption this approach has been helpful. Being able to read from the frm file was the easiest way to get the information we need.
Simple tools like DBSake made the task quite trivial, and allowed us to script table definition when needed to run long, complex tedious data recovery:
[root@master1 windmills]# /opt/tools/dbsake frmdump wmillAUTOINC1.frm
--
-- Table structure for table `wmillAUTOINC1`
-- Created with MySQL Version 5.7.20
--
CREATE TABLE `wmillAUTOINC1` (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`uuid` char(36) COLLATE utf8_bin NOT NULL,
`millid` smallint(6) NOT NULL,
`kwatts_s` int(11) NOT NULL,
`date` date NOT NULL,
`location` varchar(50) COLLATE utf8_bin NOT NULL,
`active` tinyint(2) NOT NULL DEFAULT '1',
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`strrecordtype` char(3) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_millid` (`millid`,`active`),
KEY `IDX_active` (`id`,`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC;
Of course, if the frm file was also corrupt, then we could try to get the information from the ibdata dictionary. If that is corrupted too (trust me I’ve seen all of these situations) … well a last resource was hoping the customer has a recent table definition stored somewhere, but as mentioned before, we are not so diligent, are we?
Now, though, in MySQL8 we do not have FRM files, they were dropped. Even more interesting is that we do not have the same dictionary, most of the things that we knew have changed, including the dictionary location. So what can be done?
Well Oracle have moved the FRM information—and more—to what is called Serialized Dictionary Information (SDI), the SDI is written INSIDE the ibd file, and represents the redundant copy of the information contained in the data dictionary.
The SDI is updated/modified by DDL operations on tables that reside in that tablespace. This is it: if you have one file per table normally, then you will have in that file ONLY the SDI for that table, but if you have multiple tables in a tablespace, the SDI information will refer to ALL of the tables.
To extract this information from the IBD files, Oracle provides a utility called ibd2sdi. This application parses the SDI information and reports a JSON file that can be easily manipulated to extract and build the table definition.
One exception is represented by Partitioned tables. The SDI information is contained ONLY in the first partition, and if you drop it, it is moved to the next one. I will show that later.
But let’s see how it works. In the next examples I will look for the table’s name, attributes, and datatype starting from the dictionary tables.
To obtain the info I will do this:
/opt/mysql_templates/mysql-8P/bin/./ibd2sdi /opt/mysql_instances/master8/data/mysql.ibd |jq '.[]?|.[]?|.dd_object?|("------------------------------------"?,"TABLE NAME = ",.name?,"****",(.columns?|.[]?|(.name?,.column_type_utf8?)))'
The result will be something like:
"------------------------------------"
"TABLE NAME = "
"tables"
"****"
"id"
"bigint(20) unsigned"
"schema_id"
"bigint(20) unsigned"
"name"
"varchar(64)"
"type"
"enum('BASE TABLE','VIEW','SYSTEM VIEW')"
"engine"
"varchar(64)"
"mysql_version_id"
"int(10) unsigned"
"row_format"
"enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged')"
"collation_id"
"bigint(20) unsigned"
"comment"
"varchar(2048)"
<snip>
"------------------------------------"
"TABLE NAME = "
"tablespaces"
"****"
"id"
"bigint(20) unsigned"
"name"
"varchar(259)"
"options"
"mediumtext"
"se_private_data"
"mediumtext"
"comment"
"varchar(2048)"
"engine"
"varchar(64)"
"DB_TRX_ID"
""
"DB_ROLL_PTR"
""
I cut the output for brevity, but if you run the above command yourself you’ll be able to see that this retrieves the information for ALL the tables residing in the IBD.
The other thing I hope you noticed is that I am NOT parsing ibdata, but mysql.ibd. Why? Because the dictionary was moved out from ibdata and is now in mysql.ibd.
Look what happens if I try to parse ibdata:
[root@master1 ~]# /opt/mysql_templates/mysql-8P/bin/./ibd2sdi /opt/mysql_instances/master8/data/ibdata1 |jq '.'
[INFO] ibd2sdi: SDI is empty.
Be very careful here to not mess up your mysql.ibd file.
Now what can I do to get information about my wmillAUTOINC1 table in MySQL8?
That is quite simple:
/opt/mysql_templates/mysql-8P/bin/./ibd2sdi /opt/mysql_instances/master8/data/windmills/wmillAUTOINC.ibd |jq '.'
[
"ibd2sdi",
{
"type": 1,
"id": 1068,
"object": {
"mysqld_version_id": 80013,
"dd_version": 80013,
"sdi_version": 1,
"dd_object_type": "Table",
"dd_object": {
"name": "wmillAUTOINC",
"mysql_version_id": 80011,
"created": 20180925095853,
"last_altered": 20180925095853,
"hidden": 1,
"options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=1;row_type=2;stats_auto_recalc=0;stats_sample_pages=0;",
"columns": [
{
"name": "id",
"type": 9,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": true,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 1,
"char_length": 11,
"numeric_precision": 19,
"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": "AAAAAAAAAAA=",
"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": "table_id=1838;",
"column_key": 2,
"column_type_utf8": "bigint(11)",
"elements": [],
"collation_id": 83,
"is_explicit_collation": false
},
<SNIP>
"indexes": [
{
"name": "PRIMARY",
"hidden": false,
"is_generated": false,
"ordinal_position": 1,
"comment": "",
"options": "flags=0;",
"se_private_data": "id=2261;root=4;space_id=775;table_id=1838;trx_id=6585972;",
"type": 1,
"algorithm": 2,
"is_algorithm_explicit": false,
"is_visible": true,
"engine": "InnoDB",
<Snip>
],
"foreign_keys": [],
"partitions": [],
"collation_id": 83
}
}
},
{
"type": 2,
"id": 780,
"object": {
"mysqld_version_id": 80011,
"dd_version": 80011,
"sdi_version": 1,
"dd_object_type": "Tablespace",
"dd_object": {
"name": "windmills/wmillAUTOINC",
"comment": "",
"options": "",
"se_private_data": "flags=16417;id=775;server_version=80011;space_version=1;",
"engine": "InnoDB",
"files": [
{
"ordinal_position": 1,
"filename": "./windmills/wmillAUTOINC.ibd",
"se_private_data": "id=775;"
}
]
}
}
}
]
The JSON will contains:
- A section describing the DB object at high level
- Array of columns and related information
- Array of indexes
- Partition information (not here but in the next example)
- Table space information
That is a lot more detail compared to what we had in the FRM, and it is quite relevant and interesting information as well.
Once you have extracted the SDI, any JSON parser tool script can generate the information for the SQL DDL.
I mention partitions, so let’s look at this a bit more, given they can be tricky.
As mentioned, the SDI information is present ONLY in the first partition. All other partitions hold ONLY the tablespace information. Given that, then the first thing to do is to identify which partition is the first… OR simply try to access all partitions, and when you are able to get the details, extract them.
The process is the same:
[root@master1 ~]# /opt/mysql_templates/mysql-8P/bin/./ibd2sdi /opt/mysql_instances/master8/data/windmills/wmillAUTOINCPART#P#PT20170301.ibd |jq '.'
[
"ibd2sdi",
{
"type": 1,
"id": 1460,
"object": {
"mysqld_version_id": 80013,
"dd_version": 80013,
"sdi_version": 1,
"dd_object_type": "Table",
"dd_object": {
"name": "wmillAUTOINCPART",
"mysql_version_id": 80013,
"created": 20181125110300,
"last_altered": 20181125110300,
"hidden": 1,
"options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=1;row_type=2;stats_auto_recalc=0;stats_sample_pages=0;",
"columns": [<snip>
"schema_ref": "windmills",
"se_private_id": 18446744073709552000,
"engine": "InnoDB",
"last_checked_for_upgrade_version_id": 80013,
"comment": "",
"se_private_data": "autoinc=31080;version=2;",
"row_format": 2,
"partition_type": 7,
"partition_expression": "to_days(`date`)",
"partition_expression_utf8": "to_days(`date`)",
"default_partitioning": 1,
"subpartition_type": 0,
"subpartition_expression": "",
"subpartition_expression_utf8": "",
"default_subpartitioning": 0,
],
<snip>
"foreign_keys": [],
"partitions": [
{
"name": "PT20170301",
"parent_partition_id": 18446744073709552000,
"number": 0,
"se_private_id": 1847,
"description_utf8": "736754",
"engine": "InnoDB",
"comment": "",
"options": "",
"se_private_data": "autoinc=0;version=0;",
"values": [
{
"max_value": false,
"null_value": false,
"list_num": 0,
"column_num": 0,
"value_utf8": "736754"
}
],
The difference, as you can see, is that the section related to partitions and sub partitions will be filled with all the details you might need to recreate the partitions.
We will have:
- Partition type
- Partition expression
- Partition values
- …more
Same for sub partitions.
Now again see what happens if I parse the second partition:
[root@master1 ~]# /opt/mysql_templates/mysql-8P/bin/./ibd2sdi /opt/mysql_instances/master8/data/windmills/wmillAUTOINCPART#P#PT20170401.ibd |jq '.'
[
"ibd2sdi",
{
"type": 2,
"id": 790,
"object": {
"mysqld_version_id": 80011,
"dd_version": 80011,
"sdi_version": 1,
"dd_object_type": "Tablespace",
"dd_object": {
"name": "windmills/wmillAUTOINCPART#P#PT20170401",
"comment": "",
"options": "",
"se_private_data": "flags=16417;id=785;server_version=80011;space_version=1;",
"engine": "InnoDB",
"files": [
{
"ordinal_position": 1,
"filename": "./windmills/wmillAUTOINCPART#P#PT20170401.ibd",
"se_private_data": "id=785;"
}
]
}
}
}
]
I will get only the information about the tablespace, not the table.
As promised let me show you now what happens if I delete the first partition, and the second partition becomes the first:
(root@localhost) [windmills]>alter table wmillAUTOINCPART drop partition PT20170301;
Query OK, 0 rows affected (1.84 sec)
Records: 0 Duplicates: 0 Warnings: 0
[root@master1 ~]# /opt/mysql_templates/mysql-8P/bin/./ibd2sdi /opt/mysql_instances/master8/data/windmills/wmillAUTOINCPART#P#PT20170401.ibd |jq '.'|more
[
"ibd2sdi",
{
"type": 1,
"id": 1461,
"object": {
"mysqld_version_id": 80013,
"dd_version": 80013,
"sdi_version": 1,
"dd_object_type": "Table",
"dd_object": {
"name": "wmillAUTOINCPART",
"mysql_version_id": 80013,
"created": 20181129130834,
"last_altered": 20181129130834,
"hidden": 1,
"options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=1;row_type=2;stats_auto_recalc=0;stats_sample_pages=0;",
"columns": [
{
"name": "id",
"type": 9,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": true,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 1,
As I mentioned before, each DDL updates the SDI, and here we go: I will have all the information on what’s NOW the FIRST partition. Please note the value of the attribute “created” between the first time I queried the other partition, and the one that I have now:
/opt/mysql_instances/master8/data/windmills/wmillAUTOINCPART#P#PT20170301.ibd
"created": 20181125110300,
/opt/mysql_instances/master8/data/windmills/wmillAUTOINCPART#P#PT20170401.ibd
"created": 20181129130834,
To be clear the second created is NOW (PT20170401) from when I dropped the other partition (PT20170301).
Conclusions
In the end, this solution is definitely more powerful than the FRM files. It will allow us to parse the file and identify the table definition more easily, providing us with much more detail and information.
The problems will arise if and when the IBD file becomes corrupt.
As for the manual: For InnoDB, an SDI record requires a single index page, which is 16KB in size by default. However, SDI data is compressed to reduce the storage footprint.
By which it means that for each table I have a page, if I associate record=table. Which means that in case of IBD corruption I should (likely) be able to read those pages. Unless I have bad (very bad) luck.
I still wonder how the dimension of an IBD affects the SDI retrieval, but given I have not tried it yet I will have to let you know.
As an aside, I am working on a script to facilitate the generation of the SQL, it’s not yet ready but you can find it here
Last note but keep this in mind! It is stated in the manual but in a hidden place and in small letters:
DDL operations take longer due to writing to storage, undo logs, and redo logs instead of .frm files.
References
https://stedolan.github.io/jq/
https://dev.mysql.com/doc/refman/8.0/en/ibd2sdi.html
https://dev.mysql.com/doc/refman/8.0/en/serialized-dictionary-information.html
https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-limitations.html
—
Photo by chuttersnap on Unsplash
It’s been less than a year since the company 
