分类目录归档：数据库

[MySQL异常恢复]恢复数据字典表讲解

发表于 2015 年 8 月 23 日 由 惜分飞

在以前的文章中说过mysql的数据字典的恢复(使用工具直接抽取MySQL数据字典,缺少SYS_FIELDS表),主要的数据字典有一下几个，在本文中主要对这些数据字典的意义进行一些讲解，为大家更深一步了解mysql恢复处理思路
MySQL恢复字典表

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| SYS_COLUMNS    | 
| SYS_FIELDS     | 
| SYS_INDEXES    | 
| SYS_TABLES     | 
+----------------+
4 rows in set (0.00 sec)

SYS_TABLES
这个表是mysql恢复的最核心的表之一，主要是记录数据库在InnoDB中表的信息。它默认写在InnoDB的index_ids为1的里面，它的根页在8号page上，他的主要列结构为：

mysql> desc SYS_TABLES;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| NAME         | varchar(255)        | NO   | PRI |         |       | 
| ID           | bigint(20) unsigned | NO   |     | 0       |       | 
| N_COLS       | int(10)             | YES  |     | NULL    |       | 
| TYPE         | int(10) unsigned    | YES  |     | NULL    |       | 
| MIX_ID       | bigint(20) unsigned | YES  |     | NULL    |       | 
| MIX_LEN      | int(10) unsigned    | YES  |     | NULL    |       | 
| CLUSTER_NAME | varchar(255)        | YES  |     | NULL    |       | 
| SPACE        | int(10) unsigned    | YES  |     | NULL    |       | 
+--------------+---------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

NAME:顾名思义,就是表的名字,但是注意他记录的格式是db/table,例如:xifenfei/zx_users,表示为xifenfei数据库中的zx_users表
ID:表的编号
N_COLS:表一共包含的列的数量
TYPE, MIX_ID, MIX_LEN 和 CLUSTER_NAME列,对于数据库恢复无任何意义不做描述
SPACE:表空间的标示列. 例如： ibdata1 是 SPACE 0, ibdata2 是 SPACE 1, 每一个 ibd 文件都有自己的表空间标示.

SYS_INDEXES
这个也是mysql恢复的最核心表之一,主要是记录InnoDB的index信息,它默认InnoDB的index_ids为3的里面，他的主要结构为：

mysql> desc SYS_INDEXES;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| TABLE_ID | bigint(20) unsigned | NO   | PRI | 0       |       | 
| ID       | bigint(20) unsigned | NO   | PRI | 0       |       | 
| NAME     | varchar(120)        | YES  |     | NULL    |       | 
| N_FIELDS | int(10) unsigned    | YES  |     | NULL    |       | 
| TYPE     | int(10) unsigned    | YES  |     | NULL    |       | 
| SPACE    | int(10) unsigned    | YES  |     | NULL    |       | 
| PAGE_NO  | int(10) unsigned    | YES  |     | NULL    |       | 
+----------+---------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

TABLE_ID:表标示列,为SYS_TABLES.ID
ID:为InnoDB中的index的编号,这个在恢复中非常重要,恢复之时需要根据这个去定位具体的文件
NAME:主要表的index的名字,有PRIMARY 和 普通列的index信息,一般恢复之时我们选择PRIMARY
N_FIELDS:表名index包含列的数量,在mysql恢复中不重要
TYPE:恢复之中使用不到该列,不做说明
PAGE:用途等同SYS_TABLES.SPACE
PAGE_NO:标示为每个index的root page的page号,关于index中的page结构如下图所示
Bplustree

SYS_COLUMNS
这个表主要记录数据库中表的列的情况,它存储在index_id 2中.主要用它来确定需要恢复表的列的情况,如果你知道完全的列结构,该表不是MySQL恢复所必须的,它的主要结构为：

