mysql資料庫表損壞怎麼辦,資料庫損壞了怎麼辦

本文目錄一覽：

1、面試官：MySQL許可權表損壞導致無法啟動怎麼辦？
2、如何修復MYSQL資料庫因斷電造成的數據損壞
3、如何處理mysql中表損壞問題
4、mysql資料庫表損壞怎麼辦

面試官：MySQL許可權表損壞導致無法啟動怎麼辦？

一、背景

近期，公司RDS雲產品的MySQL Server版本進行升級，由目前使用的5.7.26版本升級到最新版本5.7.31；升級後測試同學發現：在MySQL創建用戶後，5.7.31版本重新啟動集群會出現啟動失敗的現象；而5.7.26版本在相同測試場景下是正常啟動的。這到底是為什麼呢?

二、問題復現

2.1 實驗環境

2.2 操作步驟

按照測試同學的測試步驟，首先創建一個用戶：

然後關閉mysqld；這裡需要介紹一下，我們集群的關閉方式是如下方式：

這種方式的內部實現類似於kill -9模式。所以我在線下環境使用kill -9的方式來複現，操作如下：

然後重啟mysqld，操作如下：

此時問題復現了，mysqld啟動失敗，我們查看了下error日誌，信息如下：

根據報錯信息可以看出:MySQL的許可權系統表發生了損壞，導致了mysqld啟動失敗；由於在MySQL 5.7及其之前版本該表是MyISAM引擎，且該引擎不支持事務，所以在mysqld異常崩潰會導致該類型引擎表的損壞;但在mysqld啟動時是有參數控制MyISAM引擎的恢復模式，且該參數在我們產品中也配置到了my.cnf中，如下所示：

2.3 參數解析

對於該參數的官方文檔的解釋如下：

設置MyISAM存儲引擎恢復模式。選項值是OFF、DEFAULT、BACKUP、FORCE或QUICK的值的任意組合。如果指定多個值，請用逗號分隔。指定不帶參數的選項與指定DEFAULT相同，指定顯式值” “將禁用恢復(與OFF值相同)。如果啟用了恢復，則mysqld每次打開MyISAM表時，都會檢查該表是否標記為已崩潰或未正確關閉。(只有在禁用外部鎖定的情況下運行，最後一個選項才起作用。)在這種情況下，mysqld在表上運行檢查。如果表已損壞，mysqld將嘗試對其進行修復。

伺服器自動修復表之前，它將有關修復的注釋寫到錯誤日誌中。如果您希望能夠在無需用戶干預的情況下從大多數問題中恢復，則應使用選項BACKUP，FORCE。即使某些行將被刪除，這也會強制修復表，但是它將舊的數據文件保留為備份，以便您以後可以檢查發生了什麼。

全局變數，只讀變數，默認為OFF。

三、問題修復

這類MySQL用戶表損耗的問題解決方式也是有多種，我這裡列舉其中一種：

（1）my.cnf中的[mysqld]標籤下添加skip_grant_tables，啟動時跳過載入系統字典。

（2）重啟mysqld，然後修復mysql schema下的所有表。

（3）在[mysqld]標籤下注釋或刪除掉skip_grant_tables，然後重啟mysqld。

此時mysqld是可以正常啟動的，無異常。

四、深入排查

在產品化中，以上修復方式很不優雅，只是作為臨時的解決方案;並且也存在一些令人疑惑的點：

帶著這些疑問，我們繼續排查出現該現象的原因；此時Google也沒有找到一些有效的信息，那麼只能通過MySQL源代碼來尋找一些答案。

首先需要下載mysql 5.7.31版本的源代碼，並搭建mysql debug環境；具體步驟可以自動Google搜索一下，本文就不再贅述了。

在源代碼中搜索一下關鍵詞，用於打斷點的位置，然後進行調試：

定位到相關代碼，大概是sql/mysqld.cc的4958行，且存在if條件判斷，此時我們開始調試：

通過以上調試信息，可以判斷出acl_init函數返回的值為真;此時我們查看該函數的代碼 (sql/auth/sql_auth_cache.cc:1365)：

根據該函數的注釋發現：該函數是初始化負責用戶/資料庫級特權檢查的結構，並從mysql schema中的表中為其載入特權信息；且return值為1代表的是初始化許可權失敗。

