MySQL数据库抢救数据利器ibd2sql详细介绍(附带下载地址)

chenoracle

于 2025-06-03 12:32:05 发布

阅读量787

点赞数 15

分类专栏： MySQL 文章标签：数据库 mysql

本文链接：https://blog.youkuaiyun.com/chenoracle/article/details/148399016

版权

MySQL 专栏收录该内容

12 篇文章

订阅专栏

在这里插入图片描述

简介:

ibd2sql 是一个使用纯python3编写的离线解析MYSQL INNODB存储引擎的ibd文件的工具,并将其转换为 SQL 语句。
无第三方依赖包. 使用GPL-3.0 license.

特点:

1.方便: 提取表DDL
2.实用: 可替换库(--schema)/表(--table)名, 可在sql语句中输出完整的字段(--complete)
3.简单: 纯python3代码编写, 无依赖包.
4.支持众多数据类型: 支持所有mysql数据类型
5.支持复杂的表结构: 分区表, 注释, 主键, 外键, 约束, 自增, 普通索引, 前缀索引, 主键前缀索引, 唯一索引, 复合索引, 默认值, 符号, 虚拟字段, INSTANT, 无主键等情况的表
6.数据误删恢复: 可以输出被标记为deleted的数据
7.安全: 离线解析ibd文件, 仅可读权限即可
8.支持范围广: 支持mysql 5.6 or 5.7 or 8.0 or 8.4 or 9.0

版本更新:

[root@cjc-db-02 ibd2sql-1.10]# cat README_zh.md

版本   更新时间   说明
v0.1  2023.04.27  第一个版本
v0.2  2023.08.30  支持更多数据类型
v0.3  2023.10.13  支持5.7升级到8.0的ibd文件
v1.0  2024.01.05  支持debug,支持更多类型和功能
v1.1  2024.04.12  修复一些bug
v1.2  2024.04.25  新增空间坐标的支持
v1.3  2024.05.11  支持mysql 5.6, 5.7
v1.4  2024.05.21  支持溢出页, 子分区
v1.5  2024.07.10  vector and instant BUG
v1.6  2024.09.19  修复一些bug
v1.7  2024.10.29  1.修复一些bug 2.支持压缩页 3.支持drop table的恢复 4.ucs2,utf16,utf32 字符集支持             |
v1.8  2024.11.09  1.支持所有字符集 2.支持kering 插件加密的表 3.支持web控制台查看数据结构 4.支持修改lower_case_table_names|
v1.9  2025.02.21  修复已知BUG, 支持直接解析5.7的ibd文件
v1.10 2025.04.16  修复已知BUG, 添加快速统计表行数的脚本

需求与支持:

require: python3
support range: mysql5.x 8.x 9.x

下载和安装:

下载和安装:ibd2sql-1.10.tar.gz

v1.10 下载地址:离线上传到服务器

https://github.com/ddcw/ibd2sql/archive/refs/tags/v1.10.tar.gz

或者如果服务器可以联网，可以直接wget下载到服务器:

wget https://github.com/ddcw/ibd2sql/archive/refs/tags/v1.10.tar.gz

查看 ibd2sql-1.10.tar.gz:

[mysql@cjc-db-02 ibd2sql]$ ls -lrth ibd2sql-1.10.tar.gz 
-rw-r--r-- 1 root root 113K May 31 20:07 ibd2sql-1.10.tar.gz

[mysql@cjc-db-02 ibd2sql]$ md5sum ibd2sql-1.10.tar.gz 
3e73b0bf7ab7b7f65d3ddac413a44163  ibd2sql-1.10.tar.gz

解压:

[root@cjc-db-02 ibd2sql]# tar -zxvf ibd2sql-1.10.tar.gz 

[root@cjc-db-02 ibd2sql-1.10]# ls -lrth
total 164K
-rw-rw-r-- 1 root root  31K Apr 16 18:42 xfs_recovery_v0.3.py
-rw-rw-r-- 1 root root  27K Apr 16 18:42 test.sh
-rw-rw-r-- 1 root root 2.2K Apr 16 18:42 super_fast_count.py
-rw-rw-r-- 1 root root 3.5K Apr 16 18:42 README_zh.md
-rw-rw-r-- 1 root root 3.5K Apr 16 18:42 README.md
-rw-rw-r-- 1 root root 2.6K Apr 16 18:42 modify_lower_case_table_names.py
-rw-rw-r-- 1 root root 8.9K Apr 16 18:42 main.py
-rw-rw-r-- 1 root root  35K Apr 16 18:42 LICENSE
-rw-rw-r-- 1 root root  21K Apr 16 18:42 ibd2sql_web.py
-rw-rw-r-- 1 root root  12K Apr 16 18:42 ibd2sql_mini_for_redundant.py
drwxrwxr-x 2 root root   99 Apr 16 18:42 docs
drwxrwxr-x 3 root root 4.0K May 31 20:35 ibd2sql

下载和安装 Python:

下载地址:

https://www.python.org/downloads/release/python-361/

图片.png

上传:

[root@cjc-db-02 ibd2sql]# ls -lrth Python-3.6.1.tgz 
-rw-r--r-- 1 root root 22M May 31 08:44 Python-3.6.1.tgz

[root@cjc-db-02 ibd2sql]# md5sum Python-3.6.1.tgz 
2d0fc9f3a5940707590e07f03ecb08b9  Python-3.6.1.tgz

解压:

tar xvf Python-3.6.1.tgz
cd Python-3.6.1
mkdir -p /usr/local/python3
./configure --prefix=/usr/local/python3
make && make install
ln -s /usr/local/python3/bin/python3 /usr/bin/python3

[root@cjc-db-02 Python-3.6.1]# python3  -V
Python 3.6.1

下载和安装:MySQL 8.4.5

https://dev.mysql.com/downloads/mysql/

图片.png

[root@cjc-db-02 mysql]# ls -lrth
total 2.7G
-rw-r--r-- 1 7155 31415 868M Apr  1 18:11 mysql-8.4.5-linux-glibc2.17-x86_64.tar.xz
-rw-r--r-- 1 7155 31415  78M Apr  1 18:25 mysql-router-8.4.5-linux-glibc2.17-x86_64.tar.xz
-rw-r--r-- 1 7155 31415 432M Apr  1 18:44 mysql-test-8.4.5-linux-glibc2.17-x86_64.tar.xz
-rw-r--r-- 1 root root  1.4G May 31 09:51 mysql-8.4.5-linux-glibc2.17-x86_64.tar

[root@cjc-db-02 mysql]# md5sum mysql-*.tar.xz
8c7573c055a38821f8e65946f48a07ae  mysql-8.4.5-linux-glibc2.17-x86_64.tar.xz
55c76f9b10328e821fac1dbc80204a5e  mysql-router-8.4.5-linux-glibc2.17-x86_64.tar.xz

安装过程省略:
和 8.0 相比，已经删除了 relay_log_info_repository 参数，如果my.cnf配置了，需要参数这个参数。

ibd2sql工具测试:

1.ibd2sql常用命令

帮助信息:

