MySQL异常恢复之无主键情况下innodb数据恢复的方法
本文讲述了MySQL异常恢复之无主键情况下innodb数据恢复的方法。分享给大家供大家参考，具体如下：
在mysql的innodb引擎的数据库异常恢复中,一般都要求有主键或者唯一index,其实这个不是必须的,当没有index信息之时,可以在整个表级别的index_id进行恢复
创建模拟表—无主键
mysql>CREATETABLE`t1`( ->`messageId`varchar(30)charactersetutf8NOTNULL, ->`tokenId`varchar(20)charactersetutf8NOTNULL, ->`mobile`varchar(14)charactersetutf8defaultNULL, ->`msgFormat`int(1)NOTNULL, ->`msgContent`varchar(1000)charactersetutf8defaultNULL, ->`scheduleDate`timestampNOTNULLdefault'0000-00-0000:00:00', ->`deliverState`int(1)defaultNULL, ->`deliverdTime`timestampNOTNULLdefault'0000-00-0000:00:00' ->)ENGINE=INnodbDEFAULTCHARSET=utf8; QueryOK,0rowsaffected(0.00sec) mysql>insertintot1select*fromsms_service.sms_send_record; QueryOK,11rowsaffected(0.00sec) Records:11Duplicates:0Warnings:0 ………… mysql>insertintot1select*fromt1; QueryOK,81664rowsaffected(2.86sec) Records:81664Duplicates:0Warnings:0 mysql>insertintot1select*fromt1; QueryOK,163328rowsaffected(2.74sec) Records:163328Duplicates:0Warnings:0 mysql>selectcount(*)fromt1; +----------+ |count(*)| +----------+ |326656| +----------+ 1rowinset(0.15sec)
解析innodb文件
[root@web103mysql_recovery]#rm-rfpages-ibdata1/ [root@web103mysql_recovery]#./stream_parser-f/var/lib/mysql/ibdata1 Openingfile:/var/lib/mysql/ibdata1 Fileinformation: IDofdevicecontainingfile:2049 inodenumber:1344553 protection:100660(regularfile) numberofhardlinks:1 userIDofowner:27 groupIDofowner:27 deviceID(ifspecialfile):0 blocksizeforfilesystemI/O:4096 numberofblocksallocated:463312 timeoflastaccess:1440819443SatAug2911:37:232015 timeoflastmodification:1440819463SatAug2911:37:432015 timeoflaststatuschange:1440819463SatAug2911:37:432015 totalsize,inbytes:236978176(226.000MiB) Sizetoprocess:236978176(226.000MiB) Openingfile:/var/lib/mysql/ibdata1 Fileinformation: IDofdevicecontainingfile:2049 inodenumber:1344553 protection:100660(regularfile) numberofhardlinks:1 userIDofowner:27 groupIDofowner:27 deviceID(ifspecialfile):0 blocksizeforfilesystemI/O:4096 numberofblocksallocated:463312 Openingfile:/var/lib/mysql/ibdata1 Fileinformation: timeoflastaccess:1440819443SatAug2911:37:232015 timeoflastmodification:1440819463SatAug2911:37:432015 IDofdevicecontainingfile:2049 inodenumber:1344553 protection:100660timeoflaststatuschange:1440819463SatAug2911:37:432015 totalsize,inbytes:236978176(226.000MiB) Sizetoprocess:236978176(226.000MiB) Openingfile:/var/lib/mysql/ibdata1 Fileinformation: IDofdevicecontainingfile:2049 inodenumber:1344553 protection:100660(regularfile) numberofhardlinks:1 userIDofowner:27 groupIDofowner:27 deviceID(ifspecialfile):0 blocksizeforfilesystemI/O:4096 numberofblocksallocated:463312 timeoflastaccess:1440819443SatAug2911:37:232015 timeoflastmodification:1440819463SatAug2911:37:432015 timeoflaststatuschange:1440819463SatAug2911:37:432015 totalsize,inbytes:236978176(226.000MiB) Sizetoprocess:236978176(226.000MiB) (regularfile) numberofhardlinks:1 userIDofowner:27 groupIDofowner:27 deviceID(ifspecialfile):0 blocksizeforfilesystemI/O:4096 numberofblocksallocated:463312 timeoflastaccess:1440819443SatAug2911:37:232015 timeoflastmodification:1440819463SatAug2911:37:432015 timeoflaststatuschange:1440819463SatAug2911:37:432015 totalsize,inbytes:236978176(226.000MiB) Sizetoprocess:236978176(226.000MiB) Openingfile:/var/lib/mysql/ibdata1 Fileinformation: IDofdevicecontainingfile:2049 inodenumber:1344553 protection:100660(regularfile) numberofhardlinks:1 userIDofowner:27 groupIDofowner:27 deviceID(ifspecialfile):0 blocksizeforfilesystemI/O:4096 numberofblocksallocated:463312 timeoflastaccess:1440819443SatAug2911:37:232015 timeoflastmodification:1440819463SatAug2911:37:432015 timeoflaststatuschange:1440819463SatAug2911:37:432015 totalsize,inbytes:236978176(226.000MiB) Sizetoprocess:236978176(226.000MiB) Openingfile:/var/lib/mysql/ibdata1 Fileinformation: IDofdevicecontainingfile:2049 inodenumber:1344553 protection:100660(regularfile) numberofhardlinks:1 userIDofowner:27 groupIDofowner:27 deviceID(ifspecialfile):0 blocksizeforfilesystemI/O:4096 numberofblocksallocated:463312 timeoflastaccess:1440819443SatAug2911:37:232015 timeoflastmodification:1440819463SatAug2911:37:432015 timeoflaststatuschange:1440819463SatAug2911:37:432015 Openingfile:/var/lib/mysql/ibdata1 Fileinformation: IDofdevicecontainingfile:2049 inodenumber:1344553 protection:100660(regularfile) numberofhardlinks:1 userIDofowner:27 groupIDofowner:27 deviceID(ifspecialfile):0 blocksizeforfilesystemI/O:4096 numberofblocksallocated:463312 totalsize,inbytes:236978176(226.000MiB) Sizetoprocess:236978176(226.000MiB) timeoflastaccess:1440819443SatAug2911:37:232015 timeoflastmodification:1440819463SatAug2911:37:432015 timeoflaststatuschange:1440819463SatAug2911:37:432015 totalsize,inbytes:236978176(226.000MiB) Sizetoprocess:236978176(226.000MiB) Openingfile:/var/lib/mysql/ibdata1 Fileinformation: IDofdevicecontainingfile:2049 inodenumber:1344553 protection:100660(regularfile) numberofhardlinks:1 userIDofowner:27 groupIDofowner:27 deviceID(ifspecialfile):0 blocksizeforfilesystemI/O:4096 numberofblocksallocated:463312 timeoflastaccess:1440819465SatAug2911:37:452015 timeoflastmodification:1440819463SatAug2911:37:432015 timeoflaststatuschange:1440819463SatAug2911:37:432015 totalsize,inbytes:236978176(226.000MiB) Sizetoprocess:236978176(226.000MiB) Allworkersfinishedin0sec
恢复数据字典
[root@web103mysql_recovery]#./recover_dictionary.sh Generatingdictionarytablesdumps...OK Creatingtestdatabase...OK Creatingdictionarytablesindatabasetest: SYS_TABLES...OK SYS_COLUMNS...OK SYS_INDEXES...OK SYS_FIELDS...OK AllOK Loadingdictionarytablesdata: SYS_TABLES...48recsOK SYS_COLUMNS...397recsOK SYS_INDEXES...67recsOK SYS_FIELDS...89recsOK AllOK
分析数据字典,找出来index_id
这里需要注意对于没有主键的表恢复,我们对应的类型是GEN_CLUST_INDEX
mysql>select*fromSYS_TABLESwherename='test/t1'; +----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+ |NAME|ID|N_COLS|TYPE|MIX_ID|MIX_LEN|CLUSTER_NAME|SPACE| +----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+ |test/t1|100|8|1|0|0||0| +----------------------------------------+-----+-------------+------+--------+---------+--------------+-------+ 40rowsinset(0.00sec) mysql>SELECT*FROMSYS_INDEXESwheretable_id=100; +----------+-----+------------------------------+----------+------+-------+------------+ |TABLE_ID|ID|NAME|N_FIELDS|TYPE|SPACE|PAGE_NO| +----------+-----+------------------------------+----------+------+-------+------------+ |100|119|GEN_CLUST_INDEX|0|1|0|2951| +----------+-----+------------------------------+----------+------+-------+------------+ 67rowsinset(0.00sec)
恢复数据
root@web103mysql_recovery]#./c_parser-5fpages-ibdata1/FIL_PAGE_INDEX/0000000000000119.page-tdictionary/t1.sql>/tmp/2.txt2>2.sql [root@web103mysql_recovery]#more/tmp/2.txt --Pageid:10848,Format:COMPACT,Recordslist:Valid,Expectedrecords:(7373) 00000002141B0000009924F280000027133548t1"82334502212106951""SDK-BBX-010-18681""13718311436"8"尊敬的用户您好：您的手机验证码为916515如非本人操作，请拨打奥 斯卡客服：400-620-7575。""2010-01-0100:00:00"0"1970-01-0107:00:00" 00000002141C0000009924F280000027133558t1"82339012756833423""SDK-BBX-010-18681""13718311436"8"尊敬的用户您好：您的手机验证码为396108如非本人操作，请拨打奥 斯卡客服：400-620-7575。""2010-01-0100:00:00"0"1970-01-0107:00:00" 00000002141D0000009924F280000027133568t1"8234322198577796""SDK-BBX-010-18681""13718311436"8"尊敬的用户您好：您的手机验证码为935297如非本人操作，请拨打奥 斯卡客服：400-620-7575。""2010-01-0100:00:00"0"1970-01-0107:00:00" 00000002141E0000009924F280000027133578t1"10235259536125650""SDK-BBX-010-18681""13718311436"8"尊敬的用户您好：您的手机验证码为474851如非本人操作，请拨打奥 斯卡客服：400-620-7575。""2010-01-0100:00:00"0"1970-01-0107:00:00" 00000002141F0000009924F280000027133588t1"10235353811295807""SDK-BBX-010-18681""13718311436"8"尊敬的用户您好：您的手机验证码为444632如非本人操作，请拨打奥 斯卡客服：400-620-7575。""2010-01-0100:00:00"0"1970-01-0107:00:00" 0000000214200000009924F280000027133598t1"102354211240398235""SDK-BBX-010-18681""13718311436"8"尊敬的用户您好：您的手机验证码为478503如非本人操作，请拨打奥 斯卡客服：400-620-7575。""2010-01-0100:00:00"0"1970-01-0107:00:00" 0000000214210000009924F2800000271335A8t1"102354554052884567""SDK-BBX-010-18681""13718311436"8"尊敬的用户您好：您的手机验证码为216825如非本人操作，请拨打奥 斯卡客服：400-620-7575。""2010-01-0100:00:00"0"1970-01-0107:00:00" 0000000214220000009924F2800000271335B8t1"132213454294519126""SDK-BBX-010-18681""13718311436"8"尊敬的用户您好：您的手机验证码为854812如非本人操作，请拨打奥 斯卡客服：400-620-7575。""2010-01-0100:00:00"0"1970-01-0107:00:00" 0000000214230000009924F2800000271335C8t1"82329022242584577""SDK-BBX-010-18681""13718311436"8"尊敬的用户您好：您的手机验证码为253127如非本人操作，请拨打奥 斯卡客服：400-620-7575。""2010-01-0100:00:00"0"2015-08-2622:02:17" ………… [root@web103mysql_recovery]#cat/tmp/2.txt|grep-v"Pageid:"|wc-l 380731
因为没有主键,使得恢复出来记录可能有一些重复,整体而言,可以较为完美的恢复数据
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》
希望本文所述对大家MySQL数据库计有所帮助。