MySQL uses frm files and ibd files to restore table data

Introduction to frm files and ibd files

In MySQL, if we use the default storage engine innodb to create a table, two files, table name.frm and table name.ibd, will appear in the folder. If we use the Myisam storage engine, three files will appear. Here we give an example:

[root@ /data/yeyz]#ll
total 580
-rw-rw---- 1 mysql mysql 8586 Apr 3 17:44 a.frm
-rw-rw---- 1 mysql mysql 0 Apr 3 17:44 a.MYD
-rw-rw---- 1 mysql mysql 1024 Apr 3 17:44 a.MYI
-rw-rw---- 1 mysql mysql 8586 Apr 3 17:44 b.frm
-rw-rw---- 1 mysql mysql 98304 Apr 3 17:45 b.ibd
-rw-rw---- 1 mysql mysql 61 Nov 23 09:54 db.opt
-rw-rw---- 1 mysql mysql 8556 Apr 29 21:37 tbl_test_2.frm
-rw-rw---- 1 mysql mysql 98304 Apr 29 21:37 tbl_test_2.ibd
-rw-rw---- 1 mysql mysql 8556 Apr 29 21:33 tbl_test.frm
-rw-rw---- 1 mysql mysql 98304 Apr 29 21:33 tbl_test.ibd
-rw-rw---- 1 mysql mysql 8614 Apr 29 21:40 test.frm
-rw-rw---- 1 mysql mysql 98304 Apr 29 21:43 test.ibd
-rw-rw---- 1 mysql mysql 8666 Apr 2 15:13 unstandard_ins.frm
-rw-rw---- 1 mysql mysql 98304 Apr 3 11:46 unstandard_ins.ibd
-rw-rw---- 1 mysql mysql 8586 Apr 3 17:44 yeyz.frm
-rw-rw---- 1 mysql mysql 28 Apr 3 17:44 yeyz.MYD
-rw-rw---- 1 mysql mysql 2048 Apr 3 17:44 yeyz.MYI

Among them, the ibd file is the innodb table data file, and the frm file is the innodb table structure file. In the table of the mysiam storage engine, frm is the table structure, the MYI file is the index file, and the MYD file is the data file. It can also be seen here that the index and data of the innodb storage engine are together, while the index and data of the Myisam storage engine are separate.

It should be noted that neither the frm file nor the ibd file can be opened directly.

Consider a requirement where the database needs to quickly recover the data in a table. However, the database where the table is located has a large amount of data and may take a long time to recover. Therefore, full database recovery is definitely not the best option. So what should we do in this situation? We can use frm files and ibd files to recover data. Let’s analyze this process below.

frm file recovery table structure

Of course, the table structure needs to be restored using the frm file. Our first reaction is that we can copy these two files directly to a new database instance and then start the instance directly. Is this possible? Of course not. If my nephew can do it, I guess all DBAs will be laid off. Haha, without further ado, let’s see the operation process.

First, we create a new instance specifically for restoring data. If you use an online machine to perform recovery, you must bear the risk of database restart and DML blocking, so the best way is to use a dedicated instance for recovery. So how do we get the table structure we want from the frm file?

Let me take an online slow log table as an example. For the convenience of writing, I wrote the table name as "aaa". The structure of this table is as follows:

mysql--root@localhost:test_recover 12:08:43>>show create table aaa\G
*************************** 1. row ***************************
  Table: aaa
