MySQL【澳门新葡亰赌995577】 Innodb数据库误删ibdata1后MySQL数据库的恢复案例

澳门新葡亰赌995577 3

 

通过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;

  没有报错就导入成功了

http://www.bkjia.com/Mysql/955816.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/955816.htmlTechArticle通过frmnbsp;
有时候mysql没有做好数据备份,或者被数据管理员误删,或者ibdata损坏了我们如何恢复呢?别怕,只要有部分frm、ibd存在,下面就…

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

 

 

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

 

 

澳门新葡亰赌995577 1

 

 

其中有几张表的存储引擎为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

 

澳门新葡亰赌995577 2

 

生成的SQL脚本没有以分号结尾,本来想用sed命令给那些CREATE
TABLE脚本加上分号结尾,但是发现其中大量CREATE
TABLE的脚本结尾没有规律,都是以COMMNET=’xxxxx’结尾,只能手工添加分号(如下所示)

 

 

澳门新葡亰赌995577 3

 

 

导入frm文件的表结构信息

 

mysql> use test;

Database changed

mysql> source test_frm.sql

Query OK, 0 rows affected (0.02 sec)

 

Query OK, 0 rows affected (0.01 sec)

 

Query OK, 0 rows affected (0.01 sec)

 

Query OK, 0 rows affected (0.00 sec)

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

 

然后我们检查这个数据库的各类文件frm、ibd、MYI、MYD文件数量,后续做对比验证用途。

 

[root@gettestlnx02
test]# ls -lrt *.frm | wc -l

84

[root@gettestlnx02
test]# ls -lrt *.ibd | wc -l  

84

[root@gettestlnx02
test]# ls -lrt *.MYI | wc -l     

22

[root@gettestlnx02
test]# ls -lrt *.MYD | wc -l 

22

[root@gettestlnx02
test]#