How to recover innodb data without a primary key in case of MySQL exception recovery, mysqlinnodb

How to recover innodb data without a primary key in case of MySQL exception recovery, mysqlinnodb

This article describes how to recover innodb data in case of no primary key for MySQL exception recovery. We will share this with you for your reference. The details are as follows:

In the mysql innodb engine database exception recovery, the primary key or unique index is generally required. In fact, this is not necessary. When there is no index information, you can restore the index_id of the entire table.

Create a simulated table-no primary key

mysql> CREATE TABLE `t1` (  ->  `messageId` varchar(30) character set utf8 NOT NULL,  ->  `tokenId` varchar(20) character set utf8 NOT NULL,  ->  `mobile` varchar(14) 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 default '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, 11 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: 0mysql> insert into t1 select * from t1;Query OK, 163328 rows affected (2.74 sec)Records: 163328 Duplicates: 0 Warnings: 0mysql> select count(*) from t1;+----------+| count(*) |+----------+|  326656 | +----------+1 row in set (0.15 sec)

Parsing innodb files

[root@web103 mysql_recovery]# rm -rf pages-ibdata1/[root@web103 mysql_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1 Opening file: /var/lib/mysql/ibdata1File information:ID of device containing file:     2049inode number:           1344553protection:             100660 (regular file)number of hard links:          1user ID of owner:            27group ID of owner:           27device ID (if special file):       0blocksize for filesystem I/O:     4096number of blocks allocated:     463312time of last access:      1440819443 Sat Aug 29 11:37:23 2015time of last modification:   1440819463 Sat Aug 29 11:37:43 2015time of last status change:   1440819463 Sat Aug 29 11:37:43 2015total size, in bytes:      236978176 (226.000 MiB)Size to process:         236978176 (226.000 MiB)Opening file: /var/lib/mysql/ibdata1File information:ID of device containing file:     2049inode number:           1344553protection:             100660 (regular file)number of hard links:          1user ID of owner:            27group ID of owner:           27device ID (if special file):       0blocksize for filesystem I/O:     4096number of blocks allocated:     463312Opening file: /var/lib/mysql/ibdata1File information:time of last access:      1440819443 Sat Aug 29 11:37:23 2015time of last modification:   1440819463 Sat Aug 29 11:37:43 2015ID of device containing file:     2049inode number:           1344553protection:             100660 time of last status change:   1440819463 Sat Aug 29 11:37:43 2015total size, in bytes:      236978176 (226.000 MiB)Size to process:         236978176 (226.000 MiB)Opening file: /var/lib/mysql/ibdata1File information:ID of device containing file:     2049inode number:           1344553protection:             100660 (regular file)number of hard links:          1user ID of owner:            27group ID of owner:           27device ID (if special file):       0blocksize for filesystem I/O:     4096number of blocks allocated:     463312time of last access:      1440819443 Sat Aug 29 11:37:23 2015time of last modification:   1440819463 Sat Aug 29 11:37:43 2015time of last status change:   1440819463 Sat Aug 29 11:37:43 2015total size, in bytes:      236978176 (226.000 MiB)Size to process:         236978176 (226.000 MiB)(regular file)number of hard links:          1user ID of owner:            27group ID of owner:           27device ID (if special file):       0blocksize for filesystem I/O:     4096number of blocks allocated:     463312time of last access:      1440819443 Sat Aug 29 11:37:23 2015time of last modification:   1440819463 Sat Aug 29 11:37:43 2015time of last status change:   1440819463 Sat Aug 29 11:37:43 2015total size, in bytes:      236978176 (226.000 MiB)Size to process:         236978176 (226.000 MiB)Opening file: /var/lib/mysql/ibdata1File information:ID of device containing file:     2049inode number:           1344553protection:             100660 (regular file)number of hard links:          1user ID of owner:            27group ID of owner:           27device ID (if special file):       0blocksize for filesystem I/O:     4096number of blocks allocated:     463312time of last access:      1440819443 Sat Aug 29 11:37:23 2015time of last modification:   1440819463 Sat Aug 29 11:37:43 2015time of last status change:   1440819463 Sat Aug 29 11:37:43 2015total size, in bytes:      236978176 (226.000 MiB)Size to process:         236978176 (226.000 MiB)Opening file: /var/lib/mysql/ibdata1File information:ID of device containing file:     2049inode number:           1344553protection:             100660 (regular file)number of hard links:          1user ID of owner:            27group ID of owner:           27device ID (if special file):       0blocksize for filesystem I/O:     4096number of blocks allocated:     463312time of last access:      1440819443 Sat Aug 29 11:37:23 2015time of last modification:   1440819463 Sat Aug 29 11:37:43 2015time of last status change:   1440819463 Sat Aug 29 11:37:43 2015Opening file: /var/lib/mysql/ibdata1File information:ID of device containing file:     2049inode number:           1344553protection:             100660 (regular file)number of hard links:          1user ID of owner:            27group ID of owner:           27device ID (if special file):       0blocksize for filesystem I/O:     4096number of blocks allocated:     463312total size, in bytes:      236978176 (226.000 MiB)Size to process:         236978176 (226.000 MiB)time of last access:      1440819443 Sat Aug 29 11:37:23 2015time of last modification:   1440819463 Sat Aug 29 11:37:43 2015time of last status change:   1440819463 Sat Aug 29 11:37:43 2015total size, in bytes:      236978176 (226.000 MiB)Size to process:         236978176 (226.000 MiB)Opening file: /var/lib/mysql/ibdata1File information:ID of device containing file:     2049inode number:           1344553protection:             100660 (regular file)number of hard links:          1user ID of owner:            27group ID of owner:           27device ID (if special file):       0blocksize for filesystem I/O:     4096number of blocks allocated:     463312time of last access:      1440819465 Sat Aug 29 11:37:45 2015time of last modification:   1440819463 Sat Aug 29 11:37:43 2015time of last status change:   1440819463 Sat Aug 29 11:37:43 2015total size, in bytes:      236978176 (226.000 MiB)Size to process:         236978176 (226.000 MiB)All workers finished in 0 sec

Restore Data Dictionary

[root@web103 mysql_recovery]# ./recover_dictionary.sh Generating dictionary tables dumps... OKCreating test database ... OKCreating dictionary tables in database test:SYS_TABLES ... OKSYS_COLUMNS ... OKSYS_INDEXES ... OKSYS_FIELDS ... OKAll OKLoading dictionary tables data:SYS_TABLES ... 48 recs OKSYS_COLUMNS ... 397 recs OKSYS_INDEXES ... 67 recs OKSYS_FIELDS ... 89 recs OKAll OK

Analyze the data dictionary and find the index_id.

Note that for tables without primary keys, the 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 | +----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+40 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 | +----------+-----+------------------------------+----------+------+-------+------------+67 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: (73 73) 00000002141B 0000009924F2 80000027133548 t1 "82334502212106951" "SDK-BBX-010-18681" 13718311436 "8" Dear user, your phone verification code is 916515 if not operated by yourself, please call Oscar Customer Service: 400-620-7575. "00:00:00" 0 "07:00:00" 00000002141C 0000009924F2 80000027133558 t1 "82339012756833423" "SDK-BBX-010-18681" 13718311436 "8" Dear user: if your mobile phone verification code is 396108, call Oscar Customer Service: 400-620-7575. "00:00:00" 0 "07:00:00" 00000002141D 0000009924F2 80000027133568 t1 "8234322198577796" "SDK-BBX-010-18681" 13718311436 "8" Dear user: if your mobile phone verification code is 935297, call Oscar Customer Service: 400-620-7575. "00:00:00" 0 "07:00:00" 00000002141E 0000009924F2 80000027133578 t1 "10235259536125650" "SDK-BBX-010-18681" 13718311436 "8" Dear user: if your mobile phone verification code is 474851, call Oscar Customer Service: 400-620-7575. "00:00:00" 0 "07:00:00" 00000002141F 0000009924F2 80000027133588 t1 "10235353811295807" "SDK-BBX-010-18681" 13718311436 "8" Dear user: if your mobile phone verification code is 444632, call Oscar Customer Service: 400-620-7575. "00:00:00" 0 "07:00:00" 000000021420 2017009924f2 80000027133598 t1 "102354211240398235" SDK-BBX-010-18681 "13718311436" 8 "Dear user: if your mobile phone verification code is 478503, call Oscar Customer Service: 400-620-7575. "00:00:00" 0 "07:00:00" 000000021421 2017009924f2 800000271335A8 t1 "102354554052884567" "SDK-BBX-010-18681" 13718311436 "8" Dear user: if your mobile phone verification code is 216825, call Oscar Customer Service: 400-620-7575. "00:00:00" 0 "07:00:00" 000000021422 10000009924f2 800000271335B8 t1 "132213454294519126" "SDK-BBX-010-18681" 13718311436 "8" Dear user: if your mobile phone verification code is 854812, call Oscar Customer Service: 400-620-7575. "00:00:00" 0 "07:00:00" 000000021423 2017009924f2 800000271335C8 t1 "82329022242584577" "SDK-BBX-010-18681" 13718311436 "8" Dear user: if your mobile phone verification code is 253127, call Oscar Customer Service: 400-620-7575. "" 00:00:00 "0" 22:02:17 "............ [Root @ web103 mysql_recovery] # cat/tmp/2.txt | grep-v "Page id:" | wc-l316731

Because there is no primary key, the recovered records may have some duplicates. On the whole, data can be restored perfectly.