How does MySQL recover data skills with IBD files?

Bad disks, power outages and other accidents are not the norm, but the encounter is enough for you "thrilling"! If the database corruption caused by data loss, Binlog is not available, how to do? In order to restore data in a short period of time to ensure the stability of the business, in addition to the use of binlog, we also cultivate a new recovery skills!

Remember what we wrote earlier, "Just a trick to let runaway research and development fall in love with You"? The previous article mentions that the two more database recovery methods we use daily are:

650) this.width=650; "class=" wp-image-77056 size-full aligncenter "src=" http://www.linuxprobe.com/wp-content/ Uploads/2017/06/01-1.jpg "alt=" MySQL Recover data skills with IBD files? How does MySQL recover data skills with IBD files? "title=" MySQL Recover data skills with IBD files? How does MySQL recover data skills with IBD files? "Width=" height= "177"/>

Both of these methods allow for real-time fallback, but do you think it's enough to have both of these skills?

No....!

There are many unknown reasons for this intricate architecture on the web, and we can't predict it. For example, the following is the case:

The hard-working dreariness disk produces a bad path, causing the database to become corrupted. And then just damaged ibdata files and binlog files. So if you still think of a scheduled backup +binlog recovery scheme is impossible, can only use fixed-point backup back file? After careful deliberation, as an OPS person, we will not be in a desperate situation to implement the damage back, because it has a great impact on the business, but what else can be done? Below we are going to put a big trick!!!

First check the database environment, whether to open a separate table space, if it has been opened, then congratulations, there is a great opportunity to recover all the data. We can rely on the frm and IBD files in each database directory to achieve data recovery, in general, if the use of InnoDB but does not open a separate table space, all database table information and metadata will be written to the Ibdata file, so long-running, Ibdata file will become more and more large , the database performance is degraded. InnoDB provides the ability to open separate table space parameters, allowing data to be stored independently so that the Ibdata file is used only to hold some engine-related index information, and the actual data is written to the standalone frm and IBD files.

OK, with the frm and IBD files, we can start to try data recovery, and his process is both thrilling and fun than the Binlog restore! First, let's take a look at the description of IBD and frm:

. frm file: The metadata for each table is saved, including the definition of the table structure, and the file is not related to the database engine.

. ibd file: The InnoDB engine opens a separate tablespace (configured innodb_file_per_table = 1 in My.ini) to produce files that hold the data and indexes of the table.

As we all know, for the InnoDB database, if you do not copy the entire data directory, only copy the specified database directory to the new instance, the database is not recognized. So how do you recover a database based on these two files?

Recovery ideas:

Because the Ibdata file contains some index information about the engine, the Ibdata file corruption causes the table name index to be lost and cannot be started. Then we can rename the original old entire data directory, then re-initialize the database to generate a new Ibdata file, and then re-create the original database and the corresponding table, Finally, the backup tablespace ID number is changed to the new tablespace ID number (the Ibdata file has a unique tablespace index ID for each table, which is incremented by the number of new tables created) so that the original database can be restored.
As an 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 < Db_struc.sql

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

Directly open as garbled, turn into 16 binary view. VI execution:%!XXD converted to 16 binary. The result is:

650) this.width=650; "class=" AlignCenter "src=" http://s1.51cto.com/wyfs02/M02/99/A8/ Wkiom1llfjyi8lmpaaei4ssjaes466.jpg "alt=" MySQL Recover data skills with IBD files? How does MySQL recover data skills with IBD files? "title=" MySQL Recover data skills with IBD files? How does MySQL recover data skills with IBD files? "Width=" height= "287"/>

。 The G_restore table has an ID of 00fe in the MySQL database.

Modify the backed up g_restore.ibd file. Operation as above, attention need to backup first.

#cp G_restore.ibd{,_back} #vim-V g_restore.ibd

650) this.width=650; "class=" AlignCenter "src=" http://s1.51cto.com/wyfs02/M02/99/A8/wKioL1lLFKfAcRm9AAErf-0C_ O8765.jpg "alt=" MySQL Recover data skills with IBD files? How does MySQL recover data skills with IBD files? "title=" MySQL Recover data skills with IBD files? How does MySQL recover data skills with IBD files? "Width=" height= "291"/>

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

Wq Save the exit file again. Otherwise, the result of the 16 binary view is saved.

Save the results as follows:

650) this.width=650; "class=" AlignCenter "src=" http://s3.51cto.com/wyfs02/M00/99/A8/ Wkiom1llflhbtse6aacqlt3aopq490.jpg "alt=" MySQL Recover data skills with IBD files? How does MySQL recover data skills with IBD files? "title=" MySQL Recover data skills with IBD files? How does MySQL recover data skills with IBD files? "/>

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

Explanation about the Ibdata table ID:

650) this.width=650; "class=" AlignCenter "src=" http://s2.51cto.com/wyfs02/M02/99/A8/ Wkiol1llfluxu0rcaacmqv5olfc415.jpg "alt=" MySQL Recover data skills with IBD files? How does MySQL recover data skills with IBD files? "title=" MySQL Recover data skills with IBD files? How does MySQL recover data skills with IBD files? "/>

The official documentation explains that each tablespace allocates 4 bytes to store the Tablespace ID information, and the last offset address is 38. There is also a set of reserved tablespace IDs, which are also 4 bytes and the last offset address is 42.

3. Verifying and restoring MySQL data

Turn off MySQL. Modify the my.conf.
Innodb_force_recovery=6 innodb_purge_threads=0

Start the database. If not modified. The database will assume that G_restore has been corrupted.