[mysql@cjc-db-02 ibd2sql-1.5]$ python3 main.py --help
[root@cjc-db-02 ibd2sql-1.10]# 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]
               [--keyring-file KEYRING_FILE]
               [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
  --keyring-file KEYRING_FILE, -k KEYRING_FILE
                        keyring filename
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

常用命令:

--version, -v, -V: 显示版本信息。
--ddl, -d: 打印 DDL。
--sql: 打印数据的 SQL 语句。
--delete: 仅打印被标记为已删除的数据。
--complete-insert: 使用完整的插入语句。
--force, -f: 在遇到错误页面时强制解析文件。
--set: 用实际数据填充 set/enum 而不是字符串。
--multi-value: 如果数据属于同一页面，则使用单个 SQL 语句。
--replace: 使用 "REPLACE INTO" 替换 "INSERT INTO"（默认）。
--table TABLE_NAME: 替换表名（不包括 DDL）。
--schema SCHEMA_NAME: 替换库名（不包括 DDL）。
--sdi-table SDI_TABLE: 从这个 IBD 文件（分区表）读取 SDI 页面。
--where-trx WHERE_TRX: 事务过滤范围，默认 (0,281474976710656)。
--where-rollptr WHERE_ROLLPTR: 回滚指针过滤范围，默认 (0,72057594037927936)。
--limit LIMIT: 限制行数。
--debug, -D: 调试模式（输出非常大）。
--debug-file DEBUG_FILE: 调试输出文件，默认为 sys.stdout。
--page-min PAGE_MIN: 如果页面编号小于此值，则终止。
--page-max PAGE_MAX: 如果页面编号大于此值，则终止。
--page-start PAGE_START: 索引页面起始编号。
--page-count PAGE_COUNT: 页面计数编号。
--page-skip PAGE_SKIP: 解析索引页面时跳过某些页面。
--mysql5: 适用于 MySQL 5.7 标志。

2.准备测试数据

创建一张小表cjc.t1和一张大表cjc.sales_order;

mysql> create database cjc;
mysql> use cjc;
create table t1(id int primary key,name varchar(10),time time);
insert into t1 values(1,'x',now());
insert into t1 values(2,'y',now());
insert into t1 values(3,'z',now());
insert into t1 values(4,'u',now());
insert into t1 values(5,'v',now());

mysql> select * from cjc.t1;
+----+------+----------+
| id | name | time     |
+----+------+----------+
|  1 | x    | 10:35:52 |
|  2 | y    | 10:35:56 |
|  3 | z    | 10:36:02 |
|  4 | u    | 10:36:07 |
|  5 | v    | 10:36:16 |
+----+------+----------+
5 rows in set (0.00 sec)


CREATE TABLE sales_order (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(50) NOT NULL UNIQUE,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) GENERATED ALWAYS AS (quantity * price) STORED,
    order_date DATE NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'completed', 'cancelled') NOT NULL,
    INDEX idx_customer (customer_id),
    INDEX idx_date (order_date)
) ENGINE=InnoDB;

创建存储过程:

DELIMITER $$

