修复 cPanel 服务器上崩溃的 MariaDB 数据库

修复 cPanel 服务器上崩溃的 MariaDB 数据库

我有一个小型 cPanel 服务器,用于客户的项目和我自己的个人项目。我的几个朋友也共用这个服务器,他们帮忙保持磁盘旋转。最近我注意到 mysql 的 CPU 使用率大幅增加,我的几个朋友报告说他们的数据库崩溃了,使用 cPanel 修复工具修复它很有帮助。

但是,这种情况一直发生,我正在尝试找到解决方案。我的服务器有 16GB 的 RAM 和 RAID 1 磁盘。处理器是旧的 W3520。

当我重新启动 mysql 时,systemctl restart 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/

谢谢。

答案1

MyISAM 没有崩溃安全性。不要再将它用于您的朋友(和敌人)表。

删除设置以将默认值恢复为 innodb,并通过将 innodb_buffer_pool_size 设置为其中的合理部分来使用 16G 中的部分空间。更改现有表并ALTER TABLE {tablename} ENGINE=Innodb获得一些崩溃安全性。

我认为你已经让它mysqlcheck --repair完成了。

在共享托管服务器上,您的表打开缓存已经用尽,但建议查看SHOW GLOBAL STATUS信息以确定潜在的调整点。有了正确的信息dba 堆栈交换用户可以帮助调整和提高 MariaDB 的一般性能。

相关内容