TimeM

青春不散场

使用 ibdata 和 frm 文件恢复 MySQL 数据库

作者

一.使用 ibdata  frm 文件恢复 MySQL 数据库

因为磁盘空间不足,我的一个虚拟机服务器崩溃了。结果数据库服务器进程无法启动,数据也就无法导出。只能想办法从数据库原始文件 ibdata 和 frm 文件中恢复数据库。

因为没有经验,好不容易才找到了恢复方法。特此记录,以备后用。

磁盘空间不足之后,mysqld 进程无法启动,提示“Can’t connect tolocal MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)”。这真是让人无比头大,数据库根本连接不上。

1. 保存原始数据库文件

好在数据库原始文件还在。在我的系统环境和配置情况下,这些文件位于 /var/lib/mysql/ 文件夹下面。假设数据库名是 test,则这些文件表现为:

–mysql

|–test

|–1.frm

|–2.frm

|…

|–mysql

|…

|–ib_logfile0

|–ib_logfile1

|–ibdata1

|…

这些就是原始数据库文件,可以用来恢复数据库。将这些文件额外保存一份,以防万一。

2. 恢复方法

我的原始虚拟机完全没有磁盘空间而无法启动数据库服务器进程。虽然试着删除一些不需要的文件,但是数据库却始终无法连接。于是我新建了一个几乎一样的虚拟机(当然磁盘加大了),试图将这些数据库文件导入并恢复数据库。

在经历了很多错误之后,终于找到了正确的方法:

1.      安装完成新服务器之后,通过命令行新建了与原来一样的数据库:数据库名称、用户名、密码都一样。如果有多个数据库需要恢复,就都给建好。(跟配置新服务器一样,参见安装和配置 MYSQL 数据库服务器。)

2.      停止 mysqld 进程

service mysqldstop

3.      将备份的原始数据库文件中的所有 .frm 文件(保持原来的目录结构)和 ibdata1 文件复制到新服务器的数据库文件目录中(如果新服务器操作系统和配置环境一样,那么目录结构也一样),其它文件不要

4.      使用 -innodb_force_recovery=6参数启动数据库服务器进程,这里是

/etc/init.d/mysqldstart -defaults-file=/etc/my.cnf -standalone -console -innodb_force_recovery=6

OK,数据库恢复完成。

二.如何从IBData中恢复MySQL数据库

mysql 5.0.27版本采用INnodb引擎建立表

由于磁盘问题丢失了 .frm文件但 IBData和Log文件均完好

如何从其中恢复出库或导出数据?

 

成功恢复,步骤如下:
1、恢复原数据库的配置文件:包括日志大小文件路径等
2、在[mysqld]段中增加:innodb_force_recovery= 4
3、可以在另外地方建立新的数据库并用原表的结构创建表
4、将上一步中建立的.frm表文件拷贝到准备恢复的数据库中
5、启动数据库
6、停止数据库注释掉innodb_force_recovery= 4项
7、重新启动数据库,应该可以用mysqldump导出数据了

表结构文件应该可以凭借你的记忆恢复,关键是ibdata数据完好(日志文件有损的话估计应该也可以待测试)

,启动数据库启动不了需要把data下的两个log文件删了

 

 

三.Mysql ibdata 丢失或损坏如何通过frm&ibd 恢复数据

 

四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=InnoDBDEFAULT 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_qq0DISCARD TABLESPACE;
c、把之前要恢复的 .ibd文件复制到新的表结构文件夹下。使当前的ibd 和frm发生关系。ALTER TABLE weibo_qq0IMPORT TABLESPACE; 结果不出意外肯定会报错。就和我们开展数据开始说的那样,数据记录点不一致。我们看看之前ibd记录的点在什么位置。开始执行 import tablespace,报错 ERROR 1030 (HY000): Got error -1 fromstorage engine。找到mysql的错误日志,InnoDB: Error: tablespaceid in file ‘.\test\weibo_qq0.ibd’ is 112, but in the InnoDB InnoDB: datadictionary 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_qq0discard tablespace;使当前的表结构和ibd脱离关系。复制.ibd到当前的目录结构。
f、使原来数据的ibd和当前frm建立关系。 ALTER TABLE product IMPORTTABLESPACE; 这个时候没有错误,说明已经建立好了。但是查询数据还是查不出来。
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 “CREATETABLE 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 ibdata1文件的缩小释放磁盘空间

 

