通过frm&ibd 恢复 Mysql ibdata 丢失或损坏的数据教程

       
有时候mysql没有做好数据备份,或者被数据管理员误删,或者ibdata损坏了我们如何恢复呢?别怕,只要有部分frm、ibd存在,下面就是恢复教程。

  mysql存储在磁盘中,各种天灾人祸都会导致数据丢失。大公司的时候我们常常需要做好数据冷热备,对于小公司来说要做好所有数据备份需要支出大量的成本,很多公司也是不现实的。万一还没有做好备份,数据被误删除了,或者ibdata损坏了怎么办呢?别担心,只要有部分的frm、ibd存在就可以恢复部分数据。

  注意:

  一、这个是对innodb的数据恢复。myisam不需要这么麻烦,只要数据文件存在直接复制过去就可以。

  二、大家的mysql数据库必须是按表存放数据的,默认不是,但是大家生产肯定是按分表设置的吧,如果不是,不好意思,这个方法不能恢复你的数据。my.ini的设置为

  innodb_file_per_table = 1。

  1、找回表结构,如果表结构没有丢失直接到下一步

  a、先创建一个数据库,这个数据库必须是没有表和任何操作的。

  b、创建一个表结构,和要恢复的表名是一样的。表里的字段无所谓。一定要是innodb引擎的。CREATE
TABLE `weibo_qq0`( `weiboid` bigint(20)) ENGINE=InnoDB DEFAULT
CHARSET=utf8;

  c、关闭mysql, service mysqld stop;

  d、用需要恢复的frm文件覆盖刚新建的frm文件;

  e、修改my.ini 里 innodb_force_recovery=1 , 如果不成修改为
2,3,4,5,6。

  f、 启动mysql,service mysqld start;show create table weibo_qq0
就能li到表结构信息了。

  2、找回数据。记得上面把
innodb_force_recovery改掉了,需要注释掉,不然恢复模式不好操作。
这里有个关键的问题,就是innodb里的任何数据操作都是一个日志的记录点。也就是如果我们需要数据恢复,必须把之前的表的数据的日志记录点添加到一致。

  a、建立一个数据库,根据上面导出的创建表的sql执行创建表。

  b、找到记录点。先要把当前数据库的表空间废弃掉,使当前ibd的数据文件和frm分离。
ALTER TABLE weibo_qq0 DISCARD TABLESPACE;

  c、把之前要恢复的 .ibd文件复制到新的表结构文件夹下。 使当前的ibd