此後開始逐步調試，觀察return相關信息，當調試到lock_table_names函數時，我們發現在Phase 3時return值為true，且根據代碼注釋發現true代表是Failure；具體代碼如下(sql/sql_base.cc:5549)：

調試信息如下：

可以看到flags的值為0，而MYSQL_OPEN_SKIP_SCOPED_MDL_LOCK為宏定義值0x1000，與flags的值做按位與操作，結果自然也是0，當然MYSQL_LOCK_IGNORE_GLOBAL_READ_ONLY也是如此;need_global_read_lock_protection是bool類型值，代表是否需要全局讀鎖的保護，這個值是在table- mdl_request.type不為MDL_SHARED_READ_ONLY發生改變;check_readonly函數相關信息下面概述。

此時也查看了下MySQL 5.7.26版本代碼作為對比，發現lock_table_names函數下的Phase 3後的部分代碼是在5.7.29版本後新增的。如果是git clone的MySQL代碼可以用git blame命令查詢文件變化的信息：

上述展示的信息中，最左側的列值為commit id為05824063和0405ebee，有興趣的同學可以詳細看下。

此功能解決的問題是 BUG#28438114: SET READ_ONLY=1 SOMETIMES DOESN’T BLOCK CONCURRENT DDL.；當然這個代碼的變更功能也在5.7 Release Notes中有所體現，如下所示( m/doc/relnotes/mysql/5.7/en/news-5-7-29.html )：

最後我們再查看下check_readonly函數，該函數是基於read_only和super_read_only狀態執行標準化檢查，是禁止(TRUE)還是允許(FALSE)操作。代碼如下(sql/auth/sql_authorization.cc:489)：

此時第一反應就是去檢查my.cnf中是否包含read_only相關參數，檢查之後發現確實是使用了該參數，如下：

此時注釋掉該參數，然後再次啟動mysqld，發現MyISAM表可以自動修復，且正常啟動；error log信息如下：

由於docker一些限制，我們在mysqld啟動會涉及兩次;所以解決該問題的方式為：第一次mysqld的啟動時先關閉read_only參數，第二次啟動時開啟read_only參數。之所以選擇默認開啟read_only參數，是為了避免在mysqld啟動後，選主邏輯未完成時的保護措施；當然選主完成後，會自動對master執行 set global read_only=0 操作。

五、總結

六、附錄

調試的棧幀信息如下，有興趣的小夥伴可以研究下：

熟悉MySQL體系結構和innodb存儲引擎工作原理；以及MySQL備份恢復、複製、數據遷移等技術；專註於MySQL、MariaDB開源資料庫，喜好開源技術。

原文鏈接：

如何修復MYSQL資料庫因斷電造成的數據損壞

修復MYSQL資料庫因斷電造成的數據損壞在使用MySQL資料庫的時候，都碰到過因斷電造成資料庫損壞的情況，大家都知道，斷電或非正常關機是導致MySQL資料庫出現錯誤最常見的原因，如何恢復MySQL資料庫是大家都非常頭痛的問題。目前有方法可以幫助大家恢復損壞的MySQL資料庫嗎？當用戶出現斷電造成的MySQL資料庫丟失損壞時，如果能進入MySQL軟體，但是提示錯誤信息，用戶可以通過「系統維護」—「資料庫壓縮修復」，壓縮下資料庫（有些軟體在「系統設置」—「系統維護」—「資料庫備份恢復」中），這種方法只適合一小部分的資料庫故障修復，功能性並不高。現在網路上有兩種比較推薦使用的MySQL資料庫恢復方法，一種方法使用MySQL(和PHP搭配之最佳組合)的check table和repair table 的sql語句，另一種方法是使用MySQL(和PHP搭配之最佳組合)提供的多個myisamchk， isamchk數據檢測恢復工具。在很多用戶看來，這兩個方法都比較複雜，並不適合大多數的用戶使用。另外，這兩種方法並不能有效的恢復MySQL資料庫，可能還會造成資料庫被進一步損壞，造成大家無法挽回的損失。根據以上幾個原因，這兩種MySQL資料庫恢復方法並不建議大家使用。。資料庫被損壞分以下幾種情況：1、嚴重損壞2、輕度損壞3、有些表被損壞或有些表的部分記錄被損壞如何修復MYSQL資料庫因斷電造成的數據損壞

