联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有客户mysql数据库被加密,加密信息如下
由于是mysql 5.6的版本,默认情况innodb引擎,Innodb_file_per_table参数默认为true,因此数据为每个表存在为一个单独的ibd文件中,让客户提供需要恢复的表的ibd被加密文件
通过一系列底层操作,实现数据完美恢复
如果是Innodb_file_per_table参数为false(5.6之前版本默认为false),需要通过ibdata文件进行恢复
如果您的数据库(oracle,mysql sql server)不幸被比特币加密,可以联系我们
Tel/微信:17813235971 Q Q:107644445 
E-Mail:dba@xifenfei.com提供专业的解密恢复服务.
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在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)
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：[MySQL异常恢复]mysql drop table 数据恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
对于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)
至此实现删除数据完美恢复
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：[MySQL异常恢复]无主键情况下innodb数据恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在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
因为没有主键,使得恢复出来记录可能有一些重复,整体而言,可以较为完美的恢复数据
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：[MySQL异常恢复]mysql delete 数据恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在mysql(innodb引擎)中,有些时候犹豫误操作导致表中数据被删除,从而导致不可挽回的损失,本文模拟在数据库被误delete的情况下,实现较为完美删除,当然在实际中可能有少量不覆盖或者无法恢复回来,但是在覆盖不多或者未覆盖的情况下,可以实现绝大多数甚至全部恢复.因此在发生误操作时候,应当第一时间保护现场,尽可能防止复写导致不可挽回的损失.在测试恢复过程中,由于mysql和操作系统编码问题,折腾了很久，感谢Lunar的指点
创建模拟表并插入数据
mysql> CREATE TABLE `sms_send_record_del` (
-> `messageId` varchar(30) NOT NULL,
-> `tokenId` varchar(20) NOT NULL,
-> `mobile` varchar(14) default NULL,
-> `msgFormat` int(1) NOT NULL,
-> `msgContent` varchar(1000) default NULL,
-> `scheduleDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
-> `deliverState` int(1) default NULL,
-> `deliverdTime` timestamp NOT NULL default '0000-00-00 00:00:00',
-> PRIMARY KEY (`messageId`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into sms_send_record_del select * from sms_send_record;
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> checksum table sms_send_record_del;
+---------------------------------+------------+
| Table | Checksum |
+---------------------------------+------------+
| sms_service.sms_send_record_del | 2258631583 |
+---------------------------------+------------+
1 row in set (0.00 sec)
mysql> checksum table sms_send_record;
+-----------------------------+------------+
| Table | Checksum |
+-----------------------------+------------+
| sms_service.sms_send_record | 2258631583 |
+-----------------------------+------------+
1 row in set (0.00 sec)
确定innodb文件对应位置
mysql> SHOW VARIABLES LIKE 'datadir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'innodb_data_file_path'; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:10M:autoextend | +-----------------------+------------------------+ 1 row in set (0.00 sec)
删除表记录
模拟错误操作,误删除表所有数据
mysql> delete from sms_send_record_del; Query OK, 11 rows affected (0.00 sec)
解析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: 315712 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.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: 315712 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.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: 315712 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.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: 315712 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.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: 315712 Opening file: /var/lib/mysql/ibdata1 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 File information: total size, in bytes: 161480704 (154.000 MiB) ID of device containing file: 2049 Size to process: 161480704 (154.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: 315712 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.000 MiB) Opening file: /var/lib/mysql/ibdata1 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 File information: blocksize for filesystem I/O: 4096 number of blocks allocated: 315712 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: 315712 time of last access: 1440599559 Wed Aug 26 22:32:39 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.000 MiB) time of last access: 1440601884 Wed Aug 26 23:11:24 2015 time of last modification: 1440601853 Wed Aug 26 23:10:53 2015 time of last status change: 1440601853 Wed Aug 26 23:10:53 2015 total size, in bytes: 161480704 (154.000 MiB) Size to process: 161480704 (154.000 MiB) All workers finished in 0 sec
分析数据字典
mysql> show tables
-> ;
+----------------+
| Tables_in_test |
+----------------+
| SYS_COLUMNS |
| SYS_FIELDS |
| SYS_INDEXES |
| SYS_TABLES |
+----------------+
4 rows in set (0.00 sec)
mysql> select * from SYS_TABLES;
+----------------------------------------+----+-------------+------+--------+---------+--------------+-------+
| NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+----------------------------------------+----+-------------+------+--------+---------+--------------+-------+
| recover/t_delete | 74 | 2 | 1 | 0 | 0 | | 0 |
| recover/t_delete1 | 84 | 2 | 1 | 0 | 0 | | 0 |
| recover/t_xifenfei | 75 | 2 | 1 | 0 | 0 | | 0 |
| recover/zx_users | 89 | 85 | 1 | 0 | 0 | | 0 |
| sms_service/sms_send_record | 36 | 8 | 1 | 0 | 0 | | 0 |
| sms_service/sms_send_record_del | 90 | 8 | 1 | 0 | 0 | | 0 |
| SYS_FOREIGN | 11 | -2147483644 | 1 | 0 | 0 | | 0 |
| SYS_FOREIGN_COLS | 12 | -2147483644 | 1 | 0 | 0 | | 0 |
| test/SYS_COLUMNS | 86 | 7 | 1 | 0 | 0 | | 0 |
| test/SYS_FIELDS | 88 | 3 | 1 | 0 | 0 | | 0 |
| test/SYS_INDEXES | 87 | 7 | 1 | 0 | 0 | | 0 |
| test/SYS_TABLES | 85 | 8 | 1 | 0 | 0 | | 0 |
| test/zx_users | 43 | 85 | 1 | 0 | 0 | | 0 |
| xifenfei/t_delete | 44 | 8 | 1 | 0 | 0 | | 0 |
| xifenfei/t_xifenfei | 59 | 2 | 1 | 0 | 0 | | 0 |
+----------------------------------------+----+-------------+------+--------+---------+--------------+-------+
39 rows in set (0.00 sec)
mysql> select * from SYS_INDEXES WHERE TABLE_ID=90;
+----------+-----+---------+----------+------+-------+---------+
| TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+-----+---------+----------+------+-------+---------+
| 90 | 110 | PRIMARY | 1 | 3 | 0 | 2955 |
+----------+-----+---------+----------+------+-------+---------+
1 row in set (0.00 sec)
找回被删除记录
[root@web103 mysql_recovery]# ./c_parser -5Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000110.page \ [root@web103 mysql_recovery]# -t dictionary/sms_send_record_del.sql >/tmp/t_1.txt 2>/tmp/t_1.sql
加载数据并验证
mysql> use sms_service; 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> source /tmp/t_1.sql Query OK, 0 rows affected (0.00 sec) Query OK, 11 rows affected, 8 warnings (0.01 sec) Records: 11 Deleted: 0 Skipped: 0 Warnings: 8 mysql> checksum table sms_send_record_del; +---------------------------------+------------+ | Table | Checksum | +---------------------------------+------------+ | sms_service.sms_send_record_del | 2258631583 | +---------------------------------+------------+ 1 row in set (0.00 sec)
发生误操作之时,请尽可能保护现场,防止覆盖导致无可挽回的损失.
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在以前的文章中说过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结构如下图所示
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出现问题,或者误操作之时提供恢复处理.
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
昨天晚上接到一个网络服务请求，由于不小心点击了自己产品软件上面的清空数据功能(这个工具确实需要小心,在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
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：[MySQL异常恢复]使用工具直接抽取MySQL数据字典
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
熟悉Oracle的朋友都知道,当Oracle数据库无法正常启动之时,可以通过dul或者其他三方工具直接读取数据文件中数据,从而来抢救数据,减少损失,在mysql中如果使用了innodb引擎也可以在mysql数据库不启动启动下抽取相关记录.本文为抽取数据字典篇章,后续将继续提供drop恢复,truncate 恢复,delete恢复等mysql非常规恢复篇章.
创建一张get_dict测试表
mysql> use xifenfei;
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_xifenfei |
+--------------------+
| t_delete |
+--------------------+
1 row in set (0.00 sec)
mysql> create table get_dict(id int not null primary key,name varchar(100));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into get_dict value(1,'www.xifenfei.com');
Query OK, 1 row affected (0.00 sec)
mysql> insert into get_dict value(2,'www.xifenfei.com-xifenfei');
Query OK, 1 row affected (0.00 sec)
mysql> insert into get_dict value(3,'xifenfei-www.xifenfei.com');
Query OK, 1 row affected (0.00 sec)
mysql> show tables;
+--------------------+
| Tables_in_xifenfei |
+--------------------+
| get_dict |
| t_delete |
+--------------------+
2 rows in set (0.00 sec
mysql> select TABLE_NAME,TABLE_SCHEMA,TABLE_TYPE from information_schema.tables
-> where table_name='get_dict';
+------------+--------------+------------+
| TABLE_NAME | TABLE_SCHEMA | TABLE_TYPE |
+------------+--------------+------------+
| get_dict | xifenfei | BASE TABLE |
+------------+--------------+------------+
1 row in set (0.01 sec)
mysql> select TABLE_NAME,NON_UNIQUE,TABLE_SCHEMA,INDEX_SCHEMA,INDEX_NAME,COLUMN_NAME from
-> INFORMATION_SCHEMA.STATISTICS where TABLE_NAME='get_dict';
+------------+------------+--------------+--------------+------------+-------------+
| TABLE_NAME | NON_UNIQUE | TABLE_SCHEMA | INDEX_SCHEMA | INDEX_NAME | COLUMN_NAME |
+------------+------------+--------------+--------------+------------+-------------+
| get_dict | 0 | xifenfei | xifenfei | PRIMARY | id |
+------------+------------+--------------+--------------+------------+-------------+
1 row in set (0.00 sec)
mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION from
-> information_schema.COLUMNS where table_name='get_dict';
+--------------+------------+-------------+------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION |
+--------------+------------+-------------+------------------+
| xifenfei | get_dict | id | 1 |
| xifenfei | get_dict | name | 2 |
+--------------+------------+-------------+------------------+
2 rows in set (0.01 sec)
关闭mysql数据库
[root@localhost recovery_mysql]# service mysql stop Shutting down MySQL..[ OK ] [root@localhost recovery_mysql]# ps -ef|grep mysql root 18876 15827 0 18:05 pts/1 00:00:00 grep mysql
使用工具解析innodb文件
[root@localhost recovery_mysql]# ./stream_parser -f /var/lib/mysql/ibdata1 Opening file: /var/lib/mysql/ibdata1 File information: Opening file: /var/lib/mysql/ibdata1 File information: ID of device containing file: 2054 inode number: 1782889 ID of device containing file: 2054 protection: 100660 inode number: 1782889 (regular file) protection: 100660 number of hard links: 1 (regular file) user ID of owner: 101 number of hard links: 1 group ID of owner: 102 user ID of owner: 101 device ID (if special file): 0 group ID of owner: 102 blocksize for filesystem I/O: 4096 device ID (if special file): 0 number of blocks allocated: 24616 blocksize for filesystem I/O: 4096 Opening file: /var/lib/mysql/ibdata1 number of blocks allocated: 24616 File information: ……………… user ID of owner: 101 group ID of owner: 102 device ID (if special file): 0 blocksize for filesystem I/O: 4096 ID of device containing file: 2054 number of blocks allocated: 24616 inode number: 1782889 protection: 100660 (regular file) number of hard links: 1 user ID of owner: 101 group ID of owner: 102 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 24616 time of last access: 1417922668 Sun Dec 7 11:24:28 2014 time of last modification: 1418294104 Thu Dec 11 18:35:04 2014 time of last status change: 1418294104 Thu Dec 11 18:35:04 2014 time of last access: 1417922668 Sun Dec 7 11:24:28 2014 total size, in bytes: 12582912 (12.000 MiB) time of last modification: 1418294104 Thu Dec 11 18:35:04 2014 time of last status change: 1418294104 Thu Dec 11 18:35:04 2014 Size to process: 12582912 (12.000 MiB) total size, in bytes: 12582912 (12.000 MiB) Size to process: 12582912 (12.000 MiB) All workers finished in 0 sec
主要文件介绍
[root@localhost recovery_mysql]# ls -l pages-ibdata1/FIL_PAGE_INDEX/ total 1388 -rw-r--r-- 1 root root 16384 Dec 11 18:51 0000000000000001.page -rw-r--r-- 1 root root 16384 Dec 11 18:51 0000000000000002.page -rw-r--r-- 1 root root 49152 Dec 11 18:51 0000000000000003.page -rw-r--r-- 1 root root 49152 Dec 11 18:51 0000000000000004.page -rw-r--r-- 1 root root 16384 Dec 11 18:51 0000000000000005.page -rw-r--r-- 1 root root 114688 Dec 11 18:51 0000000000000011.page -rw-r--r-- 1 root root 114688 Dec 11 18:51 0000000000000012.page -rw-r--r-- 1 root root 114688 Dec 11 18:51 0000000000000013.page -rw-r--r-- 1 root root 114688 Dec 11 18:51 0000000000000014.page -rw-r--r-- 1 root root 114688 Dec 11 18:51 0000000000000015.page -rw-r--r-- 1 root root 147456 Dec 11 18:51 0000000000000016.page -rw-r--r-- 1 root root 98304 Dec 11 18:51 0000000000000017.page -rw-r--r-- 1 root root 114688 Dec 11 18:51 0000000000000018.page -rw-r--r-- 1 root root 49152 Dec 11 18:51 0000000000000019.page -rw-r--r-- 1 root root 49152 Dec 11 18:51 0000000000000020.page -rw-r--r-- 1 root root 49152 Dec 11 18:51 0000000000000021.page -rw-r--r-- 1 root root 65536 Dec 11 18:51 0000000000000025.page -rw-r--r-- 1 root root 16384 Dec 11 18:51 18446744069414584320.page
0000000000000001.page主要是记录mysql中表信息文件
0000000000000002.page主要是记录mysql中的表的列的信息文件
0000000000000003.page主要是记录mysql中表的index信息文件
抽取table数据
[root@localhost recovery_mysql]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql > dumps/default/SYS_TABLES 2> dumps/default/SYS_TABLES.sql [root@localhost recovery_mysql]# grep get dumps/default/SYS_TABLES | head -5 000000000D1D 95000001510110 SYS_TABLES "xifenfei/get\_dict" 23 2 1 0 80 "" 9 [root@localhost recovery_mysql]# cat dumps/default/SYS_TABLES.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/tmp/recovery_mysql/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);
抽取column数据
[root@localhost recovery_mysql]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql > dumps/default/SYS_COLUMNS 2> dumps/default/SYS_COLUMNS.sql [root@localhost recovery_mysql]# cat dumps/default/SYS_COLUMNS -- Page id: 10, Format: REDUNDANT, Records list: Valid, Expected records: (115 115) 000000000300 800000012D0123 SYS_COLUMNS 11 0 "ID" 1 4 0 0 000000000300 800000012D0138 SYS_COLUMNS 11 1 "FOR\_NAME" 1 4 0 0 ………… 000000000D1D 95000001510129 SYS_COLUMNS 23 0 "id" 6 1283 4 0 000000000D1D 9500000151013E SYS_COLUMNS 23 1 "name" 1 524303 100 0 -- Page id: 10, Found records: 115, Lost records: NO, Leaf page: YES [root@localhost recovery_mysql]# more dumps/default/SYS_COLUMNS.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/tmp/recovery_mysql/dumps/default/SYS_COLUMNS' REPLACE INTO TABLE `SYS_COLUMNS` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_COLUMNS\t' (`TABLE_ID`, `POS`, `NAME`, `MTYPE`, `PRTYPE`, `LEN`, `PREC`);
抽取index数据
[root@localhost recovery_mysql]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql > dumps/default/SYS_INDEXES 2> dumps/default/SYS_INDEXES.sql [root@localhost recovery_mysql]# more dumps/default/SYS_INDEXES.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/tmp/recovery_mysql/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`); [root@localhost recovery_mysql]# more dumps/default/SYS_INDEXES -- Page id: 11, Format: REDUNDANT, Records list: Valid, Expected records: (13 13) 000000000300 800000012D0177 SYS_INDEXES 11 11 "ID\_IND" 1 3 0 302 ………… 000000000B02 820000013504C8 SYS_INDEXES 20 22 "GEN\_CLUST\_INDEX" 0 1 6 3 000000000D1D 9500000151016B SYS_INDEXES 23 25 "PRIMARY" 1 3 9 3
启动mysql数据库
[root@localhost recovery_mysql]# service mysql start Starting MySQL..[ OK ] [root@localhost recovery_mysql]# ps -ef|grep mysql root 18948 1 0 19:57 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/localhost.localdomain.pid mysql 19049 18948 14 19:57 pts/1 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/localhost.localdomain.err --pid-file=/var/lib/mysql/localhost.localdomain.pid root 19078 15827 0 19:58 pts/1 00:00:00 grep mysql
创建抽取数据字典表
mysql> source dictionary/SYS_TABLES.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ………… mysql> source dictionary/SYS_INDEXES.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ………… mysql> source dictionary/SYS_COLUMNS.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ………… mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | SYS_COLUMNS | | SYS_INDEXES | | SYS_TABLES | +----------------+ 3 rows in set (0.00 sec)
加载抽取数据字典数据
mysql> source dumps/default/SYS_TABLES.sql Query OK, 0 rows affected (0.00 sec) Query OK, 11 rows affected (0.03 sec) Records: 11 Deleted: 0 Skipped: 0 Warnings: 0 mysql> source dumps/default/SYS_INDEXES.sql Query OK, 0 rows affected (0.00 sec) Query OK, 39 rows affected (0.01 sec) Records: 39 Deleted: 0 Skipped: 0 Warnings: 0 mysql> source dumps/default/SYS_COLUMNS.sql Query OK, 0 rows affected (0.00 sec) Query OK, 115 rows affected (0.00 sec) Records: 115 Deleted: 0 Skipped: 0 Warnings: 0
验证抽取数据字典数据
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)
mysql> SELECT NAME,ID from SYS_TABLES WHERE NAME='xifenfei/get_dict';
+-------------------+----+
| NAME | ID |
+-------------------+----+
| xifenfei/get_dict | 23 |
+-------------------+----+
1 row in set (0.00 sec)
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)
mysql> SELECT TABLE_ID,NAME,MTYPE FROM SYS_COLUMNS WHERE TABLE_ID=23;
+----------+------+-------+
| TABLE_ID | NAME | MTYPE |
+----------+------+-------+
| 23 | id | 6 |
| 23 | name | 1 |
+----------+------+-------+
2 rows in set (0.01 sec)
mysql> SELECT TABLE_ID,ID,NAME,TYPE FROM SYS_INDEXES WHERE TABLE_ID=23;
+----------+----+---------+------+
| TABLE_ID | ID | NAME | TYPE |
+----------+----+---------+------+
| 23 | 25 | PRIMARY | 3 |
+----------+----+---------+------+
1 row in set (0.00 sec)
这里基本上可以看出来,在mysql数据库未启动情况下,使用工具可以正常抽取mysql数据字典信息