MySQL data recovery tips using ibd files?

Introduction

Disk bad sectors, power outages and other accidents are not normal, but encountering them is enough to make you "thrilling"! What should I do if the data is lost due to database damage and Binlog is no longer available? In order to restore data losslessly in a short time to ensure business stability, in addition to using binlog, we also practiced a new recovery skill!

Do you still remember what we wrote before, "Just one trick to make out-of-control R&D fall in love with you"? As mentioned earlier, the two database recovery methods we use most every day are:

Both of the above methods can achieve real-time rollback, but do you think having these two skills is enough?

No….!

In this intricate online architecture, there are actually many unknown reasons that we cannot predict. For example, the following situation:

The hard disk that has lost its life due to hard work will develop bad sectors, causing the database to be damaged. And it happened to damage the ibdata file and binlog file. So if you still think about the solution of binlog recovery with regular backup, it is impossible. Can you only use fixed-point backup to restore? After careful consideration, as an operation and maintenance personnel, we will never implement lossy rollback as a last resort, because it will have a great impact on the business, but what else can we do? Next we are going to release a big move! ! !

First check the database environment to see if the independent table space is enabled. If it has been enabled, congratulations, there is a great chance that all data can be recovered. We can rely on the frm and ibd files in each database directory to achieve data recovery. Generally speaking, if InnoDB is used but independent table space is not enabled, all database table information and metadata will be written to the ibdata file. This will last for a long time. If run, the ibdata file will become larger and larger, and the database performance will decrease. InnoDB provides the parameter to enable independent table space, which allows data to be stored independently. In this way, the ibdata file is only used to store some engine-related index information, and the actual data is written to independent frm and ibd files.

Okay, with the frm and ibd files, we can start to try data recovery. The process is more thrilling and interesting than binlog restoration! First, let’s take a look at the instructions about ibd and frm:

.frm file: Saves the metadata of each table, including the definition of the table structure, etc. This file has nothing to do with the database engine.

.ibd file: The file generated by the InnoDB engine when the independent table space is enabled (innodb_file_per_table = 1 is configured in my.ini) to store the data and indexes of the table.

We all know that for InnoDB database, if you do not copy the entire data directory, but only copy the specified database directory to the new instance, the database will not be recognized. So how to restore the database based on these two files?

Recovery ideas:

Because some index information about the engine is stored in the ibdata file, the ibdata file is damaged, causing the table name index to be lost and unable to start. Then we can first rename the entire old data directory and back it up, then re-initialize the database to generate a new ibdata file, then re-create the original database and corresponding tables, and finally change the backup table space ID number to the new table space ID number (the ibdata file contains a unique table space index ID for each table, which is incremented by the number of new tables created), so that the original database can be restored.
for example:
Library name: test_restore
Table structure: db_struc.sql
Table files: G_RESTORE.ibd, G_RESTORE.frm

1. Create a new library and import the table structure

#mysql -uroot –p**** -e “create database test_restore”

#mysql -uroot –p**** test_restore 2. View and modify the id of the table in the test_restore library in the new instance

#vim -b /data/database/mysql/test_restore/G_RESTORE.ibd

Open it directly as garbled characters, and convert it to hexadecimal for viewing. Execute :%!xxd in Vi to convert to hexadecimal. The result is :

as the picture shows. The id of the G_RESTORE table in the mysql database is 00fe.

Modify the backed-up G_RESTORE.ibd file. The operation is the same as above, but note that you need to back up first.

#cp G_RESTORE.ibd{,_back}

#vim -v G_RESTORE.ibd

Change 011b to 00fe. Notice. After the modification is completed, you need to execute it in vim first: %!xxd -r

Then wq save and exit the file. Otherwise, the results saved will be viewed in hexadecimal.

Save the results as follows:

Replace the modified G_RESTORE.ibd file with the G_RESTORE.ibd file in the new database.

Explanation about ibdata table id:

参考官方文档解释，每个表空间分配了4个字节存储了表空间id信息,最后偏移量地址为38。还有一组预留的表空间id，同样是4个字节，最后偏移量地址为42。

3. 验证并还原mysql数据

关闭mysql。修改my.conf。
innodb_force_recovery=6 innodb_purge_threads=0

启动数据库。如果不修改。数据库会认为G_RESTORE已被损坏。

Select 一下，即可查看到还原结果，但此时插入数据会报错，应尽快将数据dump出来 ,导回原来的实例中。

导出数据，再导入数据，恢复完毕！

#mysqldump -uroot –p****** test_restore > test_restore.sql

