简介
本文章主要讲解 MySQL 异常宕机等意外情况下导致 ibd 文件损坏，配置 mysql 强制恢复（innodb_force_recovery） 1-6 级仍然无法启动的情况。下面是恢复整个实例的所有流程和思路。
这里恢复的实例是 mysql5.6 的整个流程，5.7和 5.6 类似，8.0 可以直接用
ibd2sql进行坏表的恢复。再次提醒：建议 MySQL 开启定时全备、开启
row格式binlog、创建从库等冗余操作，防止重要数据丢失！！！操作前请备份！！！
操作前请确认磁盘空间！！！
环境：
机器异常宕机后，MySQL 5.6 的实例恢复模式无法启动，没有备份和完整的 binlog，客户要求尽可能的恢复数据。
恢复思路：
- 1、先将所有
.ibd文件使用释放&导入表空间的方式恢复一遍到新实例 - 2、使用
ibd2sql将第一步无法恢复的innodb表导出为SQL并恢复 - 3、将
MyISAM表导入到新的实例 - 4、使用导入导出的方式恢复系统表（user、event、func、proc）
- 5、使用
ibd2sql导出视图并恢复 - 6、mysqlcheck 检查和修复一遍所有表
- 7、逻辑备份新实例&然后在新建个实例恢复进去 (因为不确定是否有问题)
注意：
损坏的表可能无法恢复全部数据，只能从好的数据页中获取数据。
操作前请备份！！！
使用到的工具：
dbsake: https://blog.csdn.net/weixin_45385457/article/details/141135847
ibd2sql: https://blog.csdn.net/weixin_45385457/article/details/141135987
关于实战用法请查看我的其他文章，关于详细信息请官网查看。
安装恢复工具
安装 dbsake
# 下载 curl -s get.dbsake.net > dbsake # 添加执行权限 chmod u+x dbsake # 添加到 bin 目录 mv dbsake /usr/local/bin/ # 验证 dbsake --help
安装 ibd2sql
# 安装python 3 yum install -y python3 # 下载安装包 wget https://github.com/ddcw/ibd2sql/archive/refs/heads/main.zip # 解压 unzip main.zip # 进入安装目录 cd ibd2sql-main
导入表名到 mysql
新建一个相同版本的MySQL实例，将表结构导入到新实例中。
获取 innodb 表的建表语句
dbsake frmdump $(ll *.ibd|awk '{print $NF}'|awk -F. '{print $1}'|awk '{print $1".frm"}') >/root/create_table.sql
将表结构导入到 MySQL
# 删除并创建库 # 导入表结构 mysql -S /tmp/mysql.sock -e "drop database test;create database test;use test;";mysql -S /tmp/mysql.sock test < /root/create_table.sql
注意：
导入的库名最好和原库名相同
报错处理：
修改导出 SQL 中的 bug (导入报错时使用)
# 默认值无效 # 修改 datetime 类型 sed -i 's#datetime DEFAULT NULL#datetime null DEFAULT NULL#g' create_table.sql # 修改 timestamp 类型 sed -i 's#timestamp DEFAULT NULL#datetime null DEFAULT NULL#g' create_table.sql
释放表空间导入表
在新实例中释放表空间，然后将旧实例的表空间复制到新实例中，导入表空间。
手动实现
- 1、新实例释放表空间
mysql -e "alter table table_name discard tablespace;"
- 2、将旧表结构移到新表结构库目录中
cp -a /old/data_dir/database/table_name.ibd /new/data_dir/database/table_name.ibd
- 3、授权为 mysql 用户
chown -R mysql:mysql /new/data_dir/database/table_name.ibd
- 4、导入表空间
mysql -e "alter table table_name import tablespace;"
脚本实现
以下脚本实现了自动释放表空间和导入旧表空间的功能，并将导入失败的表写入到 ibd_failed_table.log文件中，用以下一步的恢复。需要一个库一个库的恢复。
cat ibd_import.sh
#!/bin/bash # 确保 mysql 连接这里在命令行执行时能获取到表名 mysql_conn="mysql -S /tmp/mysql.sock " # mysql 连接信息 mysqladmin_conn="mysqladmin -S /tmp/mysql.sock ping" # mysqladmin 检测 MySQL 是否正常 mysql_database='test' # 恢复的库名 ibd_save_path=/data1/mysql-5.6.16-winx64/data_old/test # 老的ibd文件保存的目录 recovery_path=/opt/mydata/test # 恢复到的新实例的库路径 # 循环库中所有的表 for table_name in $(${mysql_conn} -N -e "use ${mysql_database};show tables;"|cat);do # 检查 MySQL 连接是否正常 attempt=0 while [ $attempt -lt 3 ]; do if ! ${mysqladmin_conn} >/dev/null 2>&1; then echo "尝试连接 $((${attempt} + 1))次 MySQL 连接消失!!!" attempt=$((${attempt} + 1)) sleep 10 else break fi if [ $attempt -eq 3 ];then exit fi done # 将释放表空间sql写入到文件 echo "alter table ${mysql_database}.${table_name} discard tablespace;" >ibd_tmp.sql # 执行写入 并检查报错 报错时此表跳出循环 if ! ${mysql_conn} ${mysql_database} < ibd_tmp.sql 2>>ibd_err.log ;then # 打印失败的表名 echo "${table_name} 释放表空间失败" echo "${table_name}" >>ibd_failed_table.log # 失败时删除表 ${mysql_conn} -e "use ${mysql_database}; drop table ${table_name};" continue fi # 将旧表 ibd 文件移到新的库中 # mv "${ibd_save_path}"/"${table_name}".ibd "${recovery_path}"/ cp -a "${ibd_save_path}"/"${table_name}".ibd "${recovery_path}"/ # 授权用户 chown -R mysql:mysql "${recovery_path}"/ chmod 660 "${recovery_path}"/"${table_name}".ibd # 将导入表空间sql写入到文件 echo "alter table ${mysql_database}.${table_name} IMPORT tablespace;" >ibd_tmp.sql # 失败时写入到文件并删除表 if ! ${mysql_conn} ${mysql_database} < ibd_tmp.sql 2>>ibd_err.log;then echo "${table_name} 导入表空间失败" echo "${table_name}" >>ibd_failed_table.log ${mysql_conn} -e "use ${mysql_database}; drop table ${table_name};" rm -f "${recovery_path}"/"${table_name}".ibd continue fi echo "${table_name} 导入成功" echo "${table_name}" >>ibd_success_table.log done echo "脚本执行完成！！！"
执行脚本
sh ibd_import.sh # 可以使用 nohup & 后台运行防止中断
这一步可以导入一部分表，剩下的基本都是存在坏页的表，这些表的表名保存在 ibd_failed_table.log 中。
ibd 坏表解析成 SQL
将 ibd_failed_table.log 文件中的表，通过 ibd2sql 工具导出成 SQL。
手动实现
ibd2sql 只能解析 mysql8.* 版本的 ibd 文件，所以需要先启动一个 mysql8 的实例，供 ibd2sql 获取元数据信息。具体操作步骤如下：
- 1、创建 mysql8.0实例
略
- 2、使用 dbsake 指定 frm 文件生成建表语句
dbsake -frmdump /old/data_dir/database/table_name.frm >/tmp/table_name_create_table.sql
- 3、导入建表语句到 mysql 8.0
mysql database < /tmp/table_name_create_table.sql
- 4、使用 ibd2sql 生成建表语句
cd ibd2sql-main && python3 main.py /mysql8/data_dir/database/table_name.ibd --ddl >>/tmp/table_name.sql
- 5、使用 ibd2sql 读取数据页生成 sql 语句
vim ibd2sql.sh
#!/bin/bash mysql5_filename="/mysql5/database/table_name.ibd" # 要解析的老 ibd 文件名 mysql8_filename="/mysql8/database/table_name.ibd" # 要解析的mysql8 ibd 文件名 output_path="/tmp/table_name.sql" python3 main.py ${mysql8_filename} --ddl >> ${output_path} # 获取表结构信息 filesize=`stat -c %s ${mysql5_filename}` maxpagecount=$[ ${filesize} / 16384 ] current_page=1 while [ ${current_page} -le ${maxpagecount} ];do echo "-- ${mysql5_filename} ${maxpagecount} PAGE NO: ${current_page}"; current_page=$[ ${current_page} + 1 ] # 解析数据页 写入到文件 timeout 2 python3 main.py --sdi-table ${mysql8_filename} ${mysql5_filename} --sql --mysql5 --page-start ${current_page} --page-count 1 >> ${output_path} done
- 6、导入到 MySQL
mysql database </tmp/table_name.sql
脚本实现
vim ibd2sql.sh
#!/bin/bash table_name="$1" # 要解析的表名 old_data_path="/data1/mysql-5.6.16-winx64/data_old/test" # 老的数据库路径（保存 ibd 文件的库路径） mysql8_data_path="/mydata/3309/test" # 新的 mysql8 的 路径（保存 ibd 文件的库路径） mysql8_conn="/usr/local/mysql/mysql80/bin/mysql -S /mydata/3309/mysql.sock test " # mysql8 连接信息 output_path=/tmp/table_data # 输出 .sql 文件的路径 filename="${table_name}.ibd" >${output_path}/${table_name}.sql # dbsake 获取表结构 sed 过滤后 导入 mysql8 /usr/local/bin/dbsake frmdump ${old_data_path}/${table_name}.frm |sed 's#datetime DEFAULT NULL#datetime null DEFAULT NULL#g'｜sed 's#timestamp DEFAULT NULL#datetime null DEFAULT NULL#g' ${mysql8_conn} if [ $? -ne 0 ];then echo "MySQL 表结构导入失败" exit fi sleep 1 # 指定 mysql8 的 ibd 文件生成建表语句 python3 main.py ${mysql8_data_path}/${filename} --ddl >>${output_path}/${table_name}.sql # 计算文件大小 filesize=`stat -c %s ${old_data_path}/${filename}` # 获取数据页数量 maxpagecount=$[ ${filesize} / 16384 ] current_page=1 # 循环数据页 while [ ${current_page} -le ${maxpagecount} ];do echo "-- ${filename} ${maxpagecount} PAGE NO: ${current_page}" >>ibd_progress_${table_name}.log; current_page=$[ ${current_page} + 1 ] # 指定生成回滚 sql timeout 2s python3 main.py --sdi-table ${mysql8_data_path}/${filename} ${old_data_path}/${filename} --sql --mysql5 --page-start ${current_page} --page-count 1 >>${output_path}/${table_name}.sql done
# 执行 sh ibd2sql.sh.sh table_name
并行执行
# 循环读取ibd_failed_table.log中的表名 parallel -j 7 ./ibd2sql.sh :::: /root/test/ibd_failed_table.log
# 后台运行 nohup parallel -j 7 ./test.sh :::: /root/test/ibd_failed_table.log &
# 杀死进程 停止运行 ps -ef | egrep 'python3 main.py|ibd2sql.sh|parallel' | grep -v grep | awk '{print $2}' | xargs -r kill -9
查看进度
# 查看进展文件 对比总数据页和当前数据页 tail -f ibd_progress_${table_name}.log ==> /root/ibd2sql-main/ibd_progress_t_sales_order_detail_v.log <== -- t_sales_order_detail_v.ibd 449536 PAGE NO: 267213 # 对比页数即可 -- t_sales_order_detail_v.ibd 449536 PAGE NO: 267214
导入表到mysql5 的新实例中
# 登录 mysql mysql use database # 导入数据 source /tmp/table_name.sql
这里如果使用
mysql < sql_file的方式导入有可能会报错停止。
恢复 MyISAM 表
# 进入旧实例待修复的库中 # 获取 MyISAM 表 并拷贝到数据目录 ll $(ll *.MYD | awk '{print $NF}' | awk -F. '{print $1".*"}')|awk '{print $NF}'|xargs -I {} cp -a {} /mydata/3308/test/ # 检查并修复损坏的表 mysqlcheck -u root -p --all-databases --auto-repair # 生成 SQL 查看 MyISAM 表行数 ll *.MYD | awk '{print $NF}' |awk -F. '{print "select count(*) from "$1";"}'
恢复视图
导入视图
# 进入旧数据目录 # 导出视图到 mysql5 的新实例 dbsake frmdump *.frm |grep -B 7 'CREATE ALGORITHM=' |mysql
恢复系统表
# 创建恢复库 create database mysql_bak; # 将表文件复制到库中 cp -a ./mysql/* /new_data_dir/mysql_bak/ # 授权 chown -R mysql:mysql /new_data_dir/mysql_bak/
user 表
# 将数据导出 SELECT * INTO OUTFILE '/tmp/user_data.sql' FROM mysql_bak.user; # 删除不想导入的用户 vim /tmp/user_data.sql # 导入到系统表 LOAD DATA INFILE '/tmp/user_data.sql' INTO TABLE mysql.user;
proc 表
# 将数据导出 SELECT * INTO OUTFILE '/tmp/proc_data.sql' FROM mysql_bak.proc; # 导入到系统表 LOAD DATA INFILE '/tmp/proc_data.sql' INTO TABLE mysql.proc;
其他表
略 都一样
检查并备份
以上步骤完成后检查所有的表，并备份整个实例，然后新建一个实例恢复进去，或者执行 mysql_upgrade 命令。
检查并修复 mysql5 新实例的表
mysqlcheck -u root -p --all-databases --auto-repair
利用 mysql5 的 mysql_upgrade 命令可以修复系统表的一些错误(非必选)
mysql_upgrade -u root -p
全备
mysqldump -A -R -E --triggers --master-data=2 --single-transaction >all.sql
本文章仅供参考，请验证后使用。
