The method of InnoDB data recovery in the case of Mysql exception recovery without primary key _mysql

This paper describes the method of InnoDB data recovery in the case of MySQL exception recovery without primary key. Share to everyone for your reference, specific as follows:

MySQL in the InnoDB engine database abnormal recovery, generally require a primary key or unique index, in fact this is not necessary, when there is no index information, you can restore the entire table-level index_id

Create a mock table-no primary key

mysql> CREATE TABLE ' t1 ' (-> ' messageid ' varchar () character set UTF8 not NULL,-> ' tokenid ' varchar (20 Character set UTF8 not null,-> ' mobile ' varchar character Set UTF8 default NULL,-> ' msgformat ' int (1) Not NULL,-> ' msgcontent ' varchar (1000) character set UTF8 default null,-> ' scheduledate ' timestamp not NULL Default ' 0000-00-00 00:00:00 ',-> ' deliverstate ' int (1) default null,-> ' deliverdtime ' timestamp ' not NULL def
Ault ' 0000-00-00 00:00:00 '->) engine=innodb DEFAULT Charset=utf8;
Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into T1 select * from Sms_service.sms_send_record; Query OK, Rows Affected (0.00 sec) records:11 duplicates:0 warnings:0 ... mysql> insert into t1 SELECT * from t1
; Query OK, 81664 rows Affected (2.86 sec) records:81664 duplicates:0 warnings:0 mysql> INSERT INTO T1 select * from t
1; Query OK, 163328 rows affected (2.74 sec) records:163328 duplicates:0 warnings:0 mYsql> Select COUNT (*) from T1; +----------+
|
COUNT (*) |  +----------+
| 
326656 |

 +----------+ 1 row in Set (0.15 sec)

resolves the innodb file

[root@web103 mysql_recovery]# rm-rf pages-ibdata1/[root@web103 mysql_recovery]#./stream_parser-f/var/lib/mysql/           Ibdata1 Opening File:/var/lib/mysql/ibdata1 file Information:id of device containing inode number: 1344553 protection:100660 (regular file) number of hard links:1 user ID of Owner:2 7 Group ID of owner:27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of BL Ocks allocated:463312 time of the access:1440819443 Sat Aug 11:37:23 2015 time of the last modification:1440      819463 sat Aug 11:37:43 2015 time the last status change:1440819463 Sat Aug 11:37:43 2015 total size, in bytes: 236978176 (226.000 MiB) Size to process:236978176 (226.000 MiB) Opening file:/var/lib/mysql/ibdata1 file info Rmation:id of device containing file:2049 inode number:1344553 protection:100660 (regular fi    Le) Number of hard links:      1 User ID of owner:27 group ID of owner:27 device ID (if special file): 0 blocksize for FileSystem I/O: 4096 number of blocks allocated:463312 Opening file:/var/lib/mysql/ibdata1 file Information:ti Me of last access:1440819443 Sat Aug 11:37:23 2015 time of last modification:1440819463 Sat Aug 29 11:37:43 20 ID of device containing file:2049 inode number:1344553 protection:100660 time of last stat         US change:1440819463 Sat Aug 11:37:43 2015 total size, in bytes:236978176 (226.000 MiB) size to process: 236978176 (226.000 MiB) Opening file:/var/lib/mysql/ibdata1 file Information:id of device containing I Node number:1344553 protection:100660 (regular file) number of hard links:1 user ID of O     WNER:27 group ID of owner:27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated:463312 time of the access:1440819443 Sat Aug 11:37:23 2015 time of the last modification:1440819463 The SAT Aug 11:37:43 2015 time of the last status change:1440819463 sat Aug 11:37:43 2015 total size, in bytes:236  978176 (226.000 MiB) Size to process:236978176 (226.000 MiB) (regular file) number of hard links:1 user ID of owner:27 Group ID of owner:27 device ID (if special file): 0 blocksize for filesystem  I/O: 4096 number of blocks allocated:463312 time of last access:1440819443 Sat Aug 11:37:23 2015 Last modification:1440819463 Sat Aug 11:37:43 2015 time to last status change:1440819463 sat Aug 29 11:37:43 201 5 total size, in bytes:236978176 (226.000 MiB) size to process:236978176 (226.000 MiB) Opening file:/var/l             Ib/mysql/ibdata1 File Information:id of device containing file:2049 inode number:1344553 protection: 100660 (RegularFile) Number of hard links:1 user ID of owner:27 group ID of owner:27 device ID (if speci      Al file: 0 blocksize for filesystem I/O: 4096 number of blocks allocated:463312 time of access: 1440819443 sat Aug 11:37:23 2015 time to last modification:1440819463 sat Aug 11:37:43 2015 time of last status C hange:1440819463 Sat Aug 11:37:43 2015 total size, in bytes:236978176 (226.000 MiB) size to Process:2  36978176 (226.000 MiB) Opening file:/var/lib/mysql/ibdata1 file Information:id of device containing the inode number:1344553 protection:100660 (regular file) number of hard links:1 user ID of Owner : Group ID of owner:27 device ID (if special file): 0 blocksize for filesystem I/O: 409 6 of blocks allocated:463312 time of the access:1440819443 Sat Aug 11:37:23 2015 time of the last Modifi cation:1440819463 Sat Aug 11:37:43 2015 time of the last status change:1440819463 Sat Aug-11:37:43 2015 Opening file:/var/lib/mysql/ibdat  A1 File Information:id of device containing file:2049 inode number:1344553 protection:100660  (regular file) number of hard links:1 user ID of owner:27 group ID of owner:27 device ID (If special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated:463312 total size, in B ytes:236978176 (226.000 MiB) Size to process:236978176 (226.000 MiB) time of last access:1440819443 S At Aug 11:37:23 2015 of the last modification:1440819463 Sat Aug 11:37:43 2015 time of the last status change:144 0819463 Sat Aug 11:37:43 2015 total size, in bytes:236978176 (226.000 MiB) size to process:236978176 (22           6.000 MiB) Opening file:/var/lib/mysql/ibdata1 file Information:id of device containing inode number: 1344553 Protection:100660 (regular file) number of hard links:1 user ID of owner:27 group ID of: Device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated:46 3312 time of the access:1440819465 sat Aug 11:37:45 2015 time of the last modification:1440819463 Sat Aug 29 11:3 7:43 2015 time of the last status change:1440819463 Sat Aug 11:37:43 Total size, in 2015 (226.000

 MiB) Size to process:236978176 (226.000 MiB) All workers finished in 0 sec

