主页 / server / 问题 / 1051060
Accepted
Jame Goat
Asked: 2021-01-25 19:59:59 +0800 CST

我的 MySQL 在一个月内崩溃了很多次

  • 772

我的网站（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 服务器每周至少崩溃 2 次

Wordpress + PHP+ apache +mysql，mysql每1/月崩溃一次

有没有人被困在这种情况下，并且知道如何解决它？

mysql crash

3 个回答

  1. 在 XAMPP 控制面板上启动 MySQL。

    通过单击 XAMPP 控制面板上的 Logs 按钮检查 My SQL 错误日志“mysql_error.log”。

    转到 MySQL 数据库中的“数据”目录。我在我的计算机上的 D: 驱动器上安装了 XAMPP，并且我计算机的 MySQL“数据”目录位置是“/opt/lampp/var/mysql/”。您可能有不同的位置。

    备份 MySQL“数据”文件夹

    首先，您应该使用任何压缩软件创建“数据”文件夹的备份。

    命名为“data_backup.zip”或您希望的任何类型的压缩。我使用 WinRAR 压缩软件压缩和备份 MySQL 的“数据”文件夹。

    重命名“数据”文件夹

    将“data”文件夹重命名为“data-oldfiles”。这对于将数据目录重命名为任何新目录名称非常重要。创建一个新的“数据”文件夹

    创建一个新文件夹并将文件夹名称命名为“data”

    为了解决这个问题，我们需要在 mysql 数据库中创建一个新的“data”目录。从“备份”文件夹复制内容

    转到“备份”文件夹并复制所有文件。

    将备份文件夹中的文件粘贴到数据文件夹

    现在从 XAMPP 启动 MySQL 数据库。

    现在，您的 MySQL 数据库将正常启动而不会显示任何错误。

    传输所有 MySQL 项目数据库、数据文件和日志文件

    如果您有许多用于各种项目的数据库，那么您必须将所有数据库从“data-oldfiles”文件夹转移到“data”文件夹。

    从数据旧文件复制所有数据库并粘贴到数据文件夹。

    现在您必须将数据文件“ibdata1”和所有日志文件“ib_logfile0, ib_logfile1”从数据旧文件夹复制到数据文件夹。如果您有许多 id_logile，则复制所有这些。

    MySQL 错误 这可能是由于端口被阻塞、缺少依赖项、权限不正确、崩溃或被其他方法关闭所致。从 XAMPP 启动 MySQL

    现在从 XAMPP 启动 MySQL。

    转到 phpMyAdmin 以检查所有数据库是否可用和工作。

    • 0
  2. Best Answer

    来自 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. 你是对的，就我而言，原因是 OS Killed 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 次请求/秒！！！）。

      阿帕奇访问日志

      "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. 为了处理这种情况，有一些免费的 Linux 脚本，它们基于本文中的 iptables 和 ufw，可用于阻止此类垃圾请求。

      https://vivustandard.com/fix-mysql-stops-or-crashes-randomly/

      • 外壳脚本：block_ip.sh
      • 外壳脚本：remove_ip.sh
      • 外壳脚本：run_anti_ddos.sh
    • 0

  3. 16M太小了innodb_buffer_pool_size；试试50M。1GB VM 非常小。

    也max_connections降到10。

    • 0

相关问题