mysql ibdata1文件的缩小-释放磁盘空间

 

mysql ibdata1文件存放数据,索引等。

如果不把数据库中数据分开存放的话,这个文件的大小很容易就上了G,甚至几十G。对于某些应用、磁盘较小的server来说,并不是太合适。因此要把此文件缩小。

方法:数据文件单独存放。

 

步骤:

1,备份数据库

从命令行进入MySQLServer 5.5/bin

备份全部数据库,执行命令mysqldump -q -uusername -pyourpassword–add-drop-table –all-databases > /all.sql

做完此步后,停止数据库服务。

2,修改mysql配置文件

修改my.ini文件,增加下面配置

iinnodb_file_per_table= 1

对每张表使用单独的innoDB文件, 修改/etc/my.cnf文件

3,删除原数据文件

删除原来的ibdata1文件及日志文件ib_logfile*,删除data目录下的应用数据库文件夹(mysql文件夹不要删)

4,还原数据库

启动数据库服务

从命令行进入MySQLServer 5.5/bin

还原全部数据库,执行命令mysql -uusername -pyourpassword < /all.sql

经过以上几步后,可以看到新的ibdata1文件就只有几十M了,数据及索引都变成了针对单个表的小ibd文件了,它们在相应数据库的文件夹下面。

 

注:目前还没有找到不导出数据再导入,直接缩小ibdata文件体积的办法。对于ibdata文件有坏道、表空间有损坏的数据库来说,只能跳过损坏的id,dump出数据成sql文件,再导入了。

五.如何恢复Mysql数据库

这里说的MySql恢复数据库,是指没有通过正常备份的情况下,通过Mysql保存的数据文件如何恢复数据库。

由于在一台测试机器上打算重新安装Mysql数据库,由于简单粗暴的直接卸载了,没有备份公司Discuz和Redmine使用的Mysql数据库,过程可想的悲惨。

还好的是只是卸载掉了Mysql的程序,所有的数据文件还是存在的。

下面是在恢复数据库的过程

1. Discuz数据库

Discuz数据库的恢复非常顺利, 在安装好新版本的Mysql后,直接将原来的数据库文件copy到新的数据目录中,重新启动mysql, 就能看到恢复的数据库了

2. Redmine数据库

本打算直接使用上面的经验,也能看到所有的表,但是就是执行查询的时候,总是报错”表不存在”.

后来查了一些资料,发现,原因应该是Discuz和Redmine使用的Mysql引擎不一样导致的。

Discuz使用的是MyISAM,而Redmine使用的是InnoDB.

解决的办法是,

除了要copy数据目录外,还要记得覆盖ibdata1文件。

下面是转自http://www.cnblogs.com/joeylee/archive/2012/09/27/2705685.html

以表”Table”为例: 如类型是MyISAM, 数据文件则以”Table.frm””Table.MYD””Table.MYI””三个文件存储于”/data/$databasename/”目录中. 如类型是InnoDB, 数据文件则存储在”$innodb_data_home_dir/″中的ibdata1文件中(一般情况),结构文件存在于table_name.frm中. MySQL的数据库文件直接复制便可以使用,但是那是指“MyISAM”类型的表。而使用MySQL-Front直接创建表,默认是“InnoDB”类型,这种类型的一个表在磁盘上只对应一个“*.frm”文件,不像MyISAM那样还“*.MYD,*.MYI”文件。 MyISAM类型的表直接拷到另一个数据库就可以直接使用,但是InnoDB类型的表却不行。解决方法就是:

同时拷贝innodb数据库表“*.frm”文件和innodb数据“ibdata1”文件到合适的位置。启动MySQL的Windows服务 由于MySQL这样数据混杂的形式, 往往很容易让使用者在备份时忘记了备份InnoDB, 从而导致了上述错误.

意思就是说在数据库引擎类型为InnoDB时,拷贝数据文件的同时还需要拷贝ibdata1,于是把ibdata1也拷贝过去覆盖,发现还是有点问题,于是停止mysql服务,将目录下的ib_logfile*文件全部删除掉,重新启动mysql服务,well done,可以了

高兴啊,于是稍微总结了,希望以后遇到相同的问题,能够快速解决。

1,在进行mysql数据库备份的或迁移的时候,尽量备份完成所需要的数据;