Recovery Data Dictionary

[root@web103 mysql_recovery]#./recover_dictionary.sh 
Generating dictionary tables dumps ... OK
Creating Test Database ... OK
Creating dictionary tables in database test:
sys_tables ... OK
Sys_columns ... OK
sys_indexes ... OK
Sys_fields ... OK all
OK
Loading dictionary tables data:
sys_tables ... RECs OK
Sys_columns ... 397 RECs OK
sys_indexes ... RECs OK
Sys_fields ... RECs OK all
OK

Analyze the data dictionary and find out index_id

Here we need to note that for table restores without primary keys, our corresponding type is Gen_clust_index

Mysql> SELECT * from Sys_tables where name= ' test/t1 '; 
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+ | NAME | ID | N_cols | TYPE | mix_id | Mix_len | Cluster_Name |
Space | 
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+ | Test/t1 |      100 |  8 |   1 |    0 |       0 |   | 
0 | 
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
Rows in Set (0.00 sec) mysql> SELECT * from sys_indexes where table_id=100; +----------+-----+------------------------------+----------+------+-------+------------+
| table_id | ID | NAME | N_fields | TYPE | Space |
Page_no |   +----------+-----+------------------------------+----------+------+-------+------------+
| 100 | 119 |    Gen_clust_index |  0 |   1 |    0 | 
2951 | +----------+-----+------------------------------+----------+------+-------+------------+ rows in Set (0.00 sec)

 

