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

适用于:

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

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

 

目标

如何使用undrop for innodb从损坏的表中提取数据

 

 

解决方案

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

undrop可以直接读取数据库的ibdata1文件,来获取数据字典信息和必要的恢复信息。

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

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

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

 

首先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/^(.*)\/.*$//" | 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";
}

print 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=;/;
s/`PRIMARY`//;
s/^#.*$//;
s/^WARNING.*$//;
$origline =~ s/(.*ENGINE.*$)/;/;
$origline =~ s/^#.*$//;
$origline =~ s/^WARNING.*$//;
print SCHEMAFILE "$origline" if (!/^\s*$/);
print TABLEFILE "$_\n" if (!/^\s*$/);
}
}

 

 

尽管undrop 可用令人高兴 , 但遇到必须使用它的情况是非常糟糕的。

 

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

 

 

Comment

*

沪ICP备14014813号

沪公网安备 31010802001379号