和frm发生关系。ALTER TABLE weibo_qq0 IMPORT TABLESPACE;
结果不出意外肯定会报错。就和我们开展数据开始说的那样,数据记录点不一致。我们看看之前ibd记录的点在什么位置。开始执行
import tablespace,报错 ERROR 1030 (HY000): Got error -1 from storage
engine。找到mysql的错误日志,InnoDB: Error: tablespace id in file
‘.testweibo_qq0.ibd’ is 112, but in the InnoDB InnoDB: data dictionary
it is 1. 因为 weibo_qq0
之前的记录点在112,当前的表只创建一次,所以记录点是1.

  d、那怎么从1记录到112。for ($1=1; $i<=111; $1++) {CREATE TABLE
t# (id int) ENGINE=InnoDB;}
也许很奇怪,为什么是循环111,不是112。因为在a执行创建表结构的时候已经记录增加了一次。

  e、修改表结构 alter table weibo_qq0 discard
tablespace;使当前的表结构和ibd脱离关系。复制.ibd到当前的目录结构。

  f、使原来数据的ibd和当前frm建立关系。 ALTER TABLE product IMPORT
TABLESPACE;
这个时候没有错误,说明已经建立好了。但是查询数据还是查不出来。

  g、相比这里大家已经知道为什么了,这个模式也不是说改了数据库就可以在生产环境使用。更改
innodb_force_recovery=1 , 如果不成修改为 2,3,4,5,6。直到可以
查询出数据为止,然后dump出来。数据就备份出来了。

  h、把所有数据导出后,在新的数据库导入。所有数据就生成了。

  扩展问题,很多时候我们是分表表结构怎么批量操作,提高速度呢。用循环!循环把表的空间废弃掉。

  for i in `seq 0 111`; do mysql -uroot -P33061 -h127.0.0.1 -Dtestdd
-e “CREATE TABLE inv_crawl_weibo_qq$i (id bigint(20) NOT NULL
AUTO_INCREMENT,PRIMARY KEY (id)) ENGINE=innodb “; done

  ALTER TABLE inv_crawl_weibo_qq0 DISCARD TABLESPACE;

  从备份数据把ibd复制cp到dd数据库下,注意复制过来的文件权限。

  循环导入表空间。

  ALTER TABLE inv_crawl_weibo_qq0 IMPORT TABLESPACE;

  没有报错就导入成功了

;
有时候mysql没有做好数据备份,或者被数据管理员误删,或者ibdata损坏了我们如何恢复呢?别怕,只要有部分frm、ibd存在,下面就…

[TOC]

上周,以前公司的同事朋友找我帮忙,看看能否帮忙恢复一个MySQL
数据库,具体情况为:数据库版本为MySQL
5.6(具体版本不清楚),也不清楚具体的数据库引擎;
没有数据库备份,只剩下数据库下面的一些文件(frm、idb),具体原因是因为出现问题的时候,重装了MySQL,最要命的是ibdata1等文件也没有了,当然这中间细节过程如何,不清楚也不用去纠结了。大概就是这么一个情况。

InnoDB 存储引擎

 

1. InnoDB的历史

年份 事件 备注
1995 由Heikki Tuuri 创建Innobase Oy公司,并开发InnoDB存储引擎 Innobase开始做的是数据库,希望卖掉该公司
1996 MySQL 1.0 发布
2000 MySQL3.23版本发布
2001 InnoDB存储引擎集成到MySQL数据库 作为插件的方式集成
2006 Innobase被Oracle公司收购(InnoDB作为开源产品,性能和功能很强大) InnoDB在被收购后的,MySQL中的InnoDB版本没有改变
2010 MySQL5.5版本InnoDB存储引擎称为默认存储引擎 MySQL被Sun收购,Oracle被Oracle收购,使得MySQL和InnoDB重新在一起配合开发
至今 其他存储引擎已经不再得到Oracle官方的后续开发

 

2. InnoDB的特点

• Fully ACID (InnoDB默认的Repeat Read隔离级别就支持)
• Row-level Locking(支持行锁)
• Multi-version concurrency control(MVCC)(支持多版本并发控制)
• Foreign key support(支持外键)
• Automatic deadlock detection(死锁自动检测)
• High performance、High scalability、High availability(高性能,高扩展,高可用)

因为数据库不大,将对应的文件拷贝到自己一台测试服务器的MySQL数据文件目录下后(下面实验测试,对数据库名等敏感信息做了一下混淆),如下所示,数据库名为test,show
tables可以看到相关的表。

3. InnoDB存储引擎的文件

 

3.1 概述

InnoDB的文件主要分为两个部分,一个是表空间文件,一个是重做日志文件

  • 表空间文件
    • 独立表空间文件
    • 全局表空间文件
    • undo表空间文件(from MySQL5.6)
  • 重做日志文件
    • 物理逻辑日志
    • 没有Oracle的归档重做日志

 

3.2 InnoDB – 表空间

  • 表空间的概念
    • 表空间是一个 逻辑存储 的概念
    • 表空间可以由多个文件菲律宾太阳娱乐集团,组成
    • 支持裸设备(可以直接使用 O_DIRECT方式绕过缓存,直接写入磁盘)
  • 表空间的分类
    • 系统表空间 (最早只有系统表空间)
    • 存储元数据信息
    • 存储Change Buffer信息
    • 存储Undo信息
    • 甚至一开始 所有的表和索引 的信息都是存储在系统表空间
      • 随后InnoDB对其做了改进,可以使用独立的表空间
  • 独立表空间
    • innodb-file-per-table=1 (开启支持每个表一个独立的表空间)
    • 每张用户表对应一个独立的 ibd文件
    • 分区表可以对应多个ibd文件
  • Undo表空间
    • MySQL5.6版本支持独立Undo表空间
    • innodb_undo_tablespaces
  • 临时表空间

    • MySQL5.7增加了临时表空间(ibtmp1)
    • innodb_temp_data_file_path

    shell> ll ib # MySQL的datadir目录
    -rw-r—–. 1 mysql mysql 22913 Dec 27 23:56 ib_buffer_pool
    -rw-r—–. 1 mysql mysql 12582912 Jan 3 15:27 ibdata1 # 系统表空间,默认所有信息存在这里
    -rw-r—–. 1 mysql mysql 134217728 Jan 3 15:27 ib_logfile0
    -rw-r—–. 1 mysql mysql 134217728 Jan 3 15:27 ib_logfile1
    -rw-r—–. 1 mysql mysql 12582912 Jan 3 15:27 ibtmp1 # 临时表空间
    shell> cd burn_test # 在MySQL的datadir目录下,burn_test是自定义数据库,根据配置,默认为innodb的表
    shell> ll test_1

    -rw-r—–. 1 mysql mysql 8554 Dec 3 20:14 test_1.frm #test_1表的表结构文件
    #mysqlfrm –diagnostic test_1.frm可查看表结构
    -rw-r—–. 1 mysql mysql 49152 Dec 3 20:14 test_1.ibd #ibd就是test_1这张表对应的innodb文件
    #ibd中包含了索引和数据

  • 同一个表空间(ibdata1) 存储和 独立表空间 存储就 性能
    上而言没有区别;

  • 当需要删除表(drop table)时, 独立的表空间 存储可以直接删除文件
    ,而 ibdata1 存储也只是把 该部分表空间标记为可用
    ,所以从速度上看很难说哪个更快;但是 删除文件 后, ibdata1 占用的
    空间不会释放
  • 分区表 会产生 独立 的 ibd文件 ;
  • 独立的表空间 ,一个表对应一个 ibd文件 ,给人的感觉更加直观;
  • 单个 ibd文件 直接拷贝到新的数据库中无法直接恢复:
  • 原因一: 元数据 信息还是在 ibdata1
  • 原因二:部分索引文件存在于 Change Buffer 中,目前还是存放于
    ibdata1文件中

    select * from information_schema.innodb_sys_tablespaces; — 查看表空间的元数据信息

菲律宾太阳娱乐集团 1

3.3 General表空间

官方文档

假如,新建一张表,并让该表的存储路径不是默认的/r2/mysqldata 。而是
指定存储的位置 应该如何处理?

  • 方法一

    shell> mkdir /GeneralTest
    shell> chown mysql.mysql /GeneralTest
    mysql> create table test_ger1 (a int) data directory=’/GeneralTest’;
    Query OK, 0 rows affected (0.15 sec)
    shell> cd /GeneralTest
    shell> tree
    .
    └── burn_test # dbname
    └── test_ger1.ibd # 表空间文件
    1 directory, 1 file
    shell> ll test_ger1* # 在datadir 的 burn_test 目录下
    -rw-r—–. 1 mysql mysql 8554 Jan 3 16:41 test_ger1.frm
    -rw-r—–. 1 mysql mysql 36 Jan 3 16:41 test_ger1.isl # 这是链接文件,链接到上面的ibd文件
    shell> cat test_ger1.isl # 一个文本文件,内容就是idb文件的路径
    /GeneralTest/burn_test/test_ger1.ibd

  • 方法二

  • 使用通用表空间
  • 1: 创建一个通用表空间

    mysql> create tablespace ger_space add datafile ‘/r2/testdir/ger_space.ibd’ file_block_size=8192;
    Query OK, 0 rows affected (0.07 sec)

    — datafile 指定存储路径后,在datadir下会产生一个isl文件,该文件的内容为General space的ibd文件的路径
    — 如果datafile不指定路径,则ibd文件默认存储在datadir目录下,且不需要isl文件了

    mysql> create tablespace ger2_space add datafile ‘ger2_space.ibd’ file_block_size=8192;
    Query OK, 0 rows affected (0.06 sec)
    shell> ll ger*
    -rw-r—–. 1 mysql mysql 32768 Jan 3 16:51 ger2_space.ibd # 未指定路径,存放于datadir目录
    -rw-r—–. 1 mysql mysql 26 Jan 3 16:50 ger_space.isl # 指定了其他路径,存在isl链接文件

    shell> cat ger_space.isl
    /r2/testdir/ger_space.ibd # ibd文件真实存在的路径
    mysql> select from information_schema.innodb_sys_tablespaces where name=’ger_space’G;
    **
    1. row ***

         SPACE: 55
          NAME: ger_space
          FLAG: 2089
    

    FILE_FORMAT: Barracuda

    ROW_FORMAT: Compressed
     PAGE_SIZE: 16384  -- page_size是16k
    

    ZIP_PAGE_SIZE: 8192

    SPACE_TYPE: General  --General类型
    

    FS_BLOCK_SIZE: 4096

     FILE_SIZE: 65536
    

    ALLOCATED_SIZE: 65536
    1 row in set (0.00 sec)

  • 2: 创建表

    mysql> create table test_ger2 (a int) tablespace=ger_space;
    ERROR 1478 (HY000): InnoDB: Tablespace ger_space uses block size 8192 and cannot contain a table with physical page size 16384

    –FILE_BLOCK_SIZE默认为innodb page size的大小(16kb),当你指定一个值时,例如8192,意味着你需要使用这个表空间来存储压缩表。否则就会报错:

    mysql> create table test_ger2 (a int) ROW_FORMAT=COMPRESSED tablespace=ger_space;
    Query OK, 0 rows affected (0.02 sec)

    shell> ll test_ger* # 在/r2/mysqldata/employees目录下
    -rw-r—–. 1 mysql mysql 8554 Jan 3 17:09 test_ger2.frm # 仅有一个frm文件
    shell> /r2/testdir/
    total 52
    drwxr-x—. 2 mysql mysql 4096 Jan 3 16:41 burn_test
    -rw-r—–. 1 mysql mysql 49152 Jan 3 17:09 ger_space.ibd # test_ger2的ibd文件其实存储在ger_space.ibd的通用表空间中
    mysql> create table test_ger3 (a int) ROW_FORMAT=COMPRESSED tablespace=ger_space; — test_ger3 也存放在ger_space.ibd中
    Query OK, 0 rows affected (0.09 sec)

  • 通过使用 General Space ,一个表空间可以对应多张表

  • 当对表进行alter等操作时,还是和原来一样,无需额外语法指定表空间位置
  • 可以简单的理解为把多个表的ibd文件合并在一起了

    mysql> create tablespace ger3_space add datafile ‘/r2/testdir/ger3_space.ibd’ file_block_size=4096; — 创建4K大小的General Space也是可以的
    Query OK, 0 rows affected (0.06 sec)
    — 但是注意,如果设置了innodb_page_size,且大小不是file_block_size,那么在创建表的时候会报错

    mysql> create table test_ger (a int) tablespace=ger3_space;
    ERROR 1478 (HY000): InnoDB: Tablespace ger3_space uses block size 4096 and cannot contain a table with physical page size 8192

    • 既然无法创建表,那应该在创建general space时就应该报错啊?
    • 后续涉及压缩表时可以使用
    • 这里的 file_block_size 就是 page_size
    • 注意:需要考虑在使用General Space后,备份工具是否能够支持

 

3.4 Undo表空间文件

  • innodb_undo_tablespaces = 3
  • 创建3个undo表空间

    shell> ll undo*
    -rw-r —–. 1 mysql mysql 7340032 Jan 3 17:21 undo001
    -rw-r —–. 1 mysql mysql 7340032 Jan 3 17:40 undo002
    -rw-r —–. 1 mysql mysql 7340032 Jan 3 17:21 undo003

 

3.5 重做日志文件

  • innodb_log_file_size
  • 该文件设置的尽可能的大 ,模板中给出的大小是 4G
  • 设置太小可能会导致脏页刷新 时hang住

    [root@localhost-m(252) /r2/mysqldata]# ll ib_logfile*
    -rw-r—– 1 mysql mysql 2147483648 1月 4 11:32 ib_logfile0
    -rw-r—– 1 mysql mysql 2147483648 1月 4 11:32 ib_logfile1

其中有几张表的存储引擎为MyISAM,那么这些表的数据是完全可以恢复的,但是大部分表的存储引擎为InnoDB,访问表或查看表都会提示“ERROR
1146 (42S02): Table ‘xxxx’ doesn’t exist 不存在。

 

mysql> desc think_cache;

ERROR 1146 (42S02): Table 'test.think_cache' doesn't exist

mysql> show create table think_cache;

ERROR 1146 (42S02): Table 'test.think_cache' doesn't exist

mysql> 

 

由于共享表空间的ibdata1数据文件不存在了,加之有没有备份,所以我武断的判断这个数据库真的无法恢复了,但是过后一天,这个朋友跟我说找了一家数据恢复公司将这个数据库恢复了
听到这个消息颇有点学艺不精的尴尬(其实谈不上尴尬吧,本来还在学习MySQL的路上,有些知识点不清楚也很正常。经验是需要慢慢积累的),不过更多的是好奇别人是如何恢复数据的,既然别人能够恢复,那么自己下一次遇到这种情况也要能搞定。下面就来复盘一下别人是如何恢复数据的(其实只要稍稍做点功课,发现这个其实挺简单的)

 

首先,我们来了解一下MySQL
表空间数据文件idbdat1文件相关概念和知识点:

 

   
InnoDB采用按表空间(tablespace)的方式进行存储数据,
默认配置情况下会有一个初始大小为10MB, 名字为ibdata1的文件,
该文件就是默认的表空间文件(tablespce
file),用户可以通过参数innodb_data_file_path对其进行设置,可以有多个数据文件,如果没有设置innodb_file_per_table的话,
那些Innodb存储类型的表的数据都放在这个共享表空间中,而系统变量innodb_file_per_table=1的话,那么InnoDB存储引擎类型的表就会产生一个独立表空间,独立表空间的命名规则为:表名.idb.
这些单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息,其它信息还是存放在共享表空间中。

 

   
其实当时主要是对这个概念有点模糊了,以为这个系统变量innodb_file_per_table默认是关闭的,数据都会存储在共享表空间中,那么这些文件删除了,数据就无法恢复。所以武断的下结论,其实从MySQL
5.6.6开始,
系统变量innodb_file_per_table默认是启用的。只要再多了解一点或者说更深入了解一点的话,情况就会立马就会反转。也就是说如果开启了独立表空间,可从ibd文件中恢复数据。即使共享表空间的数据文件idbdata1丢失也不要紧,反之,如果未开启独立表空间时,idbdat1被删除了,数据也会被删除,只能从备份中恢复,真的没有其他办法。

 

 

那么我们接下来看看,如何从idb文件中恢复数据吧,我们需要用到mysqlfrm工具,
需要安装MySQL Utilities,下面是安装MySQL Utilities 1.5.5

 

#
tar -xvf mysql-utilities-1.5.5.tar.gz

#
cd mysql-utilities-1.5.5

#
python ./setup.py build

#
python ./setup.py install

 

 

提取frm文件的表结构信息

 

mysqlfrm
是一个恢复性质的工具,用来读取.frm文件并从该文件中找到表定义数据生成CREATE语句。此处不对mysqlfrm工具做过多介绍,我们使用msqlfrm来生成该数据库的表的CREATE语句

 

[root@DB-Server ~]# service mysql stop

Shutting down MySQL.... SUCCESS! 

[root@DB-Server ~]# /usr/local/bin/mysqlfrm --basedir=/usr --port=3306 --user=root /data/mysql/test/ > test_frm.sql

[root@DB-Server ~]# 

 

检查导出的SQL语句,发现都是“ERROR:
The server version for this file is too low. It requires a server
version 5.6.29 or higher but your server is version 5.6.20. Try using a
newer server or use diagnostic mode”这类错误:

 

[root@DB-Server ~]# more test_frm.sql 

# Spawning server with --user=root.

# Starting the spawned server on port 3306 ... done.

# Reading .frm files

#

# Reading the think_cache.frm file.

ERROR: The server version for this file is too low. It requires a server version 5.6.29 or higher but your server is version 5.6.20. Try using a newer server or use diagnostic mode.

#

# Reading the think_session.frm file.

ERROR: The server version for this file is too low. It requires a server version 5.6.29 or higher but your server is version 5.6.20. Try using a newer server or use diagnostic mode.

#

# Reading the wx_activity_config.frm file.

ERROR: The server version for this file is too low. It requires a server version 5.6.29 or higher but your server is version 5.6.20. Try using a newer server or use diagnostic mode.

#

........................................................................................

 

从中可以看到这个数据库之前的版本为MySQL为5.6.29,而我这里的MySQL版本比这个低(MySQL 5.6.20)。所以必须找一个跟这个版本相同或高的MySQL数据库操作才行。于是在另外一台测试服务器安装了MySQL

 

[root@gettestlnx02 ~]# service mysqld stop

 

Stopping mysqld:  [  OK  ]

 

[root@gettestlnx02 tmp]# mv test  /data/mysqldata/mysql/test

 

[root@gettestlnx02 tmp]# cd /data/mysqldata/mysql/

 

/usr/bin/mysqlfrm
–basedir=/usr –port=3306 –user=root /data/mysqldata/mysql/test/ >
test_frm.sql

 

如何要查看输出信息,可以使用参数-vvv

 

/usr/bin/mysqlfrm
–basedir=/usr –port=3306 –user=root -vvv /data/mysqldata/mysql/test/
> test_frm.sql

发表评论

电子邮件地址不会被公开。 必填项已用*标注