ddcw / ibd2sql Goto Github PK
View Code? Open in Web Editor NEWparse mysql ibd file to sql for learn or recovery data
License: GNU General Public License v3.0
parse mysql ibd file to sql for learn or recovery data
License: GNU General Public License v3.0
你就完全支持嘛!搞什么适用于损坏恢复的ibd文件,其实就是只适应简单的损坏,严重觉得你就是沽名钓誉,有什么意思。 你把py搞成C代码不好吗?搞PY问题一大堆。
我在解析数据的时候,发现datetime类型和int类型的数据解析有问题。会成-3954-2-26 3:20:54、-1257699829类似的这种格式。请问这个要怎么修复呀
解析表结果时,如果列类型 timestamp且有默认值是,建表语句
xx timestamp default 'current_timestamp' ,不需要''
如果blob/text字段,在key里面,会丢失,长度
key(xx,text) ---> key(xx,text(11))
bigint类型数据做ddl里是正常能解析出来的,但是在数据里bigint类型数据都是None,而且主键字段好像也没有生成ddl标识,可以优化吗
执行命令:
python3 main.py /opt/user.ibd --debug --complete-insert --sql --delete
没有任何数据但是把--delete去掉后 可以恢复数据,但是我其实是使用delete from user 直接删除的。
1/首先感谢,在已经绝望的情况下,google看到了这个解决方案,虽然不能完美解决问题,但确实提供了许多帮助。
2/在可完全恢复的文件,从sql里面看到 decimal(10,2)的数据类型部分正确,部分变成超长的负数,大部分情况下代表的是0,还有一些null的情况变成一串0x....的长字符串
3/部分ibd文件无法恢复,只能恢复部分,一般是表字符CHARSET=utf8mb3,但实际表是完整无损的。
4/最后,其实我是在清理磁盘空间的时候,清空了undo_001,undo_002,最终发现把四个文件重命名undo_001,undo_002,ib_logfile0,ib_logfile1,重启mysql,重新生成了四个文件,数据都是原来的,一切都恢复正常了,当然可能部分提交中的数据有丢失也未知。
[2024-03-21 16:59:54] [DEBUG] ######################################## FIELD INFO END ######################################
[2024-03-21 16:59:54] [DEBUG] 没得索引
[2024-03-21 16:59:54] [DEBUG] ROW INIT FINISH FOR < test
.aaa
>
[2024-03-21 16:59:54] [DEBUG] SET FILTER (0, 281474976710656) (0, 72057594037927936)
[2024-03-21 16:59:54] [DEBUG] ################## READ ROW START (PAGE NO:5) ########################
[2024-03-21 16:59:54] [DEBUG] READ ALL ROWS FROM PAGE (PAGE_ID=5)
[2024-03-21 16:59:54] [DEBUG] RESET offset TO PAGE_NEW_INFIMUM (99)
[2024-03-21 16:59:54] [DEBUG] NO:1 READ RECORD HEADER (5 bytes) _offset:99 offset:99 START
[2024-03-21 16:59:54] [DEBUG] THIS ROW IS PAGE_NEW_INFIMUM, WILL CONTINUE. (offset:99)
[2024-03-21 16:59:54] [DEBUG] NO:2 READ RECORD HEADER (5 bytes) _offset:132 offset:132 START
[2024-03-21 16:59:54] [DEBUG] READ RECORD HEADER (5 bytes) _offset:127 offset:132 FINISH
[2024-03-21 16:59:54] [DEBUG] PAGE NO : 5
[2024-03-21 16:59:54] [DEBUG] READ ROW NO : 0 CURRENT_OFFSET:132
[2024-03-21 16:59:54] [DEBUG] REC INSTANT : False
[2024-03-21 16:59:54] [DEBUG] REC DELETED : False
[2024-03-21 16:59:54] [DEBUG] REC MIN_REC : False
[2024-03-21 16:59:54] [DEBUG] REC OWNED : 0
[2024-03-21 16:59:54] [DEBUG] REC HEAP_NO : 16
[2024-03-21 16:59:54] [DEBUG] REC TYPE : 0
[2024-03-21 16:59:54] [DEBUG] REC NEXT : 436
[2024-03-21 16:59:54] [DEBUG] 20 bytes ON BOTH SIDES OF RECORD, b'\x04\x00\x0b\x00\x00supremum\x00@\x00\x00\x00 |', b'\x00\x00\x10\x01\xb4\x00\x00\x00\x00\x03\x10\x00\x00\x00\x00\t"\x81\x00\x00'
[2024-03-21 16:59:54] [DEBUG] GET COUNT COLUMN FOR THIS ROW
[2024-03-21 16:59:54] [DEBUG] READ COLUM COUNT
[2024-03-21 16:59:54] [DEBUG] READ COLUM COUNT FINISH
[2024-03-21 16:59:54] [DEBUG] THIS ROW HAS 51 FILEDS
[2024-03-21 16:59:54] [DEBUG] READ NULL BITMASK
[2024-03-21 16:59:54] [DEBUG] NULLABLE FILED COUNT: 7 NULLABLE FIELD COUNT(FOR INSTANT):0
[2024-03-21 16:59:54] [DEBUG] NULL BITMASK: COUNT:7 ID: 124 [0, 1, 1, 1, 1, 1, 0, 0]
[2024-03-21 16:59:54] [DEBUG] READ KEY FILED
[2024-03-21 16:59:54] [DEBUG] NO CLUSTER KEY.WILL READ 6 bytes(ROWID)
[2024-03-21 16:59:54] [DEBUG] ROW_ID:784
[2024-03-21 16:59:54] [DEBUG] READ TRX(6) AND ROLLPTR(7) INFO
[2024-03-21 16:59:54] [DEBUG] TRX: 2338 ROLLPTR: 36310272013373301
[2024-03-21 16:59:54] [DEBUG] READ THE REST OF FIELD. (column count: 49)
[2024-03-21 16:59:54] [DEBUG] READ FIELD COLNO: 1 NAME: Host TYPE: char(255) CT: char
[2024-03-21 16:59:54] [DEBUG] COL 1 Host REQUIRE NOT NULL. READ DATA
[2024-03-21 16:59:54] [DEBUG] VAR FILED VAR SIZE: 32
[2024-03-21 16:59:54] [DEBUG] 151 ----> 183 data:% bdata:b'% '
[2024-03-21 16:59:54] [DEBUG] READ FIELD COLNO: 2 NAME: User TYPE: char(32) CT: char
[2024-03-21 16:59:54] [DEBUG] COL 2 User REQUIRE NOT NULL. READ DATA
[2024-03-21 16:59:54] [DEBUG] VAR FILED VAR SIZE: 0
[2024-03-21 16:59:54] [DEBUG] 183 ----> 183 data: bdata:b''
[2024-03-21 16:59:54] [DEBUG] READ FIELD COLNO: 3 NAME: Select_priv TYPE: enum('N','Y') CT: enum
[2024-03-21 16:59:54] [DEBUG] COL 3 Select_priv REQUIRE NOT NULL. READ DATA
Traceback (most recent call last):
File "main.py", line 177, in
File "ibd2sql\ibd2sql.py", line 229, in get_sql
File "ibd2sql\innodb_page_index.py", line 224, in read_row
File "ibd2sql\innodb_page_index.py", line 394, in _read_all_row
File "ibd2sql\innodb_page_index.py", line 186, in _read_field
KeyError: 32
[27124] Failed to execute script 'main' due to unhandled exception!
C:\Users\ZMI\Desktop\data\test>
用 ibd2sql
解决了表内数据误删问题,找回了数据。万分感谢!
一个小建议,命令的 usage
输出可调整一下:
usage: main.py [-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]
可改为
usage:
main.py -h
main.py --version
main.py <FILENAME> [--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]
这样与实际执行可保持一致,与 README.md
中的例子一致。
处置时紧张,一开始按 usage
中的文件名放最后方式去操作,没输出 sql
。冷静后反复尝试才发现需要将文件名前置。
最后,再说一次,感谢!!!
[root@mydb01 ibd2sql-main]# python main.py --ddl /mysql/backup/all_db/sakila/store.ibd
File "main.py", line 15
SyntaxError: Non-ASCII character '\xe8' in file main.py on line 15, but no encoding declared; see http://www.python.org/peps/pep-0263.html for details
Traceback (most recent call last):
File "/root/ibd2sql-main/main.py", line 169, in
ldata = innodb_index.index(bdata,pk,columns)
File "/root/ibd2sql-main/innodb_index.py", line 127, in index
data_list.append(read_row(columns,pk,bdata,offset))
File "/root/ibd2sql-main/innodb_index.py", line 101, in read_row
return [ innodb_type.transdata(columns[x]['dtype'],tdata[x],columns[x]['is_unsigned'],columns[x]['extra']) if tdata[x] is not None else '' for x in range(len_column)] #数据类型转换
File "/root/ibd2sql-main/innodb_index.py", line 101, in
return [ innodb_type.transdata(columns[x]['dtype'],tdata[x],columns[x]['is_unsigned'],columns[x]['extra']) if tdata[x] is not None else '' for x in range(len_column)] #数据类型转换
File "/root/ibd2sql-main/innodb_type.py", line 205, in transdata
return bdata.decode()
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe4 in position 0: invalid continuation byte
Hello, your script is lifesaver,
im a little bit noob:
how can i use this script in a directory with many .ibd files?
Sorry but i cant figure out...
windows 环境运行报page_type错误,帮忙看下,感谢。
python.exe c:/work/0.5.Source/python/ibd2sql-main/main.py
Traceback (most recent call last):
File "c:\work\0.5.Source\python\ibd2sql-main\main.py", line 3, in
import innodb_fil
File "c:\work\0.5.Source\python\ibd2sql-main\innodb_fil.py", line 1, in
import page_type
ModuleNotFoundError: No module named 'page_type'
老哥我这边执行时报这个错误,然后我在ibd2sql\mysql_json.py加了sys.setrecursionlimit(2000),运行不报错,但生成的sql文件明显有数据缺失,这个问题咋解决呢
你好 ,请教一下 ,我在使用工具恢复idb文件的时候乱码了 ,想了解可能是什么原因 表结构如下:
CREATE TABLE zxjl_keyword
(
id
bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
uuid
bigint NOT NULL COMMENT '用户uuid',
task_id
bigint NOT NULL COMMENT '任务id',
keyword_name
varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '关键词名字',
status
char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '1' COMMENT '状态 1待审核 2通过 3驳回 4已发布 5过期',
create_time
datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
value
longtext NOT NULL COMMENT '内容',
submit_status
int NOT NULL DEFAULT '0' COMMENT '状态: 0. 未提交, 1、已提交',
reason
varchar(125) DEFAULT NULL COMMENT '审核失败原因',
PRIMARY KEY (id
) USING BTREE,
KEY idx_task_id
(task_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='关键词表';
INSERT INTO wenmo
.zxjl_keyword
VALUES (17, 636182, 24, '0xe5', '0x9388e596bd3299b179180e5b7b226669656c644e616d65223a22e58e9fe4b9a6e5908d222c226669656c6456616c7565223a22e4bda0e5a5bde59180e59180222c2270726f70223a22626f6f6b4e616d65222c227265717569726564223a747275657d2c7b226669656c644e616d65223a22626964222c226669656c6456616c7565223a22313233343536222c2270726f70223a22626964222c227265717569726564223a747275657d2c7b226669656c644e616d65223a22e585b3e994aee8af8defbc88e68ea7e588b6e59ca8342d37e4b8aae5ad97efbc89222c226669656c6456616c7565223a22e59388e596bd222c2270726f70223a226b6579776f7264222c227265717569', '-9010-11-18 2:8:58', 't', -227187331, '');
如果ibd 文件部分损坏,如sdi page 等page 异常,但是有表结构的场景
对于 mysqlfrm 解析 mysql 5.* 不准确的问题 可以使用 dbsake 工具进行解析
修改一下能用了
for colid in self.column:
# ------
col = self.column[colid]
if '!hidden!' in col['name']:
continue
ddl += self._ci
# ---------
ddl += f"{col['name']}
{col['type']}" #column name
if self.COLUMN_COLL and col['type'] != 'int':
解析ibd文件,部分成功解析,解析过程有异常:
unpack requires a buffer of 12 bytes,如需要可提供ibd 文件
python3 main.py --ddl /mnt/d/temp/sys_menus_admin.ibd
Traceback (most recent call last):
File "/data/source/ibd2sql-main/main.py", line 100, in
print('\n',innodb_sdi.sdi(filename).get_ddl(),'\n')
File "/data/source/ibd2sql-main/innodb_sdi.py", line 43, in get_ddl
dic_info = self.get_dic()
File "/data/source/ibd2sql-main/innodb_sdi.py", line 132, in get_dic
unzbdata = zlib.decompress(self.bdata[offset+33:offset+33+dzip_len])
zlib.error: Error -3 while decompressing data: incorrect header check
两个版本的试了
我的mysql版本是8.0
为什么报这个错误?: row_format = compressed or its damaged or its mysql 5.7 file
可以加你联系方式吗?
操作 对ibd进行ddl输出
PS C:\Users\xiaoj\Desktop\DataBaseWorkSpace\ibd2sql-main> .\main.py C:\Users\xiaoj\Desktop\DataBaseWorkSpace\test.ibd --ddl
Traceback (most recent call last):
File "C:\Users\xiaoj\Desktop\DataBaseWorkSpace\ibd2sql-main\main.py", line 161, in
ddcw.init()
File "C:\Users\xiaoj\Desktop\DataBaseWorkSpace\ibd2sql-main\ibd2sql\ibd2sql.py", line 112, in init
self.sdi = sdi(self.read(),debug=self.debug) #sdi页
File "C:\Users\xiaoj\Desktop\DataBaseWorkSpace\ibd2sql-main\ibd2sql\innodb_page_sdi.py", line 161, in init
self._init_table()
File "C:\Users\xiaoj\Desktop\DataBaseWorkSpace\ibd2sql-main\ibd2sql\innodb_page_sdi.py", line 263, in _init_table
if x['length'] == 4294967295 or x['hidden']:
KeyError: 'hidden'
PS C:\Users\xiaoj\Desktop\DataBaseWorkSpace\ibd2sql-main>
Debug
PS C:\Users\xiaoj\Desktop\DataBaseWorkSpace\ibd2sql-main> .\main.py C:\Users\xiaoj\Desktop\DataBaseWorkSpace\test.ibd --ddl --debug
[2024-04-04 00:43:17] [DEBUG] DEBUG MODE ON
[2024-04-04 00:43:17] [DEBUG] INIT ibd2sql
[2024-04-04 00:43:17] [DEBUG] FORCE False
[2024-04-04 00:43:17] [DEBUG] SET False
[2024-04-04 00:43:17] [DEBUG] MULTIVALUE False
[2024-04-04 00:43:17] [DEBUG] AUTO_DEBUG True
[2024-04-04 00:43:17] [DEBUG] FILTER:
0 < TRX < 281474976710656
0 < ROLLPTR < 72057594037927936
[2024-04-04 00:43:17] [DEBUG] OPEN IBD FILE: C:\Users\xiaoj\Desktop\DataBaseWorkSpace\test.ibd
[2024-04-04 00:43:17] [DEBUG] ANALYZE FIRST PAGE: FIL_PAGE_TYPE_FSP_HDR
[2024-04-04 00:43:17] [DEBUG] ibd2sql.read PAGE: 0
[2024-04-04 00:43:17] [DEBUG] ANALYZE FIRST PAGE FINISH
[2024-04-04 00:43:17] [DEBUG] SDI PAGE NO: 3
[2024-04-04 00:43:17] [DEBUG] ANALYZE SDI PAGE
[2024-04-04 00:43:17] [DEBUG] ibd2sql.read PAGE: 3
Traceback (most recent call last):
File "C:\Users\xiaoj\Desktop\DataBaseWorkSpace\ibd2sql-main\main.py", line 161, in
ddcw.init()
File "C:\Users\xiaoj\Desktop\DataBaseWorkSpace\ibd2sql-main\ibd2sql\ibd2sql.py", line 112, in init
self.sdi = sdi(self.read(),debug=self.debug) #sdi页
File "C:\Users\xiaoj\Desktop\DataBaseWorkSpace\ibd2sql-main\ibd2sql\innodb_page_sdi.py", line 161, in init
self._init_table()
File "C:\Users\xiaoj\Desktop\DataBaseWorkSpace\ibd2sql-main\ibd2sql\innodb_page_sdi.py", line 263, in _init_table
if x['length'] == 4294967295 or x['hidden']:
KeyError: 'hidden'
PS C:\Users\xiaoj\Desktop\DataBaseWorkSpace\ibd2sql-main>
1、5.7 表信息:
mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.00 sec)
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.44, for Linux (x86_64) using EditLine wrapper
Connection id: 19880
Current database: testdb
Current user: yws@localhost
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.34-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8
Conn. characterset: utf8
TCP port: 20135
Uptime: 14 days 18 hours 24 min 56 sec
Threads: 1 Questions: 385 Slow queries: 0 Opens: 198 Flush tables: 4 Open tables: 21 Queries per second avg: 0.000
--------------
t1 表有1000条数据
2、在mysql 8.0上已经建立只有表结构的空表
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.44, for Linux (x86_64) using EditLine wrapper
Connection id: 45
Current database: testdb
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.33 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb3
Conn. characterset: utf8mb3
UNIX socket: /tmp/mysql_sandbox8033.sock
Uptime: 14 days 22 hours 59 min 5 sec
Threads: 2 Questions: 2068359211 Slow queries: 32 Opens: 253 Flush tables: 3 Open tables: 172 Queries per second avg: 1600.469
--------------
mysql>
mysql>
mysql>
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.