Restore Data

root@web103 mysql_recovery]#./c_parser-5f pages-ibdata1/fil_page_index/0000000000000119.page-t Dictionary/t1.sql >/tmp/2.txt 2>2.sql [root@web103 mysql_recovery]# more/tmp/2.txt--Page id:10848, format:compact, Records list:    Valid, expected records: (00000002141B) 0000009924f2 80000027133548 T1 "82334502212106951" "sdk-bbx-010-18681" "13718311436" 8 "Dear Users Hello: Your mobile phone verification code for 916515 if not my operation, please call Oscar customer service: 400-620-7575.    "" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00" 00000002141C 0000009924f2 80000027133558 T1 "82339012756833423" "sdk-bbx-010-18681" "13718311436" 8 "Dear Users Hello: Your mobile phone verification code for 396108 if not my operation, please call Oscar customer service: 400-620-7575.   "" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00" 00000002141D 0000009924f2 80000027133568 T1 "8234322198577796" "sdk-bbx-010-18681" "13718311436" 8 "Dear Users Hello: Your mobile phone verification code for 935297 if not my operation, please call Oscar customer service: 400-620-7575.   "" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00" 00000002141E 0000009924f2 80000027133578 T1 "10235259536125650" "sdk-bbx-010-18681" "13718311436" 8 "Dear Users Hello: Your mobile phone verification code for 474851 if not my operation, please call Oscar customer service: 400-620-7575.    "" "2010-01-01 00:00:00" 0 "1970-01-01 07:00:00" 00000002141F 0000009924f2 80000027133588 T1 "10235353811295807" "sdk-bbx-010-18681" "13718311436" 8 "Dear Users Hello: Your mobile phone verification code for 444632 if not my operation, please call Oscar customer service: 400-620-7575. "" 2010-01-01 00:00:00 "0" 1970-01-01 07:00:00 "000000021420 0000009924f2 80000027133598 T1" 102354211240398235 "" sdk-bbx-010-18681 "" 13718311436 "8" Respected users hello: your mobile phone verification code for 478503 if not my operation, please call Oscar customer service: 400-620-7575. "" 2010-01-01 00:00:00 "0" 1970-01-01 07:00:00 "000000021421 0000009924f2 800000271335a8 T1" 102354554052884567 "" sdk-bbx-010-18681 "" 13718311436 "8" Respected users hello: your mobile phone verification code for 216825 if not my operation, please call Oscar customer service: 400-620-7575. "" 2010-01-01 00:00:00 "0" 1970-01-01 07:00:00 "000000021422 0000009924f2 800000271335b8 T1" 132213454294519126 "" sdk-bbx-010-18681 "" 13718311436 "8" Respected users hello: your mobile phone verification code for 854812 if not my operation, please call Oscar customer service: 400-620-7575. "" 2010-01-01 00:00:00 "0" 1970-01-01 07:00:00 "000000021423 0000009924f2 800000271335c8 T1" 82329022242584577 "" sdk-bbx-010-18681 " "13718311436" 8 "Dear Users Hello: Your mobile phone verification code for 253127 if not my operation, please call Oscar customer service: 400-620-7575.
"" 2010-01-01 00:00:00 "0" 2015-08-26 22:02:17 ".....

 [root@web103 mysql_recovery]# cat/tmp/2.txt|grep-v "Page ID:" |wc-l 380731

Because there is no primary key, there may be some duplication in the recovery of records, as a whole, it can be more perfect to recover data

More information about MySQL interested readers can view the site topics: "MySQL Log operation skills Daquan", "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL commonly used function large summary"

I hope this article will help you with the MySQL database meter.