![我的 MySQL 在一个月内崩溃了好几次](https://linux22.com/image/762682/%E6%88%91%E7%9A%84%20MySQL%20%E5%9C%A8%E4%B8%80%E4%B8%AA%E6%9C%88%E5%86%85%E5%B4%A9%E6%BA%83%E4%BA%86%E5%A5%BD%E5%87%A0%E6%AC%A1.png)
我的网站(Wordpress)有时会停止工作并出现以下错误消息”
无法连接到数据库
我检查了MySQL的日志文件,发现崩溃信息如下:
----------
2021-01-21 0:44:59 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-01-21 0:44:59 0 [Note] InnoDB: Uses event mutexes
2021-01-21 0:44:59 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-01-21 0:44:59 0 [Note] InnoDB: Number of pools: 1
2021-01-21 0:45:00 0 [Note] InnoDB: Using SSE2 crc32 instructions
2021-01-21 0:45:00 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2021-01-21 0:45:00 0 [Note] InnoDB: Completed initialization of buffer pool
2021-01-21 0:45:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-01-21 0:45:00 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=215993122
2021-01-21 0:45:07 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-01-21 0:45:07 0 [Note] InnoDB: Uses event mutexes
2021-01-21 0:45:07 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-01-21 0:45:07 0 [Note] InnoDB: Number of pools: 1
2021-01-21 0:45:07 0 [Note] InnoDB: Using SSE2 crc32 instructions
2021-01-21 0:45:07 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2021-01-21 0:45:07 0 [Note] InnoDB: Completed initialization of buffer pool
2021-01-21 0:45:07 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-01-21 0:45:07 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=215993122
2021-01-21 0:50:02 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-01-21 0:50:02 0 [Note] InnoDB: Uses event mutexes
2021-01-21 0:50:02 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-01-21 0:50:02 0 [Note] InnoDB: Number of pools: 1
2021-01-21 0:50:02 0 [Note] InnoDB: Using SSE2 crc32 instructions
2021-01-21 0:50:02 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2021-01-21 0:50:02 0 [Note] InnoDB: Completed initialization of buffer pool
2021-01-21 0:50:02 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-01-21 0:50:02 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=215993122
2021-01-21 0:50:02 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2021-01-21 0:50:02 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2021-01-21 0:50:02 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2021-01-21 0:50:02 0 [Note] InnoDB: Setting file '/opt/lampp/var/mysql/ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2021-01-21 0:50:02 0 [Note] InnoDB: File '/opt/lampp/var/mysql/ibtmp1' size is now 12 MB.
2021-01-21 0:50:02 0 [Note] InnoDB: Waiting for purge to start
2021-01-21 0:50:02 0 [Note] InnoDB: 10.4.11 started; log sequence number 215993131; transaction id 221150
2021-01-21 0:50:02 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-01-21 0:50:02 0 [Note] InnoDB: Loading buffer pool(s) from /opt/lampp/var/mysql/ib_buffer_pool
2021-01-21 0:50:02 0 [Note] Server socket created on IP: '::'.
----------
我重新启动了 MySQL,我的网站运行良好。我的 MySQL 版本是:Distrib 10.4.11-MariaDB,适用于 Linux (x86_64) Ubuntu 版本 20。
一个月前,这种情况出现过几次。
我之前确实在一些帖子里搜索过解决方案,但仍然无法解决这个问题,
Wordpress + PHP+ apache +mysql,mysql 每月崩溃一次
有人遇到这种情况吗?知道如何解决吗?
答案1
在 XAMPP 控制面板上启动 MySQL。
通过单击 XAMPP 控制面板上的日志按钮检查 My SQL 错误日志“mysql_error.log”。
转到 MySQL 数据库中的“数据”目录。我将 XAMPP 安装在计算机的 D: 驱动器上,并且计算机的 MySQL“数据”目录位置为“/opt/lampp/var/mysql/”。您可能有不同的位置。
备份 MySQL“数据”文件夹
首先,您应该使用任何压缩软件创建“数据”文件夹的备份。
给出一个名称,如“data_backup.zip”或任何你想要的压缩类型。我使用 WinRAR 压缩软件来压缩和备份 MySQL“数据”文件夹。
重命名“数据”文件夹
将“data”文件夹重命名为“data-oldfiles”。将数据目录重命名为任何新目录名称非常重要。创建一个新的“data”文件夹
创建一个新文件夹并将文件夹命名为“data”
为了解决这个问题,我们需要在 mysql 数据库中创建一个新的“data”目录。从“backup”文件夹复制内容
转到“备份”文件夹并复制所有文件。
将文件从备份文件夹粘贴到数据文件夹
现在从 XAMPP 启动 MySQL 数据库。
现在,您的 MySQL 数据库将正常启动,不会显示任何错误。
传输所有 MySQL 项目数据库、数据文件和日志文件
如果您有许多用于各种项目的数据库,那么您必须将所有数据库从“data-oldfiles”文件夹转移到“data”文件夹。
从数据旧文件复制所有数据库并粘贴到数据文件夹。
现在您必须将数据文件“ibdata1”和所有日志文件“ib_logfile0、ib_logfile1”从数据旧文件文件夹复制到数据文件夹。如果您有许多 id_logile,则请复制所有文件。
MySQL 错误 这可能是由于端口被阻止、缺少依赖项、权限不正确、崩溃或以其他方式关闭造成的。从 XAMPP 启动 MySQL
现在从 XAMPP 启动 MySQL。
转到 phpMyAdmin 检查所有数据库是否可用且正常工作。
答案2
16M 太小了innodb_buffer_pool_size
;试试 50M。1GB VM 相当小。
也低至max_connections
10。
答案3
来自Dom的评论,以及WordPress专家@Kemmut的指导。
我检查了自上个月以来我的 LAMPP 的所有日志(PHP、MySQL 和 Apache)。
我总结了MySQL多次崩溃的原因如下:
你是对的,就我而言,原因是操作系统杀死了 MySQL 服务,因为它占用了大量内存(超出了我的服务器能力:1G RAM)。
MySQL 错误日志:
2021-01-21 0:50:02 0 [Note] InnoDB: Setting file '/opt/lampp/var/mysql/ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2021-01-21 0:50:02 0 [Note] InnoDB: File '/opt/lampp/var/mysql/ibtmp1' size is now 12 MB.
PHP 错误日志:
[mpm_prefork:error] [pid 11556] (12)Cannot allocate memory: AH00159: fork: Unable to fork new process [mpm_prefork:error] [pid 11556] (12)Cannot allocate memory: AH00159: fork: Unable to fork new process mmap() failed: [12] Cannot allocate memory mmap() failed: [12] Cannot allocate memory
MySql 占用大量 MEM 的原因是:大量意外的扫描请求来自未知的服务器(自上个月以来,我的服务器从单个 IP 收到的最大请求数是每秒 27 个请求!!!)。
Apache 访问日志
"POST /bbs/index.php HTTP/1.1" 302 - "POST /forum/index.php HTTP/1.1" 302 - "POST /forums/index.php HTTP/1.1" 302 - "POST /cgi-bin/php?%2D%64+%61%6C%6C%6F%77%5F%75%72%6C%5F%69%6E%63%6C%75%64%65%3D%6F%6E+%2D%64+%73%61%66%65%5F%6D%6F%64%65%3D%6F%66%66+%2D%64+%73%75%68%6F%73%69%6E%2E%73%69%6D%75%6C%61%74%69%6F%6E%3D%6F%6E+%2D%64+%64%69%73%61%62%6C%65%5F%66%75%6E%63%74%69%6F%6E%73%3D%22%22+%2D%64+%6F%70%65%6E%5F%62%61%73%65%64%69%72%3D%6E%6F%6E%65+%2D%64+%61%75%74%6F%5F%70%72%65%70%65%6E%64%5F%66%69%6C%65%3D%70%68%70%3A%2F%2F%69%6E%70%75%74+%2D%64+%63%67%69%2E%66%6F%72%63%65%5F%72%65%64%69%72%65%63%74%3D%30+%2D%64+%63%67%69%2E%72%65%64%69%72%65%63%74%5F%73%74%61%74%75%73%5F%65%6E%76%3D%30+%2D%6E HTTP/1.1" 404 758 "POST /cgi-bin/php5?%2D%64+%61%6C%6C%6F%77%5F%75%72%6C%5F%69%6E%63%6C%75%64%65%3D%6F%6E+%2D%64+%73%61%66%65%5F%6D%6F%64%65%3D%6F%66%66+%2D%64+%73%75%68%6F%73%69%6E%2E%73%69%6D%75%6C%61%74%69%6F%6E%3D%6F%6E+%2D%64+%64%69%73%61%62%6C%65%5F%66%75%6E%63%74%69%6F%6E%73%3D%22%22+%2D%64+%6F%70%65%6E%5F%62%61%73%65%64%69%72%3D%6E%6F%6E%65+%2D%64+%61%75%74%6F%5F%70%72%65%70%65%6E%64%5F%66%69%6C%65%3D%70%68%70%3A%2F%2F%69%6E%70%75%74+%2D%64+%63%67%69%2E%66%6F%72%63%65%5F%72%65%64%69%72%65%63%74%3D%30+%2D%64+%63%67%69%2E%72%65%64%69%72%65%63%74%5F%73%74%61%74%75%73%5F%65%6E%76%3D%30+%2D%6E HTTP/1.1" 404 758 "POST /cgi-bin/php-cgi?%2D%64+%61%6C%6C%6F%77%5F%75%72%6C%5F%69%6E%63%6C%75%64%65%3D%6F%6E+%2D%64+%73%61%66%65%5F%6D%6F%64%65%3D%6F%66%66+%2D%64+%73%75%68%6F%73%69%6E%2E%73%69%6D%75%6C%61%74%69%6F%6E%3D%6F%6E+%2D%64+%64%69%73%61%62%6C%65%5F%66%75%6E%63%74%69%6F%6E%73%3D%22%22+%2D%64+%6F%70%65%6E%5F%62%61%73%65%64%69%72%3D%6E%6F%6E%65+%2D%64+%61%75%74%6F%5F%70%72%65%70%65%6E%64%5F%66%69%6C%65%3D%70%68%70%3A%2F%2F%69%6E%70%75%74+%2D%64+%63%67%69%2E%66%6F%72%63%65%5F%72%65%64%69%72%65%63%74%3D%30+%2D%64+%63%67%69%2E%72%65%64%69%72%65%63%74%5F%73%74%61%74%75%73%5F%65%6E%76%3D%30+%2D%6E HTTP/1.1" 404 758 "POST /cgi-bin/php.cgi?%2D%64+%61%6C%6C%6F%77%5F%75%72%6C%5F%69%6E%63%6C%75%64%65%3D%6F%6E+%2D%64+%73%61%66%65%5F%6D%6F%64%65%3D%6F%66%66+%2D%64+%73%75%68%6F%73%69%6E%2E%73%69%6D%75%6C%61%74%69%6F%6E%3D%6F%6E+%2D%64+%64%69%73%61%62%6C%65%5F%66%75%6E%63%74%69%6F%6E%73%3D%22%22+%2D%64+%6F%70%65%6E%5F%62%61%73%65%64%69%72%3D%6E%6F%6E%65+%2D%64+%61%75%74%6F%5F%70%72%65%70%65%6E%64%5F%66%69%6C%65%3D%70%68%70%3A%2F%2F%69%6E%70%75%74+%2D%64+%63%67%69%2E%66%6F%72%63%65%5F%72%65%64%69%72%65%63%74%3D%30+%2D%64+%63%67%69%2E%72%65%64%69%72%65%63%74%5F%73%74%61%74%75%73%5F%65%6E%76%3D%30+%2D%6E HTTP/1.1" 404 758 "POST /cgi-bin/php4?%2D%64+%61%6C%6C%6F%77%5F%75%72%6C%5F%69%6E%63%6C%75%64%65%3D%6F%6E+%2D%64+%73%61%66%65%5F%6D%6F%64%65%3D%6F%66%66+%2D%64+%73%75%68%6F%73%69%6E%2E%73%69%6D%75%6C%61%74%69%6F%6E%3D%6F%6E+%2D%64+%64%69%73%61%62%6C%65%5F%66%75%6E%63%74%69%6F%6E%73%3D%22%22+%2D%64+%6F%70%65%6E%5F%62%61%73%65%64%69%72%3D%6E%6F%6E%65+%2D%64+%61%75%74%6F%5F%70%72%65%70%65%6E%64%5F%66%69%6C%65%3D%70%68%70%3A%2F%2F%69%6E%70%75%74+%2D%64+%63%67%69%2E%66%6F%72%63%65%5F%72%65%64%69%72%65%63%74%3D%30+%2D%64+%63%67%69%2E%72%65%64%69%72%65%63%74%5F%73%74%61%74%75%73%5F%65%6E%76%3D%30+%2D%6E HTTP/1.1" 404 758 "POST /%62%61%73%65/%70%6F%73%74%2E%70%68%70 HTTP/1.1" 302 - "GET /webdav/ HTTP/1.1" 302 - "GET /%69%73%70%69%72%69%74/%69%6D/%75%70%6C%6F%61%64%2E%70%68%70 HTTP/1.1" 302 - "GET /help.php HTTP/1.1" 302 - "GET /java.php HTTP/1.1" 302 - "GET /_query.php HTTP/1.1" 302 - "GET /test.php HTTP/1.1" 302
为了处理这种情况,本文中有一些基于 iptables 和 ufw 的免费 Linux 脚本可用于阻止此类垃圾请求。
https://vivustandard.com/fix-mysql-stops-or-crashes-randomly/
- Shell脚本:block_ip.sh
- Shell 脚本:remove_ip.sh
- Shell脚本:run_anti_ddos.sh