我有一个小型 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/
谢谢。