【mysql数据损坏,通过ibd和frm文件批量恢复数据库数据】

问题描述

客户那边的测试服务器下午3:00左右因不明原因,所有服务访问不了,ssh也无法连接,客户手动进行强制重启服务器,重启后将所有应用、中间件重启,重启nacos时发现无法连接数据库,通过账号密码登录到数据库发现可以正常登录,但是奇怪的问题来了。。。show tables 时可以正常看到表,但是查询表中数据时发现提示表不存在。。如下图:
在这里插入图片描述

查看mysql日志:
在这里插入图片描述

作为一名mysql菜的不能再菜的小白。。这属实难到我了,看起来像是数据损坏。。没办法,通过多方查询资料,解决办法如下:

解决办法:

首先了解InnoDB引擎创建数据库会产生的文件:

  • .ibd:包含每个表的数据和索引
  • .frm:描述表的结构
  • ibdata1:包含所有表的字典和历史操作

在这里插入图片描述

在test目录下,包含每个表的.ibd和.frm文件
在这里插入图片描述

恢复前提:ibd文件的数据都是正常的
之后也是需要通过这些文件进行恢复,我这边新启了一个mysql实例,过程略,
大体的恢复步骤如下:

  1. 在新的mysql实例中创建数据库,需要跟原数据库的库名和字符集保持一致
  2. 创建数据表,表名要求一致,对应原库data目录下的表名(我这里用工具对原库表的表结构导出创建的)
  3. 通过.ibd文件批量恢复表数据信息
    3.1 让表结构和表空间脱离
    3.2 将需要恢复的.ibd替换到新实例的/data的对应库下

1、安装mysqlfrm 工具

下载链接:

wget https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5.tar.gz
tar -xvf mysql-utilities-1.6.5.tar.gz
cd mysql-utilities-1.6.5/
安装(需要Python环境,python2.6及以上)
python ./setup.py build
python ./setup.py install
检查是否安装成功
mysqlfrm --version

2、使用mysqlfrm查看表结构创建语句

mysqlfrm 有–basedir模式以及–server 模式,推荐–server,能够还原最为准确的信息

使用方式(需要启动原mysql):

格式:mysqlfrm --server=用户名:密码@数据库地址:端口 需恢复的frm文件目录 --diagnostic > 保存SQL文件名.sql

mysqlfrm --server=root:[email protected]:13306 /home/mysql/data/test/*.frm --diagnostic > test_frm.sql

在这里插入图片描述
执行所生成的SQL文件
注:mysqlfrm仅仅显示整个数据库的表结构创建语句,可以显示在控制台也可以输出到文件,不过需要手动执行这些表的创建SQL语句。
先创建数据库

CREATE database  test;

可以查看生成的sql文件手动删除相关无用信息等,可命令行执行或者客户端执行等
命令行导入:

mysql -uroot -ppassword test < test_frm.sql

此时执行完就可以通过desc命令查看到表结构了(报错1:这步我遇到报错,下面有解决方法)

3、通过.ibd文件批量恢复表数据信息

1)让表结构和表空间脱离
生成表结构和表空间脱离的sql:

mysql -uroot -ppassword -h x.x.x.x  -P 13306 -e " SELECT concat('alter table ', table_name, ' discard tablespace;') FROM information_schema.tables WHERE table_schema = 'test';" > /root/test.sql

将sql文件中没有用的内容删除

sed '/^c/d' /root/test.sql> /root/test_discard_new.sql

将sql文件导入到新库

mysql -uroot -ppassword -h x.x.x.x -P 13307 test_AII < test_discard_new.sql

2)将需要恢复的.ibd文件替换到新库所在的/data/库名 目录下

cp /home/mysql/data/test/*.ibd  /home/mysql-test/data/test/

3)权限设置

chown -R mysql.  /home/mysql-test/data/test

4)导入表空间

mysql -uroot -ppassword -h x.x.x.x -P 13306 -e " SELECT concat('alter table ', table_name, ' import tablespace;') FROM information_schema.tables WHERE table_schema = 'test';" > /root/tem_import.sql

清除没用的数据

sed '/^c/d' /root/tem_import.sql > /root/config_import.sql

将sql文件导入(报错2:有报错,下面有解决方法)

mysql -uroot -ppassword -h x.x.x.x -P 13307 test_aii < /root/config_import.sql

此时如果操作没问题,查看数据库表已经有了原始数据了(报错3:有报错,查看下面解决方法)

恢复过程中遇到的问题:

报错1、在导入创建表结构语句的时候报错了,显示语法错误我的创建表语句如下:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5, COMMENT 'config_info'' at line 21, Time: 0.011000s

建表语句:

CREATE TABLE `test_config`.`config_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT comment 'id', 
  `data_id` varchar(255) COLLATE `utf8_bin` NOT NULL comment 'data_id', 
  `group_id` varchar(255) COLLATE `utf8_bin` DEFAULT NULL, 
  `content` longtext COLLATE `utf8_bin` comment 'content', 
  `md5` varchar(32) COLLATE `utf8_bin` DEFAULT NULL comment 'md5', 
  `gmt_create` datetime NOT NULL comment '创建时间', 
  `gmt_modified` datetime NOT NULL comment '修改时间', 
  `src_user` text COLLATE `utf8_bin` DEFAULT NULL comment 'source user', 
  `src_ip` varchar(50) COLLATE `utf8_bin` DEFAULT NULL comment 'source ip', 
  `app_name` varchar(128) COLLATE `utf8_bin` DEFAULT NULL, 
  `tenant_id` varchar(128) COLLATE `utf8_bin` DEFAULT NULL comment '租户字段', 
  `c_desc` varchar(256) COLLATE `utf8_bin` DEFAULT NULL, 
  `c_use` varchar(64) COLLATE `utf8_bin` DEFAULT NULL, 
  `effect` varchar(64) COLLATE `utf8_bin` DEFAULT NULL, 
  `type` varchar(64) COLLATE `utf8_bin` DEFAULT NULL, 
  `c_schema` text COLLATE `utf8_bin` DEFAULT NULL, 
  `encrypted_data_key` text COLLATE `utf8_bin` comment '秘钥', 
PRIMARY KEY `PRIMARY` (`id`) USING BTREE,
UNIQUE KEY `uk_configinfo_datagrouptenant` (`data_id`(255),`group_id`(255),`tenant_id`(128)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8, ROW_FORMAT = 5, COMMENT 'config_info';

解决方法:
将ROW_FORMAT 改为COMPACT或DYNAMIC就可以解决

报错2:导入表空间时,报错

ERROR 1812 (HY000): Tablespace is missing for table ops.tt7.

解决方法:
查看从原库/data/目录copy的.ibd文件的属主和属组是否是mysql的启动用户,不是则修改即可

报错3:所有步骤执行完成后,重启mysql时启动失败,查看mysql日志如下报错:

[InnoDB] [FATAL] Tablespace id is xx in the data dictionary but in file .testconfig.ibd it is xx!

这个错误意思就是库名表名一致,但是表空间id已经不同了,说明上面的执行步骤有错误,导致表空间id记录在两个地方,一个在系统表空间中的字典表,一个记录在表对应的ibd文件里。这个时候,不管出于何种原因,ibd文件使用了旧文件,就会报这个错误。并且导致mysqld进程崩溃退出。

解决方法:
将对应的库中的某个表删除,并重新生成表结构并创建表,然后重新将原表结构和表空间脱离并重新导入表空间,操作上面都有,重新执行即可

本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
THE END
分享
二维码
< <上一篇
下一篇>>