前置工作
安装MySQL8,了解MySQL的基本用法;
安装Python3,因为ibd2sql基于Python3编写;
安装Ubuntu环境,下面的数据恢复过程是在Linux下进行的。
简介
由于电脑出现故障,导致 MySQL 被损坏,恢复了半天数据,总结了这篇文章,给有需要的朋友一个参考。
如果只有 .ibd 文件,需要恢复数据库怎么办?此时,可以引用一位大神编写的开源工具 ibd2sql,帮助生成 DDL 和 DML 。
DDL: 数据定义语言,用来定义数据库对象(数据库、表、字段)。
DML: 数据操作语言,用来对数据库表中的数据进行增删改。
DQL: 数据查询语言,用来查询数据库中表的记录。
DCL: 数据控制语言,用来创建数据库用户、控制数据库的控制权限。
ibd2sql 是一个使用纯 python3 编写的离线解析 MYSQL INNODB 存储引擎的 ibd 文件的工具,无第三方依赖包,使用 GPL-3.0 license。
源码下载地址: https://github.com/ddcw/ibd2sql
恢复过程
通过 ibd2sql 工具,依次将下列 ibd 文件转为 sql 文件,这里只使用其 DDL 部分。
sys_user.ibd
sys_friend.ibd
sys_group.ibd
sys_group_user.ibd
sys_offline_message.ibd
ibd 文件转 sql文件
root@localhost:~/chat-db# python3 ../ibd2sql-main/main.py sys_user.ibd --ddl > sys_user_s.sql
root@localhost:~/chat-db# python3 ../ibd2sql-main/main.py sys_friend.ibd --ddl > sys_friend_s.sql
root@localhost:~/chat-db# python3 ../ibd2sql-main/main.py sys_group.ibd --ddl > sys_group_s.sql
root@localhost:~/chat-db# python3 ../ibd2sql-main/main.py sys_group_user.ibd --ddl > sys_group_user_s.sql
root@localhost:~/chat-db# python3 ../ibd2sql-main/main.py sys_offline_message.ibd --ddl > sys_offline_message_s.sql
创建空数据库
创建一个空数据库 chat,通过 ibd2sql 生成的 DDL 语句创建表结构。
mysql> create database chat;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE TABLE IF NOT EXISTS `chat`.`sys_user`(
-> `id` int NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) NOT NULL,
-> `password` varchar(50) NOT NULL,
-> `state` enum('online','offline') NULL DEFAULT 'offline',
-> PRIMARY KEY (`id` ),
-> UNIQUE KEY `name` (`name` )
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE IF NOT EXISTS `chat`.`sys_friend`(
-> `userid` int NOT NULL,
-> `friendid` int NOT NULL,
-> PRIMARY KEY (`userid` ,`friendid` )
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE IF NOT EXISTS `chat`.`sys_group`(
-> `id` int NOT NULL AUTO_INCREMENT,
-> `groupname` varchar(50) NOT NULL,
-> `groupdesc` varchar(200) NULL DEFAULT '',
-> PRIMARY KEY (`id` )
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE IF NOT EXISTS `chat`.`sys_group_user`(
-> `groupid` int NOT NULL,
-> `userid` int NOT NULL,
-> `grouprole` enum('creator','normal') NULL DEFAULT 'normal',
-> PRIMARY KEY (`groupid` ,`userid` )
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE IF NOT EXISTS `chat`.`sys_offline_message`(
-> `userid` int NOT NULL,
-> `message` varchar(500) NOT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
Query OK, 0 rows affected (0.03 sec)
导入表空间
通过 ibd 文件导入数据,主要是想了解 ibd 的内部细节。本来 ibd2sql 工具都已经能生成 DML 代码了,直接就能把数据插入表中。
将所有用于导入数据的 ibd 文件,复制一份到 /tmp 目录下,以防导入过程中不小心将其删除或损坏。
例子1
下面以导入 sys_user 数据为例,先来一整套丝滑的导入过程。
删除表空间
mysql> alter table sys_user discard tablespace;
Query OK, 0 rows affected (0.01 sec)
拷贝待恢复的 ibd 文件
mysql> system cp -ra /tmp/sys_user.ibd /var/lib/mysql/chat
修改所属者
mysql> system chown mysql:mysql /var/lib/mysql/chat/sys_user.ibd
导入表空间
mysql> alter table sys_user import tablespace;
Query OK, 0 rows affected, 1 warning (0.03 sec)
验证导入结果
mysql> select * from sys_user;
+----+-------------+-------------+---------+
| id | name | password | state |
+----+-------------+-------------+---------+
| 1 | xiaoxie | 123456 | offline |
| 2 | lisi | 666666 | offline |
| 4 | gaoshan | 123086 | offline |
| 6 | liuyang | 123569 | offline |
+----+--------------+------------+---------+
4 rows in set (0.00 sec)
OK,成功!
例子2
接下来继续导入其他 ibd 文件中的数据。
先导入 sys_friend,其中会穿插当时的思考过程,但整体同前。
删除表空间
mysql> alter table sys_friend discard tablespace;
Query OK, 0 rows affected (0.01 sec)
拷贝待恢复的 ibd 文件
mysql> system cp -ra /tmp/sys_friend.ibd /var/lib/mysql/chat
查看拷贝结果,发现其所属者为 root。
root@localhost:/var/lib/mysql/chat# ll
总计 584
drwxr-x--- 2 mysql mysql 4096 8月 17 02:16 ./
drwx------ 9 mysql mysql 4096 8月 17 00:26 ../
-rw-r----- 1 root root 114688 8月 17 02:05 sys_friend.ibd
-rw-r----- 1 mysql mysql 114688 8月 17 02:01 sys_group.ibd
-rw-r----- 1 mysql mysql 114688 8月 17 02:01 sys_group_user.ibd
-rw-r----- 1 mysql mysql 114688 8月 17 02:02 sys_offline_message.ibd
-rw-r----- 1 mysql mysql 131072 8月 17 02:12 sys_user.ibd
如果没有修改所属者,导入表空间将会出错。
mysql> alter table sys_friend import tablespace;
ERROR 1812 (HY000): Tablespace is missing for table `chat`.`sys_friend`.
所以,必须修改其所属者。
mysql> system chown mysql:mysql /var/lib/mysql/chat/sys_friend.ibd
查看修改结果。
root@localhost:/var/lib/mysql/chat# ll
总计 584
drwxr-x--- 2 mysql mysql 4096 8月 17 02:16 ./
drwx------ 9 mysql mysql 4096 8月 17 00:26 ../
-rw-r----- 1 mysql mysql 114688 8月 17 02:05 sys_friend.ibd
-rw-r----- 1 mysql mysql 114688 8月 17 02:01 sys_group.ibd
-rw-r----- 1 mysql mysql 114688 8月 17 02:01 sys_group_user.ibd
-rw-r----- 1 mysql mysql 114688 8月 17 02:02 sys_offline_message.ibd
-rw-r----- 1 mysql mysql 131072 8月 17 02:12 sys_user.ibd
OK,接下来导入表空间就会成功。
mysql> alter table sys_friend import tablespace;
Query OK, 0 rows affected, 1 warning (0.03 sec)
验证一下导入结果
mysql> select * from sys_friend;
+--------+-----------+
| userid | friendid |
+--------+-----------+
| 1 | 2 |
| 1 | 4 |
+--------+-----------+
2 rows in set (0.00 sec)
OK,成功!
例子3
下面继续导入其他表空间的数据,整体过程同上面一样。都是删除表空间,拷贝待恢复的 ibd 文件,修改所属者,导入表空间,验证结果。
删除 sys_group 表空间
mysql> alter table sys_group discard tablespace;
Query OK, 0 rows affected (0.02 sec)
删除之后,看看其造成什么影响。
mysql> select * from sys_group;
ERROR 1814 (HY000): Tablespace has been discarded for table 'sys_group'
继续删除 sys_group_user 和 sys_offline_message 的表空间
mysql> alter table sys_group_user discard tablespace;
Query OK, 0 rows affected (0.01 sec)
mysql> alter table sys_offline_message discard tablespace;
Query OK, 0 rows affected (0.01 sec)
拷贝待恢复的 ibd 文件
mysql> system cp -ra /tmp/sys_group.ibd /var/lib/mysql/chat
mysql> system cp -ra /tmp/sys_group_user.ibd /var/lib/mysql/chat
mysql> system cp -ra /tmp/sys_offline_message.ibd /var/lib/mysql/chat
修改所属者
root@localhost:/var/lib/mysql/chat# chown mysql:mysql sys_group.ibd
导入表空间
mysql> alter table sys_group import tablespace;
Query OK, 0 rows affected, 1 warning (0.03 sec)
验证一下导入结果
mysql> select * from sys_group;
+----+----------------------+----------------------------------------------+
| id | groupname | groupdesc |
+----+----------------------+---------------------------------------------- +
| 1 | Special Project | This is a special project, work hard |
+----+----------------------+-----------------------------------------------+
1 row in set (0.00 sec)
OK,继续修改 sys_group_user 和 sys_offline_message 的所属者。
root@localhost:/var/lib/mysql/chat# chown mysql:mysql sys_group_user.ibd
root@localhost:/var/lib/mysql/chat# chown mysql:mysql sys_offline_message.ibd
查看修改结果
mysql> select * from sys_group_user;
ERROR 1814 (HY000): Tablespace has been discarded for table 'sys_group_user'
mysql> select * from sys_offline_message;
ERROR 1814 (HY000): Tablespace has been discarded for table 'sys_offline_message'
导入表空间
mysql> alter table sys_group_user import tablespace;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> alter table sys_offline_message import tablespace;
Query OK, 0 rows affected, 1 warning (0.03 sec)
验证一下导入结果
mysql> select * from sys_group_user;
+----------+---------+--------------+
| groupid | userid | grouprole |
+----------+---------+--------------+
| 1 | 1 | creator |
| 1 | 2 | ordinary |
| 1 | 4 | ordinary |
+----------+---------+--------------+
3 rows in set (0.00 sec)
mysql> select * from sys_offline_message;
Empty set (0.00 sec)
OK,完成!