Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.