[ibd2sql] ibd2sql v1.4 新增 支持溢出页, 子分区

导读

ibd2sql是一个使用python3编写的离线解析MYSQL INNODB存储引擎的ibd文件的工具. 无第三方依赖包, 纯python3代码. 使用GPL-3.0 license.

ibd2sql v1.4 版本新增支持子分区, 溢出页(1页存不下, 就单独N页来存储). 完善测试案例, 修复已知BUG.

项目地址: https://github.com/ddcw/ibd2sql
v1.4版本下载地址: https://github.com/ddcw/ibd2sql/archive/refs/tags/v1.4.tar.gz

兼容性测试结果

版本太多了, 就选部分版本做测试.

版本	是否支持	备注
mysql-5.6.51	是	mysqlfrm解析datetime,time,timestamp会丢失精度,ENUM解析失败
mysql-5.7.17	是	mysqlfrm解析datetime,time,timestamp会丢失精度
mysql-5.7.27	是	mysqlfrm解析datetime,time,timestamp会丢失精度
mysql-5.7.35	是	mysqlfrm解析datetime,time,timestamp会丢失精度
mysql-5.7.38	是	mysqlfrm解析datetime,time,timestamp会丢失精度
mysql-5.7.41	是	mysqlfrm解析datetime,time,timestamp会丢失精度
mysql-5.7.44	是	mysqlfrm解析datetime,time,timestamp会丢失精度
mysql-8.0.12	是	不支持子分区(没得元数据信息)
mysql-8.0.13	是	不支持子分区(没得元数据信息)
mysql-8.0.16	是	不支持子分区(没得元数据信息)
mysql-8.0.18	是	不支持子分区(没得元数据信息)
mysql-8.0.22	是
mysql-8.0.24	是
mysql-8.0.26	是
mysql-8.0.28	是
mysql-8.0.30	是	ONLINE DDL(instant)不完全支持
mysql-8.0.32	是	ONLINE DDL(instant)不完全支持
mysql-8.0.33	是	ONLINE DDL(instant)不完全支持
mysql-8.0.36	是	ONLINE DDL(instant)不完全支持
mysql-8.0.37	是	ONLINE DDL(instant)不完全支持
mysql-8.4.0	是	ONLINE DDL(instant)不完全支持

参数

SHELL>python3 main.py --help
usage: main.py [--help] [--version] [--ddl] [--sql] [--delete]
[--complete-insert] [--force] [--set] [--multi-value]
[--replace] [--table TABLE_NAME] [--schema SCHEMA_NAME]
[--sdi-table SDI_TABLE] [--where-trx WHERE_TRX]
[--where-rollptr WHERE_ROLLPTR] [--limit LIMIT] [--debug]
[--debug-file DEBUG_FILE] [--page-min PAGE_MIN]
[--page-max PAGE_MAX] [--page-start PAGE_START]
[--page-count PAGE_COUNT] [--page-skip PAGE_SKIP] [--mysql5]
[FILENAME]

解析mysql 5.7/8.0的ibd文件 https://github.com/ddcw/ibd2sql

positional arguments:
FILENAME ibd filename