mysql> desc SYS_COLUMNS;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| TABLE_ID | bigint(20) unsigned | NO   | PRI | NULL    |       | 
| POS      | int(10) unsigned    | NO   | PRI | NULL    |       | 
| NAME     | varchar(255)        | YES  |     | NULL    |       | 
| MTYPE    | int(10) unsigned    | YES  |     | NULL    |       | 
| PRTYPE   | int(10) unsigned    | YES  |     | NULL    |       | 
| LEN      | int(10) unsigned    | YES  |     | NULL    |       | 
| PREC     | int(10) unsigned    | YES  |     | NULL    |       | 
+----------+---------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

TABLE_ID:表标示列,为SYS_TABLES.ID
POS:该列所在表中的位置,该值从0开始
NAME:列的名字
MTYPE 和 PRTYPE:主要是为了记录列的类型,出现此类问题主要是由于InnoDB最初并不是为MySQL而设计,到了后面为更好支持MySQL,因此出现了两种情况.
LEN:列的长度.这个需要注意编码,比如数据库是utf8编码,定义的varchar(10),实际该处长度显示为30,因为每个除英文外的字符编码为3个byte.
PREC:有些特殊类型中,列的精确度定义

SYS_FIELDS
记录所有index的列的分布信息,它存储在index_id 4中,该表不是MySQL恢复所必须的,它的主要结构为:

mysql> desc SYS_FIELDS;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| INDEX_ID | bigint(20) unsigned | NO   | PRI | NULL    |       | 
| POS      | int(10) unsigned    | NO   | PRI | NULL    |       | 
| COL_NAME | varchar(255)        | YES  |     | NULL    |       | 
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

INDEX_ID:index的标志,等同SYS_INDEXES.ID
POS:列在index中的位置,从0开始
COL_NAME:列的名称
通过上述相关表和列,然后结合MySQL相关恢复工具,就可以从底层在InnoDB出现问题,或者误操作之时提供恢复处理.

发表在 MySQL恢复 | 标签为 , , | 评论关闭

MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)

发表于 2015 年 8 月 21 日 由 惜分飞

昨天晚上接到一个网络服务请求，由于不小心点击了自己产品软件上面的清空数据功能(这个工具确实需要小心,在Oracle恢复案例中,也有xx企业erp该功能导致数据被删除请求恢复)，导致MySQL数据库被直接drop database掉了，之前没有做任何备份，只是发生故障之后，他们立即封存现场，备份出来了ibdata1文件。接到请求之后，通过让其把ibdata1文件发给过来，通过MySQL recovery工具进行分析恢复，经过6个小时的处理，基本上实现核心数据0丢失.
解析ibdata1(innodb文件)

[root@localhost recovery_MySQL]# ./stream_parser -f /tmp/ibdata1
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
Opening file: /tmp/ibdata1
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
File information:

time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 total size, in bytes:             18874368 (18.000 MiB)

(regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Opening file: /tmp/ibdata1
File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
number of hard links:                    1
user ID of owner:                        0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
group ID of owner:                       0
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
group ID of owner:                       0
device ID (if special file):             0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
blocksize for filesystem I/O:         4096
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
number of blocks allocated:          36920
Opening file: /tmp/ibdata1
total size, in bytes:             18874368 (18.000 MiB)

File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
blocksize for filesystem I/O:         4096
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
number of blocks allocated:          36920
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
Opening file: /tmp/ibdata1
total size, in bytes:             18874368 (18.000 MiB)

File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
Opening file: /tmp/ibdata1
File information:

total size, in bytes:             18874368 (18.000 MiB)

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 Size to process:                  18874368 (18.000 MiB)
(regular file)
number of hard links:                    1
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
user ID of owner:                        0
group ID of owner:                       0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
Opening file: /tmp/ibdata1
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
device ID (if special file):             0
File information:

blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
Size to process:                  18874368 (18.000 MiB)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
Opening file: /tmp/ibdata1
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
File information:

time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
ID of device containing file:         2055
total size, in bytes:             18874368 (18.000 MiB)

inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
Size to process:                  18874368 (18.000 MiB)
group ID of owner:                       0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
device ID (if special file):             0
blocksize for filesystem I/O:         4096
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
number of blocks allocated:          36920
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Opening file: /tmp/ibdata1
Size to process:                  18874368 (18.000 MiB)
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
user ID of owner:                        0
group ID of owner:                       0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
device ID (if special file):             0
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
device ID (if special file):             0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
blocksize for filesystem I/O:         4096
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)

