建站
咨詢
售后
建站咨詢
新聞中心
數(shù)據(jù)庫表的損壞并不是一個難以預(yù)料的事情，也不是一個特別罕見的事情，尤其是在操作系統(tǒng)或者存儲提供者出現(xiàn)故障時。在這種情況下，損壞的表可能會極大地影響您的業(yè)務(wù)，甚至導(dǎo)致數(shù)據(jù)丟失。為了保障您的業(yè)務(wù)，及時修復(fù)數(shù)據(jù)庫表非常重要。
本文將會詳細(xì)闡述如何修復(fù)損壞的數(shù)據(jù)庫表。備份是修復(fù)損壞的數(shù)據(jù)庫表的關(guān)鍵。避免數(shù)據(jù)損壞的更好方法之一是創(chuàng)建定期的備份，以便可以在需要時使用。在數(shù)據(jù)損壞的情況下，備份可以作為最后的手段進(jìn)行修復(fù)。
1. 檢查表損壞
在數(shù)據(jù)庫運(yùn)行時，如果遇到如下錯誤，則表可能已經(jīng)受損了。
“Error 1034: The table ‘table name’ is marked as crashed and should be repred”
“Error 144: Table ‘table name’ is marked as crashed and last (automatic?) repr fled”
這些錯誤通常是由操作系統(tǒng)或存儲設(shè)備的問題導(dǎo)致的。在Table修復(fù)被執(zhí)行之前，我們需要進(jìn)行一些檢查。
我們需要確認(rèn)表是否已經(jīng)受到損壞。在命令行執(zhí)行以下命令：
mysqlcheck –all-databases
如果輸出為空，表格就沒有受到損壞。
如果輸出為“OK”，則數(shù)據(jù)庫表可能沒有損壞。如果輸出為“Warning”或“Error”，存儲引擎可能已經(jīng)損壞，并且需要修復(fù)。
如果您已經(jīng)確定表格損壞，接下來我們需要修復(fù)它。
2. 修復(fù)表
如果數(shù)據(jù)表損壞，您可以使用以下命令來修復(fù)它：
mysqlcheck -r database_name table_name
一旦您輸入上述命令，MySQL就會嘗試修復(fù)表。這個過程可以花費(fèi)一段時間，具體時間取決于表格的大小和損壞的程度。
如果MySQL成功地修復(fù)了數(shù)據(jù)表，`mysqlcheck`命令的輸出將是以下內(nèi)容：
database_name.table_name repr status okay
如果MySQL無法修復(fù)數(shù)據(jù)表，命令的輸出將與之前的警告一樣。
3. 恢復(fù)數(shù)據(jù)
如果使用備份進(jìn)行修復(fù)，考慮你需要覆蓋損壞的數(shù)據(jù)表，并且刪除無用的數(shù)據(jù)表。在MySQL中，您可以使用以下命令來恢復(fù)數(shù)據(jù)：
mysql –u[username] –p[password] [database_name]
##重要提醒##
在使用該命令導(dǎo)入數(shù)據(jù)之前，一定要備份數(shù)據(jù)庫。您可以使用以下命令進(jìn)行備份：
mysqldump –u[username] –p[password] [database_name]> [backup_filename].sql
導(dǎo)入備份文件后，您必須使用以下命令指定您想要使用的數(shù)據(jù)表：
USE database_name;
RENAME TABLE table_name TO tmp_table_name;
RENAME TABLE backup_table_name TO table_name;
DROP TABLE tmp_table_name;
這個操作將備份的表重命名為需要修復(fù)的表。輸出的結(jié)果將是：
‘database_name.table_name’ was renamed to ‘database_name.tmp_table_name’
‘database_name.backup_table_name’ was renamed to ‘database_name.table_name’
這時，通過刪除舊表并重命名備份表，您可以進(jìn)行數(shù)據(jù)庫恢復(fù)。使用以下命令刪除舊表：
DROP TABLE tmp_table_name;
通過使用這個過程，您應(yīng)該可以成功地修復(fù)數(shù)據(jù)表。但是，如果以上方式不能解決問題，請考慮聯(lián)系專業(yè)數(shù)據(jù)庫修復(fù)公司。在關(guān)鍵時刻，數(shù)據(jù)是最寶貴的財(cái)產(chǎn)。因此，在像數(shù)據(jù)表損壞這樣的問題時，為自己的數(shù)據(jù)保障備份、恢復(fù)和修復(fù)都是非常重要的。
相關(guān)問題拓展閱讀：
- 怎樣修復(fù)損壞了的innodb 表
- SQL SERVER中一張表損壞了，沒有備份文件，如何將這張表修復(fù)一下呢？在線等待
怎樣修復(fù)損壞了的innodb 表
InnoDB表損壞
InnoDB擁有內(nèi)部恢復(fù)機(jī)制，假如數(shù)據(jù)庫崩潰了，InnoDB通過從最后一個時間戳開始運(yùn)行日志文件畢蠢兄，來嘗試修復(fù)數(shù)據(jù)庫。
大多數(shù)情況下會修復(fù)成功，而且整個過程是透明的。
假如InnoDB自行修復(fù)失敗，那么數(shù)據(jù)庫將不能啟動。
在繼續(xù)操作前，先瀏覽下MySQL的日志文件，確定數(shù)據(jù)庫是因?yàn)镮nnoDB表的損壞而崩潰。
有一種方法是更新InnoDB的日志文件計(jì)數(shù)器以跳過引起崩潰的查詢，這種情況下，手襲將造成數(shù)據(jù)的不一致性而且會經(jīng)常使主從復(fù)制中斷。
一旦確定MySQL因?yàn)镮nnoDB表損壞無法啟動時，就可以按照以下5步進(jìn)行修復(fù)：
1.添加如下配置到/etc/my.cnf文件中
innodb_force_recovery = 4
2.這時就可以重新啟動數(shù)據(jù)庫了，在innodb_force_recovery配置的作用，所有的插入與更新操作將被忽略;
3.導(dǎo)出所有的數(shù)據(jù)表;
4.關(guān)閉數(shù)據(jù)檔閉庫并刪除所有數(shù)據(jù)表文件及目錄，再運(yùn)行 mysql_install_db來創(chuàng)建MySQL默認(rèn)數(shù)據(jù)表;
5.在/etc/my.cnf中刪除innodb_force_recovery這一行，再啟動MySQL（這時MySQL正常啟動）;
6.從第3步備份的文件中恢復(fù)所有的數(shù)據(jù)。
– 恢復(fù)策略
前面說到未提交的事務(wù)和回滾了的事務(wù)也會記錄Redo Log，因此在進(jìn)行恢復(fù)時,這些事務(wù)要進(jìn)行特殊的的處理.有2中不同的恢復(fù)策略：
A. 進(jìn)行恢復(fù)時，只重做已經(jīng)提交了的事務(wù)。
B. 進(jìn)行恢復(fù)時，重做所有事務(wù)包括未提交的事務(wù)和回滾了的事務(wù)。然后通過Undo Log回滾那些未提交的事務(wù)。
– InnoDB存儲引擎的恢復(fù)機(jī)制
MySQL數(shù)據(jù)庫InnoDB存儲引擎使用了B策略, InnoDB存儲引擎中的恢復(fù)機(jī)制有幾個特點(diǎn)：
A. 在重做Redo Log時，并不關(guān)心事務(wù)性。 恢復(fù)時，沒有BEGIN，也沒有COMMIT,ROLLBACK的行為。也不關(guān)心每個日志是哪個事務(wù)的。盡管事務(wù)ID等事務(wù)相關(guān)的內(nèi)容會記入胡基Redo Log，這些內(nèi)容只是被當(dāng)作要操作的數(shù)據(jù)的一部分。
B. 使用B策略就必須要將Undo Log持久化，而且必須要在寫Redo Log之前將對應(yīng)的Undo Log寫入磁盤。Undo和Redo Log的這種關(guān)聯(lián)，使得持久化變得復(fù)雜起來。為了降褲鍵謹(jǐn)?shù)蛷?fù)雜度，InnoDB將Undo Log看作數(shù)據(jù)，因此記錄Undo Log的操作也會記錄到redo log中。這樣undo log就可以象數(shù)據(jù)一樣緩存起來，而不用在redo log之前寫入磁盤了。
包含Undo Log操作的Redo Log，看起來是這樣的：
記錄1: >
記錄2:
記錄3: >
記錄4:
記錄5: >
記錄6:
C. 到這里，還有一個問題沒有弄清楚。既然Redo沒有事務(wù)性，那豈不是會重新執(zhí)行被回滾了的事務(wù)？確實(shí)是這樣。同時Innodb也會將事務(wù)回滾時的操作也記錄到redo log中?；貪L操作本質(zhì)上也是對數(shù)據(jù)進(jìn)行修改，因此回滾時對數(shù)據(jù)的操作也會記錄到Redo Log中。
一個回滾了的事務(wù)的Redo Log，看起來是這樣的：
記錄1: >
記錄2:
記錄3: >
記錄4:
記錄5: >
記錄6:
記錄7:
記錄8: 亮巖
記錄9:
一個被回滾了的事務(wù)在恢復(fù)時的操作就是先redo再undo，因此不會破壞數(shù)據(jù)的一致性.
– InnoDB存儲引擎中相關(guān)的函數(shù)
Redo: recv_recovery_from_checkpoint_start()
Undo: recv_recovery_rollback_active()
Undo Log的Redo Log: trx_undof_page_add_undo_rec_log()
ssume you’re running MySQL with Innodb tables and you’ve got crappy hardware, driver bug, kernel bug, unlucky power failure or some rare MySQL bug and some pages in Innodb tablespace got corrupted. In such cases Innodb will typically print something like this:
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
:46:16 InnoDB: Page dump in ascii and hex (16384 bytes):
… A LOT OF HEX AND BINARY DATA…
:46:16 InnoDB: Page checksum, prior-to-4.0.14-form checksum
InnoDB: stored checksum, prior-to-4.0.14-form stored checksum
InnoDB: Page lsn, low 4 bytes of lsn at page end
InnoDB: Page number (if stored to page already) 7,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 6353
InnoDB: Page may be an index page where index id is
InnoDB: (index “PRIMARY” of table “test”.”test”)
InnoDB: Database page corruption on disk or a failed
and crash with assertion failure.
So what can you do to recover such a table ?
There are multiple things which can get corrupted and I will be looking in details on the simple one in this article – when page in clustered key index is corrupted. It is worse compared to having data corrupted in secondary indexes, in which case simple OPTIMIZE TABLE could be enough to rebuild it, but it is much better compared to table dictionary corruption when it may be much harder to recover the table.
In this example I actually went ahead and manually edited test.ibd file replacing few bytes so corruption is mild.
First I should note CHECK TABLE in INNODB is pretty useless. For my manually corrupted table I am getting:
mysql> CHECK TABLE test;
ERROR 2023 (HY000): Lost connection TO MySQL server during query
mysql> CHECK TABLE test;
+++++
| TABLE | Op | Msg_type | Msg_text
+++++
| test.test | CHECK | STATUS | OK
+++++
1 row IN SET (0.69 sec)
First run is check table in normal operation mode – in which case Innodb simply crashes if there is checksum error (even if we’re running CHECK operation). In second case I’m running withinnodb_force_recovery=1 and as you can see even though I get the message in the log file about checksum failing CHECK TABLE says table is OK. This means You Can’t Trust CHECK TABLE in Innodb to be sure your tables are good.
In this simple corruption was only in the data portion of pages so once you started Innodb withinnodb_force_recovery=1 you can do the following:
mysql> CREATE TABLE `test2` (
-> `c` char(255) DEFAULT NULL,
-> `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO test2 SELECT * FROM test;
Query OK,rows affected (0.91 sec)
Records:Duplicates: 0 Warnings: 0
Now you got all your data in MyISAM table so all you have to do is to drop old table and convert new table back to Innodb after restarting without innodb_force_recovery option. You can also rename the old table in case you will need to look into it more later. Another alternative is to dump table with MySQLDump and load it back. It is all pretty much the same stuff. I’m using MyISAM table for the reason you’ll see later.
You may think why do not you simply rebuild table by using OPTIMIZE TABLE ? This is because Running in innodb_force_recovery mode Innodb becomes read only for data operations and so you can’t insert or delete any data (though you can create or drop Innodb tables):
mysql> OPTIMIZE TABLE test;
++++——+
| TABLE | Op| Msg_type | Msg_text
++++——+
| test.test | OPTIMIZE | error | Got error -1 FROM storage engine
| test.test | OPTIMIZE | STATUS | Operation failed
++++——+
2 rows IN SET, 2 warnings (0.09 sec)
That was easy, right ?
I also thought so, so I went ahead and edited test.ibd a little more wiping one of the page headers completely. Now CHECK TABLE would crash even with innodb_force_recovery=1
:22:53 InnoDB: Assertion failure in threadin file btr/btr0btr.c line 3235
InnoDB: Failing assertion: page_get_n_recs(page) > 0 || (level == 0 && page_get_page_no(page) == dict_index_get_page(index))
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to
InnoDB: If you get repeated assertion failures or crashes, even
If you get such assertion failures most likely higher innodb_force_recovery values would not help you – they are helpful in case there is corruption in various system areas but they can’t really change anything in a way Innodb processes page data.
The next comes trial and error approach:
mysql> INSERT INTO test2 SELECT * FROM test;
ERROR 2023 (HY000): Lost connection TO MySQL server during query
You may think will will scan the table until first corrupted row and get result in MyISAM table ? Unfortunately test2 ended up to be empty after the run. At the same time I saw some data could be selected. The problem is there is some buffering taking place and as MySQL crashes it does not store all data it could recover to MyISAM table.
Using series of queries with LIMIT can be handly if you recover manually:
mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 20;
Query OK, 10 rows affected (0.00 sec)
Records: 20 Duplicates: 10 Warnings: 0
mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 100;
Query OK, 80 rows affected (0.00 sec)
Records: 100 Duplicates: 20 Warnings: 0
mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 200;
Query OK, 100 rows affected (1.47 sec)
Records: 200 Duplicates: 100 Warnings: 0
mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 300;
ERROR 2023 (HY000): Lost connection TO MySQL server during query
As you can see I can get rows from the table in the new one until we finally touch the row which crashes MySQL. In this case we can expect this is the row between 200 and 300 and we can do bunch of similar statements to find exact number doing “binary search”
Note even if you do not use MyISAM table but fetch data to the script instead make sure to use LIMIT or PK Rangers when MySQL crashes you will not get all data in the network packet you potentially could get due to buffering.
So now we found there is corrupted data in the table and we need to somehow skip over it. To do it we would need to find max PK which could be recovered and try some higher values
mysql> SELECT max(id) FROM test2;
++
| max(id)
++
|
++
1 row IN SET (0.00 sec)
mysql> INSERT IGNORE INTO test2 SELECT * FROM test WHERE id>250;
ERROR 2023 (HY000): Lost connection TO MySQL server during query
mysql> INSERT IGNORE INTO test2 SELECT * FROM test WHERE id>300;
Query OK,rows affected (7.79 sec)
Records:Duplicates: 0 Warnings: 0
So we tried to skip 30 rows and it was too little while skipping 80 rows was OK. Again using binary search you can find out how many rows do you need to skip exactly to recover as much data as possible. Row size can be good help to you. In this case we have about 280 bytes per row so we get about 50 rows per page so not a big surprise 30 rows was not enough – typically if page directory is corrupted you would need to skip at least whole page. If page is corrupted at higher level in REE you may need to skip a lot of pages (whole subtree) to use this recovery method.
It is also well possible you will need to skip over few bad pages rather than one as in this example.
Another hint – you may want to CHECK your MyISAM table you use for recovery after MySQL crashes to make sure indexes are not corrupted.
So we looked at how to get your data back from simple Innodb Table Corruption. In more complex cases you may need to use higher innodb_force_recovery modes to block purging activity, insert buffer merge or recovery from transactional logs all together. Though the lower recovery mode you can run your recovery process with better data you’re likely to get.
In some cases such as if data dictionary or “root page” for clustered index is corrupted this method will not work well – in this case you may wish to use Innodb Recovery Toolkit which is also helpful in cases you’ve want to recover deleted rows or dropped table.
I should also mention at Percona we offer assistance in MySQL Recovery, including recovery from Innodb corruptions and deleted data.
SQL SERVER中一張表損壞了，沒有備份文件，如何將這張表修復(fù)一下呢？在線等待
–先檢查是否有報(bào)錯
dbcc checktable(‘表名’)
–有報(bào)錯，就修復(fù)租困
use master
GO
— sp_dboption 用于顯示或更改數(shù)弊虛念據(jù)庫選項(xiàng)。當(dāng)為 single user 選項(xiàng)為 true 時，每次只能有一個用戶訪問數(shù)據(jù)庫。
sp_dboption ‘?dāng)?shù)據(jù)庫名’,’single user’,’true’
DBCC CHECKDB(‘?dāng)?shù)據(jù)庫名’譽(yù)薯,REPAIR_ALLOW_DATA_LOSS)
sp_dboption ‘?dāng)?shù)據(jù)庫名’,’single user’,’false’
從你的出錯來看拿氏“ 0 個分配錯誤和 1 個一致性錯誤”可能是索引問題，一致性出錯多數(shù)情況是索引問題，你可以drop 所有索引，再檢查一下表。
另數(shù)據(jù)庫比較保守的修復(fù)方法是
DBCC CHECKDB(‘?dāng)?shù)據(jù)庫名’,REPAIR_REBUILD )
因?yàn)楸组]REPAIR_ALLOW_DATA_LOSS
嘗試修復(fù)報(bào)告的所有錯誤。這些修復(fù)可能會導(dǎo)致一些租敏裂數(shù)據(jù)丟失。
REPAIR_REBUILD
執(zhí)行不會丟失數(shù)據(jù)的修復(fù)。這包括快速修復(fù)（如修復(fù)非聚集索引中缺少的行）以及更耗時的修復(fù)（如重新生成索引）。
試下能不能把表的數(shù)據(jù)導(dǎo)出來唯友select * into temp from table,然後重御慶新該表，再將會數(shù)據(jù)導(dǎo)回。鎮(zhèn)山握
數(shù)據(jù)庫表損壞怎么修復(fù)的介紹就聊到這里吧，感謝你花時間閱讀本站內(nèi)容，更多關(guān)于數(shù)據(jù)庫表損壞怎么修復(fù),數(shù)據(jù)庫表損壞，如何修復(fù)？,怎樣修復(fù)損壞了的innodb 表,SQL SERVER中一張表損壞了，沒有備份文件，如何將這張表修復(fù)一下呢？在線等待的信息別忘了在本站進(jìn)行查找喔。
香港服務(wù)器選創(chuàng)新互聯(lián)，2H2G首月10元開通。
創(chuàng)新互聯(lián)（www.cdcxhl.com）互聯(lián)網(wǎng)服務(wù)提供商,擁有超過10年的服務(wù)器租用、服務(wù)器托管、云服務(wù)器、虛擬主機(jī)、網(wǎng)站系統(tǒng)開發(fā)經(jīng)驗(yàn)。專業(yè)提供云主機(jī)、虛擬主機(jī)、域名注冊、VPS主機(jī)、云服務(wù)器、香港云服務(wù)器、免備案服務(wù)器等。
文章標(biāo)題：數(shù)據(jù)庫表損壞，如何修復(fù)？(數(shù)據(jù)庫表損壞怎么修復(fù))
URL分享：http://www.fisionsoft.com.cn/article/ccsosoe.html
