MySQL数据库每周下降一次或两次，我如何诊断？

Question

我有一个运行Ubuntu12.04LTS的linode.com帐户。在我的服务器上，我有两个wordpress实例，其流量很小(每天有100到400个用户)和一个Sendy实例(几乎从未使用过)。每周有一两次，随机时间，MySQL崩溃，当我试图访问一个wordpress站点时，我会得到一个错误，就像数据库连接失败一样(我不确定这就是确切的错误，我总是急于重启服务器以使站点恢复正常)。

我的问题是，我怎么知道是什么引起了这个问题？如何启用错误日志记录和/或查看错误日志？还有什么我能做的吗？

这是我正在运行的MySQL版本：

+-------------------------+-----------------------------+
| Variable_name           | Value                       |
+-------------------------+-----------------------------+
| innodb_version          | 5.5.41                      |
| protocol_version        | 10                          |
| slave_type_conversions  |                             |
| version                 | 5.5.41-0ubuntu0.14.04.1-log |
| version_comment         | (Ubuntu)                    |
| version_compile_machine | x86_64                      |
| version_compile_os      | debian-linux-gnu            |
+-------------------------+-----------------------------+

我查看了error.log内部，并在数据库最后一次崩溃之前发现了这些错误：

150518  0:47:26 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_771_0.MYI'; try to repair it
150518  0:47:26 [ERROR] Got an error from unknown thread, /build/buildd/mysql-5.5-5.5.41/storage/myisam/mi_write.c:226
150518  0:47:26 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_771_1.MYI'; try to repair it
150518  0:47:26 [ERROR] Got an error from unknown thread, /build/buildd/mysql-5.5-5.5.41/storage/myisam/mi_write.c:226
150518  2:45:33 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_771_0.MYI'; try to repair it
150518  2:45:34 [ERROR] Got an error from unknown thread, /build/buildd/mysql-5.5-5.5.41/storage/myisam/mi_write.c:226
150518  3:02:14 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_771_0.MYI'; try to repair it
150518  3:02:14 [ERROR] Got an error from unknown thread, /build/buildd/mysql-5.5-5.5.41/storage/myisam/mi_write.c:226
150518  3:33:57 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_771_0.MYI'; try to repair it
150518  3:33:57 [ERROR] Got an error from unknown thread, /build/buildd/mysql-5.5-5.5.41/storage/myisam/mi_write.c:226
150518  5:18:36 [Note] /usr/sbin/mysqld: Normal shutdown

150518  5:18:36 [Note] Event Scheduler: Purging the queue. 0 events
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of threa 62546  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62545  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62544  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62543  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62542  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62541  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62540  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62539  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62538  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62537  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62536  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62535  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62533  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62532  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62531  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62530  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62529  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62528  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62527  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62526  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62525  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62524  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62523  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62522  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62521  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62520  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62519  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62518  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62517  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62516  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62515  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62514  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62513  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62512  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62511  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62510  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62509  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62508  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62506  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62505  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62504  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62503  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62502  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62501  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62500  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62499  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62498  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62497  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62496  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62495  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62494  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62493  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62492  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62491  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62490  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62489  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62488  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62487  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62486  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62485  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62484  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62483  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62482  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62481  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62479  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62478  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62477  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62475  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62474  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62473  user: 'wp_taptappower'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62472  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62471  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62470  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 62469  user: 'wp_honey'
150518  5:18:38 [Warning] /usr/sbin/mysqld: Forcing close of thread 60305  user: 'wp_honey'

150518  5:18:39  InnoDB: Starting shutdown...
150518  5:18:42  InnoDB: Shutdown completed; log sequence number 17372087869
150518  5:18:42 [Note] /usr/sbin/mysqld: Shutdown complete

150518  5:19:14 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please u$
150518  5:19:14 [Note] Plugin 'FEDERATED' is disabled.
150518  5:19:14 InnoDB: The InnoDB memory heap is disabled
150518  5:19:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150518  5:19:14 InnoDB: Compressed tables use zlib 1.2.8
150518  5:19:14 InnoDB: Using Linux native AIO
150518  5:19:14 InnoDB: Initializing buffer pool, size = 128.0M
150518  5:19:14 InnoDB: Completed initialization of buffer pool
150518  5:19:14 InnoDB: highest supported file format is Barracuda.
150518  5:19:15  InnoDB: Waiting for the background threads to start
150518  5:19:16 InnoDB: 5.5.41 started; log sequence number 17372087869
150518  5:19:16 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
150518  5:19:16 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
150518  5:19:16 [Note] Server socket created on IP: '127.0.0.1'.
150518  5:19:16 [Note] Event Scheduler: Loaded 0 events
150518  5:19:16 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.41-0ubuntu0.14.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

你可以看到，在5:18:36，我在一封关于我的网站被关闭的电子邮件中醒来后重新启动了服务器。:(

Answer 1

如果您的流量非常小，您可以启用常规日志记录所有内容并使用该日志进行调试。警告说，这可能会对性能测试产生不利影响，首先是在非高峰时间.

在你对我的评论发表评论之后，请在下面找到我在我自己的my.cnf中的内容。正如我所说的，请注意这是一台开发机器，不对网络开放。当你做这件事的时候要小心注意你的负载。

general_log     = on
general_log_file= /home/pol/Downloads/software/mysql/5.6/inst/logfile.txt

重新仔细阅读问题的编辑

我注意到您有一个linode帐户，它们提供SSD驱动器--我建议您组织您的系统，以便将一般日志写入一个新的驱动器--直到找到问题的根源为止，您可以雇用它吗？显然，这取决于网站的收入，但这是一项业务，而不是一个技术性的决定。

编辑“任择议定书”的进一步评论

5:18:36 - WTF？我感觉到你的痛苦！

查看这，特别是myisamchk实用程序这里。修复MyISAM表有这么多东西这一事实应该告诉您，这个问题并非闻所未闻！在任何情况下，AIUI、MyISAM都被推荐用于5.7版本--您真的应该考虑将其转换为InnoDB！

编辑-查找文件系统和挂载点

来自这里。

df
df /path/to/dir
df /path/to/file

In this example find out partition name for a file called /users/f/foo/file.txt, enter:

$ df -T /users/f/foo/file.txt

通常，当您安装Linux时，它会创建一个/tmp分区--这在您的系统上是完整的吗？

编辑-更多关于文件/分区&C.

所以，如果我是你的话，我会暂时保持登录的。另外，每5分钟运行一次cron作业，以检查您的/tmp是如何运行的。用度，您可以跟踪单个文件--做一些像date >> track_size.txt，然后du -h >> track_size.txt这样的事情来监视事情。检查这。运行“人类伊萨克”-这也可能是有帮助的。