Opening file: /tmp/ibdata1
File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Opening file: /tmp/ibdata1
File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
protection:                         100644 (regular file)
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
number of hard links:                    1
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
total size, in bytes:             18874368 (18.000 MiB)

number of blocks allocated:          36920
Size to process:                  18874368 (18.000 MiB)
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
All workers finished in 0 sec

[root@localhost recovery_MySQL]# cd pages-ibdata1
[root@localhost pages-ibdata1]# ls -l
total 16
drwxr-xr-x 2 root root 4096 Aug 20 22:53 FIL_PAGE_INDEX
drwxr-xr-x 2 root root 4096 Aug 20 22:53 FIL_PAGE_TYPE_BLOB
[root@localhost pages-ibdata1]# cd FIL_PAGE_INDEX
[root@localhost FIL_PAGE_INDEX]# ls -l
total 9924
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000001.page
-rw-r--r-- 1 root root  278528 Aug 20 22:53 0000000000000002.page
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000003.page
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000004.page
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000005.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000011.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000012.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000013.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000014.page
-rw-r--r-- 1 root root 2883584 Aug 20 22:53 0000000000000065.page
-rw-r--r-- 1 root root  475136 Aug 20 22:53 0000000000000066.page
-rw-r--r-- 1 root root  737280 Aug 20 22:53 0000000000000067.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000068.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000069.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000070.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000071.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000072.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000073.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000074.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000075.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000076.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000077.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000078.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000079.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000080.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000081.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000082.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000083.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000084.page
-rw-r--r-- 1 root root  753664 Aug 20 22:53 0000000000000085.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000086.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000087.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000088.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000089.page
-rw-r--r-- 1 root root  901120 Aug 20 22:53 0000000000000110.page
-rw-r--r-- 1 root root 2097152 Aug 20 22:53 0000000000000115.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000116.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000117.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000118.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000119.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000120.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000121.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000122.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000123.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000124.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 18446744069414584320.page
[root@localhost FIL_PAGE_INDEX]#

分析数据字典

[root@localhost recovery_MySQL]# ./recover_dictionary.sh

Generating dictionary tables dumps... OK
Creating test database ... Warning: Using a password on the command line interface can be insecure.
OK
Creating dictionary tables in database test:
SYS_TABLES ... Warning: Using a password on the command line interface can be insecure.
OK
SYS_COLUMNS ... Warning: Using a password on the command line interface can be insecure.
OK
SYS_INDEXES ... Warning: Using a password on the command line interface can be insecure.
OK
SYS_FIELDS ... Warning: Using a password on the command line interface can be insecure.
OK
All OK
Loading dictionary tables data:
SYS_TABLES ... Warning: Using a password on the command line interface can be insecure.
34 recs OK
SYS_COLUMNS ... Warning: Using a password on the command line interface can be insecure.
3061 recs OK
SYS_INDEXES ... Warning: Using a password on the command line interface can be insecure.
206 recs OK
SYS_FIELDS ... Warning: Using a password on the command line interface can be insecure.
206 recs OK
All OK

关于MySQL数据字典恢复,也可以参考另外一篇文章:使用工具直接抽取MySQL数据字典

查看数据库中记录情况

[root@localhost recovery_MySQL]# MySQL -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34954
Server version: 5.6.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL> show tables;
+----------------+
| Tables_in_test |
+----------------+
| SYS_COLUMNS    |
| SYS_FIELDS     |
| SYS_INDEXES    |
| SYS_TABLES     |
+----------------+
4 rows in set (0.00 sec)

MySQL> SELECT NAME,ID from SYS_TABLES;
+-------------------------+----+
| NAME                    | ID |
+-------------------------+----+
| cnywmcom/zx_account_log | 31 |
| cnywmcom/zx_area_code   | 32 |
| cnywmcom/zx_goods_group | 33 |
| cnywmcom/zx_manage_type | 34 |
| cnywmcom/zx_order_goods | 35 |
| cnywmcom/zx_order_info  | 36 |
| cnywmcom/zx_param       | 37 |
| cnywmcom/zx_users       | 46 |
| cnywmcom/zx_user_type   | 38 |
| SYS_FOREIGN             | 11 |
| SYS_FOREIGN_COLS        | 12 |
+-------------------------+----+
11 rows in set (0.00 sec)

