[ibd2sql] ibd2sql v1.0 发布 & ibd文件结构说明
原创[ibd2sql] ibd2sql v1.0 发布 & ibd文件结构说明
原创
写在前面
ibd2sql v1.0 支持更多的数据类型, 比如新增了json类型
更多的表属性, 比如分区表, 虚拟字段, 唯一索引, 前缀索引等
也新增了debug功能和一些辅助debug的功能.
修复了一些之前的问题, 比如做过online ddl (instant)的表解析的时候就需要注意record header的第2bit 标记位.
注: 本文篇幅较长长长长. 其实也才2w字.
软件下载
ibd2sql 是纯python3代码写的, 无任何第三方依赖, 建议使用源码.
项目地址: https://github.com/ddcw/ibd2sql
二进制下载地址: linux : https://github.com/ddcw/ibd2sql/releases/download/v1.0/ibd2sql_v1.0_linux_x86_64.tar.gz
二进制下载地址: win: https://github.com/ddcw/ibd2sql/releases/download/v1.0/ibd2sql_v1.0_win_x86_64.zip
某盘:https://pan.baidu.com/s/1IP5ZDXIOMwlzW6QTp0b_UA提取码: ddcw
功能说明和演示
主要演示常用功能. 支持的功能可以使用--help查看
(venv) 12:07:29 [root@ddcw21 ibd2sql_v1.0_linux_x86_64]#./ibd2sql_v1.0 -h
usage: ibd2sql_v1.0 [-h] [--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] [--where WHERE]
[--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] [--parallel PARALLEL]
[FILENAME]
解析mysql8.0的ibd文件 https://github.com/ddcw/ibd2sql
positional arguments:
FILENAME ibd filename
optional arguments:
-h, --help show this help message and exit
--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)
--where WHERE filter data(TODO)
--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
--parallel PARALLEL, -p PARALLEL
parse to data/sql with N threads.(default 4) TODO
解析DDL
DDL支持如下. 基本上都支持了....
表: 字符集, 排序规则, 分区表, 表注释, schema/table替换, 分区表, 约束
字段: 是否为空,虚拟列, 默认值, 自增, 注释, 符号
索引: 普通/二级索引, 主键索引, 唯一索引,前缀索引,复合索引,虚拟列的索引
例子:
./ibd2sql_v1.0 /data/mysql_3314/mysqldata/ibd2sql/t20240109.ibd --table=newtablename --ddl
解析数据
支持的数据类型有 数字类, 时间类, blob/text类, json,set/enum,binary
不支持: 溢出页(置为null), 空间坐标
例子:
/ibd2sql_v1.0 /data/mysql_3314/mysqldata/ibd2sql/ddcw_alltype_table.ibd --sql --limit 1
./ibd2sql_v1.0 /data/mysql_3314/mysqldata/ibd2sql/ddcw_blob7.ibd --sql --limit 1
解析被删除的数据
解析被标记为deleted的数据, 在page_free链表, record header有delete flag的行
例子
./ibd2sql_v1.0 /data/mysql_3314/mysqldata/ibd2sql/ddcw_alltype_table.ibd --sql --delete
解析分区表
分区表的元数据是存放在第一个分区的ibd文件里的, 所以解析的时候要指定sdi信息.
例子: --sdi-table指定的是带有元数据信息的分区, 也就是第一个分区
/ibd2sql_v1.0 /data/mysql_3314/mysqldata/ibd2sql/ddcw_partition_hash#p#p1.ibd --sdi-table /data/mysql_3314/mysqldata/ibd2sql/ddcw_partition_hash#p#p0.ibd --sql --limit 2
debug
有时候可能遇到莫名其妙的问题, 这时候就需要debug了.
主要是 --debug 选项.
可以结合 --page-min --page-max --page-start --page-count --page-skip 来使用
例子:
./ibd2sql_v1.0 /data/mysql_3314/mysqldata/ibd2sql/ddcw_alltype_table.ibd --sql --delete --debug | more
ibd文件结构
ibd是按照page来存取的, 所以每个page都有固定的格式
|---> FIL_PAGE_SPACE_OR_CHECKSUM 4 bytes
|---> FIL_PAGE_OFFSET 4 bytes
|---> FIL_PAGE_PREV 4 bytes
|---> FIL_HEADER(38 bytes)-|---> FIL_PAGE_NEXT 4 bytes
| |---> FIL_PAGE_LSN 8 bytes
| |---> FIL_PAGE_TYPE 2 bytes
| |---> FIL_PAGE_FILE_FLUSH_LSN 8 bytes
| |---> FIL_PAGE_SPACE_ID 4 bytes
|
INNODB_PAGE(16K)-|---> PAGE_DATA
|
|
| |---> CHECKSUM 4 bytes
|---> FIL_TRAILER(8 bytes)-|
|---> FIL_PAGE_LSN 4 bytes
不同的page有不同的page_data.
若干个page 构成 1个 extent (1MB),
/* @mysql storage/innobase/include/fil0fil.h */
/** Initial size of an UNDO tablespace when it is created new
or truncated under low load.
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 */
每 256个区 有一个区描述符符(xdes)
xdes结构比较简单, 就是一堆的 xdes信息
|---> FIL_HEADER 38 bytes
|---> SPACE_HEADER (only fsp_hdr) 112 bytes
|---> XDES 0 40 bytes
XDES/FSP_HDR-|---> XDES ... 40 bytes
|---> XDES 255 40 bytes
|---> FIL_TRAILER 8 bytes
第一页比较特殊, 既是xdes, 也是fsp , 要记录 sdi 的页号(通常是4, 但如果是5..7 升上来的, 位置就不固定了, 所以要在fsp中记录.) .
mysql是使用cluster index来组织数据的, 所以所有的数据都是放在索引段(segment). 记录这些信息的page叫做 inode (index node). inode 通常位于第3页(pageno = 2). (第二页是insert buffer)
inode的结构也比较简单, 就是单纯的segment信息. 由于是btree+树存储的数据, 所以分为 叶子节点 和 非 叶子节点, 是双向链表. 也就需要使用2个segment来存储起始地址. 所以每2个segment对应一个索引.
由于mysql 8.0引入了sdi信息, 所以第一对segment固定是sdi的信息.
inode page结构如下
----------------------------------------------------------------
| FIL_HEADER(38 bytes) |
----------------------------------------------------------------
| INODE INFO(pre and next inode page)(12 bytes) |
----------------------------------------------------------------
| FSEG (SDI PAGE)(192 bytes) |
----------------------------------------------------------------
| FSEG (SDI PAGE)(192 bytes) |
----------------------------------------------------------------
| FSEG (general cluster index)(NONE LEAF PAGE)(192 bytes) |
----------------------------------------------------------------
| FSEG (general cluster index)(LEAF PAGE) (192 bytes) |
----------------------------------------------------------------
| FSEG (index)(NONE LEAF PAGE)(192 bytes) |
----------------------------------------------------------------
| FSEG (index)(LEAF PAGE) (192 bytes) |
----------------------------------------------------------------
| .............. |
----------------------------------------------------------------
| FIL_TRAILER(8 bytes) |
----------------------------------------------------------------我们要解析ibd数据, 除了要知道索引的起始位置外, 还要知道表结构信息(元数据), 存储元数据的Page 叫做sdi page , 由fsp记录. 其结构如下:
"""
|---> FIL_HEADER 38 bytes
|---> PAGE_HEADER 56 bytes
SDI_PAGE-|---> INFIMUM 13 bytes
|---> SUPEREMUM 13 bytes
|---> SDI_DATA xx
|---> PAGE_DIRECTORY xx
|---> FIL_TRAILER 8 bytes
"""
sdi_data 是 压缩的 json格式数据, 直接解压就行.
fsp和page_header结构开头都是差不多的, 都是fil_header + page_header + data + fil_trialer构成.
这里我们就看看page_header格式, 主要是PAGE_FREE记录的是标记为deleted的数据的第一条(也是链表.)
|---> PAGE_N_DIR_SLOTS 2 bytes
|---> PAGE_HEAP_TOP 2 bytes
|---> PAGE_N_HEAP 2 bytes
|---> PAGE_FREE 2 bytes
|---> PAGE_GARBAGE 2 bytes
|---> PAGE_LAST_INSERT 2 bytes
|---> PAGE_DIRECTION 2 bytes
PAGE_HEADER-|---> PAGE_N_DIRECTION 2 bytes
|---> PAGE_N_RECS 2 bytes
|---> PAGE_MAX_TRX_ID 2 bytes
|---> PAGE_LEVEL 2 bytes
|---> PAGE_INDEX_ID 2 bytes
|---> PAGE_BTR_SEG_LEAF 10 bytes
|---> PAGE_BTR_SEG_TOP 10 bytes
现在我们有了元数据信息和起始地址信息, 我们就可以去解析数据了
对于非leaf page而言 是存储的key 和 key对应的页的page_id, 而且没得trx(6)和rollptr(7). 解析的时候要注意下.
index page的data部分就是存储的数据, 是一行行串起来的.(为了方便查数据, 还在默认加了个page_directory来记录数据信息. 但解析数据并不需要这个. 因为每行数据都是串起来的, 所以我们只要找到第一行即可. 而每行最小行(PAGE_NEW_INFIMUM)和最大行(PAGE_NEW_SUPREMUM)都是固定的.(99,112)
所以我们只需要row的格式即可. row格式如下:
---------------------------------------------------------------------------
| variabels of length(1-2 byes) |
---------------------------------------------------------------------------
| null bitmask (1bit for per nullable filed) |
---------------------------------------------------------------------------
| FILED COUNT WITH TRX&ROLLPTR (for INSTANT) |
---------------------------------------------------------------------------
| record_header (5 bytes) |
---------------------------------------------------------------------------
| KEY/ROW_ID |
---------------------------------------------------------------------------
| TRX_ID (6 bytes) (only for leaf page) |
---------------------------------------------------------------------------
| ROLL_PTR (7 bytes) (only for leaf page) |
---------------------------------------------------------------------------
| Non-KEY FILEDS( PK only for seconday key) |
--------------------------------------------------------------------------每行数据都有5字节的record header来记录一些信息(比如下一页的相对地址), recorder header格式如下:
--------------------------------------------------------------------------------------------------------
| NO USE | (1 bit) | INSTANT FLAG |
--------------------------------------------------------------------------------------------------------
| NO USE | (1 bit) | 没使用 |
--------------------------------------------------------------------------------------------------------
| deleted | (1 bit) | 表示是否被标记为删除 |
--------------------------------------------------------------------------------------------------------
| min_rec | (1 bit) | 最小字段(except leaf) |
--------------------------------------------------------------------------------------------------------
| owned | (4 bit) | slot第一个字段才有, 记录这个slot大小 |
--------------------------------------------------------------------------------------------------------
| heap number | (13 bit) | 堆号(递增) 0:INFIMUM max:SUPREMUM (不一定准..) |
--------------------------------------------------------------------------------------------------------
| record_type | (3 bit) | 0:rec 1:no-leaf 2:min 3:max |
--------------------------------------------------------------------------------------------------------
| next_record | (16 bit) | 下一个字段的偏移量(距离当前offset) |
--------------------------------------------------------------------------------------------------------
这里的next_record是相对位置, 也就是有符号的int, 指向的位置是 下一行数据的 record header 和 key之间.
到这里ibd的结构大体就差不多了, 本文不涉及到 压缩页, blob页等特殊页.
innodb数据结构和计算
innodb的数据类型和mysql的数据存储方式不一样. 最大的区别就是 字节序, Innodb使用的大端, mysql使用的小端. 解析的时候要注意下. json格式的数据是mysql实现的, 也是小端, 后面将的时候也会提的.
数字类型
innodb的整型比较多, 如下:
对象 | 存储占用空间(字节) | 存储方式 | 范围(仅考虑有符号的情况) |
|---|---|---|---|
tinyint | 1 | -128-128 | |
smallint | 2 | 大端字节序 | -32768-32768 |
int | 4 | 大端字节序 | -2147483648-2147483648 |
float(n) | size = 4 if ext <= 24 else 8 | float | |
double | 8 | double | |
bigint | 8 | 大端字节序 | |
mediumint | 3 | 大端字节序 | -8388608-8388608 |
decimal(m,n) | |||
对于 整型, 存储方式如下. 其实就是第一bit为符号, 其它为大端取值. 主要是注意符号就行
整型均支持符号, 第一bit位为符号位(如果有符号的话) 取值方式为:
_t 是数据
_s 是字节数
(_t&((1<<_s)-1))-2**_s if _t < 2**_s and not is_unsigned else (_t&((1<<_s)-1))float和double都是标准类型, 直接取即可
double : struct.unpack('d',bdata)
float: struct.unpack('f',bdata)float有精度, 如果是float(24)及其以内就是4字节, 否则就是8字节(实际上就是double了)
decimal就比较复杂了.
整数部分和小数部分是分开的
每部分 的每9位10进制数占4字节, 剩余的就按 1-2 为1字节, 这样算
比如
(5,2) 整数就是2字节, 小数也是1字节
(10,3) 整数就是4+1字节, 小数就是2字节时间类型
对象 | 存储空间(字节) | 描述 | 取值范围 |
|---|---|---|---|
date | 3 | '1000-01-01' to '9999-12-31' | |
datetime(n) | 5+N | '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999' | |
time(n) | 3+N | '-838:59:59.000000' to '838:59:59.000000' | |
timestamp(n) | 4+N | '1000-01-01' to '9999-12-31' | |
year | 1 | +1900 | '1901' to '2115' |
时间类型存储还是比较简单的, 原则就是充分利用空间.
date
固定3字节 1bit符号, 14bit年 4bit月 5bit日
-----------------------------------
| signed | 1 bit |
-----------------------------------
| year | 14 bit |
-----------------------------------
| month | 4 bit |
-----------------------------------
| day | 5 bit |
-----------------------------------datetime
5 bytes + fractional seconds storage
1bit符号 year_month:17bit day:5 hour:5 minute:6 second:6
---------------------------------------------------------------------
| signed | 1 bit |
|--------------------------------------------------------------------
| year and month | 17 bit |
|--------------------------------------------------------------------
| day | 5 bit |
|--------------------------------------------------------------------
| hour | 5 bit |
|--------------------------------------------------------------------
| minute | 6 bit |
|--------------------------------------------------------------------
| second | 6 bit |
---------------------------------------------------------------------
| fractional seconds storage |each 2 digits is stored 1 byte|
---------------------------------------------------------------------
time
1bit符号 hour:11bit minute:6bit second:6bit 精度1-3bytes
-------------------------------------------------------------------
| signed | 1 bit |
-------------------------------------------------------------------
| hour | 11 bit |
-------------------------------------------------------------------
| minute | 6 bit |
-------------------------------------------------------------------
| second | 6 bit |
-------------------------------------------------------------------
| fractional seconds storage | each 2 digits is stored 1 byte |
-------------------------------------------------------------------
timestamp
4 bytes + fraction字符类型
字符类型会记录其占用空间大小(包括char类型), 使用1-2字节表示. 规则如下:
第一字节小于等于 128 字节时, 就1字节. 否则就第一字节超过128字节的部分 *256 再加上第二字节部分来表示
总大小 就是 256*256 = 65536 所以innodb限制varchar大小为64KB读取大小后, 再根据大小读取相关的数据并转码即可.
注: binary类型 不要做decode, 因为其是二进制类型, 不一定存在其对应的字符, 可以base64来表示.
类型 | 大小(字节) | 范围 | 备注 |
char(M) | L | <=255 字符 | |
BINARY(M) | M | <=255 字节 | |
VARCHAR(M), VARBINARY(M) | 1 字节长度 + L: 当 L < 1282 字节长度 + L: 当L >=128 | <=65535字节 | |
TINYBLOB, TINYTEXT | L + 1 bytes, where L < 256 | < 256 B | |
BLOB, TEXT | L + 2 bytes, where L < 2**16 | <=65535字节 | 仅非溢出页 |
MEDIUMBLOB, MEDIUMTEXT | L + 3 bytes, where L < 2**24 | 16M | 仅非溢出页 |
LONGBLOB, LONGTEXT | L + 4 bytes, where L < 2**32 | 4G | 仅非溢出页 |
其它类型
其它类型就是不好归类的, 比如set,enum, json
类型 | 大小 | 范围 | 备注 |
|---|---|---|---|
ENUM | 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) | 使用数字表示 | |
SET | 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) | 使用数字表示 | |
JSON | 仅非溢出页 | mysql二进制化的 | |
空间坐标 | 不支持 |
enum字段值是只能取一个, 所以使用1-2字节就能表示 64K种了. 但set 是 可以取多个值的, 所以要为每一个值分一个bit位来表示是否使用, 也就是8字节才能表示 8*8 = 64 种.
innodb对应json的存取就是当作blob来的. 因为mysql交给innodb的时候已经做二进制化了. 所以解析json格式实际上是解析的mysql的json数据类型.
格式如下, 基本上就是递归解析.
- -----------------
| JSON OBJECT/ARRAY |
- -----------------
|
-------------------------------------------------------------------------
| TYPE | ELEMENT_COUNT | KEY-ENTRY(if object) | VALUE-ENTRY | KEY | VALUE |
-------------------------------------------------------------------------
| | |
| | --------------
-------------------------- | | UTF8MB4 DATA |
| KEY-OFFSET | KEY-LENGTH | | --------------
-------------------------- |
|
--------------------------------
| TYPE | OFFSET/VALUE(if small) |
--------------------------------
small 2 bytes large 4 bytes
---------------------------------------------------
TYPE 1 byte
COUNT 2/4 bytes
SIZE 2/4 bytes
VALUE VALUE/OBJECT/ARRAY
---------------------------------------------------
---------------------------------------------------
OBJECT VALUE = KEY_ENTRY + VALUE_ENTRY + KEY + VALUE #KEY肯定是字符串, 所以不需要记录数据类型
ARRAY VALUE = VALUE_ENTRY + VALUE #不需要KEY
KEY_ENTRY = KEY_OFFSET(2/4bytes) + KEY_LNGTH(2 bytes)
VALUE_ENTRY = TYPE(1byte) + OFFSET(2/4 bytes)/VALUE (如果类型是int,literal之类的,就直接是值了, 否则就走OFFSET)
---------------------------------------------------
"""对于mysql的varchar类型, 记录其大小和Innodb有区别.
如果第一bit是1 就表示要使用2字节表示:
后面1字节表示 使用有多少个128字节, 然后加上前面1字节(除了第一bit)的数据(0-127) 就是最终数据
-----------------------------------------------------
| 1 bit flag | 7 bit data | if flag, 8 bit data*128 |
-----------------------------------------------------其它
这里主要是记录解析ibd文件过程中遇到的一些坑
分区表
分区表的元数据信息都放在第一个分区的.
dd['object']['partitions']
前缀索引,唯一索引
前缀索引判断条件:
indexes[x]['elements'][x]['length'] < col['char_length'] 如果是前缀索引, KEY位置存储的数据就不是完整的(主键为前缀索引的情况), 后面读剩余字段的时候还要包含前缀索引
index[x]['type']如下值:
1: PRIMARY
2: UNIQUE
3: NORMALONLINE DDL
对于使用类似如下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;"对于某行数据而言, 如果 record header中instant标记位为True, 则表示这行数据新增字段不是默认值, 而是要从数据位置读取(放在其它字段数据后面)
if recorde_header.instant and col['instant']:
read key
raed filed
read filed with instant
if not recorde_header.instant and col['instant']:
rad key
read field 新增字段取默认值新增了多个字段之后, 需要注意下不是每行数据的字段数量都相等, 这时候就要使用到有instant之后在null bitmask和recored header之间记录的行数量了(含trx&rollptr).脚本对应变量为_icc
checksum table的坑
使用ibd2sql解析了数据, 然后回写到数据库, 并使用checksum table来校验数据一致性, 却发现数据不一致.... debug半天 愣是没找到原因. 然后换字符串拼接select md5(group_concat(concat(id,k,c,pad,name))) from db1.sbtest4; 来校验数据, 发现一直的. 然后使用mysqldump来导出数据, 然后使用diff发现数据确实是一直的, 也就是checksum table再表做个instant后, 校验是存在一定问题的. bugs.mysql.com 也有类似的案例.....
最后说几句
1. 解析这个ibd文件还是花费了不少时间, 前后遇到了各种坑. 比如ONLINE DDL(instant) 操作, 跑得是快了, 但解析就麻烦了.
2. 测试的时候也花了不少时间, 后面还是写了个测试脚本来做 -_-.
3. 使用pyinstaller打包的时候, 需要注意路径, 可以编辑 main.spec修改如下内容: pathex 是工作路径, win环境记得把win的反斜杠(\) 替换为斜杆(/)
a = Analysis(['main.py'],
pathex=['/root/ei/ibd2sql_v1.0'],
binaries=[],
datas=[],
hiddenimports4. 有啥问题或者需求 可以在github上提, 也可以在文末留言.
5. 只支持8.0的mysql哈, 如果row_format不支持或者版本不对, 会提示的.
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
