怎么徹底刪除磁盤數據(mysql數據庫磁盤滿了怎么辦)
時間:2023-11-06 08:10:01 作者:承德縣山貓科技有限公司

MySQL 數據庫磁盤爆的主要原因

數據量大

MySQL 磁盤爆的常見原因之一就是數據量突然增大導致數據庫爆,這種原因往往和業務相關。比如應用程序在短時間內生成了大量的數據,如果是突然出現新增大量數據,也有可能是某些測試用例或者壓測導致。這種原因導致的數據庫磁盤暴漲可以通過清理數據的方式解決,比如清除特定時間段的數據。

建議在測試環境等數據庫,進行壓力測試或者其他自動化測試時候對數據做特殊的標志,在完成測試后自動清除測試產生的垃圾數據,避免日積月來導致磁盤爆滿影響數據庫使用。

同時按照數據庫日常使用的情況看是否需要對磁盤進行擴容,但是擴容只能緩解,不能根治。

日志文件

MySQL 在日常運行的時候回產生大量的日志文件,這些日志文件也會占用磁盤空間,日積月累可能會產生幾百 G 的日志文件。

MySQL 的日志文件主要有以下幾種:

  • bin log 二進制文件通常是占用空間大的日志類型,這和數據庫的使用情況相關。
  • error 錯誤日志占用空間大的原因主要體現在長時間的日積月累,沒有定時清除導致,比如運行了一兩年的數據庫,產生的錯誤日志往往也比較大。
  • 慢查詢日志 慢查詢日志通常不會占用太多空間,但是長時間不清理也會出現占用過多的問題,需要定時清理

臨時文件

臨時文件是數據庫運行時候產生的臨時性文件,這種臨時性文件在運行結束以后會自動釋放,但是某些異常情況下如果沒有釋放會造成比較嚴重的后果,比如短時間的慢查詢語句,這種慢查詢執行時間巨長,查詢的數據很多,MySQL 在內存不夠的情況下回臨時將中間結果存放在數據庫的臨時目錄下。

處理日志文件導致的磁盤爆滿

清理日志文件導致的磁盤爆滿比較容易,在確保文件備份轉存的前提下可以直接清理文件。使用 echo ‘’ >> host- XXXXX.err 等命令可以直接清理這些日志文件。

切記不要直接刪除相關的日志文件

查看當前數據庫服務器磁盤占用情況可以使用如下命令

怎么徹底刪除磁盤數據(mysql數據庫磁盤滿了怎么辦)(1)

查看某個文件夾下的文件磁盤占中情況可以使用 du 明令參考

# 切換到需要查看的路徑下使用 du -hl

日志文件導致的磁盤爆滿可以通過 linux 命令

echo '' >> host-xxxxx.log

處理臨時文件導致的磁盤爆滿

查看 Mysql 配置的臨時目錄

通常 MySQL 的臨時目錄在 mysql/temp 路徑下,如果想查看配置的參數,可以使用 show variableslike ‘tmpdir’ SQL 命令

show variables like 'tmpdir'

怎么徹底刪除磁盤數據(mysql數據庫磁盤滿了怎么辦)(2)

臨時文件產生的原因

臨時文件產生的主要原因是 MySQL 在執行的時候,對一些大數據量的操作的時候內存無法滿足存儲需求的時候,MySQL 會使用臨時文件,比如某個臨時表查詢出的幾百萬數據,某個聯合查詢產生的結果集等。通常情況下 MySQL 產生的臨時文件很小,同時也會及時釋放,但是一些異常情況下,比如慢查詢,會導致短時間產生大量的臨時文件。

下面是一個因為慢查詢導致臨時目錄下產生大量文件的案例。

臨時文件產生過多導致磁盤爆滿,造成數據庫短時故障

數據庫崩潰的前兆往往是短時間出現大量 IO,CPU 暴漲。出現以上征兆的時候數據庫就離崩潰不遠了,下面這個案例就是。

怎么徹底刪除磁盤數據(mysql數據庫磁盤滿了怎么辦)(3)

這個案例中我們發現在某個時間點數據庫突然 CPU 暴漲,而且是持續性暴漲,與此對應的磁盤的 IO 次數暴增,內存使用暴增。這個時候基本可以判斷數據庫出現了短時間大量的慢查詢。

為什么慢查詢會導致磁盤 IO 暴增?原因是上面說的,數據庫在查詢的時候使用的臨時表或者臨時數據如果內存不足以存放的時候,數據庫會將這些數據存放在磁盤中,隨著慢查詢越來越多就會出現集中踩踏的問題,問題愈演愈烈,終導致數據庫卡頓或者崩潰。

怎么徹底刪除磁盤數據(mysql數據庫磁盤滿了怎么辦)(4)

怎么徹底刪除磁盤數據(mysql數據庫磁盤滿了怎么辦)(5)

上面案例中通過查看數據庫執行記錄,我們發現的確出現了慢查詢積壓的情況,使用以下 SQL 查詢 可以查詢當前正在執行的 SQL 狀態

SELECT id, `state`, user,host,time,`INFO` FROM information_schema.processlist where state IS NOT NULL and state <> "" ORDER BY time desc;

怎么徹底刪除磁盤數據(mysql數據庫磁盤滿了怎么辦)(6)

上面,你可以看到數據庫正在執行超過 10W 秒的 SQL,而且是好幾個,這樣就不難理解為什么數據庫會出現短時間 CPU 和 IO 暴增的情況。

解決這些問題的辦法就是: kill 當前的慢查詢,這個辦法很管用,但是要記錄查詢的語句和使用的登錄賬號和機器,以備興師問罪。

下面這個語句可以快速生成批量 kill 語句。

SELECT concat('kill ', id, ';') FROM information_schema.processlist where user = 'HispaceCMS' and `COMMAND` = 'Query' and state IS NOT NULL and state <> '' and DB is not null and time > 1000 ORDER BY time desc

生成以后批量執行就可以了。執行完查詢磁盤占用情況,從 96% 下降到 50 左右。

怎么徹底刪除磁盤數據(mysql數據庫磁盤滿了怎么辦)(7)