#mysql -uroot –p****** test_restore 
<p>说明：变更了新的space id后的.ibd表文件，启动数据库后只能认出数据，但不能写入，这是因为原ibdata文件不仅保存了space id索引，还同时保存了一些其它的元数据。为了使元数据补全，所以采取导出、再导入的操作。</p>
<p>以上举例为单个库表的恢复过程，看到这里大家一定会产生另一个疑问吧？线上的场景不可能是只有一个表的，数据库表很多的情况下，这样一个个表的修改，速度无疑是太慢了。那么存在大量表的情况下如何恢复呢？思路是，取得备份的ibd文件的id值，按id值顺序来建表，中间跨度随便建表语句来凑够数（每个表空间索引id由创建新表的数量依次递增）。实现方式如下：</p>
<p><span style="color: #339966;"><strong>1. 获取备份数据库ibd文件的space id号，并排序。</strong></span></p>
<pre class="brush:php;toolbar:false">for ibd in `find test_restore/ -name “*.ibd”` ; do  echo -e “${ibd///// }   /c” ;hexdump -C ${ibd} |head -n 3 |tail -n 1|awk ‘{print  strtonum(“0x”$6$7)}’ ;done | sort -n  -k 3 | column -t > /tmp/

生成的ibd.txt文件，格式如下：(库名–表名–SpaceId)

2. 新建表，查看当前表空间id（假设space id为10）

#mysql -uroot –p****** -e”create table test.tt(a bool)”

#hexdump -C mysql/test/tt.ibd |head -n 3 |tail -n 1|awk ‘{print  strtonum(“0x”$6$7)}’

3. 先创建所有库，准备所有表结构，写脚本，依据space id号自动创建新表

准备好数据库表结构，可以从备份文件里取出来（我们备份方式是把结构和数据分开备份的），或者从其他有相同表结构的服务器上备份再拷贝过来。

参考备份语句：

mysqldump -uroot –p****** -d ${db} –T /data/backup/${db}/

创建原有的数据库：

mysql -uroot –p****** -e “create database ${db}”

恢复表id创建表脚本：

#!/bin/bash
#因为前面假设为10，所以从11开始创建
oid=11

#打开前面生成的ibd.txt文件，按行读取”库名–表名–SpaceId”
cat /tmp/ibd.txt | while read db tb id ;do

#假如我们需要恢复catetory表，他的id为415，基于id是创表自增的原则，即415-11=404，
#我们还需要循环创建404个表后，才真正导入catetory表结构。
for ((oid;oid<id do mysql table test.t bool echo ok done let oid="oid+1">
<p><span style="color: #339966;"><strong>4. 检查表空间id 和备份的是否一致</strong></span></p>
<pre class="brush:php;toolbar:false">for ibd in `find test_restore/ -name “*.ibd”` ; do  echo -e “${ibd///// }   /c” ;hexdump -C ${ibd} |head -n 3 |tail -n 1|awk ‘{print  strtonum(“0x”$6$7)}’ ;done | sort -n  -k 3 | column -t > /tmp/ibd2.txt

确认一致后，拷贝备份的.ibd文件到新数据库实例目录下，修改my.cnf

innodb_force_recovery=6

innodb_purge_threads=0

启动数据库。后续步骤如同单表恢复，直接导出恢复到原来实例中即可。

当然，这种方式是在数据库出现极端情况下，不得不采取的一种方式，线上最重要的还是做好主从同步和定时备份，从而规避此类风险。

关于InnoDB引擎独立表空间说明：

使用过MySQL的同学，刚开始接触最多的莫过于MyISAM表引擎了，这种引擎的数据库会分别创建三个文件：表结构、表索引、表数据空间。我们可以将某个数据库目录直接迁移到其他数据库也可以正常工作。然而当你使用InnoDB的时候，一切都变了。

InnoDB默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：ibdata1，这样就感觉不爽，增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题。通常只能将数据使用mysqldump导出，然后再导入解决这个问题。

但是可以通过修改MySQL配置文件[mysqld]部分中innodb_file_per_table的参数来开启独立表空间模式，每个数据库的每个表都会生成一个数据空间。

优点：

1．每个表都有自已独立的表空间。

2．每个表的数据和索引都会存在自已的表空间中。

3．可以实现单表在不同的数据库中移动。

4．空间可以回收（除drop table操作处，表空不能自已回收）

a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

b) 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

c) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

shortcoming:

The single table is too large, such as more than 100 G.

in conclusion:

Shared table spaces have few advantages in Insert operations. Others do not perform as well as independent table spaces. When enabling independent table spaces, please adjust appropriately: innodb_open_files.

Configuration method:
1.innodb_file_per_table setting. How to enable it:

Set under [mysqld] in my.cnf

innodb_file_per_table=1
2. Check whether it is enabled:
mysql> show variables like ‘%per_table%’;

3. Close the exclusive table space
innodb_file_per_table=0 close independent table space

mysql> show variables like ‘%per_table%’;

The above is the detailed content of MySQL data recovery tips using ibd files?. For more information, please follow other related articles on the PHP Chinese website!