MySQL> select * from SYS_INDEXES;
+----------+-----+-----------------+----------+------+-------+---------+
| TABLE_ID | ID  | NAME            | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+-----+-----------------+----------+------+-------+---------+
|       11 |  11 | ID_IND          |        1 |    3 |     0 |      46 |
|       11 |  12 | FOR_IND         |        1 |    0 |     0 |      47 |
|       11 |  13 | REF_IND         |        1 |    0 |     0 |      48 |
|       12 |  14 | ID_IND          |        2 |    3 |     0 |      49 |
|       31 |  65 | PRIMARY         |        1 |    3 |     0 |      50 |
|       31 |  66 | user_id         |        1 |    0 |     0 |     216 |
|       32 |  67 | PRIMARY         |        1 |    3 |     0 |      52 |
|       33 |  68 | PRIMARY         |        1 |    3 |     0 |     205 |
|       34 |  69 | PRIMARY         |        1 |    3 |     0 |      54 |
|       35 |  70 | PRIMARY         |        1 |    3 |     0 |      55 |
|       35 |  71 | order_id        |        1 |    0 |     0 |      56 |
|       35 |  72 | goods_id        |        1 |    0 |     0 |      57 |
|       36 |  73 | PRIMARY         |        1 |    3 |     0 |      59 |
|       36 |  74 | order_sn        |        1 |    2 |     0 |      60 |
|       36 |  75 | user_id         |        1 |    0 |     0 |      61 |
|       36 |  76 | order_status    |        1 |    0 |     0 |      62 |
|       36 |  77 | shipping_status |        1 |    0 |     0 |      63 |
|       36 |  78 | pay_status      |        1 |    0 |     0 |     192 |
|       36 |  79 | shipping_id     |        1 |    0 |     0 |     193 |
|       36 |  80 | pay_id          |        1 |    0 |     0 |     194 |
|       36 |  81 | extension_code  |        2 |    0 |     0 |     195 |
|       36 |  82 | agency_id       |        1 |    0 |     0 |     203 |
|       37 |  83 | PRIMARY         |        1 |    3 |     0 |     196 |
|       38 |  84 | PRIMARY         |        1 |    3 |     0 |     206 |
|       46 | 120 | PRIMARY         |        1 |    3 |     0 |      58 |
|       46 | 121 | user_name       |        1 |    2 |     0 |     256 |
|       46 | 122 | email           |        1 |    0 |     0 |     257 |
|       46 | 123 | parent_id       |        1 |    0 |     0 |     258 |
|       46 | 124 | flag            |        1 |    0 |     0 |     259 |
+----------+-----+-----------------+----------+------+-------+---------+
29 rows in set (0.00 sec)

通过这里，我们就可以明确的知道，我们需要恢复的表的page是多少了，这里举例说明恢复表cnywmcom.zx_account_log,我们需要去分析65号page

分析表恢复

[root@localhost recovery_MySQL]# ./c_parser -5f pages-ibdata1/FIL_PAGE_INDEX/0000000000000065.page -t dictionary/zx_account_log.sql|more
-- Page id: 223, Format: COMPACT, Records list: Invalid, Expected records: (0 7)
0B0C2000101C    55000230000000  zx_account_log  2979181 270888  "0"0"0.00"      "0.00"  "0.00"  2147483649      ""      0       ""      "0.00"  "-10643256.27"  "06."   "06."   "0782
7251.51"        "0.5"   "0.5"   "0.5"   "0.5"
000000042228    00000001040135  zx_account_log  561     1       "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436840718      "会员注册"      99      "13818289939"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"
000000042228    0000000104015A  zx_account_log  562     136     "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436841067      "会员注册"      99      "17828025855"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"
000000042228    0000000104017F  zx_account_log  563     137     "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436842239      "会员注册"      99      "18617157667"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"
000000042228    000000010401A4  zx_account_log  564     139     "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436846738      "会员注册"      99      "18113070688"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"
000000042228    000000010401C9  zx_account_log  565     1       "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436848523      "会员注册"      99      "13623646573"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"

