MySQL database InnoDB data recovery tool use summary details
- 2020-05-17 06:45:45
- OfStack
Starting from practical experience, this paper introduces an open source MySQL database InnoDB data recovery tool: innodb-tools, which can recover data from lost or damaged MySQL tables by extracting the row records of the tables from the original data files. For example, if you accidentally execute DROP TABLE, TRUNCATE TABLE, or DROP DATABASE, you can restore the data in the following ways.
Most of the following content is based on: Percona Data Recovery Tool for InnoDB, the document is in English, and written relatively obscure, here is a summary of my actual experience, for your reference.
Before introducing the innodb-tools tool for data recovery, let's make the following points clear:
1. This tool only works for InnoDB/XtraDB tables, not MyISAM tables (note: Percona claims to have a set of tools for MyISAM tables, but I haven't tried).
2. This tool restores from saved MySQL data files instead of running MySQL Server.
3. There is no guarantee that the total data 1 can be recovered. For example, the overwritten data cannot be recovered, in which case it may be necessary to recover in a systematic or physical manner, which is not the scope of this tool.
4. The best time to recover is to backup the MySQL data file as soon as possible when you find that the data is missing.
5. Using this tool requires some manual work, which is not done automatically.
The recovery process depends on your knowledge of the lost data, and you may have to choose between different versions of the data. The more you know about your data, the more likely you are to recover.
Next, here is an example of how to restore with this tool.
1. Prerequisites
First, it is important to understand that the innodb-tools tool does not restore data by connecting to database online, but by copying data offline. Note: do not copy InnoDB files directly while MySQL is running. This is not secure and will affect the data recovery process.
In order to complete data recovery, you must know the table structure (column name, data type) to be recovered. The easiest way to do this is SHOW CREATE TABLE, though several alternatives will be introduced later. Therefore, if you have an MySQL server backup, even if the data is very early and even if there are no records in the table, it can be helpful to use the innodb-tools tool for recovery. But that's not necessary.
2. Simple example
3. Build tools
To build the innodb-tools tool, you need to rely on the C compiler, make tool, and so on.
1. Download the source code of innodb-tools tool:
2. Enter the mysql-source directory in the root directory after decompression and run the configuration command (note: do not run the make command) :
3. After completing the configuration steps, go back to the root directory after decompression, run make command, compile and generate page_parser and constraints_parser tools:
The page_parser tool will parse the table's page and row structure based on the underlying implementation of InnoDB. The constraints_parser tool is not in use for the time being, and you will need to recompile and generate the table structure after you define it.
If there is a problem during compilation, click here. There are no problems with this article, so I will not list 11.
Extract the required pages
The default size of the InnoDB page is 16K, and each page belongs to a particular index in a particular table. The page_parser tool copies each page into a separate file by reading the data file, according to index ID in the header.
If your MySQL server is configured to innodb_file_per_table=1, then the system has implemented the above procedure for you. All the required pages are in the.ibd file, and usually you don't need to shred it. However, if the.ibd file may contain more than one index, it may be necessary to separate the pages. If MySQL server is not configured with innodb_file_per_table, then the data will be stored in a global table namespace (usually a file named ibdata1, as in this case) and the file will need to be shred by page.
4.1 page segmentation
Run the page_parser tool to shard:
The & # 8226; If MySQL is before 5.0 and InnoDB is in REDUNDANT format, run the following command:
The & # 8226; If MySQL is version 5.0 and InnoDB is in COMPACT format, run the following command:
When run, the page_parser tool creates an pages- < TIMESTAMP > Where TIMESTAMP is the UNIX system timestamp. Under this directory, create a subdirectory for each index ID, index ID of the page. Such as:
4.2 select the required Index ID
1 generally, we need to recover according to the table's primary key (PRIMARY index), which contains all rows. Here are some steps you can take:
If the database is still running and the tables have not been dropped by drop, launch InnoDB Tablespace Monitor and output all the tables and the error log files for indexes, index IDs to MySQL server. Create the innodb_table_monitor table to collect the storage method of the innodb storage engine table and its indexes:
If innodb_table_monitor already exists, drop table is then re-create table. After the output of the MySQL error log, drop can drop the table to stop printing out more monitoring. An example of the output is as follows:
Here, we restore the customer table under the sakila library, from which we can get its primary key information:
Index ID is 0 256, so the InnoDB pages we need to restore are located in the 0-256 subdirectories.
Note: the above method of obtaining index ID of the table is described in the original document. In practice, this paper takes a simpler way to directly restore all InnoDB pages generated by page_parser. Practice has proved that this method is also feasible :)
5. Generate table definitions
In step 4, we have found the data we need. Next, we need to find the table structure, create the table definition, compile it into constraints_parser, and then use this tool to extract the rows from the InnoDB page from the table.
The table definition includes the columns in the table, the column order, and the data types. If MySQL server is still running and the table has not been dropped by drop, the simple utility SHOW CREATE TABLE can collect this information. This table structure information is then used to create a table definition for an C structure identity, which is then compiled into the constraints_parser tool. The definition of the C structure is stored in include/ table_defs.h.
The easiest way is the create_defs.pl Perl script, which connects to MySQL server, reads the SHOW CREATE TABLE result, and outputs the generated table definition to standard output. Here is an example where the results are directly redirected to include/ table_defs.h:
If possible, the easiest way to create the table definition is with the create_defs.pl Perl script. It connects to the MySQL server and reads SHOW CREATE TABLE output, and prints the generated definition to its standard output. Here is an example:
Here is the table structure in the example:
Here is the generated table definition:
If necessary, you can edit and modify include/ table_defs.h; The constraints_parser tool is then recompiled according to include/ table_defs.h:
6. Extract line records from the page
6.1 merge pages into one file
As mentioned earlier, we need to recover the index ID 0 286 containing the data in the pages-1246363747/0-286 / directory.
Enter the following command to merge the pages:
The resulting file: pages-1246363747/0-286 /customer_pages_concatenated will be the input to the constraints_parser tool.
6.2 run the constraints_parser tool
The next step to the heart of data recovery is to run the constraints_parser tool to extract row records. As with page_parser tool 1, you need to specify the InnoDB page format (COMPACT/REDUNDANT) with the -5 or -4 parameters, and the input file with -f.
To return to the example, we can run the constraints_parser tool like this (the following command is to restore 1 single page, or to restore files that have been merged after 6.1 steps) :
Each row in the output contains the table name and the individual columns in the table. Note: there may be correct or incorrect line records. This section of the official documentation shows how to adjust the table definition to get as much valid data as possible while filtering out the garbage rows, which is not described in detail here.
7. Import data into the database
Finally, to complete the data recovery, you need to import the output of the constraints_parser tool from step 6 into the database using the LOAD DATA INFILE command. The command is as follows:
At this point, the data recovery and import process is complete. Hopefully, you won't have the opportunity to practice the methods described in this article.
Most of the following content is based on: Percona Data Recovery Tool for InnoDB, the document is in English, and written relatively obscure, here is a summary of my actual experience, for your reference.
Before introducing the innodb-tools tool for data recovery, let's make the following points clear:
1. This tool only works for InnoDB/XtraDB tables, not MyISAM tables (note: Percona claims to have a set of tools for MyISAM tables, but I haven't tried).
2. This tool restores from saved MySQL data files instead of running MySQL Server.
3. There is no guarantee that the total data 1 can be recovered. For example, the overwritten data cannot be recovered, in which case it may be necessary to recover in a systematic or physical manner, which is not the scope of this tool.
4. The best time to recover is to backup the MySQL data file as soon as possible when you find that the data is missing.
5. Using this tool requires some manual work, which is not done automatically.
The recovery process depends on your knowledge of the lost data, and you may have to choose between different versions of the data. The more you know about your data, the more likely you are to recover.
Next, here is an example of how to restore with this tool.
1. Prerequisites
First, it is important to understand that the innodb-tools tool does not restore data by connecting to database online, but by copying data offline. Note: do not copy InnoDB files directly while MySQL is running. This is not secure and will affect the data recovery process.
In order to complete data recovery, you must know the table structure (column name, data type) to be recovered. The easiest way to do this is SHOW CREATE TABLE, though several alternatives will be introduced later. Therefore, if you have an MySQL server backup, even if the data is very early and even if there are no records in the table, it can be helpful to use the innodb-tools tool for recovery. But that's not necessary.
2. Simple example
mysql> TRUNCATE TABLE customer;
3. Build tools
To build the innodb-tools tool, you need to rely on the C compiler, make tool, and so on.
1. Download the source code of innodb-tools tool:
wget https://launchpad.net/percona-data-recovery-tool-for-innodb/trunk/release-0.5/+download/percona-data-recovery-tool-for-innodb-0.5.tar.gztar -zxvf percona-data-recovery-tool-for-innodb-0.5.tar.gz
2. Enter the mysql-source directory in the root directory after decompression and run the configuration command (note: do not run the make command) :
cd percona-data-recovery-tool-for-innodb-0.5/mysql-source
./configure
3. After completing the configuration steps, go back to the root directory after decompression, run make command, compile and generate page_parser and constraints_parser tools:
cd ..
make
The page_parser tool will parse the table's page and row structure based on the underlying implementation of InnoDB. The constraints_parser tool is not in use for the time being, and you will need to recompile and generate the table structure after you define it.
If there is a problem during compilation, click here. There are no problems with this article, so I will not list 11.
Extract the required pages
The default size of the InnoDB page is 16K, and each page belongs to a particular index in a particular table. The page_parser tool copies each page into a separate file by reading the data file, according to index ID in the header.
If your MySQL server is configured to innodb_file_per_table=1, then the system has implemented the above procedure for you. All the required pages are in the.ibd file, and usually you don't need to shred it. However, if the.ibd file may contain more than one index, it may be necessary to separate the pages. If MySQL server is not configured with innodb_file_per_table, then the data will be stored in a global table namespace (usually a file named ibdata1, as in this case) and the file will need to be shred by page.
4.1 page segmentation
Run the page_parser tool to shard:
The & # 8226; If MySQL is before 5.0 and InnoDB is in REDUNDANT format, run the following command:
./page_parser -4 -f /path/to/ibdata1
The & # 8226; If MySQL is version 5.0 and InnoDB is in COMPACT format, run the following command:
./page_parser -5 -f /path/to/ibdata1
When run, the page_parser tool creates an pages- < TIMESTAMP > Where TIMESTAMP is the UNIX system timestamp. Under this directory, create a subdirectory for each index ID, index ID of the page. Such as:
pages-1330842944/FIL_PAGE_INDEX/0-1/1-00000008.page
pages-1330842944/FIL_PAGE_INDEX/0-1/6-00000008.page
4.2 select the required Index ID
1 generally, we need to recover according to the table's primary key (PRIMARY index), which contains all rows. Here are some steps you can take:
If the database is still running and the tables have not been dropped by drop, launch InnoDB Tablespace Monitor and output all the tables and the error log files for indexes, index IDs to MySQL server. Create the innodb_table_monitor table to collect the storage method of the innodb storage engine table and its indexes:
mysql> CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB;
If innodb_table_monitor already exists, drop table is then re-create table. After the output of the MySQL error log, drop can drop the table to stop printing out more monitoring. An example of the output is as follows:
TABLE: name sakila/customer, id 0 142, columns 13, indexes 4, appr.rows 0
COLUMNS: customer_id: DATA_INT len 2 prec 0; store_id: DATA_INT len 1 prec 0; first_name: type 12 len 135 prec 0; last_name: type 12 len 135 prec 0; email:
type 12 len 150 prec 0; address_id: DATA_INT len 2 prec 0; active: DATA_INT len 1 prec 0; create_date: DATA_INT len 8 prec 0; last_update: DATA_INT len 4 pr
ec 0; DB_ROW_ID: DATA_SYS prtype 256 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 257 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 258 len 7 prec 0;
INDEX: name PRIMARY, id 0 286, fields 1/11, type 3
root page 50, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: customer_id DB_TRX_ID DB_ROLL_PTR store_id first_name last_name email address_id active create_date last_update
INDEX: name idx_fk_store_id, id 0 287, fields 1/2, type 0
root page 56, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: store_id customer_id
INDEX: name idx_fk_address_id, id 0 288, fields 1/2, type 0
root page 63, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: address_id customer_id
INDEX: name idx_last_name, id 0 289, fields 1/2, type 0
root page 1493, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: last_name customer_id
Here, we restore the customer table under the sakila library, from which we can get its primary key information:
INDEX: name PRIMARY, id 0 286, fields 1/11, type 3
Index ID is 0 256, so the InnoDB pages we need to restore are located in the 0-256 subdirectories.
Note: the above method of obtaining index ID of the table is described in the original document. In practice, this paper takes a simpler way to directly restore all InnoDB pages generated by page_parser. Practice has proved that this method is also feasible :)
5. Generate table definitions
In step 4, we have found the data we need. Next, we need to find the table structure, create the table definition, compile it into constraints_parser, and then use this tool to extract the rows from the InnoDB page from the table.
The table definition includes the columns in the table, the column order, and the data types. If MySQL server is still running and the table has not been dropped by drop, the simple utility SHOW CREATE TABLE can collect this information. This table structure information is then used to create a table definition for an C structure identity, which is then compiled into the constraints_parser tool. The definition of the C structure is stored in include/ table_defs.h.
The easiest way is the create_defs.pl Perl script, which connects to MySQL server, reads the SHOW CREATE TABLE result, and outputs the generated table definition to standard output. Here is an example where the results are directly redirected to include/ table_defs.h:
If possible, the easiest way to create the table definition is with the create_defs.pl Perl script. It connects to the MySQL server and reads SHOW CREATE TABLE output, and prints the generated definition to its standard output. Here is an example:
$ ./create_defs.pl --host=localhost --user=root --password=123456 --db=sakila --table=customer > include/table_defs.h
Here is the table structure in the example:
CREATE TABLE `customer` (
`customer_id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`store_id` tinyint(3) UNSIGNED NOT NULL,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`email` varchar(50) DEFAULT NULL,
`address_id` smallint(5) UNSIGNED NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
`create_date` datetime NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`customer_id`),
KEY `idx_fk_store_id` (`store_id`),
KEY `idx_fk_address_id` (`address_id`),
KEY `idx_last_name` (`last_name`),
CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Here is the generated table definition:
#ifndef table_defs_h
#define table_defs_h
// Table definitions
table_def_t table_definitions[] = {
{
name: "customer",
{
{ /* smallint(5) unsigned */
name: "customer_id",
type: FT_UINT,
fixed_length: 2,
has_limits: TRUE,
limits: {
can_be_null: FALSE,
uint_min_val: 0,
uint_max_val: 65535
},
can_be_null: FALSE
},
{ /* Innodb's internally used field */
name: "DB_TRX_ID",
type: FT_INTERNAL,
fixed_length: 6,
can_be_null: FALSE
},
{ /* Innodb's internally used field */
name: "DB_ROLL_PTR",
type: FT_INTERNAL,
fixed_length: 7,
can_be_null: FALSE
},
{ /* tinyint(3) unsigned */
name: "store_id",
type: FT_UINT,
fixed_length: 1,
has_limits: TRUE,
limits: {
can_be_null: FALSE,
uint_min_val: 0,
uint_max_val: 255
},
can_be_null: FALSE
},
{ /* varchar(45) */
name: "first_name",
type: FT_CHAR,
min_length: 0,
max_length: 45,
has_limits: TRUE,
limits: {
can_be_null: FALSE,
char_min_len: 0,
char_max_len: 45,
char_ascii_only: TRUE
},
can_be_null: FALSE
},
{ /* varchar(45) */
name: "last_name",
type: FT_CHAR,
min_length: 0,
max_length: 45,
has_limits: TRUE,
limits: {
can_be_null: FALSE,
char_min_len: 0,
char_max_len: 45,
char_ascii_only: TRUE
},
can_be_null: FALSE
},
{ /* varchar(50) */
name: "email",
type: FT_CHAR,
min_length: 0,
max_length: 50,
has_limits: TRUE,
limits: {
can_be_null: TRUE,
char_min_len: 0,
char_max_len: 50,
char_ascii_only: TRUE
},
can_be_null: TRUE
},
{ /* smallint(5) unsigned */
name: "address_id",
type: FT_UINT,
fixed_length: 2,
has_limits: TRUE,
limits: {
can_be_null: FALSE,
uint_min_val: 0,
uint_max_val: 65535
},
can_be_null: FALSE
},
{ /* tinyint(1) */
name: "active",
type: FT_INT,
fixed_length: 1,
can_be_null: FALSE
},
{ /* datetime */
name: "create_date",
type: FT_DATETIME,
fixed_length: 8,
can_be_null: FALSE
},
{ /* timestamp */
name: "last_update",
type: FT_UINT,
fixed_length: 4,
can_be_null: FALSE
},
{ type: FT_NONE }
}
},
};
#endif
If necessary, you can edit and modify include/ table_defs.h; The constraints_parser tool is then recompiled according to include/ table_defs.h:
$ make
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c tables_dict.c -o lib/tables_dict.o
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -g -I include -I mysql-source/include -I mysql-source/innobase/include -o constraints_parser constraints_parser.c lib/tables_dict.o lib/print_data.o lib/check_data.o lib/libut.a lib/libmystrings.a
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -g -I include -I mysql-source/include -I mysql-source/innobase/include -o page_parser page_parser.c lib/tables_dict.o lib/libut.a
6. Extract line records from the page
6.1 merge pages into one file
As mentioned earlier, we need to recover the index ID 0 286 containing the data in the pages-1246363747/0-286 / directory.
total 120
-rw-r--r-- 1 root root 16384 Jun 30 05:09 1254-00001254.page
-rw-r--r-- 1 root root 16384 Jun 30 05:09 1255-00001255.page
-rw-r--r-- 1 root root 16384 Jun 30 05:09 1256-00001256.page
-rw-r--r-- 1 root root 16384 Jun 30 05:09 1257-00001257.page
-rw-r--r-- 1 root root 16384 Jun 30 05:09 50-00000050.page
-rw-r--r-- 1 root root 16384 Jun 30 05:09 74-00000050.page
Enter the following command to merge the pages:
$ find pages-1246363747/0-286/ -type f -name '*.page' | sort -n | xargs cat > pages-1246363747/0-286/customer_pages_concatenated
The resulting file: pages-1246363747/0-286 /customer_pages_concatenated will be the input to the constraints_parser tool.
6.2 run the constraints_parser tool
The next step to the heart of data recovery is to run the constraints_parser tool to extract row records. As with page_parser tool 1, you need to specify the InnoDB page format (COMPACT/REDUNDANT) with the -5 or -4 parameters, and the input file with -f.
To return to the example, we can run the constraints_parser tool like this (the following command is to restore 1 single page, or to restore files that have been merged after 6.1 steps) :
$ ./constraints_parser -5 -f pages-1246363747/0-286/50-00000050.page
Each row in the output contains the table name and the individual columns in the table. Note: there may be correct or incorrect line records. This section of the official documentation shows how to adjust the table definition to get as much valid data as possible while filtering out the garbage rows, which is not described in detail here.
customer 0 120 "" "" "" 32770 0 "0000-00-00 00:12:80" 0
customer 0 0 "" "" "" 0 0 "9120-22-48 29:44:00" 2
customer 61953 0 "" "" "" 2816 0 "7952-32-67 11:43:49" 0
customer 0 0 "" "" "" 0 0 "0000-00-00 00:00:00" 0
... snip ...
customer 0 0 "" "" "" 0 0 "0000-00-00 00:00:00" 16777728
customer 28262 114 "" "" NULL 25965 117 "4603-91-96 76:21:28" 5111809
customer 0 82 "" "" "" 22867 77 "2775-94-58 03:19:18" 1397573972
customer 2 1 "PATRICIA" "JOHNSON" "PATRICIA.JOHNSON@sakilacustomer.org" 6 1 "2006-02-14 22:04:36" 1140008240
customer 3 1 "LINDA" "WILLIAMS" "LINDA.WILLIAMS@sakilacustomer.org" 7 1 "2006-02-14 22:04:36" 1140008240
customer 4 2 "BARBARA" "JONES" "BARBARA.JONES@sakilacustomer.org" 8 1 "2006-02-14 22:04:36" 1140008240
customer 5 1 "ELIZABETH" "BROWN" "ELIZABETH.BROWN@sakilacustomer.org" 9 1 "2006-02-14 22:04:36" 1140008240
customer 6 2 "JENNIFER" "DAVIS" "JENNIFER.DAVIS@sakilacustomer.org" 10 1 "2006-02-14 22:04:36" 1140008240
customer 7 1 "MARIA" "MILLER" "MARIA.MILLER@sakilacustomer.org" 11 1 "2006-02-14 22:04:36" 1140008240
customer 8 2 "SUSAN" "WILSON" "SUSAN.WILSON@sakilacustomer.org" 12 1 "2006-02-14 22:04:36" 1140008240
customer 9 2 "MARGARET" "MOORE" "MARGARET.MOORE@sakilacustomer.org" 13 1 "2006-02-14 22:04:36" 1140008240
... snip ...
customer 0 0 "" "" "" 0 0 "0000-00-00 00:00:00" 0
customer 0 0 "" "" "" 0 0 "7679-35-98 86:44:53" 720578985
7. Import data into the database
Finally, to complete the data recovery, you need to import the output of the constraints_parser tool from step 6 into the database using the LOAD DATA INFILE command. The command is as follows:
wget https://launchpad.net/percona-data-recovery-tool-for-innodb/trunk/release-0.5/+download/percona-data-recovery-tool-for-innodb-0.5.tar.gztar -zxvf percona-data-recovery-tool-for-innodb-0.5.tar.gz
At this point, the data recovery and import process is complete. Hopefully, you won't have the opportunity to practice the methods described in this article.