MySQL 如何对InnoDB使用Undrop来恢复InnoDB数据

适用于：

MySQL服务器版本4.1到5.6 [发行版4.1到5.6]

本文信息适用于所有平台。

目标

如何使用undropforinnodb从损坏的表中提取数据

解决方案

使用工具有时可能从无法用innodb_force_recovery读取的表中恢复数据。

Undrop工具用于不被使用或不被启用的DB服务器使用的ibdata文件。

通常，该工具从整个（多个）ibdata文件且/或独立的InnoDB tablespace文件 (innodb_file_per_table在使用中的.ibd文件)提取索引页。Blob页被提取到可应用的另外的子目录

一旦数据被提取到索引页，下一步就是从数据目录恢复主键或一般聚类索引ID，然后将数据提取到适于使用LOAD DATA INFILE的文件。

如果可以的话，以要恢复的（多个）数据库的至少一个schema dump启动，需要时使用innodb_force_recovery。即使一个过时的备份也好过什么都没有。虽然UnDROP有时能从ibdata文件中提取一个有效表定义，它不擅于处理所有列类型。如果你完全没有备份，.frm文件能被用于重建表定义。如果你没有任何备份或.frm文件，那么最后一招就是UnDROP 能从idbata提取的表定义能尝试至少恢复一些数据。

UnDROP工具如下，以它们被用于提取数据的顺序排列：

stream_parser

stream_parser是用于从ibdata提取页的工具。它的使用很简单：

./stream_parser f

<path_to_ibdata>

页会被默认提取到”pages-<ibdata_file_name>”。索引页被储存在子目录

FIL_PAGE_INDEX，且blob页被储存在子目录FIL_PAGE_TYPE_BLOB。

要提取表的所有数据，有必要识别表的主键的数据目录索引ID (在没有主键时的一般索引)。这能通过使用UnDROP工具的”recover_dictionary.sh”脚本，将从被提取的索引页提取的字典数据放到在运行服务器的’test’ schema，像这样：

$ ./recover_dictionary.sh

Generating dictionary tables dumps... OK
Creating test database ... OK
Creating dictionary tables in database test:
SYS_TABLES ... OK
SYS_COLUMNS ... OK
SYS_INDEXES ... OK
SYS_FIELDS ... OK
All OK
Loading dictionary tables data:
SYS_TABLES ... 1845 recs OK
SYS_COLUMNS ... 22029 recs OK
SYS_INDEXES ... 4994 recs OK
SYS_FIELDS ... 6070 recs OK

All OK

现在字典能被查询来找出索引相对于任何给定表的索引ID。

给出的示例是对于在moodle2 schema中的表mdl2_user：

mysql> SELECT SYS_TABLES.NAME TABLE_NAME, SYS_TABLES.ID TABLE_ID,
SYS_INDEXES.NAME INDEX_NAME, SYS_INDEXES.ID INDEX_ID FROM SYS_TABLES LEFT JOIN
SYS_INDEXES ON SYS_TABLES.ID = SYS_INDEXES.TABLE_ID WHERE SYS_INDEXES.NAME LIKE '%PRIMARY%' AND SYS_TABLES.NAME LIKE 'moodle2/mdl2_user' AND SYS_INDEXES.NAME IN ('PRIMARY', 'GENERAL_CLUSTERED_INDEX');

+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐ ‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+
| TABLE_NAME | TABLE_ID | INDEX_NAME | INDEX_ID |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐ ‐‐‐‐‐‐+
| moodle2/mdl2_user | 646 | PRIMARY | 1867 |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐ +‐‐‐‐‐‐‐‐‐‐+

1 row in set (0.00 sec)

INDEX_ID返回与名称1相应的提取的页文件：

