How To:利用frm和idb文件进行数据恢复.txt
在另外一个机器上准备测试数据，并传输到dbadb05机器的/mysql/backup/reco/位置下。
开始尝试恢复数据
一、使用mysqlfrm获取表结构信息及DDL语句。
[mysql@dbadb05 reco]$ ll total 108 -rw-r----- 1 mysql mysql 8602 Aug 6 11:21 t.frm -rw-r----- 1 mysql mysql 98304 Aug 6 11:21 t.ibd [mysql@dbadb05 reco]$ mysqlfrm t.frm --basedir=/mysql/mysql --port=6607 --show-stats --verbose # Starting the spawned server on port 6607 ... done. # Reading .frm files # # Reading the t.frm file. # # CREATE statement for t.frm: # CREATE TABLE `t` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 # File Statistics: # Last Modified : Mon Aug 6 15:44:35 2018 # Creation Time : Mon Aug 6 15:44:35 2018 # Last Accessed : Mon Aug 6 15:44:35 2018 # Mode : 33184 # Size : 8602 # Table Statistics: # Engine : HEAP # frm Version : 10 # MySQL Version : 5.7.22 # frm File_Version : 5 # IO_SIZE : 4096 # Def Partition Engine : None #...done.
二、使用第一步中恢复的DDL语句创建表，并discard tablespace
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | syk | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> create database reco; Query OK, 1 row affected (0.00 sec) mysql> use reco; Database changed mysql> CREATE TABLE `t` ( -> `a` int(11) DEFAULT NULL, -> `b` int(11) DEFAULT NULL, -> `c` varchar(10) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected (0.02 sec) mysql> alter table t discard tablespace; Query OK, 0 rows affected (0.02 sec)
三、拷贝ibd文件到对应路径，并import tablespace
[mysql@dbadb05 reco]$ ll total 16 -rw-r----- 1 mysql mysql 67 Aug 6 15:46 db.opt -rw-r----- 1 mysql mysql 8602 Aug 6 15:47 t.frm [mysql@dbadb05 reco]$ cp /mysql/backup/reco/t.ibd . [mysql@dbadb05 reco]$ ls -lrt total 112 -rw-r----- 1 mysql mysql 67 Aug 6 15:46 db.opt -rw-r----- 1 mysql mysql 8602 Aug 6 15:47 t.frm -rw-r----- 1 mysql mysql 98304 Aug 6 15:49 t.ibd [mysql@dbadb05 reco]$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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 reco; 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, 1 warning mysql> show warnings\G; *************************** 1. row *************************** Level: Warning Code: 1287 Message: 'COM_FIELD_LIST' is deprecated and will be removed in a future release. Please use SHOW COLUMNS FROM statement instead *************************** 2. row *************************** Level: Warning Code: 1814 Message: InnoDB: Tablespace has been discarded for table 't' 2 rows in set (0.00 sec) ERROR: No query specified mysql> alter table t import tablespace; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> show warnings\G; *************************** 1. row *************************** Level: Warning Code: 1810 Message: InnoDB: IO Read error: (2, No such file or directory) Error opening './reco/t.cfg', will attempt to import without schema verification 1 row in set (0.00 sec) ERROR: No query specified
四、查看表中的数据
mysql> select * from t; +------+------+------+ | a | b | c | +------+------+------+ | 1 | 1 | a | +------+------+------+ 1 row in set (0.00 sec)
五、没有mysqlfrm工具，恢复DDL的方法
仅供mysqlfrm工具时，尝试性恢复。
1、随意建立一个数据库和一张表
mysql> create database reco; Query OK, 1 row affected (0.01 sec) mysql> use reco; Database changed mysql> create table t( c1 int); Query OK, 0 rows affected (0.03 sec) mysql> show create table t\G; *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `c1` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) ERROR: No query specified
2、拷贝需要恢复的frm文件到对应位置，并覆盖
mysql> system cp /mysql/backup/reco/t.frm /mysql/data/reco/t.frm mysql> show create table t\G; *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `c1` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) ERROR: No query specified mysql> flush table t; Query OK, 0 rows affected (0.01 sec) mysql> show create table t\G; ERROR 1146 (42S02): Table 'reco.t' doesn't exist ERROR: No query specified
3、此时报错，表报错，意料中的事情，查看mysql的错误日志
[Warning] InnoDB: Table reco/t contains 1 user defined columns in InnoDB, but 3 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
注意到日志中的信息，我们定义了1个列，但是MySQL中有3列。
4、删除原来的表，建立相同个数的列，名称和类型随意。
mysql> drop table t; Query OK, 0 rows affected (0.02 sec) mysql> create table t(c1 int,c2 int, c3 int); Query OK, 0 rows affected (0.02 sec) mysql> show create table t\G; *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, `c3` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) ERROR: No query specified
5、重复第二步，覆盖frm文件，就可以获得定义语句，结果与第一部分得出的一样
mysql> system cp /mysql/backup/reco/t.frm /mysql/data/reco/t.frm mysql> show create table t\G; *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, `c3` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) ERROR: No query specified mysql> flush table t; Query OK, 0 rows affected (0.01 sec) mysql> show create table t\G; *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.01 sec) ERROR: No query specified
六、安全性问题
MySQL的这个设计，方便了恢复。但是有着严重的安全隐患，一旦这两个文件被非法获取，所有数据将被泄露。
