☰

我的 MySQL 在一个月内崩溃了好几次

tag-icon • tag-icon mysql crash
我的 MySQL 在一个月内崩溃了好几次

我的网站（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。

一个月前，这种情况出现过几次。

我之前确实在一些帖子里搜索过解决方案，但仍然无法解决这个问题，

MySQL 服务器每周至少崩溃两次

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_connections10。

答案3

来自Dom的评论，以及WordPress专家@Kemmut的指导。

https://wordpress.org/support/topic/my-website-down-related-to-mysql-crashed-many-times-in-a-month/#post-13963272

我检查了自上个月以来我的 LAMPP 的所有日志（PHP、MySQL 和 Apache）。

我总结了MySQL多次崩溃的原因如下：

  1. 你是对的，就我而言，原因是操作系统杀死了 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

  2. 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

  3. 为了处理这种情况，本文中有一些基于 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

相关内容