至此该表的数据已经恢复出来,剩下任务就是把数据保存为文件,并且导入到新库中.然后依次处理相关表.至此完成这次drop database的恢复.在恢复过程中,遇到不少坑,比如有些记录在对应的page中无法找到,需要进行额外处理.对于乱码问题需要进一步处理等.通过一系列的恢复,给客户恢复了所有核心表,实现核心数据0丢失.通过这次的恢复,已经证明我们不光是Oracle恢复中的行业强者,在MySQL数据库的各种故障中,我们也有立足之地.如果有MySQL数据库的恢复(误删除数据/delete,误删除表/drop table,误截断表/truncate table,误删除数据库/drop database,MySQL不能启动,ibdata1文件损坏/丢失等MySQL各种恢复)请求,如果您遇到MySQL恢复问题无法自行解决,请联系我们提供专业服务,最大程度减小您的损失：

Phone:17813235971    Q Q:107644445    E-Mail:dba@xifenfei.com

发表在 MySQL恢复 | 标签为 , , , , , | 评论关闭

exp dmp文件损坏(坏块/corruption)恢复—跳过dmp坏块

发表于 2015 年 8 月 18 日 由 惜分飞

在有些情况下，大家都知道通过dul可以恢复损坏的dmp文件的表的数据,但是该方法有很多问题，特别是对很多数据类型的支持不够完美,比如lob,long raw类型等,而且还有可能恢复出来数据大量丢失,本人通过对dmp结构的分析,使用使用一些特殊的技巧方法,可以实现对于损坏的dmp文件,通过跳过异常坏块所在表,继续恢复后续表,从而最大程度减少损坏
创建测试表

SQL> conn xifenfei/"www.xifenfei.com"
Connected.
SQL> create table t_xifenfei1 as select * from dba_objects;

Table created.

SQL> create table t_xifenfei2 as select * from v$sql;

Table created.

SQL> create table t_xifenfei3 as select * from dba_tables;

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_XIFENFEI1                    TABLE
T_XIFENFEI2                    TABLE
T_XIFENFEI3                    TABLE

SQL> select count(*) from t_xifenfei1;

  COUNT(*)
----------
     86275

SQL>  select count(*) from t_xifenfei2;

  COUNT(*)
----------
      3387

SQL>  select count(*) from t_xifenfei3;

  COUNT(*)
----------
      2800

导出来dmp文件

[oracle@web103 ~]$ exp "'/ as sysdba'" owner=xifenfei file=/data/temp/t_xifenfei.dmp log=/data/temp/exp_t_xifenfei.log

Export: Release 11.2.0.4.0 - Production on Tue Aug 18 22:08:30 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user XIFENFEI 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user XIFENFEI 
About to export XIFENFEI's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export XIFENFEI's tables via Conventional Path ...
. . exporting table                    T_XIFENFEI1      86275 rows exported
. . exporting table                    T_XIFENFEI2       3387 rows exported
. . exporting table                    T_XIFENFEI3       2800 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

获取dmp file中的T_XIFENFEI2所在位置
计划在该表上通过dd进行破坏,因此需要事先知道该表所在的dmp文件位置范围

