首页 > 数据库 >【MySQL核心】数据恢复-批量修复 ibd 文件实战-拯救即将跑路的你

【MySQL核心】数据恢复-批量修复 ibd 文件实战-拯救即将跑路的你

时间：2024-08-12 16:51:50浏览次数：14  
标签：数据恢复 name -- MySQL ibd sql mysql table

简介

本文章主要讲解 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

全备

mysqldumpe -A -R -E --triggers --master-data=2  --single-transaction >all.sql

本文章仅供参考，请验证后使用。

标签：数据恢复,name,--,MySQL,ibd,sql,mysql,table
From： https://blog.csdn.net/weixin_45385457/article/details/141136121

相关文章

  • MySQL
    1、初识MySQLJavaEE：企业级Java开发Web前端（页面：展示：数据）后台（连接点：连接数据库JDBC,连接前端（控制视图跳转，给前端传递数据））数据库（存数据，Txt,Excel,Word）只会写代码，学好数据库，基本混饭吃：操作系统，数据结构与算法！当一个不错的程序猿！离散数学，数字电路，体系结构，编译原理。+实战经验......
  • JDBC加载MySQL驱动【底层实现】
    JDBC4.0如何加载引入依赖<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.32</version></dependency>上代码importjava.sql.Connection;impor......
  • 基于SpringBoot+MySQL+SSM+Vue.js的物业管理系统(附论文)
    获取见最下方名片信息获取见最下方名片信息获取见最下方名片信息演示视频基于SpringBoot+MySQL+SSM+Vue.js的物业管理系统(附论文)技术描述开发工具：Idea/Eclipse数据库：MySQLJar包仓库：Maven前端框架：Vue/ElementUI后端框架：Spring+SpringMVC+Mybatis+Sprin......
  • 基于SpringBoot+MySQL+SSM+Vue.js的大学生兼职系统(附论文)
    获取见最下方名片信息获取见最下方名片信息获取见最下方名片信息演示视频基于SpringBoot+MySQL+SSM+Vue.js的大学生兼职系统(附论文)技术描述开发工具：Idea/Eclipse数据库：MySQLJar包仓库：Maven前端框架：Vue/ElementUI后端框架：Spring+SpringMVC+Mybatis+Spr......
  • 基于SpringBoot+MySQL+SSM+Vue.js的线上教育培训办公系统(附论文)
    获取见最下方名片信息获取见最下方名片信息获取见最下方名片信息演示视频基于SpringBoot+MySQL+SSM+Vue.js的线上教育培训办公系统(附论文)技术描述开发工具：Idea/Eclipse数据库：MySQLJar包仓库：Maven前端框架：Vue/ElementUI后端框架：Spring+SpringMVC+Mybat......
  • mysql 计算两个日期的时间差（年龄）
    TIMESTAMPDIFF(时间粒度,开始时间,结束时间)1、时间粒度：秒：SECOND分：MINUTE时：HOUR天：DAY周：WEEK季度：QUARTER月：MONTH年：YEAR2、计算相差的时间粒度数，实例：秒数：SELECTTIMESTAMPDIFF(SECOND,'2000-06-1500:00:00',DATE_FORMAT(NOW(),'%Y-%m-%d%H:%i:%S'))小时数SELE......
  • 通过Shell脚本创建指定ip上的MySQL非系统库的所有表的表结构
    dbaloginSource='/data/dba/mysql8/bin/mysql-uroot_dba-ppassword-hxxx'dbaloginTarget='mysql-h127.0.0.1-P3340-uroot-proot'#showdatabases过滤出数据库名dbs=`$dbaloginSource-e"showdatabases\G"2>/dev/null|grep......
  • MySQL——删除数据（二）DELETE 删除全部数据
            在DELETE语句中如果没有使用WHERE子句，则会将表中的所有记录都删除。        例如，删除student表中的所有记录，在删除数据之前首先使用查询语句查看student表中的所有记录，执行结果如下所示:mysql>select*fromstudent;+------+---------+---......
  • MySQL——删除数据（一）DELETE 删除部分数据
            删除数据是指对表中存在的记录进行删除，这是数据库的常见操作，比如一个学生转学了，就需要在student表中将其信息记录删除。MySQL中使用DELETE语句来删除表中的记录，其语法格式如下所示:DELETEFROM表名[WHERE条件表达式]    在上面的语法格式中，......
  • 【MYSQL】表的设计
    目录数据库约束notnull约束unique（唯一）约束default默认值约束primarykey（主键）约束foreignkey（外键）约束check约束总结表的设计三大范式第一范式第二范式第三范式表的关系数据库约束是关系型数据库的一个重要功能，主要作用是保证数据的完整性，也可能理解数据......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99