恢复InnoDB独立表空间数据

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

   有时候需要只有在.ibd文件的情况下恢复数据。我们尽力将它load到新的实例或者其他实例当中,可能会遇到 table id 的错误。

     我这里有两种方式来恢复单个ibd数据。

     前提是:你需要.ibd的文件,和对应该表的 CREATE TABLE 语句。

     第一种:模拟Innodb internal  table id计数方式。启用innodb_file_per_table,建立work  table,直至internal table id 等于 (要恢复表的table id  -1)

     第二种:手动修改16进制的.ibd文件来改变table  id

     最后,由于innodb元数据的中内部结构,我们需要对dump import 我们已经恢复的表。

     方法1、创建 work table.

     1、新建立一个MySQL 实例,并启用innodb_file_per_table

     2、找到work table 在新实例中的table id,和需要恢复表的table id,

     note:

     对于第二步(2a--2f)详细过程是找出实例中各个.ibd文件对应的table id,我已经写了php 脚本来做这个事情,

     2a:创建测试库:

mysql> CREATE DATABASE test1;
mysql> USE test1;

     2b:在test1中创建对应.ibd(需要恢复的表) 文件的数据表

mysql> CREATE TABLE `product` (
  `PRODUCT_ID` bigint(20) unsigned NOT NULL auto_increment,
  `BRAND_ID` int(10) unsigned default NULL,
  `PRODUCT_TYPE_ID` int(10) unsigned default NULL,
  `GROUP_ID` int(10) unsigned default NULL,
  `PRODUCT_NAME` varchar(500) NOT NULL,
  `DEFAULT_EMAIL_ID` varchar(48) default NULL,
  `PRODUCT_STATUS` tinyint(1) NOT NULL,
  `CLIENT_ID` bigint(20) unsigned default NULL,
  `LAST_MODIFIED_BY` varchar(45) NOT NULL,
  `LAST_MODIFIED_DATE` datetime NOT NULL,
  PRIMARY KEY  (`PRODUCT_ID`)
  ) ENGINE=InnoDB;

     2c:删除表空间

mysql> ALTER TABLE product DISCARD TABLESPACE;

     2d: 拷贝原先的.ibd 文件(需要恢复的表的数据文件)到 test1的数据库目录下

     2e:导入表空间 

mysql> ALTER TABLE product IMPORT TABLESPACE;

        该步骤通常情况下会报错,除非导入的表空间对应的table id 等于新建表的table id

        报错信息;

ERROR 1030 (HY000): Got error -1 from storage engine

     2f:检查error.log ,我们能够找到该.ibd文件的 table id

081010 11:47:40  InnoDB: Error: tablespace id in file
'.test1product.ibd' is 1193, but in the InnoDB
InnoDB: data dictionary it is 1

        我们知道 internal  table id 是1,.ibd file对应的 table id 是1193

     3、清空 test1

     3a、手动移动 .ibd 文件到 到其他位置(一会儿还会需要)

     3b、删除表

mysql> DROP TABLE product;

        这个步骤不会重设 internal table 计数器

      4、创建相应数量的表来使 internal table id 的值增加

         在这个案例中,我们需要在 test1中创建 1191个 innodb table,(table id 1已经被占用,需要比对应.ibd 文件的table id 小1, 所以是 1193-2=1191 )

         执行下面的程序:     

for ($1=1; $i<=1191; $1++) {
  CREATE TABLE t# (id int) ENGINE=InnoDB;
}

       注:我已经用Php 程序搞定

      5、完成以上步骤后,删除所有的db 和 table。

         drop database test1;

      6、重新执行步骤 2a-2e

mysql> CREATE DATABASE test1;
mysql> USE test1;
mysql> CREATE TABLE `product` ( ... ) ENGINE=InnoDB;
mysql> ALTER TABLE product DISCARD TABLESPACE;

        拷贝对应的.ibd file 到对应的 test1数据库目录      

mysql> ALTER TABLE product IMPORT TABLESPACE;
Success!
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| product         |
+-----------------+
1 row in set (0.00 sec)

      7、用mysqldump来备份该表(这一步必须执行),然后可以在任何一个实例中进行恢复。

      以上的情况常常发生在数据库 crash 或者表空间损坏的情况下。

      如果发生以上 情况,先尝试force innodb recovery 并dump出数据。  从 1开始innodb_force_recovery=1 (and try 2,3,4,5,6) 直到能够dump出数据。

      对于以上的例子,我是设置 innodb_force_recovery=5 来解决问题。

      以下是我的操作记录:     

