未加星标

MySQL 8 and The FRM Drop… How To Recover Table DDL

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二04 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

… or what I should keep in mind in case of disaster


MySQL 8 and The FRM Drop… How To Recover Table DDL

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>

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

代码区博客精选文章
分页:12
转载请注明
本文标题:MySQL 8 and The FRM Drop… How To Recover Table DDL
本站链接:https://www.codesec.net/view/621381.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(73)