标签归档：mysql drop table 恢复

[MySQL异常恢复]mysql ibd文件恢复

发表于 2016 年 12 月 25 日 由 惜分飞

在mysql中由于某种原因保存有ibd文件，但是表已经被删除或者frm文件损坏亦或者ibdata文件损坏/丢失等。本文模拟在这种情况下，通过mysql自身技术即可完成ibd文件恢复.
测试环境mysql版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.25    |
+-----------+
1 row in set (0.00 sec)

mysql主要参数

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_force_recovery';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_force_recovery | 0     |
+-----------------------+-------+
1 row in set (0.00 sec)

innodb_file_per_table这个参数为on才能够实现每个表存储单独的ibd文件.innodb_force_recovery参数默认范围0

测试表情况

mysql> use xifenfei;
Database changed
mysql> show tables;
+-----------------------------+
| Tables_in_xifenfei          |
+-----------------------------+
| user_login                  |
+-----------------------------+
1 rows in set (0.00 sec)

mysql> select count(*) from user_login;
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.02 sec)

mysql> desc user_login;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| ID         | varchar(255) | NO   | PRI | NULL    |       |
| ACCOUNT    | varchar(255) | YES  |     | NULL    |       |
| LifeCycle  | int(11)      | YES  |     | NULL    |       |
| Name       | varchar(255) | YES  |     | NULL    |       |
| Password   | varchar(255) | YES  |     | NULL    |       |
| Role       | varchar(255) | YES  |     | NULL    |       |
| UTime      | varchar(255) | YES  |     | NULL    |       |
| UserID     | varchar(255) | YES  |     | NULL    |       |
| UserName   | varchar(255) | YES  |     | NULL    |       |
| UserStatus | int(11)      | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
10 rows in set (0.05 sec)

mysql> select * from user_login limit 1;
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
| ID                               | ACCOUNT | LifeCycle | Name      | Password
                        | Role | UTime               | UserID
        | UserName | UserStatus |
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
| 010d6c85a76c44cba80d07cbd8590bb2 | hyh     |         0 | 胡元会    | 698d51a19
d8a121ce581499d7b701668 | |6|  | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065e
d5cfee8 | NULL     |          1 |
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
1 row in set (0.00 sec)

mysql> show create table user_login \G;
*************************** 1. row *************
       Table: user_login
