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#