CPFL> SEARCH TABLE T_XIFENFEI2 FROM EXPFILE  /tmp/t_xifenfei.dmp
9980561: TABLE "T_XIFENFEI2"
9980581: CREATE TABLE "T_XIFENFEI2" (表定义忽略)
9985356: BIND information for 87 columns
 col[  1] type 1 max length 1000 cset 852 (ZHS16GBK) form 1
 col[  2] type 112 max length 86 cset 852 (ZHS16GBK) form 1
 col[  3] type 1 max length 13 cset 852 (ZHS16GBK) form 1
 col[  4] type 2 max length 22
 col[  5] type 2 max length 22
 col[  6] type 2 max length 22
 col[  7] type 2 max length 22
 col[  8] type 2 max length 22
 col[  9] type 2 max length 22
 col[ 10] type 2 max length 22
 col[ 11] type 2 max length 22
 col[ 12] type 2 max length 22
 col[ 13] type 2 max length 22
 col[ 14] type 2 max length 22
 col[ 15] type 2 max length 22
 col[ 16] type 2 max length 22
 col[ 17] type 1 max length 38 cset 852 (ZHS16GBK) form 1
 col[ 18] type 2 max length 22
 col[ 19] type 2 max length 22
 col[ 20] type 2 max length 22
 col[ 21] type 2 max length 22
 col[ 22] type 2 max length 22
 col[ 23] type 2 max length 22
 col[ 24] type 2 max length 22
 col[ 25] type 2 max length 22
 col[ 26] type 2 max length 22
 col[ 27] type 2 max length 22
 col[ 28] type 2 max length 22
 col[ 29] type 2 max length 22
 col[ 30] type 2 max length 22
 col[ 31] type 1 max length 10 cset 852 (ZHS16GBK) form 1
 col[ 32] type 2 max length 22
 col[ 33] type 23 max length 2000
 col[ 34] type 2 max length 22
 col[ 35] type 2 max length 22
 col[ 36] type 2 max length 22
 col[ 37] type 1 max length 30 cset 852 (ZHS16GBK) form 1
 col[ 38] type 2 max length 22
 col[ 39] type 23 max length 8
 col[ 40] type 23 max length 8
 col[ 41] type 2 max length 22
 col[ 42] type 2 max length 22
 col[ 43] type 2 max length 22
 col[ 44] type 2 max length 22
 col[ 45] type 1 max length 64 cset 852 (ZHS16GBK) form 1
 col[ 46] type 2 max length 22
 col[ 47] type 1 max length 64 cset 852 (ZHS16GBK) form 1
 col[ 48] type 2 max length 22
 col[ 49] type 1 max length 64 cset 852 (ZHS16GBK) form 1
 col[ 50] type 2 max length 22
 col[ 51] type 2 max length 22
 col[ 52] type 1 max length 64 cset 852 (ZHS16GBK) form 1
 col[ 53] type 2 max length 22
 col[ 54] type 2 max length 22
 col[ 55] type 2 max length 22
 col[ 56] type 23 max length 8
 col[ 57] type 2 max length 22
 col[ 58] type 1 max length 1 cset 852 (ZHS16GBK) form 1
 col[ 59] type 1 max length 19 cset 852 (ZHS16GBK) form 1
 col[ 60] type 2 max length 22
 col[ 61] type 1 max length 38 cset 852 (ZHS16GBK) form 1
 col[ 62] type 1 max length 1 cset 852 (ZHS16GBK) form 1
 col[ 63] type 1 max length 1 cset 852 (ZHS16GBK) form 1
 col[ 64] type 1 max length 1 cset 852 (ZHS16GBK) form 1
 col[ 65] type 1 max length 1 cset 852 (ZHS16GBK) form 1
 col[ 66] type 2 max length 22
 col[ 67] type 1 max length 64 cset 852 (ZHS16GBK) form 1
 col[ 68] type 1 max length 30 cset 852 (ZHS16GBK) form 1
 col[ 69] type 1 max length 30 cset 852 (ZHS16GBK) form 1
 col[ 70] type 2 max length 22
 col[ 71] type 2 max length 22
 col[ 72] type 2 max length 22
 col[ 73] type 2 max length 22
 col[ 74] type 12 max length 7
 col[ 75] type 23 max length 2000
 col[ 76] type 2 max length 22
 col[ 77] type 2 max length 22
 col[ 78] type 2 max length 22
 col[ 79] type 2 max length 22
 col[ 80] type 2 max length 22
 col[ 81] type 2 max length 22
 col[ 82] type 2 max length 22
 col[ 83] type 2 max length 22
 col[ 84] type 2 max length 22
 col[ 85] type 2 max length 22
 col[ 86] type 2 max length 22
 col[ 87] type 2 max length 22
