mysql8 根据ibd文件恢复表

原文

https://github.com/ddcw/ibd2sql

 

环境：mysql 8.0.33

一、创建测试数据

Mysql8.0.32环境：
mysql> create table t1(id int ,name varchar(200));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 select 1,'a';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select 1,'a';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select 1,'a';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;
Empty set (0.00 sec)

mysql> alter table t1 add age int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update t1 set age=10;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;
+--------+
| NAME   |
+--------+
| czg/t1 |
+--------+
1 row in set (0.01 sec)

 

二、安装工具

root@db-0:~# wget  https://github.com/ddcw/ibd2sql/archive/refs/tags/v1.2.tar.gz

root@db-0:~# tar xvf v1.2.tar.gz 
ibd2sql-1.2/
ibd2sql-1.2/LICENSE
ibd2sql-1.2/README.md
ibd2sql-1.2/README_OLD.md
ibd2sql-1.2/getsql.py
ibd2sql-1.2/ibd2sql/
ibd2sql-1.2/ibd2sql/COLLATIONS.py
ibd2sql-1.2/ibd2sql/__init__.py
ibd2sql-1.2/ibd2sql/ibd2sql.py
ibd2sql-1.2/ibd2sql/innodb_page.py
ibd2sql-1.2/ibd2sql/innodb_page_expage.py
ibd2sql-1.2/ibd2sql/innodb_page_ibuf.py
ibd2sql-1.2/ibd2sql/innodb_page_index.py
ibd2sql-1.2/ibd2sql/innodb_page_inode.py
ibd2sql-1.2/ibd2sql/innodb_page_sdi.py
ibd2sql-1.2/ibd2sql/innodb_page_spaceORxdes.py
ibd2sql-1.2/ibd2sql/innodb_type.py
ibd2sql-1.2/ibd2sql/mysql_json.py
ibd2sql-1.2/ibd2sql/page_type.py
ibd2sql-1.2/main.py
root@db-0:~# cd ibd2sql-1.2/
root@db-0:~/ibd2sql-1.2# ll
total 100
drwxrwxr-x  3 root root  4096 Apr 25 17:09 ./
drwx------ 37 root root  4096 May  8 13:42 ../
-rw-rw-r--  1 root root 35149 Apr 25 17:09 LICENSE
-rw-rw-r--  1 root root  8696 Apr 25 17:09 README.md
-rw-rw-r--  1 root root 19259 Apr 25 17:09 README_OLD.md
-rw-rw-r--  1 root root 11457 Apr 25 17:09 getsql.py
drwxrwxr-x  2 root root  4096 Apr 25 17:09 ibd2sql/
-rw-rw-r--  1 root root  6313 Apr 25 17:09 main.py

root@db-0:~/ibd2sql-1.2# python3 main.py  -h
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]
               [FILENAME]

解析mysql8.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
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 --delete --sql

 

三、恢复数据

root@db-0:~/ibd2sql-1.2# python3 main.py  /usr/local/mysql8/data/ceshi/t1.ibd --ddl --sql --complete
CREATE TABLE IF NOT EXISTS `ceshi`.`t1`(
    `id` int NULL,
    `name` varchar(200) NULL,
    `age` int NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
INSERT INTO `ceshi`.`t1`(`id`,`name`,`age`) VALUES (1, 'a', NULL);
INSERT INTO `ceshi`.`t1`(`id`,`name`,`age`) VALUES (1, 'a', NULL);
INSERT INTO `ceshi`.`t1`(`id`,`name`,`age`) VALUES (1, 'a', NULL);
root@db-0:~/ibd2sql-1.2#