MySQL: how to restore data from ibd files

When an independent tablespace is used, if the metadata file ibdata of the innodb Storage engine is damaged accidentally, we can save valuable data. when innodb uses an independent tablespace, The ibdata file records the id of each innodb table, as long as the table id in ibd is the same as that recorded in the ibdata file, you can open the table and read the data.

# Creating a table

Create table 'ibdtest '(
'Id' int (11) not null AUTO_INCREMENT,
'Fid' int (11) not null comment' id' in Table B ',
'Content' char (255) not null comment' operation content, system generated ',
'Mark' char (255) not null comment' COMMENT ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8

# Add data
INSERT ibdtest (fid, content, mark) VALUES (1, '1', '1'), (2, '2', '2 ');

SELECT * FROM ibdtest;

 

Disable mysql and copy ibdtest. ibd to other databases to simulate disasters.

[Root @ localhost ~] #/Opt/soft/mysql/bin/mysqladmin-p123456 shutdown

120130 18:31:50 mysqld_safe mysqld from pidfile/opt/soft/mysql/60137. localdomain. pid ended

[1] + Done/opt/soft/mysql/bin/mysqld_safe -- defaults-file =/opt/soft/mysql/config/my. cnf -- user = mysql

[Root @ localhost ~] # Cd/home/soft/mysql/data/test/
[Root @ localhost test] # ll
Total 1296
-Rw ----. 1 mysql 8612 Jan 18 a. frm
-Rw ----. 1 mysql 98304 Jan 18 a. ibd
-Rw ----. 1 mysql 8624 Jan 30 area. frm
-Rw ----. 1 mysql 98304 Jan 30 area. ibd
-Rw ----. 1 mysql 8642 Jan 18 B. frm
-Rw ----. 1 mysql 98304 Jan 18 B. ibd
-Rw ----. 1 mysql 8693 Jan 30 ibdtest. frm
-Rw ----. 1 mysql 98304 Jan 30 ibdtest. ibd
-Rw ----. 1 mysql 8728 Jan 6 testa. frm
-Rw ----. 1 mysql 98304 Jan 10 testa. ibd
-Rw ----. 1 mysql 8693 Jan 30 testmc. frm
-Rw ----. 1 mysql 98304 Jan 30 testmc. ibd
-Rw ----. 1 mysql 8693 Jan 30 testme. frm
-Rw ----. 1 mysql 98304 Jan 30 testme. ibd
-Rw ----. 1 mysql 8693 Jan 30 testmm. frm
-Rw ----. 1 mysql 98304 Jan 30 testmm. ibd
-Rw ----. 1 mysql 8693 Jan 30 tew.u. frm
-Rw ----. 1 mysql 98304 Jan 30 tew.u. ibd
-Rw ----. 1 mysql 8693 Jan 30 testmv. frm
-Rw ----. 1 mysql 98304 Jan 30 testmv. ibd
-Rw ----. 1 mysql 8694 Jan 4 testuser. frm
-Rw ----. 1 mysql 98304 Jan 4 testuser. ibd
-Rw ----. 1 mysql 8644 Jan 14 user. frm
-Rw ----. 1 mysql 98304 Jan 14 user. ibd
[Root @ localhost test] # cp ibdtest. ibd/home/download/
[Root @ localhost test] # cd/home/download/

# Run vim to enable ibd and view it in hexadecimal notation.
[Root @ localhost download] # vim-B ibdtest. ibd
: %! Xxd

The id of the table in the current mysql database is 0x10, that is, 16.

In this case, we assume that ibdata is damaged due to a disaster...

Only ibdtest. ibd is left. We will jump to another mysql server and use the same table creation statement to create an ibdtest table.

In this case, open ibdtest. ibd under the mysql server to see:

 

The id of this table is 0x16, that is, 22. Therefore, we only need to change the id of the original ibdtest. ibd table to 0x16.

Remember to use: % When exiting the save operation! Xxd-r

Exit and save.

And overwrite the modified file with the new ibdtest. ibd,

This mysql server will think that the table is damaged and cannot be opened. It doesn't matter. Modify innodb_force_recovery = 6,

Restart the mysql service:

Under Select, you will know whether the data has been restored:

At this point, the write operation cannot be performed. dump the data out as soon as possible, modify innodb_force_recovery = 0, restart the service, and after the new table is created, it will be okay to reverse the data back. data recovery will not be demonstrated.

From the column of ylqmf