2,如果直接拷贝原有数据库文件”*.frm”、”*.MYD”、”*.MYI”等文件时候,如果原数据库引擎是InnoDB,切记还需拷贝ibdata1文件

3,备份数据库的时候,最好是用相关的工具进行备份或是导出sql文件,以免浪费时间在数据库恢复上

4,msyql版本或是备份工具的版本不同,也可能引起数据恢复有问题。

实践证明以上问题是存在的,解决方案是可行的,哈哈,为了以后方便,写了这篇博客随笔,希望大牛看到了不要鄙视,欢迎拍砖。

1:MyISAM类型的数据文件可以在不同操作系统中COPY,这点很重要,布署的时候方便点。(只需要拷贝 数据库名字文件夹下面的文件,这样数据库就拷贝完了)

2: InnoDB类型的 要注意多拷贝 ibdata1 , 最好不要是直接复制文件夹,而是应该用sql导入导出

 

 

 

六、Mysqlibdata1文件恢复问题

1、导入ibdata1文件到data文件夹后Mysql不能正常启动怎么办?

解决办法将data文件夹中的ib_logfile0、ib_logfile1、lenovo-PC.err文件删除,因为mysql会根据你的电脑创建相应的以上文件,所以在恢复bdata1之前应该先将这些文件删除;

2、导入bdata1文件后成功启动Mysql 但不能打开数据表和读写数据怎么办?

2、1有可能是因为bdata1文件被损坏,按照正常方式是不能正常使用,那么就要用到mysql 提供的数据恢复参数了,打开 Mysql 安装目录下的my.ini 文件在

[mysqld]加上

innodb_force_recovery= 4 就可以了。

可参考http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html进行相应设置

个人总结的不清楚可参考以上文章

 

 

七、MYSQL INNODB 数据库恢复

MySQL的数据库文件直接复制便可以使用,但是那是指“MyISAM”类型的表。
而使用MySQL-Front直接创建表,默认是“InnoDB”类型,这种类型的一个表在磁盘上只对应一个“*.frm”文件,不像MyISAM那样还“*.MYD,*.MYI”文件。
MyISAM类型的表直接拷到另一个数据库就可以直接使用,但是InnoDB类型的表
却不行。解决方法就是:

同时拷贝innodb数据库表“*.frm”文件和innodb数据“ibdata1”文件到合适的位置。启动MySQL的Windows服务,如果不能成功的话,查看data文件夹中有个“*.err”错误日志文件,其中会对启动失败的原因有所描述的。比如我碰到过两种错误原因。
一种是类似这样的错误信息:

INIFilecode

InnoDB:Error: log file .\ib_logfile0 is of different size 0 10485760 bytes InnoDB:than specified in the .cnf file 0 25165824 bytes!

这是因为在mysql配置文件中配置的日志文件大小与实际的不相符。
解决方法是直接删掉旧的“ib_logfile0”等日志文件,重启MySQL后会自动生成新的日志文件的。
另一中则是这样的错误信息

INIFilecode

InnoDB:Operating system error number 5 in a file operation. InnoDB: The error meansmysqld does not have the access rights to InnoDB: the directory. It may also beyou have created a subdirectory InnoDB: of the same name as a data file.InnoDB: File name .\ibdata1 InnoDB: File operation call: ‘open’. InnoDB: Cannotcontinue operation.

经检查原来是“ibdata1”文件在复制的过程中不知怎的被加上只读属性了。
解决方法是去掉“ibdata1”文件的只读属性便可。

15.2.8.1.强制恢复

如果数据库页被破坏,你可能想要用SELECTINTO OUTFILE从从数据库转储你的表,通常以这种方法获取的大多数数据是完好的。即使这样,损坏可能导致SELECT* FROM tbl_name或者InnoDB后台操作崩溃或断言,或者甚至使得InnoDB前滚恢复崩溃。尽管如此,你可以用它来强制InnoDB存储引擎启动同时阻止后台操作运行,以便你能转储你的表。例如:你可以在重启服务器之前,在选项文件的[mysqld]节添加如下的行:

[mysqld]

innodb_force_recovery= 4

innodb_force_recovery被允许的非零值如下。一个更大的数字包含所有更小数字的预防措施。如果你能够用一个多数是4的选项值来转储你的表,那么你是比较安全的,只有一些在损坏的单独页面上的数据会丢失。一个为6的值更夸张,因为数据库页被留在一个陈旧的状态,这个状态反过来可以引发对B树和其它数据库结构的更多破坏。

·1 (SRV_FORCE_IGNORE_CORRUPT)

即使服务器检测到一个损坏的页,也让服务器运行着;试着让SELECT * FROMtbl_name 跳过损坏的索引记录和页,这样有助于转储表。

·2 (SRV_FORCE_NO_BACKGROUND)

阻止主线程运行,如果崩溃可能在净化操作过程中发生,这将阻止它。

·3 (SRV_FORCE_NO_TRX_UNDO)

恢复后不运行事务回滚。

·4 (SRV_FORCE_NO_IBUF_MERGE)

也阻止插入缓冲合并操作。如果你可能会导致一个崩溃。最好不要做这些操作,不要计算表统计表。

·5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

启动数据库之时不查看未完成日志:InnoDB把未完成的事务视为已提交的。

·6 (SRV_FORCE_NO_LOG_REDO)

不要在恢复连接中做日志前滚。

数据库不能另外地带着这些选项中被允许的选项来使用。作为一个安全措施,当innodb_force_recovery被设置为大于0的值时,InnoDB阻止用户执行INSERT, UPDATE或DELETE操作.

即使强制恢复被使用,你也可以DROP或CREATE表。如果你知道一个给定的表正在导致回滚崩溃,你可以移除它。你也可以用这个来停止由失败的大宗导入或失败的ALTERTABLE导致的失控回滚。你可以杀掉mysqld进程,然后设置innodb_force_recovery为3,使得数据库被挂起而不需要回滚,然后舍弃导致失控回滚的表。
15.2.8.2. 检查点

InnoDB实现一种被认识为“模糊”检查点设置的检查点机制。InnoDB以小批量从缓冲池刷新已修改的数据库页。没必要以单个批次刷新缓冲池,单批次刷新实际操作中可能会在检查点设置进程中停止用户SQL语句的处理。

在崩溃恢复中,InnoDB找寻被写进日志的检查点标签。它知道所有在该标签之前对数据库的修改被呈现在数据库的磁盘映像中。然后InnoDB从检查点往前扫描日志文件,对数据库应用已写入日志的修改。

InnoDB以循环方式写日志文件。所有使得缓冲池里的数据库页与磁盘上的映像不同的已提交修改必须出现在日志文件中,以备万一InnoDB需要做一个恢复。这意味着,当InnoDB开始重新使用一个日志文件,它需要确认在磁盘上的数据库页映像包含已写进InnoDB准备重新使用的日志文件里的修改。换句话说,InnoDB必须创建一个检查点,这经常涉及已修改数据库页到磁盘的刷新。

前面的叙述解释了为什么使你的日志文件非常大会在设置检查点中节约磁盘I/O。设置日志文件总的大小和缓冲池一样大或者甚至比缓冲池大通常是有意义的。大日志文件的缺点是崩溃恢复要花更长的时间,因为有更多写入日志的信息要应用到数据库上。
15.2.9. 把一个InnoDB数据库移到另一台机器

在Windows上, InnoDB 总是在内部以小写名字的方式存储数据库和表。要从Unix把二进制格式的数据库移到Windows,或者从Windows移到Unix,你应该让所有表和数据库的名字小写。要实现这个,一个方便的方式是在创建任何数据库和表之前,在你的my.cnf或my.ini文件的[mysqld]节内添加如下行:

[mysqld]

lower_case_table_names=1

类似于MyISAM数据文件,InnoDB数据和日志文件在所有有相同浮点数格式的平台上是二进制兼容的。你可以拷贝所有列在15.2.8节,“InnoDB数据库的备份和恢复”里的相关文件来简单地移动一个InnoDB数据库。如果浮点格式不同,但你没有在表中使用FLOAT或DOUBLE数据类型,则过程是一样:简单地拷贝相关文件。如果格式不容,且你的表包含浮点数据,你必须使用mysqldump在一台机器转储你的表,然后在另一台机器导入转储文件。

假设表空间有足够的空间供导入事务产生的大型回滚片断使用,则提高性能的一个方法是在导入数据时关掉autocommit模式。仅在导入整个表或表的一个片断之后提交。

晓楠

时光不老,青春不散

发表评论

电子邮件地址不会被公开。