Create Table: CREATE TABLE `aaa` (
 `maintain_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment column',
 `slowquery_filename` varchar(50) DEFAULT NULL COMMENT 'Slow log file name',
 `slowquery_path` varchar(150) DEFAULT NULL COMMENT 'Full path of slow log',
 `slowquery_process` tinyint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Whether the slow log is parsed',
 `slowquery_uploadtime` datetime DEFAULT CURRENT_TIMESTAMP,
 `slowquery_analyzetime` date DEFAULT NULL COMMENT 'Slow log analysis time',
 `slowquery_starttime` date DEFAULT NULL,
 `slowquery_endtime` date DEFAULT NULL,
 `instance_ip` varchar(15) DEFAULT NULL COMMENT 'Slow log IP address',
 `instance_port` int(11) DEFAULT NULL COMMENT 'Slow log port number address',
 PRIMARY KEY (`maintain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set, 1 warning (0.01 sec)

To get such a table from the frm file, we need to do the following steps:

1. Create a table with the same name as aaa on the instance. Since we don't know the structure of this table, we can set it to have only one field id, that is,

create table aaa (id int);

We know that at this time, new aaa.frm and aaa.ibd files will be generated in the corresponding data directory. Then we use the backed up aaa.frm to replace the previous aaa.frm and restart the database.

Yes, you read it right. We use the backup table structure file to replace the generated table structure file.

2. Take a look at the error log output after restart, as follows:

2019-03-22T03:17:28.652390Z 16 
[Warning] InnoDB: Table test_recover/store_goods_price contains 1 user 
defined columns in InnoDB, but 12 columns in MySQL. Please check 
INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-04-02T07:56:31.558461Z 41 
[Warning] InnoDB: Table test_recover/dv_control contains 1 user defined 
columns in InnoDB, but 14 columns in MySQL. Please check
 INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2019-05-23T03:14:10.161122Z 92 
[Warning] InnoDB: Table test_recover/aaa contains 1 
user defined columns in InnoDB, but 10 columns in MySQL. Please check 
INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

As you can see, the error log in lines 10-12 tells us that table aaa contains only one field, but frm contains 10 fields, and the number of fields does not match.

This is consistent with our expected result, because when we created table aaa, we only gave it one field id, and the aaa table we want to restore has 10 fields, which cannot be read from frm. At this point, you may easily think of, if we adjust the fields of this aaa table to 10, what will be the final result?

3. Update the number of fields in the aaa table to 10, then re-copy the frm file and modify the parameter innodb_force_recovery=6 in the configuration file. Let's see the final result:

mysql--root:(none) 12:04:20>>use test_recover;
Database changed
mysql--root:test_recover 12:04:25>>create table aaa (id1 int,id2 int,id3 int,id4 int,id5 int,id6 int,id7 int,id8 int,id9 int,id10 int);
Query OK, 0 rows affected (0.03 sec)

mysql--root@localhost:test_recover 12:05:08>>show create table aaa\G
*************************** 1. row ***************************
  Table: aaa
Create Table: CREATE TABLE `aaa` (
 `id1` int(11) DEFAULT NULL,
 `id2` int(11) DEFAULT NULL,
 `id3` int(11) DEFAULT NULL,
 `id4` int(11) DEFAULT NULL,
 `id5` int(11) DEFAULT NULL,
 `id6` int(11) DEFAULT NULL,
 `id7` int(11) DEFAULT NULL,
 `id8` int(11) DEFAULT NULL,
 `id9` int(11) DEFAULT NULL,
 `id10` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Then we restart the instance and check table aaa again, we can see the following results:

mysql--root:test_recover 12:08:43>>show create table aaa\G
*************************** 1. row ***************************
  Table: aaa
Create Table: CREATE TABLE `aaa` (
 `maintain_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment column',
 `slowquery_filename` varchar(50) DEFAULT NULL COMMENT 'Slow log file name',
 `slowquery_path` varchar(150) DEFAULT NULL COMMENT 'Full path of slow log',
 `slowquery_process` tinyint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Whether the slow log is parsed',
 `slowquery_uploadtime` datetime DEFAULT CURRENT_TIMESTAMP,
 `slowquery_analyzetime` date DEFAULT NULL COMMENT 'Slow log analysis time',
 `slowquery_starttime` date DEFAULT NULL,
 `slowquery_endtime` date DEFAULT NULL,
 `instance_ip` varchar(15) DEFAULT NULL COMMENT 'Slow log IP address',
 `instance_port` int(11) DEFAULT NULL COMMENT 'Slow log port number address',
 PRIMARY KEY (`maintain_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set, 1 warning (0.01 sec)

It can be seen that the table structure we want has been restored from the frm file. It should be noted that we did not use the ibd file in this process.

Summarize the steps to restore the table structure using frm files:

1. First create a table with the same name, and then start the instance

2. Use the backed-up frm file to replace the generated frm file and restart the instance

3. Check the error log and get the number of fields m in the backed up frm file from the error log

4. Recreate the table with the same name, ensure that the number of fields is m, consistent with the backup table, and then re-copy the backup frm file to the corresponding directory

5. Modify the parameter innodb_force_recovery=6 in the instance configuration file, and then restart the database. You can see the corresponding table structure creation statement. We save it and use it when restoring data in the next step. This step is quite important

6. Comment out the parameter innodb_force_recovery=6, use the default value again, and then restart the database to prepare to recover the table data.

At this point, the table structure has been restored.

Let me explain the innodb_force_recovery parameter. The maximum value of this parameter is 6. At this level, only some query functions are supported, and DML is not supported. As the name suggests, this parameter is used in some forced recovery scenarios. In general, this parameter can be omitted and the default value can be used. Students who are interested in learning more can refer to the official documentation.

ibd file recovery table data

After the previous step is completed, we have obtained the corresponding table structure. Now let's see how to restore the table data.

The method of restoring table data is relatively simple, the general steps are as follows:

1. Use the table creation statement obtained in the previous step to recreate a table and then execute:

flush table aaa for export;

This syntax is used to write the data in the table to the disk and obtain the lock of the table to prepare for subsequent recovery.

2. Then we use the following statement:

alter table aaa discard tablespace;

This statement will delete the current ibd file.

3. Then we use the ibd file we backed up earlier and copy it to the corresponding instance directory

4. Finally, reload the ibd file and use the following statement:

alter table aaa import tablespace;

Restart the database, and our data will be restored successfully.

Brief summary

The entire recovery process has been introduced. The more clever part is to obtain the table structure information from the frm file. We used the method of piecing together the table creation statement twice, and finally obtained the table structure of the table to be recovered, and then used the alter table discard tablespace and alter table import tablespace methods to recover the data in the table. The whole process looks complicated, but in fact, you can abstract a script according to the steps. In this way, when you restore next time, you only need to enter the name of the table to be restored, and you can quickly restore the table structure and data. It can be regarded as an emergency data recovery plan.

The above is the details of how MySQL uses frm files and ibd files to recover table data. For more information about MySQL table data recovery, please pay attention to other related articles on 123WORDPRESS.COM!