Select to see the results of the restore, but at this time the insertion of data will be error, you should dump the data as soon as possible, back to the original instance.

Export data, and then import the data, the recovery is complete!

#mysqldump-uroot–p****** test_restore > test_restore.sql#mysql-uroot–p****** test_restore < Test_restore.sql

Note: the. ibd table file after the new space ID has been changed, only the data can be recognized when the database is started, but it cannot be written because the original Ibdata file not only preserves the Space ID index, but also saves some other metadata. To make the metadata complete, the export and re-import operations are taken.

The above example is a single library table recovery process, see here everyone will have another question it? The scene on the line can not be only a table, database tables A lot of cases, such a table modification, the speed is undoubtedly too slow. How to recover if there are a large number of tables? The idea is to get the ID value of the IBD file backed up, build the table by the ID value order, the middle span arbitrarily build the table statement to offerings (each tablespace index ID is incremented by the number of new tables created). Here's how it's implemented:

1. Get the space ID number of the IBD file that backs up the database and sort it.

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)} ';d One | Sort-n-K 3 | Column-t >/tmp/

The generated ibd.txt file is in the following format: (Library name – table name –spaceid)

650) this.width=650; "class=" AlignCenter "src=" http://s1.51cto.com/wyfs02/M01/99/A8/ Wkiol1llfnztnpsqaaddr2rdh7a825.jpg "alt=" MySQL Recover data skills with IBD files? How does MySQL recover data skills with IBD files? "title=" MySQL Recover data skills with IBD files? How does MySQL recover data skills with IBD files? "Width=" height= "225"/>
2. Create a new table to view the current Tablespace ID (assuming space ID is 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 St Rtonum ("0x" $6$7)} '

3. Create all libraries, prepare all table structures, write scripts, and create new tables automatically based on space ID number

Prepare the database table structure, which can be taken from the backup file (we backup the structure and data separately), or from other servers with the same table structure to back up and then copy it.

Refer to the BACKUP statement:

mysqldump-uroot–p******-D ${db}–t/data/backup/${db}/

To create an existing database:

mysql-uroot–p******-E "CREATE Database ${db}"

Restore table ID CREATE TABLE script:

#!/bin/bash# since the previous assumption is 10, create oid=11# from 11 to open the previously generated ibd.txt file, read "Library name – Table name –spaceid" by row Cat/tmp/ibd.txt | While read db TB ID;d o# If we need to restore the Catetory table, his ID is 415, based on the principle that the ID is the self-increment of the table, that is, the 415-11=404, #我们还需要循环创建404个表后, to actually import the Catetory table structure. For ((oid;oid<id;oid++)); domysql-uroot–p******-E "CREATE TABLE test.t (a bool);d ROP table test.t;" && echo "${oid} OK" done# loop after creating 404 tables, the ID is 415, identical to the original backed up. ibd file number, import table structure mysql-uroot–p****** ${db} </data/backup/${db}/${tb%%.ibd}.sql && echo "${oid} $ {db}/${tb%%.ibd}.sql OK "let Oid=oid+1done

4. Check that the Tablespace ID and backup are consistent

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)} ';d One | Sort-n-K 3 | Column-t >/tmp/ibd2.txt

After confirming the consistency, copy the backup. ibd file to the new DB instance directory and modify the MY.CNF

Innodb_force_recovery=6

Innodb_purge_threads=0

Start the database. The next steps are like single-table restores, and the direct export is restored to the original instance.

Of course, this approach is in the extreme case of the database, have to take a way, the most important on the line or master-slave synchronization and scheduled backup, so as to avoid such risks.

About the InnoDB engine stand-alone table space Description:

Students who have used MySQL have just started to touch the MyISAM table engine, the database of this engine will create three files: Table structure, table index, table data space. We can migrate a database directory directly to a different database and work properly. When you use InnoDB, however, everything changes.

InnoDB default will store all database InnoDB engine table data in a shared space: ibdata1, so feel uncomfortable, add and delete the database, ibdata1 file does not automatically shrink, the backup of a single database will become a problem. You can usually only export data using mysqldump and then import to resolve this issue.

However, you can turn on stand-alone tablespace mode by modifying the parameters of innodb_file_per_table in the MySQL configuration file [mysqld] section, and each table in the database will generate a data space.

Advantages:

1. Each table has a self-contained table space.

2. The data and indexes for each table will exist in the table space themselves.

3. You can implement a single table to move through different databases.

4. Space can be recycled (except for the drop table operation, the meter is not able to recycle)

A) The drop table operation automatically reclaims the tablespace, if for statistical analysis or a daily value table, delete a large amount of data can pass: ALTER TABLE TableName ENGINE=INNODB;

b) The use of Turncate table for Innodb-plugin InnoDB also shrinks the space.

c) For tables that use stand-alone table spaces, no matter how they are deleted, the fragmentation of the tablespace does not affect performance too much and there is a chance to process it.

Disadvantages:

Single table increased too large, such as more than 100 g.

Conclusion:

Shared tablespace has few advantages over insert operations. Others do not have a separate table space to perform well. When you enable stand-alone table spaces, make a reasonable adjustment: Innodb_open_files.

How to configure:
1.innodb_file_per_table settings. Open method:

Set under [Mysqld] in MY.CNF

Innodb_file_per_table=1
2. Check to see if it is turned on:
Mysql> Show variables like '%per_table% ';

3. Turn off the exclusive table space
Innodb_file_per_table=0 closing a separate table space

Mysql> Show variables like '%per_table% ';

How does MySQL recover data skills with IBD files?