Suppose you are using the InnoDB driver in MySQL and the instance cannot start due to driver error, kernel error, power failure or some rare MySQL error. What can you do? Case Description A portal mysql InnoDB database instance is damaged and the database service cannot be started. Service data in the database is restored using the FRM and bid files of the database on the file system.
Suppose you are using the InnoDB driver in MySQL and the instance cannot start due to driver error, kernel error, power failure or some rare MySQL error. What can you do?
Case description
A portal mysql InnoDB database instance is damaged and the database service cannot be started. FRM and BID files of the database on the file system are used to restore service data in the database.
Related knowledge points
The Mysql InnoDB database stores the basic dictionary information of the instance in the IBDatA1 file under the data directory and the Mysql instance, which can be understood as the System tablespace and the data dictionary of the Oracle database. If the data instance is damaged, the instance cannot be started.
Tablename. FRM and tablename.ibd files are stored in the tablename. FRM file. Ibd suffix file record table data. These two files can be used to restore business data in the database in case the mysql instance fails to start.
During recovery, you need to obtain the number of fields in each data table and the TABLE ID (version 5.5) of each tablespace file. The number of fields is recorded in the FRM file.
1. Analyze the FRM file format to obtain the number of table fields
Open_binary_frm = open_binary_frm = open_binary_frm
The open_binary_frm function calls get_from_pos at line 766 in the table.cc file, which calculates the starting offset of the field information set in the FRM file:
Pos =buf+length; pos=buf+length; pos=buf+length; pos=buf+length Therefore, after byte 64 is read, the entire buffer is converted into a small head and then the final target POS is read.
To interpret this code, the value of pos is the FRM file read (head+8 dword) *4 bytes from byte 64, skipping the number of bytes recorded in head+4 dword.
For example:
The fourth and fifth bytes are 03 00, the small header is 0003 after conversion, and the eighth and ninth bytes are 01 00 after conversion, 00 01. According to the above algorithm, read 3+1*4=7 bytes from the 64th byte, and convert the small header to “2F 2F 00 00 00 00 20 00”. Finally, truncate the third byte to “00 00 20 00”, which is equal to 8192 in base 10. This is the initial offset for saving the field information in the FRM file.
Looking at the open_binary_frm function again, the number of table fields we need is actually recorded at the start of the save offset of the field information +258:
In this case, the position is 8192+258=8450=0x2102
04 00 After the small header is converted to 0004, the table has four fields.
At this point, the field quantity information is read.
Get the space_id of the table
The space_id can be obtained from bytes 38 to 39 and 42 to 43 of the IBD file:
They’re exactly the same. Just read one of them.
Manual Recovery Process
1. The simulated database dictionary information is corrupted
When the ibData file in the data directory of the database is renamed to ibdata.1, the database service fails to be started and the following information is displayed in the Err log:
Generate table DDL statement in secondary instance
Create a table in the target library with the same number of fields as the original library (any field name), copy the original FRM file to the secondary instance, and set Innodb_force_recovery ==6
Create an empty table on the target instance and separate the data
Execute the create table command above, align space_id, and perform table space separation
4. Overwrite the target library file with the same name with the original FRM and IBD files
Stop the target instance service, overwrite the target instance file with the same name with the FRM and IBD files in the original database, and import the tablespace:
Recovery process sorting and related tools development
Important points during recovery are as follows:
1, batch fastGets the number of fields in the table
The production system contains a large number of data tables. It is slow to manually analyze FRM files one by one. Therefore, development tools are required to automatically obtain FRM files in batches.
2, batchThe values get the space_id and generate the alignment statement
Space_id alignment means that a table in the target library must have the same sequence created in the full instance (version 5.5, version 5.6 and above are not required). For example, for a table in the original library Space_id =100, 99 empty tables must be created in the target library before creating this table to make the Space_id of the table the same. The core logic is as follows:
3, in situ recovery
Due to the large number and volume of data tables in the production system, it takes too long to copy all data tables to the data directory of the target instance. Therefore, you need to point the data directory of the target instance to the original instance and rename all files in the data directory of the original instance in batches. Part of the code is as follows:
The process to comb
1. Establish auxiliary instances and target instances (manual)
Mysql > select space_id, space_id from mysql > select space_id from mysql > select space_id from mysql
SQL > create table > create table > create table > create table > create table > create table
Createtable createtable (createtable, createtable, createtable);
5. Target library coverage (tool)
Data and cloud
The original link