optional arguments:
--help, -h show help
--version, -v, -V show version
--ddl, -d print ddl
--sql print data by sql
--delete print data only for flag of deleted
--complete-insert use complete insert statements for sql
--force, -f force pasrser file when Error Page
--set set/enum to fill in actual data instead of strings
--multi-value single sql if data belong to one page
--replace "REPLACE INTO" replace to "INSERT INTO" (default)
--table TABLE_NAME replace table name except ddl
--schema SCHEMA_NAME replace table name except ddl
--sdi-table SDI_TABLE
read SDI PAGE from this file(ibd)(partition table)
--where-trx WHERE_TRX
default (0,281474976710656)
--where-rollptr WHERE_ROLLPTR
default (0,72057594037927936)
--limit LIMIT limit rows
--debug, -D will DEBUG (it's too big)
--debug-file DEBUG_FILE
default sys.stdout if DEBUG
--page-min PAGE_MIN if PAGE NO less than it, will break
--page-max PAGE_MAX if PAGE NO great than it, will break
--page-start PAGE_START
INDEX PAGE START NO
--page-count PAGE_COUNT
page count NO
--page-skip PAGE_SKIP
skip some pages when start parse index page
--mysql5 for mysql5.7 flag
Example:
ibd2sql /data/db1/xxx.ibd --ddl --sql
ibd2sql /data/db1/xxx.ibd --delete --sql
ibd2sql /data/db1/xxx#p#p1.ibd --sdi-table /data/db1/xxx#p#p0.ibd --sql
ibd2sql /mysql57/db1/xxx.ibd --sdi-table /mysql80/db1/xxx.ibd --sql --mysql5


FILENAME 目标文件, 即要解析的ibd文件

--help 仅打印帮助信息,不做任何操作

--version 仅展示版本信息, 不做任何操作

--ddl 打印目标文件的DDL信息.

--sql 打印目标文件的数据, 并拼接为SQL语句

--delete 打印目标文件被标记为deleted的数据, 需要和--sql联合使用.

--complete-insert 打印的SQL语句更完整, 即增加字段信息(某些数据库需要字段信息)

--force 对于某些可能报错的场景可以使用此选项跳过. 目前无实际使用场景.

--set 本来是对set/enum的值取int还是实际值, 现默认启用. 故此参数无效

--multi-value 对于生成的INSERT语句, 按照每页作为一个SQL语句. 即insert into table values(),(),();

--replace 使用replace语句代替insert语句. 和--multi-value冲突

--table 使用指定的表名替代元数据信息中的表名.

--schema 使用指定的库名替代元数据中的库名.

--sdi-table 指定元数据表文件. 对于5.x和分区表这种元数据信息不在指定的目标文件中, 则需要单独指定元数据文件.

--where-trx 指定事务范围. 默认(0,281474976710656)

--where-rollptr 指定回滚指针范围. 默认(0,72057594037927936)

--limit 仅打印N行数据. 同DML中的limit.

--debug 使用DEBUG功能, 会生成大量的解析日志信息.

--debug-file 当启用debug功能时, 可使用此选项指定debug日志文件. 默认stdout

--page-min 如果正在解析的页号小于这个值, 则跳过该页.

--page-max 如果正在解析的页号大于这个值, 则跳过.

--page-start 指定第一个数据页(叶子节点). 方便跳过坏块.

--page-count 解析的页数量. 通常和--page-start联合使用.

--page-skip 跳过的页数量.

--mysql5 如果是mysql 5.6/5.7 除了指定--sdi-table选项外, 还应指定这个选项, 方便ibd2sql失败为mysql5的数据文件.

使用例子

为了方便展示, 如下使用xxx.ibd文件来代替实际的ibd文件, 实际解析的时候需要 相对/绝对路径.

未特别说明的场景, 均是指mysql 8.x环境.

解析出表结构(DDL)

python3 main.py xxx.ibd --ddl


解析出数据(DML)

python3 main.py xxx.ibd --sql


解析表数据(DDL+DML)

python3 main.py xxx.ibd --ddl --sql


解析被误删的数据

python3 main.py xxx.ibd --sql --delete


分区表解析

分区表需要指定元数据信息

python3 main.py /data/mysql_3314/mysqldata/ibd2sql/ddcw_partition_range#p#p1.ibd --sql --sdi-table /data/mysql_3314/mysqldata/ibd2sql/ddcw_partition_range#p#p0.ibd


5.6 & 5.7

如果是mysql5.6或者5.7, 则需要先使用mysqlfrm提取元数据信息并写入到mysql8.0的环境中, 以供ibd2sql获取元数据信息.

# 提取出DDL
mysqlfrm /data/mysql_3308/mysqldata/db1/ddcw_alltype_table.frm --diagnostic

# 就可以使用本工具解析了
python3 main.py --sdi-table /data/mysql_3314/mysqldata/ibd2sql/ddcw_alltype_table.ibd /data/mysql_3308/mysqldata/db1/ddcw_alltype_table.ibd --sql --mysql5


注: mysqlfrm 存在 timestamp等数据类型的精度丢失问题. 需要人工确认. 如果可以直接从数据库里面获取元数据信息更好.

ibd文件损坏的场景

如果ibd文件数据页损坏, 则可以跳过该页, 或者暴力解析.

对于想并发解析的, 也可以使用本方法.

如果是ibd文件的元数据信息损坏, 则要先恢复元数据信息. 然后使用--sdi-table选项指定正确的元数据信息文件.

我这里没有做重定向, 是直接打印在屏幕上的(方便演示)

filename="/tmp/ddcw_alltype_table.ibd" # 要解析的ibd文件名
python3 main.py ${filename} --ddl # 获取表结构信息
filesize=`stat -c %s ${filename}`
maxpagecount=$[ ${filesize} / 16384 ]
current_page=1
while [ ${current_page} -le ${maxpagecount} ];do
echo "-- ${filename} PAGE NO: ${current_page}";
current_page=$[ ${current_page} + 1 ]
python3 main.py ${filename} --sql --page-start ${current_page} --page-count 1 2>/dev/null ;
done


IBD FILE

我们知道 mysql的表 由一个ibd文件组成, 如果是5.7的, 还有一个frm文件.

ibd文件被 按照PAGE_SIZE(16KB)分为若干个页. 为了方便管理页, 又整了个区(extent). 每个区固定1MB. 区多了之后,就有专门的 PAGE (XDES)来描述区信息. 对于数据/主键和(二级)索引 都是使用BTREE+来存的, 叶子节点(LEAF-PAGE)和非叶子节点(Non LEAF-PAGE) 是独立的链表, 也叫做段(segment), 所以每个索引都有2个段, 通常记录段的起始位置即可. 非叶子节点的起始页 叫做ROOT PAGE (传说的开始.)

ibd文件里面包含了多个索引(主键和普通索引), 每个索引由2个segment组成, 分别为 非叶子节点 和叶子节点. 每次空间申请以extent (通常1MB)为单位, 每个extent由若干个PAGE(16384)组成.

INDEX1
INDEX2 SEGMENT (none leaf page) EXTENT PAGE HEADER
TABLE --> IBD FILE --> PK --> --> EXTENT --> PAGE --> DATA
INDEX3 SEGMENT (leaf page) EXTENT PAGE TRAILER
INDEX4


page size | FSP_EXTENT_SIZE | Initial Size | Pages
----------+------------------+--------------+-------
4 KB | 256 pages = 1 MB | 16 MB | 4096
8 KB | 128 pages = 1 MB | 16 MB | 2048
16 KB | 64 pages = 1 MB | 16 MB | 1024
32 KB | 64 pages = 2 MB | 16 MB | 512
64 KB | 64 pages = 4 MB | 16 MB | 256


注: 未特殊说明, 均为大端字节序

PAGE

看起来那么多东西, 实际上都是由页组成的. 页都有固定构成,

1. FIL_HEADER,
2. PAGE_DATA(数据, 每种页都不一样),
3. FIL_TRAILER

FIL_HEADER

每个页开头的部分, 记录校验值, 上一页,下一页之类的信息

对于第一页FIL_PAGE_TYPE_FSP_HDR, FIL_PAGE_PREV是mysql server版本(花里胡哨的)

On page 0 of the tablespace, this is the server version ID

一共有 34 种PAGE. 本文只介绍一部分. 够用就行.

FIL_PAGE_INDEX = 17855;
FIL_PAGE_RTREE = 17854;
FIL_PAGE_SDI = 17853;
FIL_PAGE_TYPE_UNUSED = 1;
FIL_PAGE_UNDO_LOG = 2;
FIL_PAGE_INODE = 3;
FIL_PAGE_IBUF_FREE_LIST = 4;
FIL_PAGE_TYPE_ALLOCATED = 0;
FIL_PAGE_IBUF_BITMAP = 5;
FIL_PAGE_TYPE_SYS = 6;
FIL_PAGE_TYPE_TRX_SYS = 7;
FIL_PAGE_TYPE_FSP_HDR = 8;
FIL_PAGE_TYPE_XDES = 9;
FIL_PAGE_TYPE_BLOB = 10;
FIL_PAGE_TYPE_ZBLOB = 11;
FIL_PAGE_TYPE_ZBLOB2 = 12;
FIL_PAGE_TYPE_UNKNOWN = 13;
FIL_PAGE_COMPRESSED = 14;
FIL_PAGE_ENCRYPTED = 15;
FIL_PAGE_COMPRESSED_AND_ENCRYPTED = 16;
FIL_PAGE_ENCRYPTED_RTREE = 17;
FIL_PAGE_SDI_BLOB = 18;
FIL_PAGE_SDI_ZBLOB = 19;
FIL_PAGE_TYPE_LEGACY_DBLWR = 20;
FIL_PAGE_TYPE_RSEG_ARRAY = 21;
FIL_PAGE_TYPE_LOB_INDEX = 22;
FIL_PAGE_TYPE_LOB_DATA = 23;
FIL_PAGE_TYPE_LOB_FIRST = 24;
FIL_PAGE_TYPE_ZLOB_FIRST = 25;
FIL_PAGE_TYPE_ZLOB_DATA = 26;
FIL_PAGE_TYPE_ZLOB_INDEX = 27;
FIL_PAGE_TYPE_ZLOB_FRAG = 28;
FIL_PAGE_TYPE_ZLOB_FRAG_ENTRY = 29;
FIL_PAGE_TYPE_LAST = FIL_PAGE_TYPE_ZLOB_FRAG_ENTRY;


FIL_TRAILER

这个比较简单, 就结尾8字节, 4字节校验值, 4字节lsn. 校验值和FIL_HEADER做对比的

the low 4 bytes of this are used to store the page checksum, the last 4 bytes should be identical to the last 4 bytes of FIL_PAGE_LSN

FIL_PAGE_TYPE_FSP_HDR

先来看看第一页 (虽然编号是8). 这一页记录这个表空间/ibd的基础信息的. 基础结构如下

如果是 5.7 升级到8.0的, SDI_PAGE就不固定位置了, 所以要在FIL_PAGE_TYPE_FSP_HDR里面记录.

#MAGIC_SIZE=3 KEY_LEN=32 SERVER_UUID_LEN=36
#(MAGIC_SIZE + sizeof(uint32) + (KEY_LEN * 2) + SERVER_UUID_LEN + sizeof(uint32))
INFO_SIZE = 3+4+32*2+36+4
INFO_MAX_SIZE = INFO_SIZE + 4
SDI_OFFSET = 38+112+40*256 + INFO_MAX_SIZE
SDI_VERSION = 1


XDES信息等到FIL_PAGE_TYPE_XDES了再讲, 不然FIL_PAGE_TYPE_XDES就没啥讲的了.

FIL_PAGE_IBUF_BITMAP

第二页是insert buffer 页. 对解析数据没得帮助, 就跳过咯.

FIL_PAGE_INODE

第三页是 INODE 页, 就是记录有哪些segment的. segment对应索引.

也只有这上面三页是固定的.

如果一个inode page不够, 那就再来一个, 毕竟innodb 支持64个二级索引, 每个 192*2 字节就是24576字节了, 超过一般的16384了. 但我们这里不做普通索引的解析, 所以下一个INODE在哪就不关心了.

FSEG 就是前面说的段, 2个段对应一个索引(非叶子节点和叶子节点), 通常非叶子节点是准确的, 但叶子节点信息就不对了. 所以需要我们通过 非叶子节点去寻找 叶子节点. 等后面讲INDEX_PAGE的时候再细谈.

FIL_PAGE_SDI

SDI PAGE是记录元数据信息的页, 8.0才有的(所以没了frm文件). 基本上和INDEX PAGE 一样(编号也接近).

结构如下:

我们可以使用官方的 ibd2sdi解析ibd文件得到元数据信息, 美化过的JSON数据. 我们也可以自己解析.

参考代码: https://cloud.tencent.com/developer/article/2272631

offset = struct.unpack('>H',self.bdata[PAGE_NEW_INFIMUM-2:PAGE_NEW_INFIMUM])[0] + PAGE_NEW_INFIMUM
dtype,did = struct.unpack('>LQ',self.bdata[offset:offset+12])
dtrx = int.from_bytes(self.bdata[offset+12:offset+12+6],'big')
dundo = int.from_bytes(self.bdata[offset+12+6:offset+12+6+7],'big')
dunzip_len,dzip_len = struct.unpack('>LL',self.bdata[offset+33-8:offset+33])
unzbdata = zlib.decompress(self.bdata[offset+33:offset+33+dzip_len])
dic_info = json.loads(unzbdata.decode())
return dic_info if len(unzbdata) == dunzip_len else {}


解析出来之后可以人工拼接为DDL, 但是太麻烦了(属性太多了, ibd2sql也是最近才支持的子分区). 可以使用ibd2sql 将其转化为DDL.

前缀索引判断条件:

indexes[x]['elements'][x]['length'] < col['char_length']


索引类型判断

index[x]['type']如下值:
1: PRIMARY
2: UNIQUE
3: NORMAL


虚拟列无实际数据, 解析的时候要注意下.

8.0.12 及其之前 字段无 hidden属性

8.0.13 及其之前, 时间字段默认函数, 不能有括号

对于使用类似如下DDL 添加字段默认ALGORITHM是 INSTANT

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INSTANT;


为了快速添加字段, 会在元数据信息记录相关信息

dd_object: "se_private_data": "instant_col=1;"
column: "se_private_data": "default=636363;table_id=2041;"


FIL_PAGE_TYPE_XDES

这个PAGE对解析ibd文件其实没啥帮助的. 也就不细看了.

XDES格式如下:

FIL_PAGE_INDEX

INDEX PAGE就是记录数据的页, 也是本文的重点之一, 会花费较多的篇幅来介绍.

结构如下:

PAGE_HEADER 结构如下,:

基本上都是对数据查询有帮助的(各种链表), 我们解析ibd文件并不需要这么多信息.

为了后面解析 ROW, 我这里就还是称它为RECORD

INFIMUM 和 SUPEREMUM 分别表示最小字段和最大字段.

RECORD DATA 要分多种情况, 叶子节点/非叶子节点 下的 主键索引和二级索引.

主要是instant data这里比较坑, 数据是放在普通字段的后面的. 读取的时候要注意下. 怎么知道是否有做过instant呢? 这就是我们马上要讲的 record_header

record_header 结构如下:

这个next_record是指向的 下一个字段的record_header和key中间. 所以对于 var-length,null-bitmask,record_header都得反向读.

PAGE_DIRECTORY 只是查找数据方便, 使用2字节表示每个slot. 对于我们全量解析数据没用. 就不看了.

有了这些信息, 还不能解析数据, 因为对于非varchar类 的数据类型 存储方式千奇百怪(固定+metadata). 得等COLUMN TYPE讲完了来.

FIL_PAGE_TYPE_LOB_FIRST

参考: https://cloud.tencent.com/developer/article/2417124

当某行数据在1页里面记录不下的时候, 就放到溢出页里面, index_page只记录基础信息20字节, 格式如下:

这个SPACE_ID指向的就是 lob_first. 我们这里就先不看压缩页了.

FIL_PAGE_TYPE_LOB_FIRST 格式如下:

FLST_BASE_NODE_SIZE 这种结构, 之前讲过, 就是 4+6+6 也就是 LEN, PRE_PAGENO,PRE_OFFSET NEXT_PAGENO, NEXT_OFFSET.

如果是0/4294967295就表示没得上/下节点了

再来看看这个 LOB_PAGE_DATA, 就是ENTRY, 每个60字节, 第一页10个constexpr static ulint node_count() {return (10);} 参考: storage/innobase/include/lob0index.h :: index_entry_t

这里面就是记录 实际的值了, 全部加起来就是这行数据这个字段的 值了. 直接上表:

ENTRY:

OFFSET_PAGE_NO 就是指的LOB DATA的页号, OFFSET_DATA_LEN 就是lOB DATA页里面存储的数据大小(虽然是4字节, 实际只使用2字节).

FIL_PAGE_TYPE_LOB_INDEX

记录索引信息, 就是ENTRY, 比较简单.结构如下:

参考: storage/innobase/include/lob0index.h

FIL_PAGE_TYPE_LOB_DATA

存放LOB数据的, 结构同FIL_PAGE_TYPE_LOB_INDEX 就把entry换成 data就是了.

参考: storage/innobase/include/lob0pages.h

FRM FILE

mysql 5.7没得sdi page, 但是有frm文件, 这里面也是记录的元数据信息.

结构如下:

对象	大小	描述
frm_type	2	frm类型, 510 是表 22868 是视图
HEADER_FORMAT	64	文件头,
KEY		索引信息
default		默认值
engine		存储引擎(HEADER_FORMAT也有)
comment		表的注释
record		字段信息

看起来还是比较简单的. 接下来我们来看看详情.

frm_type就不看了, 没啥好说的, 就2字节, 510 是表 22868 是视图 .

不细看了, 感兴趣的自己去看: https://cloud.tencent.com/developer/article/2409341

参考: mysqlfrm

COLUMN TYPE

这一章主要讲mysql的数据类型. 严格来说是innodb的数据类型. 除了JSON,均为大端字节序

数字类型

数字类型, 存在符号. 符号使用第一bit位.

对象	大小	描述
tinyint	1
smallint	2
mediumint	3
int	4
float(n)	size = 4 if ext 剑圣无痕 ibdsqlv版本 数据运维 0 0 上一篇 Python Selenium：网页自动化的利器 下一篇 ubuntu怎么关闭正在运行的程序？ 相关文章 Oracle如何使用授予和撤销权限的语法和示例 Awesome Project: 探索 MatrixOrigin 云原生分布式数据库 下载丨66页PDF，云和恩墨技术通讯（2024年7月刊） 社区版oceanbase安装 Oracle 导出CSV工具-sqluldr2 ETL数据集成丨快速将MySQL数据迁移至Doris数据库 发布评论 取消回复 要发表评论，您必须先登录。 剑圣无痕 这个人很懒，什么都没有留下～ 文章8471 评论0 百度搜索：蓝易云 – centos7系统手动安装ceph教程 出行365：用国产数据库为国人出行“护航” tidb8.1的磁盘选择，关于网络ssd，和本地ssd的选择对性能影响很大，差距60倍。