Conventional export
9986063: start of table data
19675141: TABLE "T_XIFENFEI3"

使用dd命令破坏T_XIFENFEI2所在位置的dmp文件

[oracle@web103 ~]$ dd if=/dev/zero of=/data/temp/t_xifenfei.dmp bs=1024 count=2 conv=notrunc seek=9747
2+0 records in
2+0 records out
2048 bytes (2.0 kB) copied, 1.6e-05 seconds, 128 MB/s

尝试imp导入被破坏的dmp文件数据

[oracle@web103 ~]$ imp "'/ as sysdba'" fromuser=xifenfei touser=xifenfeinew 
>file=/data/temp/t_xifenfei.dmp log=/data/temp/imp_t_xifenfei.log

Import: Release 11.2.0.4.0 - Production on Tue Aug 18 22:35:09 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing XIFENFEI's objects into XIFENFEINEW
. . importing table                  "T_XIFENFEI1"      86275 rows imported
IMP-00017: following statement failed with ORACLE error 1740:
 "CREATE TABLE "T_XIFENFEI2" ("SQL_TEXT" VARCHAR2(1000), "SQL_FULLTEXT" CLOB,"
 " "SQL_ID" VARCHAR2(13), "SHARABLE_MEM" NUMBER, "PERSISTENT_MEM" NUMBER, "RU"
 "NTIME_MEM" NUMBER, "SORTS" NUMBER, "LOADED_VERSIONS" NUMBER, "OPEN_VERSIONS"
 "" NUMBER, "USERS_OPENING" NUMBER, "FETCHES" NUMBER, "EXECUTIONS" NUMBER, "P"
 "X_SERVERS_EXECUTIONS" NUMBER, "END_OF_FETCH_COU"
IMP-00003: ORACLE error 1740 encountered
ORA-01740: missing double quote in identifier
IMP-00008: unrecognized statement in the export file: 
  
IMP-00008: unrecognized statement in the export file:

导入报IMP-00003 ORA-01740 IMP-00008,由于dmp文件被dd破坏(而且破坏位置是T_XIFENFEI2所在之处),因此imp导入到T_XIFENFEI2之时,抛出大量异常,imp终止

检查导入表情况

SQL> conn xifenfeinew/"www.xifenfei.com"
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_XIFENFEI1                    TABLE

SQL> select count(*) from t_xifenfei1;

  COUNT(*)
----------
     86275

和预期相符,表t_xifenfei1导入进去,但是t_xifenfei2由于坏块原因未导入,由于t_xifenfei3在t_xifenfei2之后,因此也未导入

使用CPFL程序抽取正常dmp文件

CPFL>getdmp '/data/temp/t_xifenfei.dmp' skip table 'T_XIFENFEI2'
>/data/temp/t_xifenfeinew.dmp

重新导入dmp文件

[oracle@web103 ~]$ imp "'/ as sysdba'" fromuser=xifenfei touser=xifenfeinew 
>file=/data/temp/t_xifenfeinew.dmp log=/data/temp/imp_t_xifenfeinew.log

Import: Release 11.2.0.4.0 - Production on Tue Aug 18 22:41:04 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing XIFENFEI's objects into XIFENFEINEW
. . importing table                  "T_XIFENFEI1"      86275 rows imported 
. . importing table                  "T_XIFENFEI3"       2800 rows imported
Import terminated successfully no warnings.

导入了t_xifenfei1，t_xifenfei3，果然t_xifenfei2被跳过

验证导入数据

[oracle@web103 ~]$ sqlplus xifenfeinew/"www.xifenfei.com"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 18 22:41:32 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_XIFENFEI1                    TABLE
T_XIFENFEI3                    TABLE

SQL> select count(*) from t_xifenfei1;

  COUNT(*)
----------
     86275

SQL> select count(*) from t_xifenfei3;

  COUNT(*)
----------
      2800

SQL>

通过验证数据证明，通过CPFL完美跳过了坏块所在表,实现后续数据完美恢复

发表在 Oracle | 标签为 , , , , , , | 评论关闭