MySQL 5.6.26 restores data through frm & ibd
Create A database bkjia on server
Root (yoon)> show create table bkjia \ G
* *************************** 1. row ***************************
Table: bkjia
Create Table: create table 'bkjia '(
'Id' int (11) default null,
'Name' varchar (20) DEFAULT NULL
) ENGINE = InnoDB default charset = utf8
Insert data
Root (bkjia)> insert into bkjia values (1, 'Hank ');
Query OK, 1 row affected (1.01 sec)
Root (bkjia)> insert into bkjia values (2, 'bkjia ');
Query OK, 1 row affected (0.04 sec)
Root (bkjia)> insert into bkjia values (3, 'gary ');
Query OK, 1 row affected (0.00 sec)
Root (bkjia)> insert into bkjia values (4, 'niko ');
Query OK, 1 row affected (0.02 sec)
Root (bkjia)> select * from bkjia;
+ ------ +
| Id | name |
+ ------ +
| 1 | HANK |
| 2 | bkjia |
| 3 | GARY |
| 4 | NIKO |
+ ------ +
4 rows in set (0.00 sec)
On server B, add the innodb_force_recovery = 1 parameter and restart. Create an empty database bkjia and create the table yoon.
Mysql> create database bkjia;
Use yooQuery OK, 1 row affected (1.01 sec)
Mysql> use bkjia;
Database changed
Mysql> create table 'bkjia '(
-> 'Id' int (11) default null,
-> 'Name' varchar (20) DEFAULT NULL
->) ENGINE = InnoDB default charset = utf8;
Query OK, 0 rows affected (0.09 sec)
Detach the table structure and Space
Mysql> alter table bkjia discard tablespace;
Query OK, 0 rows affected (0.02 sec)
Transfer ibd data files on server A to server B
Scp bkjia. ibd 182.19.4.7:/export/data/mysql/data/bkjia
Modify permissions
Chown-R mysql. mysql bkjia. ibd
Create a relationship between the table structure and the space
Mysql> alter table bkjia import tablespace;
Query OK, 0 rows affected, 1 warning (0.04 sec)
Query data
Mysql> select * from bkjia;
+ ------ +
| Id | name |
+ ------ +
| 1 | HANK |
| 2 | bkjia |
| 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 the ibd file across servers
6. Modify ibd file permissions and execute alter table tb import tablespace;
This article permanently updates the link address: