Email: service@parnassusdata.com 7 x 24 online support!

Home

DOWNLOAD DBRECOVER

Recover Corrupt MySQL Database

Recover Corrupt MySQL Database

Posted by PDSERVICE on Nov 07, 2019 In
The unDROP for InnoDB tool can be used to recover corrupt MySQL database. In this post we will show how to repair MySQL database if its files became corrupted and even innodb_force_recovery=6 doesn’t help.
 
The corruption of InnoDB tablespace may be caused by many reasons. A dying hard drive can write garbage, thus page checksum will be wrong. InnoDB then reports to the error log:
 
Shell
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.
1
2
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.
MySQL is well know for poor start-up script. A simple upgrade procedure may end up with two mysqld processes writing to the same tablespace. That leads to the corruption too. Sometimes power reset corrupts not only InnoDB files, but file system becomes unusable for the operating system.
 
InnoDB is very strict when it works with pages. If checksum doesn’t match or some field in the header carries unexpected value InnoDB wisely prefers to crash to avoid further corruption.
 
The manual suggests to start MySQL with innodb_force_recovery option. The purpose of this option is to let user dump their data. There are no means to repair the tablespace. The user must drop the tablespace, create new one and load the data back.
 
innodb_force_recovery accepts values from one to six. The higher value, the more tests InnoDB disables.
 
In this post we will assume MySQL can not start even with innodb_force_recovery=6.
 
The recovery toolkit works directly with InnoDB files, it can read records from the InnoDB page. If some part of the page is damaged it will just skip that piece and continue to read the records further in the page.
 
So, let’s corrupt some InnoDB file and recover the table.
 
InnoDB corruption
For sake of simplicity we will overwrite a part of .ibd file in the area with user data.
In real life the corruption may be at any place of index PRIMARY.
At the middle of the PRIMARY index of table sakila.actor we will rewrite the data with 128 characters ‘A’:
 
Shell
0000C058  00 00 00 02 00 32 01 00 02 00 1C 69 6E 66 69 6D 75 6D 00 05 .....2.....infimum..
0000C06C  00 0B 00 00 73 75 70 72 65 6D 75 6D 07 08 00 00 10 00 29 00 ....supremum......).
0000C080  01 00 00 00 00 05 1E 9F 00 00 01 4D 01 10 50 45 4E 45 4C 4F ...........M..PENELO
0000C094  50 45 47 55 49 4E 45 53 53 43 F2 F5 A9 08 04 00 00 18 00 26 PEGUINESSC.........&
0000C0A8  00 02 00 00 00 00 05 1E 9F 00 00 01 4D 01 1A 4E 49 43 4B 57 ............M..NICKW
0000C0BC  41 48 4C 42 45 52 47 43 F2 F5 A9 05 02 00 00 20 00 21 00 03 AHLBERGC....... .!..
0000C0D0  00 00 00 00 05 1E 9F 00 00 01 4D 01 24 45 44 43 48 41 53 45 ..........M.$EDCHASE
0000C0E4  43 F2 F5 A9 05 08 04 00 28 00 27 00 04 00 00 00 00 05 1E 9F C.......(.'.........
0000C0F8  00 00 01 4D 01 2E 4A 45 4E 4E 49 46 45 52 44 41 56 49 53 43 ...M..JENNIFERDAVISC
0000C10C  F2 F5 A9 0C 06 00 00 30 00 2C 00 05 00 00 00 00 05 1E 9F 00 .......0.,..........
0000C120  00 01 4D 01 38 4A 4F 48 4E 4E 59 4C 4F 4C 4C 4F 42 52 49 47 ..M.8JOHNNYLOLLOBRIG
0000C134  49 44 41 43 F2 F5 A9 09 05 00 00 38 00 28 00 06 00 00 00 00 IDAC.......8.(......
0000C148  05 1E 9F 00 00 01 41 41 41 41 41 41 41 41 41 41 41 41 41 41 ......AAAAAAAAAAAAAA
0000C15C  41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 AAAAAAAAAAAAAAAAAAAA
0000C170  41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 AAAAAAAAAAAAAAAAAAAA
0000C184  41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 AAAAAAAAAAAAAAAAAAAA
0000C198  41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 AAAAAAAAAAAAAAAAAAAA
0000C1AC  41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 AAAAAAAAAAAAAAAAAAAA
0000C1C0  41 41 41 41 41 41 41 41 41 41 41 41 41 41 4E 4B 43 F2 F5 A9 AAAAAAAAAAAAAANKC...
0000C1D4  05 09 00 00 58 00 28 00 0A 00 00 00 00 05 1E 9F 00 00 01 4D ....X.(............M
0000C1E8  01 6A 43 48 52 49 53 54 49 41 4E 47 41 42 4C 45 43 F2 F5 A9 .jCHRISTIANGABLEC...
0000C1FC  04 04 00 00 60 00 22 00 0B 00 00 00 00 05 1E 9F 00 00 01 4D ....`."............M
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
0000C058  00 00 00 02 00 32 01 00 02 00 1C 69 6E 66 69 6D 75 6D 00 05 .....2.....infimum..
0000C06C  00 0B 00 00 73 75 70 72 65 6D 75 6D 07 08 00 00 10 00 29 00 ....supremum......).
0000C080  01 00 00 00 00 05 1E 9F 00 00 01 4D 01 10 50 45 4E 45 4C 4F ...........M..PENELO
0000C094  50 45 47 55 49 4E 45 53 53 43 F2 F5 A9 08 04 00 00 18 00 26 PEGUINESSC.........&
0000C0A8  00 02 00 00 00 00 05 1E 9F 00 00 01 4D 01 1A 4E 49 43 4B 57 ............M..NICKW
0000C0BC  41 48 4C 42 45 52 47 43 F2 F5 A9 05 02 00 00 20 00 21 00 03 AHLBERGC....... .!..
0000C0D0  00 00 00 00 05 1E 9F 00 00 01 4D 01 24 45 44 43 48 41 53 45 ..........M.$EDCHASE
0000C0E4  43 F2 F5 A9 05 08 04 00 28 00 27 00 04 00 00 00 00 05 1E 9F C.......(.'.........
0000C0F8  00 00 01 4D 01 2E 4A 45 4E 4E 49 46 45 52 44 41 56 49 53 43 ...M..JENNIFERDAVISC
0000C10C  F2 F5 A9 0C 06 00 00 30 00 2C 00 05 00 00 00 00 05 1E 9F 00 .......0.,..........
0000C120  00 01 4D 01 38 4A 4F 48 4E 4E 59 4C 4F 4C 4C 4F 42 52 49 47 ..M.8JOHNNYLOLLOBRIG
0000C134  49 44 41 43 F2 F5 A9 09 05 00 00 38 00 28 00 06 00 00 00 00 IDAC.......8.(......
0000C148  05 1E 9F 00 00 01 41 41 41 41 41 41 41 41 41 41 41 41 41 41 ......AAAAAAAAAAAAAA
0000C15C  41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 AAAAAAAAAAAAAAAAAAAA
0000C170  41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 AAAAAAAAAAAAAAAAAAAA
0000C184  41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 AAAAAAAAAAAAAAAAAAAA
0000C198  41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 AAAAAAAAAAAAAAAAAAAA
0000C1AC  41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 AAAAAAAAAAAAAAAAAAAA
0000C1C0  41 41 41 41 41 41 41 41 41 41 41 41 41 41 4E 4B 43 F2 F5 A9 AAAAAAAAAAAAAANKC...
0000C1D4  05 09 00 00 58 00 28 00 0A 00 00 00 00 05 1E 9F 00 00 01 4D ....X.(............M
0000C1E8  01 6A 43 48 52 49 53 54 49 41 4E 47 41 42 4C 45 43 F2 F5 A9 .jCHRISTIANGABLEC...
0000C1FC  04 04 00 00 60 00 22 00 0B 00 00 00 00 05 1E 9F 00 00 01 4D ....`."............M
Corrupted InnoDB table crashes MySQL
When MySQL reads a page with user data check sum is wrong and the server crashes.
 
Shell
mysql> SELECT COUNT(*) FROM sakila.actor
+----------+
| COUNT(*) |
+----------+
|      200 |
+----------+
 
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
1
2
3
4
5
6
7
8
mysql> SELECT COUNT(*) FROM sakila.actor
+----------+
| COUNT(*) |
+----------+
|      200 |
+----------+
 
ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
Before the crash MySQL write to the error log what exactly went wrong and dumps the faulty page:
 
Shell
Version: '5.6.19-67.0'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Percona Server (GPL), Release 67.0, Revision 618
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.
InnoDB: You may have to recover from a backup.
2014-07-14 20:18:44 7f060bfff700 InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 1bce9a5000000004ffffffffffffffff0000000026c3095945bf00000000000000000
1
2
3
4
5
6
Version: '5.6.19-67.0'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Percona Server (GPL), Release 67.0, Revision 618
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.
InnoDB: You may have to recover from a backup.
2014-07-14 20:18:44 7f060bfff700 InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 1bce9a5000000004ffffffffffffffff0000000026c3095945bf00000000000000000
Recovering InnoDB Table Corruption
When you see a corruption in the InnoDB tablespace the first thing to try is to start MySQL with innodb_force_recovery option. It makes sense to try all values starting from one to six.
 
We assume that MySQL doesn’t start even with innodb_force_recovery=6 or it starts, but any SELECT crashes it.
 
The recovery plan is following
 
Split corrupted InnoDB tablespace into pages; sort the pages by type and index_id
Fetch records from PRIMARY index of the table
DROP corrupted table and create new one
Load records back into MySQL
We would need to parse two tablespaces: ibdata1 and actor.ibd (since option innodb_file_per_table=ON). The InnoDB dictionary is stored in ibdata1, we need it to know index_id of the PRIMARY index of table sakila.actor.
 
Split corrupted InnoDB tablespace
Shell
root@test:~/recovery/undrop-for-innodb# ./stream_parser -f /var/lib/mysql/ibdata1
Opening file: /var/lib/mysql/ibdata1
File information:
 
ID of device containing file:        64768
inode number:                         8028
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                      106
group ID of owner:                     114
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36864
time of last access:            1406832698 Thu Jul 31 14:51:38 2014
time of last modification:      1406833058 Thu Jul 31 14:57:38 2014
time of last status change:     1406833058 Thu Jul 31 14:57:38 2014
total size, in bytes:             18874368 (18.000 MiB)
 
Size to process:                  18874368 (18.000 MiB)
All workers finished in 0 sec
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
root@test:~/recovery/undrop-for-innodb# ./stream_parser -f /var/lib/mysql/ibdata1
Opening file: /var/lib/mysql/ibdata1
File information:
 
ID of device containing file:        64768
inode number:                         8028
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                      106
group ID of owner:                     114
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36864
time of last access:            1406832698 Thu Jul 31 14:51:38 2014
time of last modification:      1406833058 Thu Jul 31 14:57:38 2014
time of last status change:     1406833058 Thu Jul 31 14:57:38 2014
total size, in bytes:             18874368 (18.000 MiB)
 
Size to process:                  18874368 (18.000 MiB)
All workers finished in 0 sec
Now actor.ibd‘s turn
 
Shell
root@test:~/recovery/undrop-for-innodb# ./stream_parser -f /var/lib/mysql/sakila/actor.ibd
Opening file: /var/lib/mysql/sakila/actor.ibd
File information:
 
ID of device containing file:        64768
inode number:                         8037
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                      106
group ID of owner:                     114
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:            224
time of last access:            1406832349 Thu Jul 31 14:45:49 2014
time of last modification:      1406832300 Thu Jul 31 14:45:00 2014
time of last status change:     1406832300 Thu Jul 31 14:45:00 2014
total size, in bytes:               114688 (112.000 kiB)
 
Size to process:                    114688 (112.000 kiB)
All workers finished in 0 sec
 
root@test:~/recovery/undrop-for-innodb# 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
root@test:~/recovery/undrop-for-innodb# ./stream_parser -f /var/lib/mysql/sakila/actor.ibd
Opening file: /var/lib/mysql/sakila/actor.ibd
File information:
 
ID of device containing file:        64768
inode number:                         8037
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                      106
group ID of owner:                     114
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:            224
time of last access:            1406832349 Thu Jul 31 14:45:49 2014
time of last modification:      1406832300 Thu Jul 31 14:45:00 2014
time of last status change:     1406832300 Thu Jul 31 14:45:00 2014
total size, in bytes:               114688 (112.000 kiB)
 
Size to process:                    114688 (112.000 kiB)
All workers finished in 0 sec
 
root@test:~/recovery/undrop-for-innodb# 
Recover InnoDB dictionary
We need to know index_id of the PRIMARY index of table sakila.actor. See more about InnoDB dictionary. Now we will just get index_id of sakila.actor:
 
Shell
root@test:~/recovery/undrop-for-innodb# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql |grep actor
000000000504    85000001320110  SYS_TABLES      "sakila/actor"  13      4       1       0       0       ""      1
00000000050D    8E0000013B0110  SYS_TABLES      "sakila/film\_actor"    20      3       1       0       0       ""      8
...
root@test:~/recovery/undrop-for-innodb# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql |grep 13
000000000300    810000012D01D3  SYS_INDEXES     11      13      "REF\_IND"      1       0       0       304
000000000504    85000001320178  SYS_INDEXES     13      15      "PRIMARY"       1       3       1       3
000000000504    850000013201A6  SYS_INDEXES     13      16      "idx\_actor\_last\_name"        1       0       1       4
000000000505    860000013301CE  SYS_INDEXES     14      17      "PRIMARY"       1       3       2       3
...
1
2
3
4
5
6
7
8
9
10
root@test:~/recovery/undrop-for-innodb# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql |grep actor
000000000504    85000001320110  SYS_TABLES      "sakila/actor"  13      4       1       0       0       ""      1
00000000050D    8E0000013B0110  SYS_TABLES      "sakila/film\_actor"    20      3       1       0       0       ""      8
...
root@test:~/recovery/undrop-for-innodb# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql |grep 13
000000000300    810000012D01D3  SYS_INDEXES     11      13      "REF\_IND"      1       0       0       304
000000000504    85000001320178  SYS_INDEXES     13      15      "PRIMARY"       1       3       1       3
000000000504    850000013201A6  SYS_INDEXES     13      16      "idx\_actor\_last\_name"        1       0       1       4
000000000505    860000013301CE  SYS_INDEXES     14      17      "PRIMARY"       1       3       2       3
...
So, the index_id of the PRIMARY index of sakila.actor table is 15, the fifth column in the dump.
 
Recover records from PRIMARY index of the table
c_parser reads InnoDB pages, matches them with a given table structure and dumps records in tab-separated values format.
 
Opposite to InnoDB, when c_parser hits corrupted area it skips it and continue reading the page.
 
Let’s read the records from index_id 15, which is the PRIMARY index according to the dictionary.
 
Shell
root@test:~/recovery/undrop-for-innodb# ./c_parser -6f pages-actor.ibd/FIL_PAGE_INDEX/0000000000000015.page -t sakila/actor.sql > dumps/default/actor 2> dumps/default/actor_load.sql
root@test:~/recovery/undrop-for-innodb# cat dumps/default/actor
-- Page id: 3, Format: COMPACT, Records list: Invalid, Expected records: (0 200)
72656D756D07    08000010002900  actor   30064   "\0\0\0\0"      ""      "1972-09-20 23:07:44"
1050454E454C    4F50454755494E  actor   19713   "ESSC▒" ""      "2100-08-09 07:52:36"
00000000051E    9F0000014D011A  actor   2       "NICK"  "WAHLBERG"      "2006-02-15 04:34:33"
00000000051E    9F0000014D0124  actor   3       "ED"    "CHASE" "2006-02-15 04:34:33"
00000000051E    9F0000014D012E  actor   4       "JENNIFER"      "DAVIS" "2006-02-15 04:34:33"
00000000051E    9F0000014D0138  actor   5       "JOHNNY"        "LOLLOBRIGIDA"  "2006-02-15 04:34:33"
00000000051E    9F000001414141  actor   6       "AAAAA" "AAAAAAAAA"     "2004-09-10 01:53:05"
 
00000000051E    9F0000014D016A  actor   10      "CHRISTIAN"     "GABLE" "2006-02-15 04:34:33"
...
1
2
3
4
5
6
7
8
9
10
11
12
13
root@test:~/recovery/undrop-for-innodb# ./c_parser -6f pages-actor.ibd/FIL_PAGE_INDEX/0000000000000015.page -t sakila/actor.sql > dumps/default/actor 2> dumps/default/actor_load.sql
root@test:~/recovery/undrop-for-innodb# cat dumps/default/actor
-- Page id: 3, Format: COMPACT, Records list: Invalid, Expected records: (0 200)
72656D756D07    08000010002900  actor   30064   "\0\0\0\0"      ""      "1972-09-20 23:07:44"
1050454E454C    4F50454755494E  actor   19713   "ESSC▒" ""      "2100-08-09 07:52:36"
00000000051E    9F0000014D011A  actor   2       "NICK"  "WAHLBERG"      "2006-02-15 04:34:33"
00000000051E    9F0000014D0124  actor   3       "ED"    "CHASE" "2006-02-15 04:34:33"
00000000051E    9F0000014D012E  actor   4       "JENNIFER"      "DAVIS" "2006-02-15 04:34:33"
00000000051E    9F0000014D0138  actor   5       "JOHNNY"        "LOLLOBRIGIDA"  "2006-02-15 04:34:33"
00000000051E    9F000001414141  actor   6       "AAAAA" "AAAAAAAAA"     "2004-09-10 01:53:05"
 
00000000051E    9F0000014D016A  actor   10      "CHRISTIAN"     "GABLE" "2006-02-15 04:34:33"
...
We have identify some valid records, but certainly there is also some “garbage”. Pay attention at the recovered records before “Nick Wahlberg”. Definitely there should be a records of “Penelope Guiness”, since we have not overwritten that data in the actor.ibd file.
 
Filters
We can improve the recovery quality by applying filters on possible values of certain fields. There are 200 records on the original table, but the first two “garbage” records have some weird identifiers (30064 and 19713). We know that actor identifier should be in the range of [1..300]. Therefore we tell the parser to match that condition. For this purpose we add a hint in the comments of actor.sql file that defines actor table. This comment should be in special format for parser to recognize them. Listing of the part of actor.sql file(note a comma after the comment!):
 
Shell
CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT
    /*!FILTER
     int_min_val: 1
     int_max_val: 300 */,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT
    /*!FILTER
     int_min_val: 1
     int_max_val: 300 */,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;
After applying the filter, recognized records have much better look:
 
Shell
root@test:~/recovery/undrop-for-innodb# ./c_parser -6f pages-actor.ibd/FIL_PAGE_INDEX/0000000000000015.page -t sakila/actor.sql > dumps/default/actor 2> dumps/default/actor_load.sql
root@test:~/recovery/undrop-for-innodb# head -10 dumps/default/actor
-- Page id: 3, Format: COMPACT, Records list: Invalid, Expected records: (0 200)
00000000051E    9F0000014D0110  actor   1       "PENELOPE"      "GUINESS"       "2006-02-15 04:34:33"
00000000051E    9F0000014D011A  actor   2       "NICK"  "WAHLBERG"      "2006-02-15 04:34:33"
00000000051E    9F0000014D0124  actor   3       "ED"    "CHASE" "2006-02-15 04:34:33"
00000000051E    9F0000014D012E  actor   4       "JENNIFER"      "DAVIS" "2006-02-15 04:34:33"
00000000051E    9F0000014D0138  actor   5       "JOHNNY"        "LOLLOBRIGIDA"  "2006-02-15 04:34:33"
00000000051E    9F000001414141  actor   6       "AAAAA" "AAAAAAAAA"     "2004-09-10 01:53:05"
00000000051E    9F0000014D016A  actor   10      "CHRISTIAN"     "GABLE" "2006-02-15 04:34:33"
00000000051E    9F0000014D0174  actor   11      "ZERO"  "CAGE"  "2006-02-15 04:34:33"
00000000051E    9F0000014D017E  actor   12      "KARL"  "BERRY" "2006-02-15 04:34:33"
1
2
3
4
5
6
7
8
9
10
11
12
root@test:~/recovery/undrop-for-innodb# ./c_parser -6f pages-actor.ibd/FIL_PAGE_INDEX/0000000000000015.page -t sakila/actor.sql > dumps/default/actor 2> dumps/default/actor_load.sql
root@test:~/recovery/undrop-for-innodb# head -10 dumps/default/actor
-- Page id: 3, Format: COMPACT, Records list: Invalid, Expected records: (0 200)
00000000051E    9F0000014D0110  actor   1       "PENELOPE"      "GUINESS"       "2006-02-15 04:34:33"
00000000051E    9F0000014D011A  actor   2       "NICK"  "WAHLBERG"      "2006-02-15 04:34:33"
00000000051E    9F0000014D0124  actor   3       "ED"    "CHASE" "2006-02-15 04:34:33"
00000000051E    9F0000014D012E  actor   4       "JENNIFER"      "DAVIS" "2006-02-15 04:34:33"
00000000051E    9F0000014D0138  actor   5       "JOHNNY"        "LOLLOBRIGIDA"  "2006-02-15 04:34:33"
00000000051E    9F000001414141  actor   6       "AAAAA" "AAAAAAAAA"     "2004-09-10 01:53:05"
00000000051E    9F0000014D016A  actor   10      "CHRISTIAN"     "GABLE" "2006-02-15 04:34:33"
00000000051E    9F0000014D0174  actor   11      "ZERO"  "CAGE"  "2006-02-15 04:34:33"
00000000051E    9F0000014D017E  actor   12      "KARL"  "BERRY" "2006-02-15 04:34:33"
You see, the record for “Penelope Guiness” is already here. The only issue remained – invalid record 6-“AAAAA”-“AAAAAAAAA”. This happens because the record appeared to have actor_id of 6, that corresponds to our expectations. Ideally, the dump must have not junk records, so you may try to add more filters on other fields.
Or, we can delete this records in the database manually later.
 
DROP corrupted table and create new one
As soon as we have dumps of all tables we need to create new instance of MySQL.
 
If it’s a single table corruption it makes sense to try innodb_force_recovery=6 to DROP the table.
 
If MySQL can’t even start, try to move the corrupt actor.ibd elsewhere. In the recovery mode after DROP TABLE actor MySQL will remove a record from the dictionary. Remove actor.frm if it still remains.
 
The point is to get clean up&running MySQL, ready to import the table dump.
 
Once MySQL is ready create an empty table actor:
 
Shell
mysql> CREATE TABLE `actor` (
    ->     `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    ->     `first_name` varchar(45) NOT NULL,
    ->     `last_name` varchar(45) NOT NULL,
    ->     `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->      PRIMARY KEY (`actor_id`),
    ->      KEY `idx_actor_last_name` (`last_name`)
    ->      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.01 sec)
 
mysql>
1
2
3
4
5
6
7
8
9
10
11
mysql> CREATE TABLE `actor` (
    ->     `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    ->     `first_name` varchar(45) NOT NULL,
    ->     `last_name` varchar(45) NOT NULL,
    ->     `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->      PRIMARY KEY (`actor_id`),
    ->      KEY `idx_actor_last_name` (`last_name`)
    ->      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.01 sec)
 
mysql>
Load records back into MySQL
Then we will load information from recovered dump:
 
Shell
root@test:~/recovery/undrop-for-innodb# mysql --local-infile -uroot -p$mypass
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> source dumps/default/actor_load.sql
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 199 rows affected, 1 warning (0.00 sec)
Records: 198  Deleted: 1  Skipped: 0  Warnings: 1
1
2
3
4
5
6
7
8
9
10
11
12
13
root@test:~/recovery/undrop-for-innodb# mysql --local-infile -uroot -p$mypass
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> source dumps/default/actor_load.sql
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 199 rows affected, 1 warning (0.00 sec)
Records: 198  Deleted: 1  Skipped: 0  Warnings: 1
The final step is to know how much data we’ve lost due to the corruption.
The c_parser provides count of expected and actually found records.
In the beginning of each page it give number of expected records:
 
Shell
-- Page id: 3, Format: COMPACT, Records list: Invalid, Expected records: (0 200)
1
-- Page id: 3, Format: COMPACT, Records list: Invalid, Expected records: (0 200)
Which means 200 records are expected, but the list of records is broken (thus, Records list: Invalid).
 
In the end of each page it gives a summary of what was actually found
 
Shell
-- Page id: 3, Found records: 197, Lost records: YES, Leaf page: YES
1
-- Page id: 3, Found records: 197, Lost records: YES, Leaf page: YES