【法器篇】天啦噜，库崩了没备份
背景
数据库没有做备份，一天突然由于断电或其他原因导致无法启动了，且设置了innodb_force_recovery=6都无法启动，里面的数据怎么才能恢复出来？
本例采用解析建表语句+表空间传输的方式进行恢复。
环境
本案例基于下面版本进行的测试验证
- 5.7.44
- 8.0.40
- ibd2sql v1.10+
- 编写的一键恢复脚本
- 10.10.2.12【源端-故障实例】
- 10.10.2.13【目标端-恢复实例】
恢复演示
这边解析演示为单个表，入了需要某个库下的表或多个库下表，可使用下面脚本执行
1.创建测试数据
在已有数据库上创建测试库表
create database yqtest;
create table yqtest.tt01(id int primary key,vname varchar(20));
insert into yqtest.tt01 values (1,'张飞'),(2,'关羽'),(3,'刘备');
create table yqtest.tt02(id int auto_increment primary key,vname varchar(20));
insert into yqtest.tt02(vname) values ('洪七公'),('欧阳锋'),('黄药师');
-- 分区表
CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE,PRIMARY KEY (id,purchased))
PARTITION BY RANGE(YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005),
PARTITION p4 VALUES LESS THAN (2010),
PARTITION p5 VALUES LESS THAN (2015)
);
INSERT INTO tr VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'alarm clock', '1997-11-05'),
(3, 'chair', '2009-03-10'),
(4, 'bookcase', '1989-01-10'),
(5, 'exercise bike', '2014-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'espresso maker', '2011-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '2006-09-16'),
(10, 'lava lamp', '1998-12-25');
create database abc;
create table abc.td01(id varchar(20),va varchar(10),vb int);
insert into abc.td01 values('a','张三',20),('b','李四',18);
2.拷贝数据目录
假设当前源端的MySQL已无法启动，但数据文件还是存留着的，需要基于这些数据文件进行数据恢复
-- 将数据文件拷贝到目标端去
scp -r /data/mysql3310/data 10.10.2.13:/root/data_back
3.目标端新建一个同版本的MySQL实例
4.在目标实例下载安装ibd2sql
-- 安装python3
yum install -y python3
-- 下载ibd2sql
wget wget https://github.com/ddcw/ibd2sql/archive/refs/heads/main.zip
unzip main.zip
cd ibd2sql-main
5.解析建表语句并创建表
解析表结构
[root@test13 ~]# python3 /opt/ibd2sql-main/main.py /root/data_back/abc/td01.ibd --ddl
CREATE TABLE IF NOT EXISTS `abc`.`td01`(
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT,
`id` varchar(20) NULL,
`va` varchar(10) NULL,
`vb` int NULL,
PRIMARY KEY (`my_row_id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
目标库创建表
[root@mysql.sock][abc]> CREATE TABLE IF NOT EXISTS `abc`.`td01`(
-> `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT,
-> `id` varchar(20) NULL,
-> `va` varchar(10) NULL,
-> `vb` int NULL,
-> PRIMARY KEY (`my_row_id` )
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
Query OK, 0 rows affected (0.03 sec)
6.丢弃表空间
执行丢弃表空间，此表的所有数据都会丢失
[root@mysql.sock][abc]> ALTER TABLE td01 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
7.拷贝文件到目标目录下
[root@test13 ~]# cp /root/data_back/abc/td01.ibd /data/mysql3310/data/abc/
[root@test13 ~]# chown mysql:mysql /data/mysql3310/data/abc/td01.ibd
8.导入表空间
导入表空间，可发现数据已加载恢复
[root@mysql.sock][abc]> ALTER TABLE td01 IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.03 sec)
[root@mysql.sock][abc]> select * from td01;
+-----------+------+--------+------+
| my_row_id | id | va | vb |
+-----------+------+--------+------+
| 1 | a | 张三 | 20 |
| 2 | b | 李四 | 18 |
+-----------+------+--------+------+
2 rows in set (0.00 sec)
一键脚本恢复
- 对于单库或多库的恢复，会有需要表，因此使用脚本恢复，以下脚本遍历指定库下的ibd文件后执行恢复
- 使用脚本一键解析指定库的表结构，暂时不支持分区表，分区表需要手动解析恢复
[root@test13 ~]# cat ibd2sql_recover.sh
#!/bin/bash
set -eo pipefail
# 配置参数
DATABASES=("abc" "yqtest") # 需要恢复的数据库列表
BACKUP_DIR="/root/data_back" # 备份文件根目录
MYSQL_BIN="/usr/local/mysql80/bin/mysql"
MYSQL_USER="super_user" # MySQL用户
MYSQL_PASSWORD='s123456' # MySQL密码
MYSQL_HOST="127.0.0.1" # MySQL主机
MYSQL_PORT="3310" # MySQL端口
MYSQL_DATA_DIR="/data/mysql3310/data" # MySQL数据目录
IBD2SQL_SCRIPT="/opt/ibd2sql-main/main.py" # ibd2sql脚本路径
# 安全凭证文件处理
cleanup() {
rm -f "${TMP_CNF_FILE}"
exit 1
}
trap cleanup EXIT TERM INT
TMP_CNF_FILE=$(mktemp)
cat > "${TMP_CNF_FILE}" <<EOF
[client]
user = "${MYSQL_USER}"
password = "${MYSQL_PASSWORD}"
host = "${MYSQL_HOST}"
port = "${MYSQL_PORT}"
EOF
chmod 600 "${TMP_CNF_FILE}"
# 检查必要命令
command -v $MYSQL_BIN >/dev/null || { echo "mysql客户端未安装"; exit 1; }
command -v python3 >/dev/null || { echo "python3未安装"; exit 1; }
# 遍历数据库
for DB in "${DATABASES[@]}"; do
echo "▌▌▌ 正在处理数据库: $DB"
$MYSQL_BIN --defaults-extra-file="${TMP_CNF_FILE}" \
-e "CREATE DATABASE IF NOT EXISTS \`$DB\` CHARACTER SET utf8mb4;" || exit $?
IBDPATH="$BACKUP_DIR/$DB"
[[ -d "$IBDPATH" ]] || { echo "▌! 目录 $IBDPATH 不存在，跳过"; continue; }
# 遍历.ibd文件（排除分区文件）
find "$IBDPATH" -name '*.ibd' -print0 | while IFS= read -r -d '' IBD_FILE; do
# 过滤分区文件（匹配 #p# 或 #P# 模式）
if [[ $(basename "$IBD_FILE") =~ [#][pP][#] ]]; then
echo "▌! 跳过分区文件: $(basename "$IBD_FILE")"
continue
fi
# 提取表名（处理可能的特殊字符）
TABLE=$(basename "$IBD_FILE" .ibd | sed 's/#[^#]*$//') # 处理含#的非分区表
echo "▌▌▌→ 正在处理表: $TABLE"
# 解析建表语句
echo "▌→ 解析表结构..."
DDL=$(python3 "$IBD2SQL_SCRIPT" "$IBD_FILE" --ddl) || {
echo "▌! 解析失败: $IBD_FILE";
continue # 跳过失败文件继续后续流程
}
# 创建表结构
echo "▌→ 创建表结构..."
$MYSQL_BIN --defaults-extra-file="${TMP_CNF_FILE}" "$DB" \
-e "DROP TABLE IF EXISTS \`${TABLE}\`; $DDL" || {
echo "▌! 创建表失败: $DB.$TABLE";
continue
}
# 表空间操作
echo "▌→ 丢弃表空间..."
$MYSQL_BIN --defaults-extra-file="${TMP_CNF_FILE}" "$DB" \
-e "ALTER TABLE \`${TABLE}\` DISCARD TABLESPACE;" || continue
# 复制文件
TARGET_IBD="${MYSQL_DATA_DIR}/${DB}/${TABLE}.ibd"
echo "▌→ 复制文件到: $TARGET_IBD"
cp -v "$IBD_FILE" "$TARGET_IBD" || continue
chown -v mysql:mysql "$TARGET_IBD" || continue
# 导入表空间
echo "▌→ 导入表空间..."
$MYSQL_BIN --defaults-extra-file="${TMP_CNF_FILE}" "$DB" \
-e "ALTER TABLE \`${TABLE}\` IMPORT TABLESPACE;" || continue
echo "▌√ 表 $DB.$TABLE 恢复完成"
done
done
echo "所有数据库恢复操作已完成"
[root@test13 ~]# sh ibd2sql_recover.sh
▌▌▌ 正在处理数据库: abc
▌▌▌→ 正在处理表: td01
▌→ 解析表结构...
▌→ 创建表结构...
▌→ 丢弃表空间...
▌→ 复制文件到: /data/mysql3310/data/abc/td01.ibd
'/root/data_back/abc/td01.ibd' -> '/data/mysql3310/data/abc/td01.ibd'
'/data/mysql3310/data/abc/td01.ibd' 的所有者已从 root:root 更改为 mysql:mysql
▌→ 导入表空间...
▌√ 表 abc.td01 恢复完成
▌▌▌ 正在处理数据库: yqtest
▌▌▌→ 正在处理表: tt01
▌→ 解析表结构...
▌→ 创建表结构...
▌→ 丢弃表空间...
▌→ 复制文件到: /data/mysql3310/data/yqtest/tt01.ibd
'/root/data_back/yqtest/tt01.ibd' -> '/data/mysql3310/data/yqtest/tt01.ibd'
'/data/mysql3310/data/yqtest/tt01.ibd' 的所有者已从 root:root 更改为 mysql:mysql
▌→ 导入表空间...
▌√ 表 yqtest.tt01 恢复完成
▌▌▌→ 正在处理表: tt02
▌→ 解析表结构...
▌→ 创建表结构...
▌→ 丢弃表空间...
▌→ 复制文件到: /data/mysql3310/data/yqtest/tt02.ibd
'/root/data_back/yqtest/tt02.ibd' -> '/data/mysql3310/data/yqtest/tt02.ibd'
'/data/mysql3310/data/yqtest/tt02.ibd' 的所有者已从 root:root 更改为 mysql:mysql
▌→ 导入表空间...
▌√ 表 yqtest.tt02 恢复完成
所有数据库恢复操作已完成
