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

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
```shell

python3 main.py xxx.ibd --ddl

python3 main.py xxx.ibd --sql

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

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

# 然后导入到8.0环境(以获取SDI信息.)
....

# 就可以使用本工具解析了
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

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

                        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  

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;

#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

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 {}

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

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

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;"

(5,2)   整数就是2字节, 小数也是1字节
(10,3) 整数就是4+1字节, 小数就是2字节

固定3字节 1bit符号,  14bit年  4bit月  5bit日
-----------------------------------
|     signed   |     1  bit       |
-----------------------------------
|      year    |     14 bit       |
-----------------------------------
|     month    |     4  bit       |
-----------------------------------
|      day     |     5  bit       |
-----------------------------------

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|
---------------------------------------------------------------------

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  |
-------------------------------------------------------------------

4 bytes + fraction

year 使用1字节存储, 值+1900 即表示年	 范围: '1901' to '2115'

如果第一bit是1 就表示要使用2字节表示:
后面1字节表示 使用有多少个128字节, 然后加上前面1字节(除了第一bit)的数据(0-127) 就是最终数据
-----------------------------------------------------
| 1 bit flag | 7 bit data | if flag, 8 bit data*128 |
-----------------------------------------------------

                                                               - -----------------
                                                              | 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) |
                                         --------------------------------