在 cPanel 伺服器上修復崩潰的 MariaDB 數據庫

我有一個小型 cPanel 伺服器，用於我客戶的項目和我自己的個人項目。我的一些朋友也分享了這一點，他們插話以保持磁碟旋轉。最近我注意到 mysql 的 CPU 使用率大幅增加，我的幾個朋友報告說他們的數據庫崩潰了，並且從 cPanel 修復工具修復它有所幫助。

但是，這種情況一直在發生，我正在嘗試為此找到解決方案。我的伺服器有 16GB 的 RAM 和 RAID 1 磁碟。處理器是舊的，稱為 W3520。

systemctl restart mysql當我使用以下日誌重新啟動 mysql 時。

2020-11-24 19:04:12 0 [Note] InnoDB: Using Linux native AIO
2020-11-24 19:04:12 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-11-24 19:04:12 0 [Note] InnoDB: Uses event mutexes
2020-11-24 19:04:12 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2020-11-24 19:04:12 0 [Note] InnoDB: Number of pools: 1
2020-11-24 19:04:12 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-11-24 19:04:12 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-11-24 19:04:12 0 [Note] InnoDB: Completed initialization of buffer pool
2020-11-24 19:04:12 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-11-24 19:04:13 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-11-24 19:04:13 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-11-24 19:04:13 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-11-24 19:04:13 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-11-24 19:04:13 0 [Note] InnoDB: Waiting for purge to start
2020-11-24 19:04:13 0 [Note] InnoDB: 10.3.27 started; log sequence number 43130164885; transaction id 86603248
2020-11-24 19:04:13 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2020-11-24 19:04:13 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-11-24 19:04:13 0 [Note] Server socket created on IP: '::'.
2020-11-24 19:04:13 0 [Note] InnoDB: Buffer pool(s) load completed at 201124 19:04:13
2020-11-24 19:04:13 0 [Note] Reading of all Master_info entries succeeded
2020-11-24 19:04:13 0 [Note] Added new Master_info '' to hash table
2020-11-24 19:04:13 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.27-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
2020-11-24 19:05:38 52 [ERROR] Got error 127 when reading table './[database]/[table]'
2020-11-24 19:05:38 52 [ERROR] mysqld: Table '[table]' is marked as crashed and should be repaired
2020-11-24 19:05:38 52 [ERROR] mysqld: Table '[table]' is marked as crashed and should be repaired

這也顯示了一些其他要修復的表。我現在已經使用 WHM 的 PHPMyAdmin 界面進行了修復。然後使用重新載入mysqlsystemctl restart mysql

但是，問題似乎仍然存在，因為我從 mysql 獲得的 CPU 使用率是 ±100%

我也嘗試過修復它，mysql_upgrade -u root --force -p但沒有產生任何結果。我也跑去mysqlcheck --repair --all-databases嘗試修復崩潰的數據庫，但也沒有產生任何結果。

以下是我的 my.cnf 文件內容。

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
log-error=/var/lib/mysql/domain.com.err
innodb_file_per_table=1
default-storage-engine=MyISAM
max_allowed_packet=268435456
open_files_limit=10000
performance_schema = off
sql_mode="NO_AUTO_CREATE_USER"

我正在執行以下版本，

MariaDB 10.3.27

cPanel/WHM v90.0.17

Linux domain.com 3.10.0-1160.6.1.el7.x86_64 #1 SMP Tue Nov 17 13:59:11 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

我剛剛在日誌中也發現了以下錯誤。

https://paste.ubuntu.com/p/ZH66kBd23y/

謝謝你。

MyISAM 沒有碰撞安全。停止將它用於您的朋友（和敵人）桌子。

刪除設置以將預設值恢復為 innodb 並通過將 innodb_buffer_pool_size 設置為此合理部分來使用部分 16G。更改現有表ALTER TABLE {tablename} ENGINE=Innodb並獲得一些碰撞安全性。

我假設你已經mysqlcheck --repair完成了。

在共享託管伺服器上，您的table_open_cache可能已用盡，但建議查看SHOW GLOBAL STATUS資訊以辨識潛在的調整點。使用正確的資訊，dba stackexchange使用者可以幫助調整和一般 MariaDB 性能。

引用自：https://serverfault.com/questions/1043779