Documentation Home
MySQL 5.7 参考手册

B.3.3.3 MySQL 老是崩溃怎么办

每个 MySQL 版本在发布之前都在许多平台上进行了测试。这并不意味着 MySQL 中没有错误，但如果有错误，它们应该很少并且很难找到。如果遇到问题，尝试找出导致系统崩溃的确切原因总是有帮助的，因为您更有可能快速解决问题。

首先，您应该尝试找出问题是否出在mysqld服务器死机或您的问题是否与您的客户端有关。您可以通过执行mysqladmin version检查您的mysqld服务器已经运行了 多长时间。如果 mysqld 死机并重新启动，您可以通过查看服务器的错误日志找到原因。请参阅 第 5.4.2 节，“错误日志”。

在某些系统上，您可以在错误日志中找到有关mysqld 死机位置的堆栈跟踪，您可以使用程序解决该问题resolve_stack_dump。参见 第 5.8 节，“调试 MySQL”。请注意，错误日志中写入的变量值可能并不总是 100% 正确。

许多意外的服务器退出是由损坏的数据文件或索引文件引起的。write()MySQL在每个 SQL 语句之后和客户端收到结果通知之前通过系统调用更新磁盘上的文件 。（如果您在 delay_key_write启用系统变量的情况下运行，则情况并非如此，在这种情况下，将写入数据文件而不是索引文件。）这意味着即使mysqld崩溃，数据文件内容也是安全的，因为操作系统确保未刷新的数据被写入磁盘。您可以通过使用 选项 启动mysqld来强制 MySQL 在每个 SQL 语句之后将所有内容刷新到磁盘。--flush

前面的意思是通常你不应该得到损坏的表，除非发生以下情况之一：

  • MySQL 服务器或服务器主机在更新过程中被杀死。

  • 您在mysqld中发现了一个错误，该错误导致它在更新过程中死机。

  • 某些外部程序在未正确锁定表的情况下 与mysqld 同时操作数据文件或索引文件。

  • 您在不支持良好文件系统锁（通常由锁管理器处理）的系统上使用相同的数据目录 运行许多mysqldlockd服务器，或者您正在运行多个禁用外部锁定的服务器。

  • 您有一个崩溃的数据文件或索引文件，其中包含使mysqld感到困惑的非常损坏的数据。

  • 您在数据存储代码中发现了一个错误。这不太可能，但至少有可能。在这种情况下，您可以尝试通过ALTER TABLE在表的修复副本上使用将存储引擎更改为另一个引擎。

Because it is very difficult to know why something is crashing, first try to check whether things that work for others result in an unexpected exit for you. Try the following things:

  • Stop the mysqld server with mysqladmin shutdown, run myisamchk --silent --force */*.MYI from the data directory to check all MyISAM tables, and restart mysqld. This ensures that you are running from a clean state. See Chapter 5, MySQL Server Administration.

  • Start mysqld with the general query log enabled (see Section 5.4.3, “The General Query Log”). Then try to determine from the information written to the log whether some specific query kills the server. About 95% of all bugs are related to a particular query. Normally, this is one of the last queries in the log file just before the server restarts. See Section 5.4.3, “The General Query Log”. If you can repeatedly kill MySQL with a specific query, even when you have checked all tables just before issuing it, then you have isolated the bug and should submit a bug report for it. See 第 1.6 节，“如何报告错误或问题”。

  • 尝试制作一个我们可以用来重复问题的测试用例。参见第 5.8 节，“调试 MySQL”。

  • 尝试fork_big.pl脚本。（它位于tests源代码分发目录中。）

  • 配置 MySQL 进行调试可以在出现问题时更轻松地收集有关可能错误的信息。使用CMake-DWITH_DEBUG=1选项 重新配置 MySQL， 然后重新编译。参见 第 5.8 节，“调试 MySQL”。

  • Make sure that you have applied the latest patches for your operating system.

  • Use the --skip-external-locking option to mysqld. On some systems, the lockd lock manager does not work properly; the --skip-external-locking option tells mysqld not to use external locking. (This means that you cannot run two mysqld servers on the same data directory and that you must be careful if you use myisamchk. Nevertheless, it may be instructive to try the option as a test.)

  • If mysqld appears to be running but not responding, try mysqladmin -u root processlist. Sometimes mysqld is not hung even though it seems unresponsive. The problem may be that all connections are in use, or there may be some internal lock problem. mysqladmin -u root processlist usually is able to make a connection even in these cases, and can provide useful information about the current number of connections and their status.

  • Run the command mysqladmin -i 5 status or mysqladmin -i 5 -r status in a separate window to produce statistics while running other queries.

  • Try the following:

    1. Start mysqld from gdb (or another debugger). See Section 5.8, “Debugging MySQL”.

    2. Run your test scripts.

    3. Print the backtrace and the local variables at the three lowest levels. In gdb, you can do this with the following commands when mysqld has crashed inside gdb: 

      backtrace
info local
up
info local
up
info local

      With gdb, you can also examine which threads exist with info threads and switch to a specific thread with thread N, where N is the thread ID.

  • Try to simulate your application with a Perl script to force MySQL to exit or misbehave.

  • Send a normal bug report. See Section 1.6, “How to Report Bugs or Problems”. Be even more detailed than usual. Because MySQL works for many people, the crash might result from something that exists only on your computer (for example, an error that is related to your particular system libraries).

  • If you have a problem with tables containing dynamic-length rows and you are using only VARCHAR columns (not BLOB or TEXT columns), you can try to change all VARCHAR to CHAR with ALTER TABLE. This forces MySQL to use fixed-size rows. Fixed-size rows take a little extra space, but are much more tolerant to corruption.

    The current dynamic row code has been in use for several years with very few problems, but dynamic-length rows are by nature more prone to errors, so it may be a good idea to try this strategy to see whether it helps.

  • 诊断问题时要考虑硬件故障的可能性。有缺陷的硬件可能是数据损坏的原因。在对硬件进行故障排除时，请特别注意内存和磁盘子系统。