MySQL異常恢復之無主鍵情況下innodb資料恢復的方法
本文講述了MySQL異常恢復之無主鍵情況下innodb資料恢復的方法。分享給大家供大家參考,具體如下:
在mysql的innodb引擎的資料庫異常恢復中,一般都要求有主鍵或者唯一index,其實這個不是必須的,當沒有index資訊之時,可以在整個表級別的index_id進行恢復
建立模擬表—無主鍵
?
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)
|
解析innodb檔案
?
[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
|
恢復資料字典
?
[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
|
分析資料字典,找出來index_id
這裡需要注意對於沒有主鍵的表恢復,我們對應的型別是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)
|
恢復資料
?
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 "尊敬的使用者您好:您的手機驗證碼為916515如非本人操作,請撥打奧 斯卡客服: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 "尊敬的使用者您好:您的手機驗證碼為396108如非本人操作,請撥打奧 斯卡客服: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 "尊敬的使用者您好:您的手機驗證碼為935297如非本人操作,請撥打奧 斯卡客服: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 "尊敬的使用者您好:您的手機驗證碼為474851如非本人操作,請撥打奧 斯卡客服: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 "尊敬的使用者您好:您的手機驗證碼為444632如非本人操作,請撥打奧 斯卡客服: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 "尊敬的使用者您好:您的手機驗證碼為478503如非本人操作,請撥打奧 斯卡客服: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 "尊敬的使用者您好:您的手機驗證碼為216825如非本人操作,請撥打奧 斯卡客服: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 "尊敬的使用者您好:您的手機驗證碼為854812如非本人操作,請撥打奧 斯卡客服: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 "尊敬的使用者您好:您的手機驗證碼為253127如非本人操作,請撥打奧 斯卡客服: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
|
因為沒有主鍵,使得恢復出來記錄可能有一些重複,整體而言,可以較為完美的恢復資料
更多關於MySQL相關內容感興趣的讀者可檢視本站專題:《MySQL日誌操作技巧大全》、《MySQL事務操作技巧彙總》、《MySQL儲存過程技巧大全》、《MySQL資料庫鎖相關技巧彙總》及《MySQL常用函式大彙總》
希望本文所述對大家MySQL資料庫計有所幫助。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4301/viewspace-2811077/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 恢復MySQL InnoDB表結構的方法MySql
- undo表空間檔案丟失恢復(3)--無備份無redo的情況下恢復
- undo表空間檔案丟失恢復(4)--無備份無recover的情況下恢復
- Oracle其它情況的恢復Oracle
- catalog損壞情況下的資料庫恢復例項資料庫
- undo表空間檔案丟失恢復(2)--無備份有redo的情況下恢復
- mysql資料恢復MySql資料恢復
- 寶塔資料庫恢復 mysql資料庫丟失恢復 mysql資料庫刪除庫恢復 寶塔mysql資料庫恢復資料庫MySql
- 【硬碟資料恢復】加電有異響的硬碟資料恢復硬碟資料恢復
- Oracle 無備份情況恢復ocr和olrOracle
- 【MySQL】Innodb 恢復工具介紹MySql
- 【伺服器資料恢復】異常斷電導致ESXI無法連線儲存的資料恢復案例伺服器資料恢復
- 6_Oracle truncate異常恢復之bbed修復Oracle
- 7_Oracle truncate異常恢復之plsql修復OracleSQL
- windows重灌/資料庫物理檔案完好的情況下恢復資料庫Windows資料庫
- 【備份恢復】無備份線上恢復非關鍵資料檔案
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- 【北亞資料恢復】異常斷電導致Oracle資料庫報錯的oracle資料恢復資料恢復Oracle資料庫
- 恢復資料,資料塊恢復
- raid5常見故障資料恢復方法/伺服器資料恢復常用方法AI資料恢復伺服器
- linux 下mysql資料庫密碼恢復的方法LinuxMySql資料庫密碼
- MySQL資料庫的恢復MySql資料庫
- 【資料庫資料恢復】linux系統下MYSQL資料庫資料恢復案例資料庫資料恢復LinuxMySql
- 恢復之非歸檔模式下的恢復模式
- 恢復之利用備份在所有控制檔案丟失情況下恢復(四)
- 恢復之利用備份在所有控制檔案丟失情況下恢復(三)
- 恢復之利用備份在所有控制檔案丟失情況下恢復(二)
- 恢復之利用備份在所有控制檔案丟失情況下恢復(一)
- 恢復之資料庫關閉時的完全恢復資料庫
- DG同步異常恢復文件
- SQLServer異常故障恢復(二)SQLServer
- MySQL:Innodb恢復的學習筆記MySql筆記
- SeSparse資料恢復方案研究及恢復方法演示資料恢復
- 【虛擬機器資料恢復】異常斷電導致虛擬機器無法啟動的資料恢復案例虛擬機資料恢復
- 【MySQL】二、Innodb 恢復工具介紹MySql
- MySQL 引擎特性:InnoDB崩潰恢復MySql
- 資料底層損壞的恢復方法—拼碎片恢復資料
- 資料恢復:AMDU資料抽取恢復資料恢復