如何處理mysql中表損壞問題

5.9.4. 表維護和崩潰恢復

後面幾節討論如何使用myisamchk來檢查或維護MyISAM表(對應.MYI和.MYD文件的表)。

你可以使用myisamchk實用程序來獲得有關你的資料庫表的信息或檢查、修復、優化他們。下列小節描述如何調用myisamchk（包括它的選項的描述），如何建立表的維護計劃，以及如何使用myisamchk執行各種功能。

儘管用myisamchk修復表很安全，在修復(或任何可以大量更改表的維護操作)之前先進行備份也是很好的習慣

影響索引的myisamchk操作會使ULLTEXT索引用full-text參數重建，不再與MySQL伺服器使用的值兼容。要想避免，請閱讀5.9.5.1節，「用於myisamchk的一般選項」的說明。

在許多情況下，你會發現使用SQL語句實現MyISAM表的維護比執行myisamchk操作要容易地多：

· 要想檢查或維護MyISAM表，使用CHECK TABLE或REPAIR TABLE。

· 要想優化MyISAM表，使用OPTIMIZE TABLE。

· 要想分析MyISAM表，使用ANALYZE TABLE。

可以直接這些語句，或使用mysqlcheck客戶端程序，可以提供命令行介面。

這些語句比myisamchk有利的地方是伺服器可以做任何工作。使用myisamchk，你必須確保伺服器在同一時間不使用表。否則，myisamchk和伺服器之間會出現不期望的相互干涉。

5.9.5. myisamchk：MyISAM表維護實用工具

5.9.5.1. 用於myisamchk的一般選項

5.9.5.2. 用於myisamchk的檢查選項

5.9.5.3. myisamchk的修複選項

5.9.5.4. 用於myisamchk的其它選項

5.9.5.5. myisamchk內存使用

5.9.5.6. 將myisamchk用於崩潰恢復

5.9.5.7. 如何檢查MyISAM表的錯誤

5.9.5.8. 如何修復表

5.9.5.9. 表優化

可以使用myisamchk實用程序來獲得有關資料庫表的信息或檢查、修復、優化他們。myisamchk適用MyISAM表(對應.MYI和.MYD文件的表)。

調用myisamchk的方法：

shell myisamchk [options] tbl_name …

options指定你想讓myisamchk做什麼。在後面描述它們。還可以通過調用myisamchk –help得到選項列表。

tbl_name是你想要檢查或修復的資料庫表。如果你不在資料庫目錄的某處運行myisamchk，你必須指定資料庫目錄的路徑，因為myisamchk不知道你的資料庫位於哪兒。實際上，myisamchk不在乎你正在操作的文件是否位於一個資料庫目錄；你可以將對應於資料庫表的文件拷貝到別處並且在那裡執行恢復操作。

如果你願意，可以用myisamchk命令行命名幾個表。還可以通過命名索引文件(用「 .MYI」後綴)來指定一個表。它允許你通過使用模式「*.MYI」指定在一個目錄所有的表。例如，如果你在資料庫目錄，可以這樣在目錄下檢查所有的MyISAM表：

shell myisamchk *.MYI

如果你不在資料庫目錄下，可通過指定到目錄的路徑檢查所有在那裡的表：

shell myisamchk /path/to/database_dir/*.MYI

你甚至可以通過為MySQL數據目錄的路徑指定一個通配符來檢查所有的資料庫中的所有表：

shell myisamchk /path/to/datadir/*/*.MYI

推薦的快速檢查所有MyISAM表的方式是：

shell myisamchk –silent –fast /path/to/datadir/*/*.MYI

如果你想要檢查所有MyISAM表並修復任何破壞的表，可以使用下面的命令：

shell myisamchk –silent –force –fast –update-state \

-O key_buffer=64M -O sort_buffer=64M \

-O read_buffer=1M -O write_buffer=1M \

/path/to/datadir/*/*.MYI

該命令假定你有大於64MB的自由內存。關於用myisamchk分配內存的詳細信息，參見5.9.5.5節，「myisamchk內存使用」。