C:Program FilesMySQLmysql-5.0.68bin>
mysqldump -uroot -P3385 test1 product > product_dump1.txt
mysqldump: Couldn't execute 'show table status like 'product'':
Lost connection to MySQL server during query (2013)

C:Program FilesMySQLmysql-5.0.68bin>
mysqldump -uroot -P3385 test1 product > product_dump2.txt
mysqldump: Couldn't execute 'show table status like 'product'':
Lost connection to MySQL server during query (2013)

C:Program FilesMySQLmysql-5.0.68bin>
mysqldump -uroot -P3385 test1 product > product_dump3.txt
mysqldump: Couldn't execute 'show table status like 'product'':
Lost connection to MySQL server during query (2013)

C:Program FilesMySQLmysql-5.0.68bin>
mysqldump -uroot -P3385 test1 product > product_dump4.txt
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */
* FROM `product`': Lost connection to MySQL server during
query (2013)

C:Program FilesMySQLmysql-5.0.68bin>
mysqldump -uroot -P3385 test1 product > product_dump5.txt

C:Program FilesMySQLmysql-5.0.68bin>
mysqladmin -u root -P 3385 shutdown

C:Program FilesMySQLmysql-5.0.68bin>
mysqldump -uroot -P3385 test1 product > product_dump6.txt

      设置为5的原因是由于以下error中的信息:

InnoDB: Error: trying to access update undo rec field 19
in index PRIMARY of table test1/product
InnoDB: but index has only 12 fields

      以上是 undo  log中的信息,文档中设置为5的解释是:

      "Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed"

      方法二 修改.ibd file

      在此之前先备份数据(ibdata  file,ib_logfile, data)

      按照下面的 1-5步来 操作:

      http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html

      Let me post them here for completeness, however:

  1. Use mysqldump to dump all your InnoDB tables.
  2. Stop the server.
  3. Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.
  4. Remove any .frm files for InnoDB tables.
  5. Configure a new tablespace.
  6. Restart the server.
  7. Import the dump files.

   重复 2a-2f的过程,来获得 该.ibd  file的table id,

   在windows上使用 Freeware Hex Editor XVI32 (http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm)

   以下是修改部分:

   For me, and I assume it should be the same for you, but just look at the values to be sure, I see the tablespace id values listed at position 37 and 41 (positions 25 and 29 in hex). In the actual hex column, if you're previous tablespace id was 2, then in positions 37 and 41, you'd see 02 and 02.

   (Note these positions can change. For instance, I tested on a table with an internal id of 1193. This in hex is 04A9. However, when searching the file, for the first instance of the table id, I found the '04' in position 39 and 'A9' in position 40. Then, for the second instance of the table id, the '04' was at position 43 and the 'A9' was at position 44. So, you'll have to convert the table id to hex, and then search for that value, near the beginning of the file.)

   Note that this value (02) may vary depending on what your actual tablespace id is.

   Then, simply modify both of those fields to 01, and save the file.

   再执行下面的部分:

1. ALTER TABLE tbl_name DISCARD TABLESPACE;
2. Put the newly saved .ibd file back in the proper database directory
3. ALTER TABLE tbl_name IMPORT TABLESPACE;

   以下是涉及到的 php 脚本:

$dbhost = "localhost:3385";
$dbname = "test1";
$dbuser = "root";
$dbpwd  = "";

mysql_connect($dbhost,$dbuser,$dbpwd) or die(mysql_error());

for ($i = 1033; $i <= 1190; $i++) {
   $dbquery = "CREATE TABLE test1.t" . $i . " (id int) ENGINE=InnoDB";

   echo "" . $dbquery . "";

      $result = mysql_db_query($dbname,$dbquery) or die(mysql_error());

      $j = 0;

      while($row = mysql_fetch_array($result)) {
         $j++;
         echo $row[0];
      }
}

mysql_close();
PHP Internal Table ID Finder - Used to determine the internal Table ID from the binary .ibd file:
/*
Tested with tables from 4.1.23, 5.0.68, 5.1.28, and 6.0.7.
*/

// Set the filename
$filename = "C:\Users\Chris\Desktop\mysql\working\ibds\z1.ibd";

// Read 2 bytes in at a time
$offset = 2;

// Echo filename and path
echo "filename = $filename

";

// Open the filename - need 'rb' for binary file on Windows
$handle = fopen($filename, "rb");

// Define redundant, local variables for possible later functionality and/or checks
$ibd_id_bin = 0;
$ibd_id_hex = 0;
$ibd_id_dec = 0;
$ibd_id_bin2 = 0;
$ibd_id_hex2 = 0;
$ibd_id_dec2 = 0;

// Find the filesize (note: below command messes up script)
//$filesize = filesize($filename));

// Only loop through first 21 bytes - as table is is in $array[18] and $array[20]
for ($z = 0; $z <= 20; $z++) {

	// Set variable $contents equal to 2 ($offset) bytes of binary data
	$contents = fread($handle, $offset);

	// Convert $contents from binary data to hex data
	$contents2 = bin2hex($contents);

	// Convert $contents2 from hex data to decimal data
	$contents3 = hexdec($contents2);

	// Debug Output
	//echo "contents[$z] = " . $contents . "";
	//echo "contents2[$z] = " . $contents2 . "

";
	//echo "contents3[$z] = " . $contents3 . "

";

	// If position 19, array position [18], then store the values
	if ($z == 18) {
		$ibd_id_bin = $contents;
		$ibd_id_hex = $contents2;
		$ibd_id_dec = $contents3;
	}

	// If position 21, array position [20], then store the values
	if ($z == 20) {
		$ibd_id_bin2 = $contents;
		$ibd_id_hex2 = $contents2;
		$ibd_id_dec2 = $contents3;
	}
}
fclose($handle);

// More Debug output
//echo "

The table id is $ibd_id_dec

";
//echo "

The table id is $ibd_id_dec2

";

// Check to see if both values are equal.  If so, then it's
// most certain this is the correct value.
// If not, then there's a chance the positions are off for
// this table (due to versions, etc.).
if ($ibd_id_dec == $ibd_id_dec2) {
	echo "

The table id is $ibd_id_dec

";
} else {
	echo "The values from positions [18] and [20] did not match,";
             echo "so please enable debug output, and check for proper positions.";
}




本文转自 位鹏飞 51CTO博客,原文链接:http://blog.51cto.com/weipengfei/1049982,如需转载请自行联系原作者
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
18
分享
相关文章
InnoDb行格式、数据页结构、索引底层原理和如何建立索引
InnoDb行格式、数据页结构、索引底层原理和如何建立索引
178 0
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分,包括表空间、段、区、页和行。表空间是最高逻辑层,包含多个段;段由若干个区组成,每个区包含64个连续的页,页用于存储多条行记录。文章还详细解析了Page结构,分为通用部分(文件头与文件尾)、数据记录部分和页目录部分。此外,文中探讨了行记录格式,包括四种行格式(Redundant、Compact、Dynamic和Compressed),重点介绍了Compact行记录格式及其溢出机制。最后,文章解释了不同行格式的特点及应用场景,帮助理解InnoDB存储引擎的工作原理。
MySQL底层概述—4.InnoDB数据文件
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
235 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
【赵渝强老师】MySQL InnoDB的表空间
InnoDB是MySQL默认的存储引擎,主要由存储结构、内存结构和线程结构组成。其存储结构分为逻辑和物理两部分,逻辑存储结构包括表空间、段、区和页。表空间是InnoDB逻辑结构的最高层,所有数据都存放在其中。默认情况下,InnoDB有一个共享表空间ibdata1,用于存放撤销信息、系统事务信息等。启用参数`innodb_file_per_table`后,每张表的数据可以单独存放在一个表空间内,但撤销信息等仍存放在共享表空间中。
顺序读和InnoDB的数据组织
【7月更文挑战第7天】自增主键优化顺序读:保证数据物理排序,提升范围查询效率。InnoDB引擎中,主键决定数据在页的存储。当插入的数据引起页分裂,如从1、2、3、5、6、7插入4,会导致相邻逻辑页在磁盘上可能分散,影响性能。了解页结构深化数据库知识,面试时可根据情况深入讨论。
46 2
PolarDB产品使用问题之如何用InnoDB引擎创建Federated表
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
102 1
MySQL数据库——InnoDB引擎-逻辑存储结构(表空间、段、区、页、行)
MySQL数据库——InnoDB引擎-逻辑存储结构(表空间、段、区、页、行)
223 7
【MySQL技术内幕】4.4-InnoDB数据页结构
【MySQL技术内幕】4.4-InnoDB数据页结构
154 1
InnoDB一棵B+树可以存放多少行数据?
InnoDB一棵B+树可以存放多少行数据?
94 0
InnoDB一棵B+树可以存放多少行数据?