Create Table: CREATE TABLE `user_login` (
  `ID` varchar(255) NOT NULL,
  `ACCOUNT` varchar(255) DEFAULT NULL,
  `LifeCycle` int(11) DEFAULT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `Password` varchar(255) DEFAULT NULL,
  `Role` varchar(255) DEFAULT NULL,
  `UTime` varchar(255) DEFAULT NULL,
  `UserID` varchar(255) DEFAULT NULL,
  `UserName` varchar(255) DEFAULT NULL,
  `UserStatus` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> show variables like 'datadir';
+---------------+-----------------------------------------------+
| Variable_name | Value                                         |
+---------------+-----------------------------------------------+
| datadir       | D:\xifenfei\mysql-5.6.25-winx64\data\ |
+---------------+-----------------------------------------------+
1 row in set (0.00 sec)

备份ibd文件

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

2016-12-02  20:07            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,789,591,040 可用字节
C:\Users\XIFENFEI>cp D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd d:/
C:\Users\XIFENFEI>dir d:\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 d:\ 的目录

2016-12-25  23:15            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,789,591,040 可用字节

模拟删除表(ibd文件也被删除)

mysql> drop table xifenfei.user_login;
Query OK, 0 rows affected (0.03 sec)


C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

找不到文件

创建新表

mysql> CREATE TABLE `user_login` (
    ->   `ID` varchar(255) NOT NULL,
    ->   `ACCOUNT` varchar(255) DEFAULT NULL,
    ->   `LifeCycle` int(11) DEFAULT NULL,
    ->   `Name` varchar(255) DEFAULT NULL,
    ->   `Password` varchar(255) DEFAULT NULL,
    ->   `Role` varchar(255) DEFAULT NULL,
    ->   `UTime` varchar(255) DEFAULT NULL,
    ->   `UserID` varchar(255) DEFAULT NULL,
    ->   `UserName` varchar(255) DEFAULT NULL,
    ->   `UserStatus` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

2016-12-25  23:19            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,789,591,040 可用字节

mysql> select count(*) from xifenfei.user_login;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

停掉mysql,替换user_login.ibd

C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

2016-12-25  23:22            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,787,141,632 可用字节

C:\Users\XIFENFEI>cp d:\user_login.ibd D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
C:\Users\XIFENFEI>dir D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei\user_login.ibd
 驱动器 D 中的卷没有标签。
 卷的序列号是 4215-1F18

 D:\xifenfei\mysql-5.6.25-winx64\data\xifenfei 的目录

2016-12-02  20:07            98,304 user_login.ibd
               1 个文件         98,304 字节
               0 个目录 78,787,141,632 可用字节

启动mysql 服务,查询数据库

mysql> select count(*) from xifenfei.user_login;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> exit
Bye

C:\Users\XIFENFEI>mysql -uroot
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

mysql 日志报错

2016-12-25 23:31:07 11632 [Note] MySQL: ready for connections.
Version: '5.6.25'  socket: ''  port: 3306  MySQL Community Server (GPL)
InnoDB: Error: tablespace id is 56 in the data dictionary
InnoDB: but in file .\xifenfei\user_login.ibd it is 47!
2016-12-25 23:31:31 2eb8  InnoDB: Assertion failure in thread 11960 in file fil0fil.cc line 796
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be

很明显由于替换的ibd文件和现在数据库记录的ibd文件的page的字典信息不匹配，因为数据库无法正常查询该数据，而且mysql为了安全直接把实例给crash了.

恢复操作

mysql> show variables like 'innodb_force_recovery';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_force_recovery | 1     |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> alter table xifenfei.user_login discard tablespace;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> alter table xifenfei.user_login import tablespace;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> select count(*) from xifenfei.user_login;
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.00 sec)

mysql> select * from xifenfei.user_login limit 1;
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
| ID                               | ACCOUNT | LifeCycle | Name      | Password
                        | Role | UTime               | UserID
        | UserName | UserStatus |
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
| 010d6c85a76c44cba80d07cbd8590bb2 | hyh     |         0 | 胡元会    | 698d51a19
d8a121ce581499d7b701668 | |6|  | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065e
d5cfee8 | NULL     |          1 |
+----------------------------------+---------+-----------+-----------+----------
------------------------+------+---------------------+--------------------------
--------+----------+------------+
1 row in set (0.00 sec)

通过mysql自带的discard tablespace和import tablespace操作后,表数据已经可以完成查询了.
mysql日志

2016-12-25 23:34:08 10464 [ERROR] InnoDB: Failed to find tablespace for table '"xifenfei"."user_login"' in the cache. Attempting to load the tablespace with space id 56.
2016-12-25 23:34:08 10464 [ERROR] InnoDB: In file '.\xifenfei\user_login.ibd', tablespace id and flags are 47 and 0, but in the InnoDB data dictionary they are 56 and 0. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2016-12-25 23:34:08 10464 [ERROR] InnoDB: Could not find a valid tablespace file for 'xifenfei/user_login'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2016-12-25 23:34:08 30e8 InnoDB: cannot calculate statistics for table "xifenfei"."user_login" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2016-12-25 23:34:08 10464 [ERROR] InnoDB: Cannot delete tablespace 56 because it is not found in the tablespace memory cache.
2016-12-25 23:34:08 10464 [Warning] InnoDB: Cannot delete tablespace 56 in DISCARD TABLESPACE. Tablespace not found
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk - done!
2016-12-25 23:34:41 10464 [Note] InnoDB: Phase I - Update all pages
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk - done!
2016-12-25 23:34:41 10464 [Warning] InnoDB: Tablespace 'xifenfei/user_login' exists in the cache with id 47 != 56
2016-12-25 23:34:41 10464 [Warning] InnoDB: Freeing existing tablespace 'xifenfei/user_login' entry from the cache with id 56
2016-12-25 23:34:41 10464 [Note] InnoDB: Phase III - Flush changes to disk
2016-12-25 23:34:41 10464 [Note] InnoDB: Phase IV - Flush complete

mysql日志依旧报了page字典信息不匹配.但是数据已经可以访问,通过mysqldump导出重新创建表即可.如果由于ibd损坏使用该方法无法恢复,请参考:MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)

发表在 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恢复 | 标签为 , , , , , | 评论关闭