當你運行myisamchk時，必須確保其它程序不使用表。否則，當你運行myisamchk時，會顯示下面的錯誤消息：

warning: clients are using or haven’t closed the table properly

這說明你正嘗試檢查正被另一個還沒有關閉文件或已經終止而沒有正確地關閉文件的程序(例如mysqld伺服器)更新的表。

如果mysqld正在運行，你必須通過FLUSH TABLES強制清空仍然在內存中的任何錶修改。當你運行myisamchk時，必須確保其它程序不使用表。避免該問題的最容易的方法是使用CHECK TABLE而不用myisamchk來檢查表。

5.9.5.1. 用於myisamchk的一般選項

本節描述的選項可以用於用myisamchk執行的任何類型的表維護操作。本節後面的章節中描述的選項只適合具體操作，例如檢查或修復表。

· –help，-?

顯示幫助消息並退出。

· –debug=debug_options, -# debug_options

輸出調試記錄文件。debug_options字元串經常是’d:t:o,filename’。

· –silent，-s

沉默模式。僅當發生錯誤時寫輸出。你能使用-s兩次(-ss)使myisamchk沉默。

· –verbose，-v

冗長模式。列印更多的信息。這能與-d和-e一起使用。為了更冗長，使用-v多次(-vv, -vvv)！

· –version, -V

顯示版本信息並退出。

· –wait, -w

如果表被鎖定，不是提示錯誤終止，而是在繼續前等待到表被解鎖。請注意如果用–skip-external-locking選項運行mysqld，只能用另一個myisamchk命令鎖定表。

還可以通過–var_name=value選項設置下面的變數：

變數

默認值

decode_bits

ft_max_word_len

取決於版本

ft_min_word_len

ft_stopword_file

內建列表

key_buffer_size

523264

myisam_block_size

1024

read_buffer_size

262136

sort_buffer_size

2097144

sort_key_blocks

stats_method

nulls_unequal

write_buffer_size

262136

可以用myisamchk –help檢查myisamchk變數及其默認值：

當用排序鍵值修復鍵值時使用sort_buffer_size，使用–recover時這是很普通的情況。

當用–extend-check檢查表或通過一行一行地將鍵值插入表中(如同普通插入)來修改鍵值時使用Key_buffer_size。在以下情況通過鍵值緩衝區進行修復：

· 使用–safe-recover。

· 當直接創建鍵值文件時，需要對鍵值排序的臨時文件有兩倍大。通常是當CHAR、VARCHAR、或TEXT列的鍵值較大的情況，因為排序操作在處理過程中需要保存全部鍵值。如果你有大量臨時空間，可以通過排序強制使用myisamchk來修復，可以使用–sort-recover選項。

通過鍵值緩衝區的修復佔用的硬碟空間比使用排序么少，但是要慢。

如果想要快速修復，將key_buffer_size和sort_buffer_size變數設置到大約可用內存的25%。可以將兩個變數設置為較大的值，因為一個時間只使用一個變數。

myisam_block_size是用於索引塊的內存大小。

stats_method影響當給定–analyze選項時，如何為索引統計搜集處理NULL值。它如同myisam_stats_method系統變數。詳細信息參見5.3.3節，「伺服器系統變數」和7.4.7節，「MyISAM索引統計集合」的myisam_stats_method的描述。

ft_min_word_len和ft_max_word_len表示FULLTEXT索引的最小和最大字長。ft_stopword_file為停止字文件的文件名。需要在以下環境中對其進行設置。

如果你使用myisamchk來修改表索引(例如修復或分析)，使用最小和最大字長和停止字文件的默認全文參數值（除非你另外指定）重建FULLTEXT索引。這樣會導致查詢失敗。

出現這些問題是因為只有伺服器知道這些參數。它們沒有保存在MyISAM索引文件中。如果你修改了伺服器中的最小或最大字長或停止字文件，要避免該問題，為用於mysqld的myisamchk指定相同的ft_min_word_len，ft_max_word_len和ft_stopword_file值。例如，如果你將最小字長設置為3,可以這樣使用myisamchk來修復表：

shell myisamchk –recover –ft_min_word_len=3 tbl_name.MYI

要想確保myisamchk和伺服器使用相同的全文