Shulou(Shulou.com)05/31 Report--

MySQL how to use ibd files to recover data, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

# create a table

CREATE TABLE `ibdtest` (

`id`int (11) NOT NULL AUTO_INCREMENT

Id' in table b of `fid` int (11) NOT NULL COMMENT'

`content` char (255) NOT NULL COMMENT 'operation content, system generated'

`mark`char (255) NOT NULL COMMENT 'remarks'

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

# add data

INSERT ibdtest (fid,content,mark) VALUES (1), (2) 2)

SELECT * FROM ibdtest

Close the mysql to ibdtest.ibd copy and put it into something else to simulate the disaster.

[root@localhost] # / opt/soft//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-rw----. 1 mysql mysql 8612 Jan 18 00:06 a.frm

-rw-rw----. 1 mysql mysql 98304 Jan 18 00:24 a.ibd

-rw-rw----. 1 mysql mysql 8624 Jan 30 08:34 area.frm

-rw-rw----. 1 mysql mysql 98304 Jan 30 08:36 area.ibd

-rw-rw----. 1 mysql mysql 8642 Jan 18 00:05 b.frm

-rw-rw----. 1 mysql mysql 98304 Jan 18 00:08 b.ibd

-rw-rw----. 1 mysql mysql 8693 Jan 30 18:27 ibdtest.frm

-rw-rw----. 1 mysql mysql 98304 Jan 30 18:28 ibdtest.ibd

-rw-rw----. 1 mysql mysql 8728 Jan 6 16:23 testa.frm

-rw-rw----. 1 mysql mysql 98304 Jan 10 04:10 testa.ibd

-rw-rw----. 1 mysql mysql 8693 Jan 30 14:30 testmc.frm

-rw-rw----. 1 mysql mysql 98304 Jan 30 14:30 testmc.ibd

-rw-rw----. 1 mysql mysql 8693 Jan 30 13:54 testme.frm

-rw-rw----. 1 mysql mysql 98304 Jan 30 13:55 testme.ibd

-rw-rw----. 1 mysql mysql 8693 Jan 30 14:40 testmm.frm

-rw-rw----. 1 mysql mysql 98304 Jan 30 14:45 testmm.ibd

-rw-rw----. 1 mysql mysql 8693 Jan 30 13:40 testmu.frm

-rw-rw----. 1 mysql mysql 98304 Jan 30 13:40 testmu.ibd

-rw-rw----. 1 mysql mysql 8693 Jan 30 11:08 testmv.frm

-rw-rw----. 1 mysql mysql 98304 Jan 30 11:10 testmv.ibd

-rw-rw----. 1 mysql mysql 8694 Jan 4 21:55 testuser.frm

-rw-rw----. 1 mysql mysql 98304 Jan 4 22:04 testuser.ibd

-rw-rw----. 1 mysql mysql 8644 Jan 14 21:55 user.frm

-rw-rw----. 1 mysql mysql 98304 Jan 14 21:55 user.ibd

[root@localhost test] # cp ibdtest.ibd / home/download/

[root@localhost test] # cd / home/download/

# vim Open ibd and use hexadecimal to view

[root@localhost download] # vim-b ibdtest.ibd

%! xxd

From the following figure, you can see that the id of this table in the current my is 0x10, that is, 16. 0.

At this point, we assume that the disaster occurs and the ibdata is damaged.

With only ibdtest.ibd text left, we jump to another mysql server and create the ibdtest table with the same table-building statement.

At this point, let's open the ibdtest.ibd under the mysql server and take a look:

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

Be sure to use:%! xxd-r when you exit saving

Exit save.

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

The mysql server will think that the table is corrupted 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 recovered or not:

At this point, the write operation cannot be performed. You should dump the data as soon as possible, modify innodb_force_recovery = 0, restart the service, and ok the data back after creating a new table. If you recover the data, there will be no demonstration.

After reading the above, have you mastered how to use ibd files to recover data in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.