分类目录归档：MySQL恢复

[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异常恢复]mysql drop table 数据恢复

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

对于MySQL数据库的innodb引擎的数据库中,由于误操作删除表,或者由于sqldump自动生成语句含drop table create table语句导致数据丢失,在没有覆盖的情况下,可以实现完美恢复
创建测试表

mysql> CREATE TABLE recover.`t_drop` (
    ->   `messageId` varchar(30) NOT NULL,
    ->   `msgContent` varchar(1000) default NULL,
    ->   `scheduleDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    ->   `deliverState` int(1) default NULL,
    ->   PRIMARY KEY  (`messageId`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into recover.t_drop select messageId,msgContent,scheduleDate,deliverState from sms_send_record;
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from recover.`t_drop`;
+--------------------+----------------------------------------------------------------------------------+---------------------+--------------+
| messageId          | msgContent                                                                       | scheduleDate        | deliverState |
+--------------------+----------------------------------------------------------------------------------+---------------------+--------------+
| 10235259536125650  | 尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 10235353811295807  | 尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 102354211240398235 | 尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 102354554052884567 | 尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 132213454294519126 | 尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 82329022242584577  | 尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 82329022242584581  | 尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 8233400415607376   | 尊敬的用户您好：您的手机验证码为338470如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 82334502212106951  | 尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 82339012756833423  | 尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 8234322198577796   | 尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥斯卡客服：400-620-757    | 2010-01-01 00:00:00 |            0 | 
+--------------------+----------------------------------------------------------------------------------+---------------------+--------------+
11 rows in set (0.00 sec)

mysql> checksum table t_drop;
+-----------------+-----------+
| Table           | Checksum  |
+-----------------+-----------+
| recover.t_drop  | 920719058 | 
+-----------------+-----------+
1 row in set (0.00 sec)

删除测试表,后续用来恢复

mysql> drop table recover.t_drop;
Query OK, 0 rows affected (0.00 sec)

解析ibdata文件

[root@web103 mysql_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1 
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440855928 Sat Aug 29 21:45:28 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440855928 Sat Aug 29 21:45:28 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Worker(1): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(0): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(4): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(3): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(2): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(7): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(5): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(6): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
All workers finished in 1 sec

恢复mysql字典

[root@web103 mysql_recovery]# ./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 ... 162 recs OK
SYS_COLUMNS ... 1247 recs OK
SYS_INDEXES ... 216 recs OK
SYS_FIELDS ... 411 recs OK
All OK
[root@web103 mysql_recovery]# mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10162
Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, 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> select * from SYS_TABLES WHERE name like 'recover%';
+-------------------------+-----+--------+------+--------+---------+--------------+-------+
| NAME                    | ID  | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+-------------------------+-----+--------+------+--------+---------+--------------+-------+
| recover/#sql-64a9_1f6d  | 120 |      8 |    1 |      0 |       0 |              |     0 | 
| recover/#sql2-64a9-1f6d | 115 |      8 |    1 |      0 |       0 |              |     0 | 
| recover/t_delete        |  74 |      2 |    1 |      0 |       0 |              |     0 | 
| recover/t_delete1       |  84 |      2 |    1 |      0 |       0 |              |     0 | 
| recover/t_drop          | 125 |      4 |    1 |      0 |       0 |              |     0 | 
| recover/t_truncate      | 120 |      8 |    1 |      0 |       0 |              |     0 | 
| recover/t_xifenfei      |  75 |      2 |    1 |      0 |       0 |              |     0 | 
| recover/zx_users        |  89 |     85 |    1 |      0 |       0 |              |     0 | 
+-------------------------+-----+--------+------+--------+---------+--------------+-------+
8 rows in set (0.00 sec)

mysql> select * from SYS_INDEXES WHERE TABLE_ID=125;
+----------+-----+---------+----------+------+-------+------------+
| TABLE_ID | ID  | NAME    | N_FIELDS | TYPE | SPACE | PAGE_NO    |
+----------+-----+---------+----------+------+-------+------------+
|      125 | 142 | PRIMARY |        1 |    3 |     0 | 4294967295 | 
+----------+-----+---------+----------+------+-------+------------+
1 row in set (0.00 sec)

恢复被删除表记录

[root@web103 mysql_recovery]# ./c_parser -5f pages-ibdata1/FIL_PAGE_INDEX/0000000000000142.page  -t dictionary/t_drop.sql >dumps/default/t_drop 2>2.sql
[root@web103 mysql_recovery]# more dumps/default/t_drop
-- Page id: 9860, Format: COMPACT, Records list: Valid, Expected records: (11 11)
00000099F9F2    80000026800110  t_drop  "10235259536125650"     "尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800129  t_drop  "10235353811295807"     "尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800142  t_drop  "102354211240398235"    "尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    8000002680015C  t_drop  "102354554052884567"    "尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800176  t_drop  "132213454294519126"    "尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800190  t_drop  "82329022242584577"     "尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001A9  t_drop  "82329022242584581"     "尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001C2  t_drop  "8233400415607376"      "尊敬的用户您好：您的手机验证码为338470如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001DA  t_drop  "82334502212106951"     "尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001F3  t_drop  "82339012756833423"     "尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    8000002680020C  t_drop  "8234322198577796"      "尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥斯卡客服：400-620-7575。"  "2010-01-01 00:00:00"   0
-- Page id: 9860, Found records: 11, Lost records: NO, Leaf page: YES

恢复数据入库

mysql> source dictionary/t_drop.sql
Query OK, 0 rows affected (0.00 sec)

mysql> source 2.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 11 rows affected (0.00 sec)
Records: 11  Deleted: 0  Skipped: 0  Warnings: 0

mysql> checksum table t_drop;
+-----------------+-----------+
| Table           | Checksum  |
+-----------------+-----------+
| recover.t_drop  | 920719058 | 
+-----------------+-----------+
1 row in set (0.00 sec)

至此实现删除数据完美恢复

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

[MySQL异常恢复]无主键情况下innodb数据恢复

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

在mysql的innodb引擎的数据库异常恢复中,一般都要求有主键或者唯一index,其实这个不是必须的,当没有index信息之时,可以在整个表级别的index_id进行恢复
创建模拟表—无主键

mysql>  CREATE TABLE `t1` (
    ->   `messageId` varchar(30) character set utf8 NOT NULL,
    ->   `tokenId` varchar(20) character set utf8 NOT NULL,
    ->   `mobile` varchar(14) character set utf8 default NULL,
    ->   `msgFormat` int(1) NOT NULL,
    ->   `msgContent` varchar(1000) character set utf8 default NULL,
    ->   `scheduleDate` timestamp NOT NULL default '0000-00-00 00:00:00',
    ->   `deliverState` int(1) default NULL,
    ->   `deliverdTime` timestamp NOT NULL default '0000-00-00 00:00:00'
    -> ) ENGINE=INnodb DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 select * from sms_service.sms_send_record;
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0
…………
mysql> insert into t1 select * from t1;
Query OK, 81664 rows affected (2.86 sec)
Records: 81664  Duplicates: 0  Warnings: 0

mysql> insert into t1 select * from t1;
Query OK, 163328 rows affected (2.74 sec)
Records: 163328  Duplicates: 0  Warnings: 0

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   326656 | 
+----------+
1 row in set (0.15 sec)

解析innodb文件

[root@web103 mysql_recovery]# rm -rf pages-ibdata1/
[root@web103 mysql_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1 
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
Opening file: /var/lib/mysql/ibdata1
File information:

time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
(regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
time of last access:            1440819443 Sat Aug 29 11:37:23 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         463312
time of last access:            1440819465 Sat Aug 29 11:37:45 2015
time of last modification:      1440819463 Sat Aug 29 11:37:43 2015
time of last status change:     1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
All workers finished in 0 sec

恢复数据字典

[root@web103 mysql_recovery]# ./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 ... 48 recs OK
SYS_COLUMNS ... 397 recs OK
SYS_INDEXES ... 67 recs OK
SYS_FIELDS ... 89 recs OK
All OK

分析数据字典,找出来index_id
这里需要注意对于没有主键的表恢复,我们对应的类型是GEN_CLUST_INDEX

mysql> select * from SYS_TABLES where name='test/t1';
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
| NAME                                   | ID  | N_COLS      | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
| test/t1                                | 100 |           8 |    1 |      0 |       0 |              |     0 | 
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
40 rows in set (0.00 sec)

mysql> SELECT * FROM SYS_INDEXES where table_id=100;
+----------+-----+------------------------------+----------+------+-------+------------+
| TABLE_ID | ID  | NAME                         | N_FIELDS | TYPE | SPACE | PAGE_NO    |
+----------+-----+------------------------------+----------+------+-------+------------+
|      100 | 119 | GEN_CLUST_INDEX              |        0 |    1 |     0 |       2951 | 
+----------+-----+------------------------------+----------+------+-------+------------+
67 rows in set (0.00 sec)

恢复数据

root@web103 mysql_recovery]# ./c_parser -5f pages-ibdata1/FIL_PAGE_INDEX/0000000000000119.page  -t dictionary/t1.sql >/tmp/2.txt 2>2.sql
[root@web103 mysql_recovery]# more /tmp/2.txt
-- Page id: 10848, Format: COMPACT, Records list: Valid, Expected records: (73 73)
00000002141B    0000009924F2    80000027133548  t1      "82334502212106951"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
00000002141C    0000009924F2    80000027133558  t1      "82339012756833423"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
00000002141D    0000009924F2    80000027133568  t1      "8234322198577796"      "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
00000002141E    0000009924F2    80000027133578  t1      "10235259536125650"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
00000002141F    0000009924F2    80000027133588  t1      "10235353811295807"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
000000021420    0000009924F2    80000027133598  t1      "102354211240398235"    "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
000000021421    0000009924F2    800000271335A8  t1      "102354554052884567"    "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
000000021422    0000009924F2    800000271335B8  t1      "132213454294519126"    "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "1970-01-01 07:00:00"
000000021423    0000009924F2    800000271335C8  t1      "82329022242584577"     "SDK-BBX-010-18681"     "13718311436"   8       "尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥
斯卡客服：400-620-7575。"       "2010-01-01 00:00:00"   0       "2015-08-26 22:02:17"
…………
[root@web103 mysql_recovery]# cat /tmp/2.txt|grep  -v "Page id:"|wc -l
380731

因为没有主键,使得恢复出来记录可能有一些重复,整体而言,可以较为完美的恢复数据

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