MySQL exception recovery method for innodb data recovery without a primary key
- 2020-12-26 05:59:21
- OfStack
This article describes the method of innodb data recovery in the case of MySQL exception recovery without primary key. To share for your reference, the details are as follows:
In the case of database exception recovery in mysql's innodb engine, primary key or only 1index is generally required, but this is not required and can be restored at the entire table level of index_id when index information is not available
Create mock 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: 0
mysql> insert into t1 select * from t1;
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)
Parse 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 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
time of last access: 1440819443 Sat Aug 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 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 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
Opening file: /var/lib/mysql/ibdata1
File information:
time of last access: 1440819443 Sat Aug 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
ID of device containing file: 2049
inode number: 1344553
protection: 100660 time of last status change: 1440819463 Sat Aug 29 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 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
time of last access: 1440819443 Sat Aug 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes: 236978176 (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 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 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 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
time of last access: 1440819443 Sat Aug 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 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 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
time of last access: 1440819443 Sat Aug 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 11:37:43 2015
Opening file: /var/lib/mysql/ibdata1
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 bytes: 236978176 (226.000 MiB)
Size to process: 236978176 (226.000 MiB)
time of last access: 1440819443 Sat Aug 29 11:37:23 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 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 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
time of last access: 1440819465 Sat Aug 29 11:37:45 2015
time of last modification: 1440819463 Sat Aug 29 11:37:43 2015
time of last status change: 1440819463 Sat Aug 29 11:37:43 2015
total size, in bytes: 236978176 (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 ... 48 recs OK
SYS_COLUMNS ... 397 recs OK
SYS_INDEXES ... 67 recs OK
SYS_FIELDS ... 89 recs OK
All OK
Analyze the data dictionary to find index_id
Note here that for table recovery without a primary key, 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 |
+----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+
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 mobile verification code is 916515 If not operated by me, please call Oao
Ska 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 user: Your mobile verification code is 396108 If not operated by me, please call Oao
Ska 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 user: Your mobile verification code is 935297 If not operated by me, please call Oao
Ska 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 user: Your mobile verification code is 474851 If not operated by me, please call Oao
Ska 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 user: Your mobile verification code is 444632 If not operated by me, please call Oao
Ska 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 " Dear user: Your mobile verification code is 478503 If not operated by me, please call Oao
Ska 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 " Dear user: Your mobile verification code is 216825 If not operated by me, please call Oao
Ska 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 " Dear user: Your mobile verification code is 854812 If not operated by me, please call Oao
Ska 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 user: Your mobile verification code is 253127 If not operated by me, please call Oao
Ska 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
Since there is no primary key, there may be some duplicates in the recovered record. Overall, the data can be recovered perfectly
For more information about MySQL, please refer to MySQL Log Tips, MySQL Transaction Tips, MySQL Stored Procedure Tips, MySQL Database Lock Tips and MySQL Common Functions.
I hope this article has been helpful to you with the MySQL database.