MySQL 5.6.26 data recovery process through frm & ibd
Create the database yoon on server A
Root (yoon) > show create table yoon\ G
* * 1. Row *
Table: yoon
Create Table: CREATE TABLE `yoon` (
`id`int (11) DEFAULT NULL
`name` varchar (20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Insert data
Root (yoon) > insert into yoon values (1MIT HANK')
Query OK, 1 row affected (1.01 sec)
Root (yoon) > insert into yoon values (2mai YouON')
Query OK, 1 row affected (0.04 sec)
Root (yoon) > insert into yoon values (3mai GARY')
Query OK, 1 row affected (0.00 sec)
Root (yoon) > insert into yoon values (4meme Niko)
Query OK, 1 row affected (0.02 sec)
Root (yoon) > select * from yoon
+-+ +
| | id | name |
+-+ +
| | 1 | HANK |
| | 2 | YOON |
| | 3 | GARY |
| | 4 | NIKO |
+-+ +
4 rows in set (0.00 sec)
On server B, add the parameter innodb_force_recovery=1 and restart, create an empty database yoon, and create the table yoon
Mysql > create database yoon
Use yooQuery OK, 1 row affected (1.01 sec)
Mysql > use yoon
Database changed
Mysql > CREATE TABLE `yoon` (
-> `id` int (11) DEFAULT NULL
-> `name` varchar (20) DEFAULT NULL
->) ENGINE=InnoDB DEFAULT CHARSET=utf8
Query OK, 0 rows affected (0.09 sec)
Separate the table structure from the space
Mysql > alter table yoon discard tablespace
Query OK, 0 rows affected (0.02 sec)
Transfer the ibd data file from server A to server B
Scp yoon.ibd 182.19.4.7:/export/data/mysql/data/yoon
Modify permissions
Chown-R mysql.mysql yoon.ibd
Establish a relationship between table structure and space
Mysql > alter table yoon import tablespace
Query OK, 0 rows affected, 1 warning (0.04 sec)
Query data
Mysql > select * from yoon
+-+ +
| | id | name |
+-+ +
| | 1 | HANK |
| | 2 | YOON |
| | 3 | GARY |
| | 4 | NIKO |
+-+ +
4 rows in set (0.00 sec)
The detailed steps are as follows:
1. Stop the mysql service, add innodb_force_recovery=1, and start the mysql service
2. Create a new database and create tables with the same table structure (the table structure must be the same)
3. Execute alter table tb discard tablespace
4. Delete the ibd file of the table
5. Copy table ibd files across servers
6. Modify ibd file permissions and execute alter table tb import tablespace