$ ls pages‐ibdata1/FIL_PAGE_INDEX/*1867.page
pages‐ibdata1/FIL_PAGE_INDEX/0000000000001867.page
c_parser


此时有了已知的表定义，数据能被c_parser恢复，像这样，其中mdl2_user.sql 包含表定义：


$ ./c_parser ‐b "./pages‐ibdata1/FIL_PAGE_TYPE_BLOB" ‐p "dumps/moodle2" ‐l
dumps/moodle/mdl2_user.load ‐5f pagesibdata1/
FIL_PAGE_INDEX/0000000000001867.page ‐t mdl2_user.sql

对每个schema使用以下脚本和在转储目录中的给定表定义，所有在数据目录找到的表能被提取到适合LOAD DATA INFILE的文件，以及分开的.load文件来加载它们。如果原始表定义不能被提供或从.frm文件被提取，反注释sys_parser行只作为最后方法。如果原始表defs能被提供，它们应该被作为分开的sql文件储存在dumps/<schema>/<table>.sql：

#!/bin/bash

RECOVERY_DB="test"
USER="root"
PASS="somepass"
DUMPS="dumps"

# Create schema

echo > ${DUMPS}/schema.sql
for DB in `mysql ‐‐user=${USER} ‐‐password=${PASS} ‐NBe "select name from
${RECOVERY_DB}.sys_tables" | sed ‐r "s/^(.*)\/.*$/\1/" | grep ‐v SYS_ | sort ‐u `
do
mkdir ‐p ${DUMPS}/${DB}
echo "Creating schema for $DB..."
echo >> ${DUMPS}/schema.sql
echo "CREATE DATABASE IF NOT EXISTS $DB;" >> ${DUMPS}/schema.sql
for TABLE in `mysql ${RECOVERY_DB} ‐‐user=${USER} ‐‐password=${PASS} ‐NBe
"SELECT NAME FROM SYS_TABLES WHERE NAME LIKE '${DB}/%'"`

do
echo $TABLE
# ./sys_parser ‐u${USER} ‐p${PASS} ‐d ${RECOVERY_DB} ${TABLE} | tee
${DUMPS}/${TABLE}.sql >> ${DUMPS}/schema.sql
PKEY=`mysql ${RECOVERY_DB} ‐BNe "SELECT SYS_INDEXES.ID FROM SYS_TABLES
LEFT JOIN SYS_INDEXES ON (SYS_TABLES.ID = SYS_INDEXES.TABLE_ID) WHERE
SYS_TABLES.NAME = \"${TABLE}\" AND SYS_INDEXES.NAME=\"PRIMARY\""`
echo "pkey = $PKEY"
PAGE="pages‐ibdata1/FIL_PAGE_INDEX/`printf '%016u' ${PKEY}`.page"
echo "PAGE = $PAGE"
./c_parser ‐b "./pages‐ibdata1/FIL_PAGE_TYPE_BLOB" ‐p "./${DUMPS}/${DB}" ‐
l ${DUMPS}/${TABLE}.load ‐5f ${PAGE} ‐t ${DUMPS}/${TABLE}.sql > ${DUMPS}/${TABLE}

done
done

sys_parser

像之前所说的，sys_parser能被用于从ibdata文件提取表定义，但它仅应当作为最后手段。可能需要一些猜测，而且如果不能提供有效的表定义，从ibdata完整提取可用数据就不太可能了。

从.frm文件提取有效表定义

使用MySQL Utilities包的mysqlfrm从未损坏的.frm文件提取有效定义是可能的。要注意的是在诊断模式下使用mysqlfrm可能遇到与使用sys_parser从ibdata提取数据相同的问题。所以使用mysqlfrm与服务器标识是很重要的。输出必须被调整来与c_parser运作，因为如果命令行，警告，或默认字符集信息在表定义中被找到，c_parser会终止。这里是提取表定义和单个schema.sql被用于从在给定目录中找到的所有.frm文件创建schema(s)的方式：

#!/bin/bash

for FRM in `find ../datadir/ ‐type f ‐wholename "*frm" | sort`

do

mysqlfrm ‐‐server=root:somepass@localhost:../datadir/mysql.sock ‐‐port=33307

$FRM | extract_schema.pl

if [ ${PIPESTATUS[0]} ‐ne 0 ]; then

echo "$FRM is corrupt"

fi

done

extract_schema.pl像这样：

!/usr/bin/perl

open (SCHEMAFILE, '>>', "schema.sql") or die "Can not write to schema.sql $!";

$schema = "";

$table = "";


while (<STDIN>) {

$origline = $ _;

chomp;

if (/^ CREATE TABLE.*$/) {

m/.*CREATE TABLE \`(. *)\`\.\`(.*)\`[[:space:]]\(/;

$schema = $1;

$table = $2;

print "Creat ing $schema.$table\n";

unless (‐e $schema or mkdir $schem a) {

die "Unable to create dir for sche ma $schema";

}

p rint SCHEMAFILE "CREATE DATABASE IF NOT EXISTS $schema;\n";

print SCHEMAFILE "USE $schema;\n";

print SCHEMAFILE "$origline";

open (TABLEFILE, '>', "$schem a/$table.sql") or die "Can not write to

$schema/$table.sql";

print TABLEF ILE "CREATE TABLE $table (\n";

} else {

s/ENGINE=(.*?)[[:space:]].*/ENGINE=\1;/;

s/`PRIMARY`//;

s/^#.*$//;

s/^WARNING.*$//;

$origline =~ s/(.*ENGINE.*$)/\1;/;

$origline =~ s/^#.*$//;

$origline =~ s/^WARNING.*$//;

print SCHEMAFILE "$origline" if (!/^\s*$/);

print TABLEFILE "$_\n" if (!/^\s*$/);

}

}

尽管UnDROP 可用令人高兴，但遇到必须使用它的情况是非常糟糕的。当数据库损坏时，它不能保证任何数据能被恢复。避免陷入这种情况的方法就是使用MySQL Enterprise Backup来定期创建可用的备份，以及至少一个复制slave。关心数据安全的聪明管理员会在远程创建一个复制slave，将服务器的定期备份作为灾难恢复的准备。