CREATE PROCEDURE GenerateSalesOrders()
BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE batchSize INT DEFAULT 1000;  -- 每批插入1000条
    DECLARE maxRecords INT DEFAULT 1000000;
    
    WHILE counter < maxRecords DO
        INSERT INTO sales_order (
            order_no, 
            customer_id, 
            product_id, 
            quantity, 
            price, 
            order_date, 
            status
        )
        SELECT
            CONCAT('ORD', LPAD(counter + n, 10, '0')),  -- 唯一订单号
            FLOOR(1 + RAND() * 1000),                   -- 客户ID 1-1000
            FLOOR(1 + RAND() * 500),                   -- 产品ID 1-500
            FLOOR(1 + RAND() * 10),                    -- 数量 1-10
            ROUND(10 + RAND() * 990, 2),               -- 价格 10.00-999.99
            DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND() * 2000) DAY),  -- 日期 2020-2025
            ELT(FLOOR(1 + RAND() * 5), 'pending', 'processing', 'shipped', 'completed', 'cancelled') -- 状态
        FROM (
            SELECT a.N + b.N * 10 + c.N * 100 AS n
            FROM 
                (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
                (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
                (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
            ORDER BY n
            LIMIT batchSize
        ) numbers;
        
        SET counter = counter + batchSize;
        COMMIT;
        SELECT CONCAT('Inserted: ', counter, '/1000000') AS progress;
    END WHILE;
END$$

DELIMITER ;

调用存储过程（耗时约5-15分钟）

CALL GenerateSalesOrders();
......
+--------------------------+
| progress                 |
+--------------------------+
| Inserted: 998000/1000000 |
+--------------------------+
1 row in set (2 min 38.18 sec)

+--------------------------+
| progress                 |
+--------------------------+
| Inserted: 999000/1000000 |
+--------------------------+
1 row in set (2 min 38.39 sec)

+---------------------------+
| progress                  |
+---------------------------+
| Inserted: 1000000/1000000 |
+---------------------------+
1 row in set (2 min 38.48 sec)

Query OK, 0 rows affected (2 min 38.48 sec)

mysql> select * from sales_order limit 10;
+----+---------------+-------------+------------+----------+--------+--------------+------------+------------+
| id | order_no      | customer_id | product_id | quantity | price  | total_amount | order_date | status     |
+----+---------------+-------------+------------+----------+--------+--------------+------------+------------+
|  1 | ORD0000000000 |         916 |         55 |        9 | 685.24 |      6167.16 | 2020-01-07 | cancelled  |
|  2 | ORD0000000001 |         845 |        155 |        1 | 143.77 |       143.77 | 2023-06-30 | completed  |
|  3 | ORD0000000002 |          12 |        351 |        5 | 258.78 |      1293.90 | 2024-08-15 | shipped    |
|  4 | ORD0000000003 |         800 |        301 |        7 | 219.38 |      1535.66 | 2021-05-14 | completed  |
|  5 | ORD0000000004 |         321 |        382 |        9 | 959.75 |      8637.75 | 2021-05-12 | processing |
|  6 | ORD0000000005 |          84 |        160 |        4 | 787.42 |      3149.68 | 2024-10-24 | pending    |
|  7 | ORD0000000006 |         565 |        351 |        9 | 957.87 |      8620.83 | 2021-12-02 | cancelled  |
|  8 | ORD0000000007 |         355 |         65 |        6 | 509.51 |      3057.06 | 2024-04-27 | shipped    |
|  9 | ORD0000000008 |         795 |        338 |       10 | 941.79 |      9417.90 | 2023-12-22 | cancelled  |
| 10 | ORD0000000009 |         844 |        403 |        5 |  55.32 |       276.60 | 2024-02-16 | completed  |
+----+---------------+-------------+------------+----------+--------+--------------+------------+------------+
10 rows in set (0.00 sec)

mysql> select count(*) from sales_order;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.29 sec)


[mysql@cjc-db-02 cjc]$ ls -lrth
total 185M
-rw-r----- 1 mysql mysql 112K May 31 10:36 t1.ibd
-rw-r----- 1 mysql mysql 184M May 31 10:56 sales_order.ibd

停库

mysql> shutdown;
Query OK, 0 rows affected (0.28 sec)

数据文件拷贝到临时目录

[mysql@cjc-db-02 3308]$ cp /mysqldata/3308/data/cjc/*.ibd /mysqldata/3308/ibd2sql/
[mysql@cjc-db-02 3308]$ ls -lrth /mysqldata/3308/ibd2sql/
total 185M
-rw-r----- 1 mysql mysql 184M May 31 11:05 sales_order.ibd
-rw-r----- 1 mysql mysql 112K May 31 11:05 t1.ibd

ibd2sql解析:

场景一:解析完整的ibd文件
场景二:解析完整的ibd文件，合并同一页面的语句
场景三:解析完整的ibd文件，更换表名
场景四:ibd文件损坏，解析ibd文件部分内容
场景五:恢复误删除的数据
场景六:web控制台查看数据结构

场景一:解析完整的ibd文件

cd /soft/ibd2sql/ibd2sql-1.10
python3 main.py /mysqldata/3308/ibd2sql/t1.ibd --sql --ddl

小表:

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/t1.ibd --sql --ddl
CREATE TABLE IF NOT EXISTS `cjc`.`t1`(
    `id` int NOT NULL,
    `name` varchar(10) NULL,
    `time` time NULL,
    PRIMARY KEY  (`id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `cjc`.`t1` VALUES (1, 'x', '10:35:52');
INSERT INTO `cjc`.`t1` VALUES (2, 'y', '10:35:56');
INSERT INTO `cjc`.`t1` VALUES (3, 'z', '10:36:2');
INSERT INTO `cjc`.`t1` VALUES (4, 'u', '10:36:7');
INSERT INTO `cjc`.`t1` VALUES (5, 'v', '10:36:16');

输出到文件

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/t1.ibd --sql --ddl  > /mysqldata/3308/ibd2sql/t1.sql

大表:

[mysql@cjc-db-02 ibd2sql-1.5]$ python3 main.py /mysqldata/3308/ibd2sql/sales_order.ibd --sql --ddl > /mysqldata/3308/ibd2sql/sales_order.sql

看看执行时间，需要约8分钟，解析107MB的数据。

[root@cjc-db-02 ibd2sql-1.10]# date && python3 main.py /mysqldata/3308/ibd2sql/sales_order.ibd --sql --ddl > /mysqldata/3308/ibd2sql/sales_order.sql && date
Sat May 31 21:16:49 CST 2025
Sat May 31 21:24:31 CST 2025

[mysql@cjc-db-02 ibd2sql]$ ls -lrth
total 291M
-rw-r----- 1 mysql mysql 184M May 31 11:05 sales_order.ibd
-rw-r----- 1 mysql mysql 112K May 31 11:05 t1.ibd
-rw-r--r-- 1 root  root  107M May 31 21:24 sales_order.sql
-rw-r--r-- 1 root  root   465 May 31 21:25 t1.sql

[mysql@cjc-db-02 ibd2sql]$ cat sales_order.sql |wc -l
1000015

[mysql@cjc-db-02 ibd2sql]$ more sales_order.sql
CREATE TABLE IF NOT EXISTS `cjc`.`sales_order`(
    `id` bigint NOT NULL AUTO_INCREMENT,
    `order_no` varchar(50) NOT NULL,
    `customer_id` int NOT NULL,
    `product_id` int NOT NULL,
    `quantity` int NOT NULL,
    `price` decimal(10,2) NOT NULL,
    `total_amount` decimal(10,2) NULL,
    `order_date` date NOT NULL,
    `status` enum('pending','processing','shipped','completed','cancelled') NOT NULL,
    PRIMARY KEY  (`id` ),
    UNIQUE KEY `order_no` (`order_no` ),
    KEY `idx_customer` (`customer_id` ),
    KEY `idx_date` (`order_date` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `cjc`.`sales_order` VALUES (1, 'ORD0000000000', 916, 55, 9, 685.24, '2020-1-7', 'cancelled');
INSERT INTO `cjc`.`sales_order` VALUES (2, 'ORD0000000001', 845, 155, 1, 143.77, '2023-6-30', 'completed');
INSERT INTO `cjc`.`sales_order` VALUES (3, 'ORD0000000002', 12, 351, 5, 258.78, '2024-8-15', 'shipped');
......
INSERT INTO `cjc`.`sales_order` VALUES (1022975, 'ORD0000999997', 429, 286, 6, 147.59, '2025-5-21', 'shipped');
INSERT INTO `cjc`.`sales_order` VALUES (1022976, 'ORD0000999998', 554, 133, 7, 534.72, '2023-8-2', 'completed');
INSERT INTO `cjc`.`sales_order` VALUES (1022977, 'ORD0000999999', 457, 115, 8, 195.82, '2023-5-13', 'shipped');

场景二:解析完整的ibd文件，合并同一页面的语句

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/t1.ibd --sql --ddl --complete-insert --table sales_order --multi-value > /mysqldata/3308/ibd2sql/t1_01.sql

[mysql@cjc-db-02 ibd2sql]$ ls -lrth t1*.sql
-rw-r--r-- 1 root  root  465 May 31 21:25 t1.sql
-rw-rw-r-- 1 mysql mysql 379 May 31 21:28 t1_01.sql

将5条insert语句合并为一条

[mysql@cjc-db-02 ibd2sql]$ cat t1_01.sql 
CREATE TABLE IF NOT EXISTS `cjc`.`sales_order`(
    `id` int NOT NULL,
    `name` varchar(10) NULL,
    `time` time NULL,
    PRIMARY KEY  (`id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `cjc`.`sales_order`(`id`,`name`,`time`) VALUES (1, 'x', '10:35:52'),(2, 'y', '10:35:56'),(3, 'z', '10:36:2'),(4, 'u', '10:36:7'),(5, 'v', '10:36:16');

类似于mysqldump的 --extended-insert 参数，可以提高恢复速度。

-e, --extended-insert 
Use multiple-row INSERT syntax that include severalVALUES lists.

[root@cjc-db-02 ibd2sql-1.10]# date && python3 main.py /mysqldata/3308/ibd2sql/sales_order.ibd --sql --ddl --complete-insert --table sales_order --multi-value > /mysqldata/3308/ibd2sql/sales_order_01.sql && date
Sat May 31 21:30:42 CST 2025
Sat May 31 21:37:55 CST 2025

文件大小从107MB缩小到70MB。

[mysql@cjc-db-02 ibd2sql]$ ls -lrth sales_order*.sql
-rw-r--r-- 1 root root 107M May 31 21:24 sales_order.sql
-rw-r--r-- 1 root root  70M May 31 21:37 sales_order_01.sql

行数从1000015缩小到4460

[mysql@cjc-db-02 ibd2sql]$ cat sales_order.sql |wc -l
1000015
[mysql@cjc-db-02 ibd2sql]$ cat sales_order_01.sql |wc -l
4460

场景三:解析完整的ibd文件，更换表名

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/t1.ibd --sql --ddl --complete-insert --table t1_change > /mysqldata/3308/ibd2sql/t1_change.sql

[root@cjc-db-02 ibd2sql-1.10]# cat /mysqldata/3308/ibd2sql/t1_change.sql
CREATE TABLE IF NOT EXISTS `cjc`.`t1_change`(
    `id` int NOT NULL,
    `name` varchar(10) NULL,
    `time` time NULL,
    PRIMARY KEY  (`id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `cjc`.`t1_change`(`id`,`name`,`time`) VALUES (1, 'x', '10:35:52');
INSERT INTO `cjc`.`t1_change`(`id`,`name`,`time`) VALUES (2, 'y', '10:35:56');
INSERT INTO `cjc`.`t1_change`(`id`,`name`,`time`) VALUES (3, 'z', '10:36:2');
INSERT INTO `cjc`.`t1_change`(`id`,`name`,`time`) VALUES (4, 'u', '10:36:7');
INSERT INTO `cjc`.`t1_change`(`id`,`name`,`time`) VALUES (5, 'v', '10:36:16');

场景四:ibd文件损坏，解析ibd文件部分内容

破坏文件，模拟数据文件损坏

[root@cjc-db-02 ibd2sql]# cp -a sales_order.ibd sales_order_01.ibd

破坏最后100MB的数据

dd if=/dev/urandom of=/mysqldata/3308/ibd2sql/sales_order_01.ibd \
      bs=1M \
      seek=$(($(stat -c %s /mysqldata/3308/ibd2sql/sales_order_01.ibd )/1048576 - 100)) \
      count=100 \
      conv=notrunc

前台日志:

100+0 records in
100+0 records out
104857600 bytes (105 MB) copied, 8.77186 s, 12.0 MB/s

参数说明:
conv=notrunc 不截断文件，仅覆盖指定部分

开始解析

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/sales_order_01.ibd --sql --ddl > /mysqldata/3308/ibd2sql/sales_order_02.sql

前台报错:

Traceback (most recent call last):
  File "main.py", line 224, in <module>
    ddcw.get_sql()
  File "/soft/ibd2sql/ibd2sql-1.10/ibd2sql/ibd2sql.py", line 263, in get_sql
    for x in _tdata:
  File "/soft/ibd2sql/ibd2sql-1.10/ibd2sql/innodb_page_index.py", line 380, in read_row
    self._read_all_row()
  File "/soft/ibd2sql/ibd2sql-1.10/ibd2sql/innodb_page_index.py", line 505, in _read_all_row
    if rheader.instant_flag and _t_COLUMN_COUNT > _COLUMN_COUNT:
UnboundLocalError: local variable '_COLUMN_COUNT' referenced before assignment

数据量不完整

[mysql@cjc-db-02 ibd2sql]$ cat sales_order_02.sql |wc -l
511102

[mysql@cjc-db-02 ibd2sql]$ ls -lrth sales_order*.sql
-rw-r--r-- 1 root  root  107M May 31 21:24 sales_order.sql
-rw-r--r-- 1 root  root   70M May 31 21:37 sales_order_01.sql
-rw-rw-r-- 1 mysql mysql  55M May 31 21:51 sales_order_02.sql

[mysql@cjc-db-02 ibd2sql]$ tail -n 10 sales_order_02.sql 
INSERT INTO `cjc`.`sales_order` VALUES (522831, 'ORD0000511077', 138, 346, 1, 141.50, '2022-12-17', 'processing');
INSERT INTO `cjc`.`sales_order` VALUES (522832, 'ORD0000511078', 903, 300, 3, 641.24, '2021-10-16', 'completed');
INSERT INTO `cjc`.`sales_order` VALUES (522833, 'ORD0000511079', 634, 500, 1, 481.16, '2020-7-10', 'pending');
INSERT INTO `cjc`.`sales_order` VALUES (522834, 'ORD0000511080', 966, 338, 5, 395.44, '2022-9-19', 'processing');
INSERT INTO `cjc`.`sales_order` VALUES (522835, 'ORD0000511081', 79, 226, 1, 760.00, '2023-12-12', 'processing');
INSERT INTO `cjc`.`sales_order` VALUES (522836, 'ORD0000511082', 491, 231, 9, 776.41, '2022-1-24', 'shipped');
INSERT INTO `cjc`.`sales_order` VALUES (522837, 'ORD0000511083', 693, 383, 8, 468.53, '2020-4-29', 'cancelled');
INSERT INTO `cjc`.`sales_order` VALUES (522838, 'ORD0000511084', 368, 55, 5, 877.08, '2020-4-27', 'completed');
INSERT INTO `cjc`.`sales_order` VALUES (522839, 'ORD0000511085', 152, 380, 4, 450.51, '2021-1-15', 'completed');
INSERT INTO `cjc`.`sales_order` VALUES (522840, 'ORD0000511086', 516, 363, 1, 211.71, '2024-5-4', 'processing');

遇到错误页面强制解析

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/sales_order_01.ibd --sql --ddl --force > /mysqldata/3308/ibd2sql/sales_order_02X.sql
Traceback (most recent call last):
  File "main.py", line 224, in <module>
    ddcw.get_sql()
  File "/soft/ibd2sql/ibd2sql-1.10/ibd2sql/ibd2sql.py", line 263, in get_sql
    for x in _tdata:
  File "/soft/ibd2sql/ibd2sql-1.10/ibd2sql/innodb_page_index.py", line 380, in read_row
    self._read_all_row()
  File "/soft/ibd2sql/ibd2sql-1.10/ibd2sql/innodb_page_index.py", line 505, in _read_all_row
    if rheader.instant_flag and _t_COLUMN_COUNT > _COLUMN_COUNT:
UnboundLocalError: local variable '_COLUMN_COUNT' referenced before assignment

[mysql@cjc-db-02 ibd2sql]$ cat sales_order_02X.sql |wc -l
511102

因为破坏的是末尾数据页的数据，如果破坏的是中间的数据页的数据，不清楚 --force 参数是否可以跳过中间丢失的数据，继续恢复后面的数据？

场景五:恢复误删除的数据

在 MySQL 的 InnoDB 存储引擎中，执行 DELETE FROM t1 删除数据时，实际上只是进行标记删除，在行记录的头部设置 deleted_flag=1，而非立即物理删除。
在数据被覆盖之前，理论上还没有恢复出来。
启动数据库

[mysql@cjc-db-02 ~]$ mysqld --defaults-file=/etc/my.cnf --user=mysql &

mysql> select * from cjc.t1;
+----+------+----------+
| id | name | time     |
+----+------+----------+
|  1 | x    | 10:35:52 |
|  2 | y    | 10:35:56 |
|  3 | z    | 10:36:02 |
|  4 | u    | 10:36:07 |
|  5 | v    | 10:36:16 |
+----+------+----------+
5 rows in set (0.57 sec)

模拟误删除

mysql> delete from cjc.t1 where id>3;
Query OK, 2 rows affected (0.36 sec)

mysql> select * from cjc.t1;
+----+------+----------+
| id | name | time     |
+----+------+----------+
|  1 | x    | 10:35:52 |
|  2 | y    | 10:35:56 |
|  3 | z    | 10:36:02 |
+----+------+----------+
3 rows in set (0.00 sec)

[root@cjc-db-02 ibd2sql-1.10]# cp -a /mysqldata/3308/data/cjc/t1.ibd /mysqldata/3308/ibd2sql/t1_XXX.ibd

仅打印被标记为已删除的数据

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/t1_XXX.ibd --sql --ddl --delete

CREATE TABLE IF NOT EXISTS `cjc`.`t1`(
    `id` int NOT NULL,
    `name` varchar(10) NULL,
    `time` time NULL,
    PRIMARY KEY  (`id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `cjc`.`t1` VALUES (5, 'v', '10:36:16');
INSERT INTO `cjc`.`t1` VALUES (4, 'u', '10:36:7');

使用完整的插入语句

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/t1_XXX.ibd --sql --ddl --delete --complete-insert

CREATE TABLE IF NOT EXISTS `cjc`.`t1`(
    `id` int NOT NULL,
    `name` varchar(10) NULL,
    `time` time NULL,
    PRIMARY KEY  (`id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `cjc`.`t1`(`id`,`name`,`time`) VALUES (5, 'v', '10:36:16');
INSERT INTO `cjc`.`t1`(`id`,`name`,`time`) VALUES (4, 'u', '10:36:7');

场景六:web控制台查看数据结构

支持web控制台查看数据结构

[root@cjc-db-02 ibd2sql-1.10]# python3 ibd2sql_web.py /mysqldata/3308/ibd2sql/t1.ibd 

###############################
#     ibd2sql web console     #
###############################

http://0.0.0.0:8080
改成实际IP

图片.png

[root@cjc-db-02 ibd2sql-1.10]# python3 ibd2sql_web.py /mysqldata/3308/ibd2sql/sales_order.ibd

图片.png

下载ibd2sql

关注《IT小Chen》公众号，私信关键字“ibd2sql”，可获取下载链接！

参考:

https://github.com/ddcw/ibd2sql

###chenjuchao 20250601###
欢迎关注我的公众号《IT小Chen》

简介:

ibd2sql 是一个使用纯python3编写的离线解析MYSQL INNODB存储引擎的ibd文件的工具,并将其转换为 SQL 语句。
无第三方依赖包. 使用GPL-3.0 license.

特点:

1.方便: 提取表DDL
2.实用: 可替换库(--schema)/表(--table)名, 可在sql语句中输出完整的字段(--complete)
3.简单: 纯python3代码编写, 无依赖包.
4.支持众多数据类型: 支持所有mysql数据类型
5.支持复杂的表结构: 分区表, 注释, 主键, 外键, 约束, 自增, 普通索引, 前缀索引, 主键前缀索引, 唯一索引, 复合索引, 默认值, 符号, 虚拟字段, INSTANT, 无主键等情况的表
6.数据误删恢复: 可以输出被标记为deleted的数据
7.安全: 离线解析ibd文件, 仅可读权限即可
8.支持范围广: 支持mysql 5.6 or 5.7 or 8.0 or 8.4 or 9.0

版本更新:

[root@cjc-db-02 ibd2sql-1.10]# cat README_zh.md

版本   更新时间   说明
v0.1  2023.04.27  第一个版本
v0.2  2023.08.30  支持更多数据类型
v0.3  2023.10.13  支持5.7升级到8.0的ibd文件
v1.0  2024.01.05  支持debug,支持更多类型和功能
v1.1  2024.04.12  修复一些bug
v1.2  2024.04.25  新增空间坐标的支持
v1.3  2024.05.11  支持mysql 5.6, 5.7
v1.4  2024.05.21  支持溢出页, 子分区
v1.5  2024.07.10  vector and instant BUG
v1.6  2024.09.19  修复一些bug
v1.7  2024.10.29  1.修复一些bug 2.支持压缩页 3.支持drop table的恢复 4.ucs2,utf16,utf32 字符集支持             |
v1.8  2024.11.09  1.支持所有字符集 2.支持kering 插件加密的表 3.支持web控制台查看数据结构 4.支持修改lower_case_table_names|
v1.9  2025.02.21  修复已知BUG, 支持直接解析5.7的ibd文件
v1.10 2025.04.16  修复已知BUG, 添加快速统计表行数的脚本

需求与支持:

require: python3
support range: mysql5.x 8.x 9.x

下载和安装:

下载和安装:ibd2sql-1.10.tar.gz

v1.10 下载地址:离线上传到服务器

https://github.com/ddcw/ibd2sql/archive/refs/tags/v1.10.tar.gz

或者如果服务器可以联网，可以直接wget下载到服务器:

wget https://github.com/ddcw/ibd2sql/archive/refs/tags/v1.10.tar.gz

查看 ibd2sql-1.10.tar.gz:

[mysql@cjc-db-02 ibd2sql]$ ls -lrth ibd2sql-1.10.tar.gz 
-rw-r--r-- 1 root root 113K May 31 20:07 ibd2sql-1.10.tar.gz

[mysql@cjc-db-02 ibd2sql]$ md5sum ibd2sql-1.10.tar.gz 
3e73b0bf7ab7b7f65d3ddac413a44163  ibd2sql-1.10.tar.gz

解压:

[root@cjc-db-02 ibd2sql]# tar -zxvf ibd2sql-1.10.tar.gz 

[root@cjc-db-02 ibd2sql-1.10]# ls -lrth
total 164K
-rw-rw-r-- 1 root root  31K Apr 16 18:42 xfs_recovery_v0.3.py
-rw-rw-r-- 1 root root  27K Apr 16 18:42 test.sh
-rw-rw-r-- 1 root root 2.2K Apr 16 18:42 super_fast_count.py
-rw-rw-r-- 1 root root 3.5K Apr 16 18:42 README_zh.md
-rw-rw-r-- 1 root root 3.5K Apr 16 18:42 README.md
-rw-rw-r-- 1 root root 2.6K Apr 16 18:42 modify_lower_case_table_names.py
-rw-rw-r-- 1 root root 8.9K Apr 16 18:42 main.py
-rw-rw-r-- 1 root root  35K Apr 16 18:42 LICENSE
-rw-rw-r-- 1 root root  21K Apr 16 18:42 ibd2sql_web.py
-rw-rw-r-- 1 root root  12K Apr 16 18:42 ibd2sql_mini_for_redundant.py
drwxrwxr-x 2 root root   99 Apr 16 18:42 docs
drwxrwxr-x 3 root root 4.0K May 31 20:35 ibd2sql

下载和安装 Python:

下载地址:

https://www.python.org/downloads/release/python-361/

图片.png

上传:

[root@cjc-db-02 ibd2sql]# ls -lrth Python-3.6.1.tgz 
-rw-r--r-- 1 root root 22M May 31 08:44 Python-3.6.1.tgz

[root@cjc-db-02 ibd2sql]# md5sum Python-3.6.1.tgz 
2d0fc9f3a5940707590e07f03ecb08b9  Python-3.6.1.tgz

解压:

tar xvf Python-3.6.1.tgz
cd Python-3.6.1
mkdir -p /usr/local/python3
./configure --prefix=/usr/local/python3
make && make install
ln -s /usr/local/python3/bin/python3 /usr/bin/python3

[root@cjc-db-02 Python-3.6.1]# python3  -V
Python 3.6.1

下载和安装:MySQL 8.4.5

https://dev.mysql.com/downloads/mysql/

图片.png

[root@cjc-db-02 mysql]# ls -lrth
total 2.7G
-rw-r--r-- 1 7155 31415 868M Apr  1 18:11 mysql-8.4.5-linux-glibc2.17-x86_64.tar.xz
-rw-r--r-- 1 7155 31415  78M Apr  1 18:25 mysql-router-8.4.5-linux-glibc2.17-x86_64.tar.xz
-rw-r--r-- 1 7155 31415 432M Apr  1 18:44 mysql-test-8.4.5-linux-glibc2.17-x86_64.tar.xz
-rw-r--r-- 1 root root  1.4G May 31 09:51 mysql-8.4.5-linux-glibc2.17-x86_64.tar

[root@cjc-db-02 mysql]# md5sum mysql-*.tar.xz
8c7573c055a38821f8e65946f48a07ae  mysql-8.4.5-linux-glibc2.17-x86_64.tar.xz
55c76f9b10328e821fac1dbc80204a5e  mysql-router-8.4.5-linux-glibc2.17-x86_64.tar.xz

安装过程省略:
和 8.0 相比，已经删除了 relay_log_info_repository 参数，如果my.cnf配置了，需要参数这个参数。

ibd2sql工具测试:

1.ibd2sql常用命令

帮助信息:

[mysql@cjc-db-02 ibd2sql-1.5]$ python3 main.py --help
[root@cjc-db-02 ibd2sql-1.10]# 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]
               [--keyring-file KEYRING_FILE]
               [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
  --keyring-file KEYRING_FILE, -k KEYRING_FILE
                        keyring filename
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

常用命令:

--version, -v, -V: 显示版本信息。
--ddl, -d: 打印 DDL。
--sql: 打印数据的 SQL 语句。
--delete: 仅打印被标记为已删除的数据。
--complete-insert: 使用完整的插入语句。
--force, -f: 在遇到错误页面时强制解析文件。
--set: 用实际数据填充 set/enum 而不是字符串。
--multi-value: 如果数据属于同一页面，则使用单个 SQL 语句。
--replace: 使用 "REPLACE INTO" 替换 "INSERT INTO"（默认）。
--table TABLE_NAME: 替换表名（不包括 DDL）。
--schema SCHEMA_NAME: 替换库名（不包括 DDL）。
--sdi-table SDI_TABLE: 从这个 IBD 文件（分区表）读取 SDI 页面。
--where-trx WHERE_TRX: 事务过滤范围，默认 (0,281474976710656)。
--where-rollptr WHERE_ROLLPTR: 回滚指针过滤范围，默认 (0,72057594037927936)。
--limit LIMIT: 限制行数。
--debug, -D: 调试模式（输出非常大）。
--debug-file DEBUG_FILE: 调试输出文件，默认为 sys.stdout。
--page-min PAGE_MIN: 如果页面编号小于此值，则终止。
--page-max PAGE_MAX: 如果页面编号大于此值，则终止。
--page-start PAGE_START: 索引页面起始编号。
--page-count PAGE_COUNT: 页面计数编号。
--page-skip PAGE_SKIP: 解析索引页面时跳过某些页面。
--mysql5: 适用于 MySQL 5.7 标志。

2.准备测试数据

创建一张小表cjc.t1和一张大表cjc.sales_order;

mysql> create database cjc;
mysql> use cjc;
create table t1(id int primary key,name varchar(10),time time);
insert into t1 values(1,'x',now());
insert into t1 values(2,'y',now());
insert into t1 values(3,'z',now());
insert into t1 values(4,'u',now());
insert into t1 values(5,'v',now());

mysql> select * from cjc.t1;
+----+------+----------+
| id | name | time     |
+----+------+----------+
|  1 | x    | 10:35:52 |
|  2 | y    | 10:35:56 |
|  3 | z    | 10:36:02 |
|  4 | u    | 10:36:07 |
|  5 | v    | 10:36:16 |
+----+------+----------+
5 rows in set (0.00 sec)


CREATE TABLE sales_order (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(50) NOT NULL UNIQUE,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) GENERATED ALWAYS AS (quantity * price) STORED,
    order_date DATE NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'completed', 'cancelled') NOT NULL,
    INDEX idx_customer (customer_id),
    INDEX idx_date (order_date)
) ENGINE=InnoDB;

创建存储过程:

DELIMITER $$

CREATE PROCEDURE GenerateSalesOrders()
BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE batchSize INT DEFAULT 1000;  -- 每批插入1000条
    DECLARE maxRecords INT DEFAULT 1000000;
    
    WHILE counter < maxRecords DO
        INSERT INTO sales_order (
            order_no, 
            customer_id, 
            product_id, 
            quantity, 
            price, 
            order_date, 
            status
        )
        SELECT
            CONCAT('ORD', LPAD(counter + n, 10, '0')),  -- 唯一订单号
            FLOOR(1 + RAND() * 1000),                   -- 客户ID 1-1000
            FLOOR(1 + RAND() * 500),                   -- 产品ID 1-500
            FLOOR(1 + RAND() * 10),                    -- 数量 1-10
            ROUND(10 + RAND() * 990, 2),               -- 价格 10.00-999.99
            DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND() * 2000) DAY),  -- 日期 2020-2025
            ELT(FLOOR(1 + RAND() * 5), 'pending', 'processing', 'shipped', 'completed', 'cancelled') -- 状态
        FROM (
            SELECT a.N + b.N * 10 + c.N * 100 AS n
            FROM 
                (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
                (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
                (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
            ORDER BY n
            LIMIT batchSize
        ) numbers;
        
        SET counter = counter + batchSize;
        COMMIT;
        SELECT CONCAT('Inserted: ', counter, '/1000000') AS progress;
    END WHILE;
END$$

DELIMITER ;

调用存储过程（耗时约5-15分钟）

CALL GenerateSalesOrders();
......
+--------------------------+
| progress                 |
+--------------------------+
| Inserted: 998000/1000000 |
+--------------------------+
1 row in set (2 min 38.18 sec)

+--------------------------+
| progress                 |
+--------------------------+
| Inserted: 999000/1000000 |
+--------------------------+
1 row in set (2 min 38.39 sec)

+---------------------------+
| progress                  |
+---------------------------+
| Inserted: 1000000/1000000 |
+---------------------------+
1 row in set (2 min 38.48 sec)

Query OK, 0 rows affected (2 min 38.48 sec)

mysql> select * from sales_order limit 10;
+----+---------------+-------------+------------+----------+--------+--------------+------------+------------+
| id | order_no      | customer_id | product_id | quantity | price  | total_amount | order_date | status     |
+----+---------------+-------------+------------+----------+--------+--------------+------------+------------+
|  1 | ORD0000000000 |         916 |         55 |        9 | 685.24 |      6167.16 | 2020-01-07 | cancelled  |
|  2 | ORD0000000001 |         845 |        155 |        1 | 143.77 |       143.77 | 2023-06-30 | completed  |
|  3 | ORD0000000002 |          12 |        351 |        5 | 258.78 |      1293.90 | 2024-08-15 | shipped    |
|  4 | ORD0000000003 |         800 |        301 |        7 | 219.38 |      1535.66 | 2021-05-14 | completed  |
|  5 | ORD0000000004 |         321 |        382 |        9 | 959.75 |      8637.75 | 2021-05-12 | processing |
|  6 | ORD0000000005 |          84 |        160 |        4 | 787.42 |      3149.68 | 2024-10-24 | pending    |
|  7 | ORD0000000006 |         565 |        351 |        9 | 957.87 |      8620.83 | 2021-12-02 | cancelled  |
|  8 | ORD0000000007 |         355 |         65 |        6 | 509.51 |      3057.06 | 2024-04-27 | shipped    |
|  9 | ORD0000000008 |         795 |        338 |       10 | 941.79 |      9417.90 | 2023-12-22 | cancelled  |
| 10 | ORD0000000009 |         844 |        403 |        5 |  55.32 |       276.60 | 2024-02-16 | completed  |
+----+---------------+-------------+------------+----------+--------+--------------+------------+------------+
10 rows in set (0.00 sec)

mysql> select count(*) from sales_order;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.29 sec)


[mysql@cjc-db-02 cjc]$ ls -lrth
total 185M
-rw-r----- 1 mysql mysql 112K May 31 10:36 t1.ibd
-rw-r----- 1 mysql mysql 184M May 31 10:56 sales_order.ibd

停库

mysql> shutdown;
Query OK, 0 rows affected (0.28 sec)

数据文件拷贝到临时目录

[mysql@cjc-db-02 3308]$ cp /mysqldata/3308/data/cjc/*.ibd /mysqldata/3308/ibd2sql/
[mysql@cjc-db-02 3308]$ ls -lrth /mysqldata/3308/ibd2sql/
total 185M
-rw-r----- 1 mysql mysql 184M May 31 11:05 sales_order.ibd
-rw-r----- 1 mysql mysql 112K May 31 11:05 t1.ibd

ibd2sql解析:

场景一:解析完整的ibd文件
场景二:解析完整的ibd文件，合并同一页面的语句
场景三:解析完整的ibd文件，更换表名
场景四:ibd文件损坏，解析ibd文件部分内容
场景五:恢复误删除的数据
场景六:web控制台查看数据结构

场景一:解析完整的ibd文件

cd /soft/ibd2sql/ibd2sql-1.10
python3 main.py /mysqldata/3308/ibd2sql/t1.ibd --sql --ddl

小表:

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/t1.ibd --sql --ddl
CREATE TABLE IF NOT EXISTS `cjc`.`t1`(
    `id` int NOT NULL,
    `name` varchar(10) NULL,
    `time` time NULL,
    PRIMARY KEY  (`id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `cjc`.`t1` VALUES (1, 'x', '10:35:52');
INSERT INTO `cjc`.`t1` VALUES (2, 'y', '10:35:56');
INSERT INTO `cjc`.`t1` VALUES (3, 'z', '10:36:2');
INSERT INTO `cjc`.`t1` VALUES (4, 'u', '10:36:7');
INSERT INTO `cjc`.`t1` VALUES (5, 'v', '10:36:16');

输出到文件

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/t1.ibd --sql --ddl  > /mysqldata/3308/ibd2sql/t1.sql

大表:

[mysql@cjc-db-02 ibd2sql-1.5]$ python3 main.py /mysqldata/3308/ibd2sql/sales_order.ibd --sql --ddl > /mysqldata/3308/ibd2sql/sales_order.sql

看看执行时间，需要约8分钟，解析107MB的数据。

[root@cjc-db-02 ibd2sql-1.10]# date && python3 main.py /mysqldata/3308/ibd2sql/sales_order.ibd --sql --ddl > /mysqldata/3308/ibd2sql/sales_order.sql && date
Sat May 31 21:16:49 CST 2025
Sat May 31 21:24:31 CST 2025

[mysql@cjc-db-02 ibd2sql]$ ls -lrth
total 291M
-rw-r----- 1 mysql mysql 184M May 31 11:05 sales_order.ibd
-rw-r----- 1 mysql mysql 112K May 31 11:05 t1.ibd
-rw-r--r-- 1 root  root  107M May 31 21:24 sales_order.sql
-rw-r--r-- 1 root  root   465 May 31 21:25 t1.sql

[mysql@cjc-db-02 ibd2sql]$ cat sales_order.sql |wc -l
1000015

[mysql@cjc-db-02 ibd2sql]$ more sales_order.sql
CREATE TABLE IF NOT EXISTS `cjc`.`sales_order`(
    `id` bigint NOT NULL AUTO_INCREMENT,
    `order_no` varchar(50) NOT NULL,
    `customer_id` int NOT NULL,
    `product_id` int NOT NULL,
    `quantity` int NOT NULL,
    `price` decimal(10,2) NOT NULL,
    `total_amount` decimal(10,2) NULL,
    `order_date` date NOT NULL,
    `status` enum('pending','processing','shipped','completed','cancelled') NOT NULL,
    PRIMARY KEY  (`id` ),
    UNIQUE KEY `order_no` (`order_no` ),
    KEY `idx_customer` (`customer_id` ),
    KEY `idx_date` (`order_date` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `cjc`.`sales_order` VALUES (1, 'ORD0000000000', 916, 55, 9, 685.24, '2020-1-7', 'cancelled');
INSERT INTO `cjc`.`sales_order` VALUES (2, 'ORD0000000001', 845, 155, 1, 143.77, '2023-6-30', 'completed');
INSERT INTO `cjc`.`sales_order` VALUES (3, 'ORD0000000002', 12, 351, 5, 258.78, '2024-8-15', 'shipped');
......
INSERT INTO `cjc`.`sales_order` VALUES (1022975, 'ORD0000999997', 429, 286, 6, 147.59, '2025-5-21', 'shipped');
INSERT INTO `cjc`.`sales_order` VALUES (1022976, 'ORD0000999998', 554, 133, 7, 534.72, '2023-8-2', 'completed');
INSERT INTO `cjc`.`sales_order` VALUES (1022977, 'ORD0000999999', 457, 115, 8, 195.82, '2023-5-13', 'shipped');

场景二:解析完整的ibd文件，合并同一页面的语句

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/t1.ibd --sql --ddl --complete-insert --table sales_order --multi-value > /mysqldata/3308/ibd2sql/t1_01.sql

[mysql@cjc-db-02 ibd2sql]$ ls -lrth t1*.sql
-rw-r--r-- 1 root  root  465 May 31 21:25 t1.sql
-rw-rw-r-- 1 mysql mysql 379 May 31 21:28 t1_01.sql

将5条insert语句合并为一条

[mysql@cjc-db-02 ibd2sql]$ cat t1_01.sql 
CREATE TABLE IF NOT EXISTS `cjc`.`sales_order`(
    `id` int NOT NULL,
    `name` varchar(10) NULL,
    `time` time NULL,
    PRIMARY KEY  (`id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `cjc`.`sales_order`(`id`,`name`,`time`) VALUES (1, 'x', '10:35:52'),(2, 'y', '10:35:56'),(3, 'z', '10:36:2'),(4, 'u', '10:36:7'),(5, 'v', '10:36:16');

类似于mysqldump的 --extended-insert 参数，可以提高恢复速度。

-e, --extended-insert 
Use multiple-row INSERT syntax that include severalVALUES lists.

[root@cjc-db-02 ibd2sql-1.10]# date && python3 main.py /mysqldata/3308/ibd2sql/sales_order.ibd --sql --ddl --complete-insert --table sales_order --multi-value > /mysqldata/3308/ibd2sql/sales_order_01.sql && date
Sat May 31 21:30:42 CST 2025
Sat May 31 21:37:55 CST 2025

文件大小从107MB缩小到70MB。

[mysql@cjc-db-02 ibd2sql]$ ls -lrth sales_order*.sql
-rw-r--r-- 1 root root 107M May 31 21:24 sales_order.sql
-rw-r--r-- 1 root root  70M May 31 21:37 sales_order_01.sql

行数从1000015缩小到4460

[mysql@cjc-db-02 ibd2sql]$ cat sales_order.sql |wc -l
1000015
[mysql@cjc-db-02 ibd2sql]$ cat sales_order_01.sql |wc -l
4460

场景三:解析完整的ibd文件，更换表名

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/t1.ibd --sql --ddl --complete-insert --table t1_change > /mysqldata/3308/ibd2sql/t1_change.sql

[root@cjc-db-02 ibd2sql-1.10]# cat /mysqldata/3308/ibd2sql/t1_change.sql
CREATE TABLE IF NOT EXISTS `cjc`.`t1_change`(
    `id` int NOT NULL,
    `name` varchar(10) NULL,
    `time` time NULL,
    PRIMARY KEY  (`id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `cjc`.`t1_change`(`id`,`name`,`time`) VALUES (1, 'x', '10:35:52');
INSERT INTO `cjc`.`t1_change`(`id`,`name`,`time`) VALUES (2, 'y', '10:35:56');
INSERT INTO `cjc`.`t1_change`(`id`,`name`,`time`) VALUES (3, 'z', '10:36:2');
INSERT INTO `cjc`.`t1_change`(`id`,`name`,`time`) VALUES (4, 'u', '10:36:7');
INSERT INTO `cjc`.`t1_change`(`id`,`name`,`time`) VALUES (5, 'v', '10:36:16');

场景四:ibd文件损坏，解析ibd文件部分内容

破坏文件，模拟数据文件损坏

[root@cjc-db-02 ibd2sql]# cp -a sales_order.ibd sales_order_01.ibd

破坏最后100MB的数据

dd if=/dev/urandom of=/mysqldata/3308/ibd2sql/sales_order_01.ibd \
      bs=1M \
      seek=$(($(stat -c %s /mysqldata/3308/ibd2sql/sales_order_01.ibd )/1048576 - 100)) \
      count=100 \
      conv=notrunc

前台日志:

100+0 records in
100+0 records out
104857600 bytes (105 MB) copied, 8.77186 s, 12.0 MB/s

参数说明:
conv=notrunc 不截断文件，仅覆盖指定部分

开始解析

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/sales_order_01.ibd --sql --ddl > /mysqldata/3308/ibd2sql/sales_order_02.sql

前台报错:

Traceback (most recent call last):
  File "main.py", line 224, in <module>
    ddcw.get_sql()
  File "/soft/ibd2sql/ibd2sql-1.10/ibd2sql/ibd2sql.py", line 263, in get_sql
    for x in _tdata:
  File "/soft/ibd2sql/ibd2sql-1.10/ibd2sql/innodb_page_index.py", line 380, in read_row
    self._read_all_row()
  File "/soft/ibd2sql/ibd2sql-1.10/ibd2sql/innodb_page_index.py", line 505, in _read_all_row
    if rheader.instant_flag and _t_COLUMN_COUNT > _COLUMN_COUNT:
UnboundLocalError: local variable '_COLUMN_COUNT' referenced before assignment

数据量不完整

[mysql@cjc-db-02 ibd2sql]$ cat sales_order_02.sql |wc -l
511102

[mysql@cjc-db-02 ibd2sql]$ ls -lrth sales_order*.sql
-rw-r--r-- 1 root  root  107M May 31 21:24 sales_order.sql
-rw-r--r-- 1 root  root   70M May 31 21:37 sales_order_01.sql
-rw-rw-r-- 1 mysql mysql  55M May 31 21:51 sales_order_02.sql

[mysql@cjc-db-02 ibd2sql]$ tail -n 10 sales_order_02.sql 
INSERT INTO `cjc`.`sales_order` VALUES (522831, 'ORD0000511077', 138, 346, 1, 141.50, '2022-12-17', 'processing');
INSERT INTO `cjc`.`sales_order` VALUES (522832, 'ORD0000511078', 903, 300, 3, 641.24, '2021-10-16', 'completed');
INSERT INTO `cjc`.`sales_order` VALUES (522833, 'ORD0000511079', 634, 500, 1, 481.16, '2020-7-10', 'pending');
INSERT INTO `cjc`.`sales_order` VALUES (522834, 'ORD0000511080', 966, 338, 5, 395.44, '2022-9-19', 'processing');
INSERT INTO `cjc`.`sales_order` VALUES (522835, 'ORD0000511081', 79, 226, 1, 760.00, '2023-12-12', 'processing');
INSERT INTO `cjc`.`sales_order` VALUES (522836, 'ORD0000511082', 491, 231, 9, 776.41, '2022-1-24', 'shipped');
INSERT INTO `cjc`.`sales_order` VALUES (522837, 'ORD0000511083', 693, 383, 8, 468.53, '2020-4-29', 'cancelled');
INSERT INTO `cjc`.`sales_order` VALUES (522838, 'ORD0000511084', 368, 55, 5, 877.08, '2020-4-27', 'completed');
INSERT INTO `cjc`.`sales_order` VALUES (522839, 'ORD0000511085', 152, 380, 4, 450.51, '2021-1-15', 'completed');
INSERT INTO `cjc`.`sales_order` VALUES (522840, 'ORD0000511086', 516, 363, 1, 211.71, '2024-5-4', 'processing');

遇到错误页面强制解析

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/sales_order_01.ibd --sql --ddl --force > /mysqldata/3308/ibd2sql/sales_order_02X.sql
Traceback (most recent call last):
  File "main.py", line 224, in <module>
    ddcw.get_sql()
  File "/soft/ibd2sql/ibd2sql-1.10/ibd2sql/ibd2sql.py", line 263, in get_sql
    for x in _tdata:
  File "/soft/ibd2sql/ibd2sql-1.10/ibd2sql/innodb_page_index.py", line 380, in read_row
    self._read_all_row()
  File "/soft/ibd2sql/ibd2sql-1.10/ibd2sql/innodb_page_index.py", line 505, in _read_all_row
    if rheader.instant_flag and _t_COLUMN_COUNT > _COLUMN_COUNT:
UnboundLocalError: local variable '_COLUMN_COUNT' referenced before assignment

[mysql@cjc-db-02 ibd2sql]$ cat sales_order_02X.sql |wc -l
511102

因为破坏的是末尾数据页的数据，如果破坏的是中间的数据页的数据，不清楚 --force 参数是否可以跳过中间丢失的数据，继续恢复后面的数据？

场景五:恢复误删除的数据

[mysql@cjc-db-02 ~]$ mysqld --defaults-file=/etc/my.cnf --user=mysql &

mysql> select * from cjc.t1;
+----+------+----------+
| id | name | time     |
+----+------+----------+
|  1 | x    | 10:35:52 |
|  2 | y    | 10:35:56 |
|  3 | z    | 10:36:02 |
|  4 | u    | 10:36:07 |
|  5 | v    | 10:36:16 |
+----+------+----------+
5 rows in set (0.57 sec)

模拟误删除

mysql> delete from cjc.t1 where id>3;
Query OK, 2 rows affected (0.36 sec)

mysql> select * from cjc.t1;
+----+------+----------+
| id | name | time     |
+----+------+----------+
|  1 | x    | 10:35:52 |
|  2 | y    | 10:35:56 |
|  3 | z    | 10:36:02 |
+----+------+----------+
3 rows in set (0.00 sec)

[root@cjc-db-02 ibd2sql-1.10]# cp -a /mysqldata/3308/data/cjc/t1.ibd /mysqldata/3308/ibd2sql/t1_XXX.ibd

仅打印被标记为已删除的数据

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/t1_XXX.ibd --sql --ddl --delete

CREATE TABLE IF NOT EXISTS `cjc`.`t1`(
    `id` int NOT NULL,
    `name` varchar(10) NULL,
    `time` time NULL,
    PRIMARY KEY  (`id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `cjc`.`t1` VALUES (5, 'v', '10:36:16');
INSERT INTO `cjc`.`t1` VALUES (4, 'u', '10:36:7');

使用完整的插入语句

[root@cjc-db-02 ibd2sql-1.10]# python3 main.py /mysqldata/3308/ibd2sql/t1_XXX.ibd --sql --ddl --delete --complete-insert

CREATE TABLE IF NOT EXISTS `cjc`.`t1`(
    `id` int NOT NULL,
    `name` varchar(10) NULL,
    `time` time NULL,
    PRIMARY KEY  (`id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;
INSERT INTO `cjc`.`t1`(`id`,`name`,`time`) VALUES (5, 'v', '10:36:16');
INSERT INTO `cjc`.`t1`(`id`,`name`,`time`) VALUES (4, 'u', '10:36:7');

场景六:web控制台查看数据结构

支持web控制台查看数据结构

[root@cjc-db-02 ibd2sql-1.10]# python3 ibd2sql_web.py /mysqldata/3308/ibd2sql/t1.ibd 

###############################
#     ibd2sql web console     #
###############################

http://0.0.0.0:8080
改成实际IP

图片.png

[root@cjc-db-02 ibd2sql-1.10]# python3 ibd2sql_web.py /mysqldata/3308/ibd2sql/sales_order.ibd

图片.png

下载ibd2sql

关注《IT小Chen》公众号，私信关键字“ibd2sql”，可获取下载链接！

参考:

https://github.com/ddcw/ibd2sql

###chenjuchao 20250601###
欢迎关注我的公众号《IT小Chen》