Mysql innoDB 经常崩溃。

Question

我的数据库（mysql）服务器一直崩溃/重启，我不知道该怎么办。我一直在dbname.org.err文件中看到以下内容：

131205 18:49:05 mysqld_safe mysqld from pid file /var/lib/mysql/les.lesplan.org.pid ended
131205 18:50:12 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
131205 18:50:12 [Note] Plugin 'FEDERATED' is disabled.
131205 18:50:12 InnoDB: The InnoDB memory heap is disabled
131205 18:50:12 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131205 18:50:12 InnoDB: Compressed tables use zlib 1.2.3
131205 18:50:12 InnoDB: Using Linux native AIO
131205 18:50:12 InnoDB: Initializing buffer pool, size = 128.0M
131205 18:50:12 InnoDB: Completed initialization of buffer pool
131205 18:50:12 InnoDB: highest supported file format is Barracuda.
131205 18:50:12  InnoDB: Waiting for the background threads to start
131205 18:50:13 InnoDB: 5.5.32 started; log sequence number 94296300
131205 18:50:13 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
131205 18:50:13 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
131205 18:50:13 [Note] Server socket created on IP: '0.0.0.0'.
131205 18:50:14 [Note] Event Scheduler: Loaded 0 events
131205 18:50:14 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
131206 07:32:53 mysqld_safe Number of processes running now: 0
131206 07:32:53 mysqld_safe mysqld restarted
131206  7:32:54 [Note] Plugin 'FEDERATED' is disabled.
131206  7:32:54 InnoDB: The InnoDB memory heap is disabled
131206  7:32:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131206  7:32:54 InnoDB: Compressed tables use zlib 1.2.3
131206  7:32:54 InnoDB: Using Linux native AIO
131206  7:32:54 InnoDB: Initializing buffer pool, size = 128.0M
131206  7:32:54 InnoDB: Completed initialization of buffer pool
131206  7:32:54 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
131206  7:32:54  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
131206  7:32:54  InnoDB: Waiting for the background threads to start
131206  7:32:55 InnoDB: 5.5.32 started; log sequence number 94790638
131206  7:32:55 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
131206  7:32:55 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
131206  7:32:55 [Note] Server socket created on IP: '0.0.0.0'.
131206  7:32:55 [Note] Event Scheduler: Loaded 0 events
131206  7:32:55 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
131206 07:33:07 mysqld_safe Number of processes running now: 0
131206 07:33:07 mysqld_safe mysqld restarted
131206  7:33:07 [Note] Plugin 'FEDERATED' is disabled.
131206  7:33:07 InnoDB: The InnoDB memory heap is disabled
131206  7:33:07 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131206  7:33:07 InnoDB: Compressed tables use zlib 1.2.3
131206  7:33:07 InnoDB: Using Linux native AIO
131206  7:33:07 InnoDB: Initializing buffer pool, size = 128.0M
131206  7:33:07 InnoDB: Completed initialization of buffer pool
131206  7:33:07 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
131206  7:33:07  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
131206  7:33:08  InnoDB: Waiting for the background threads to start
131206  7:33:09 InnoDB: 5.5.32 started; log sequence number 94790648
131206  7:33:09 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
131206  7:33:09 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
131206  7:33:09 [Note] Server socket created on IP: '0.0.0.0'.
131206  7:33:09 [Note] Event Scheduler: Loaded 0 events
131206  7:33:09 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
131206 07:38:38 mysqld_safe Number of processes running now: 0
131206 07:38:38 mysqld_safe mysqld restarted
131206  7:38:38 [Note] Plugin 'FEDERATED' is disabled.
131206  7:38:38 InnoDB: The InnoDB memory heap is disabled
131206  7:38:38 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131206  7:38:38 InnoDB: Compressed tables use zlib 1.2.3
131206  7:38:38 InnoDB: Using Linux native AIO
131206  7:38:38 InnoDB: Initializing buffer pool, size = 128.0M
131206  7:38:38 InnoDB: Completed initialization of buffer pool
131206  7:38:38 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
131206  7:38:38  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
131206  7:38:38  InnoDB: Waiting for the background threads to start
131206  7:38:39 InnoDB: 5.5.32 started; log sequence number 94790674
131206  7:38:39 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
131206  7:38:39 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
131206  7:38:39 [Note] Server socket created on IP: '0.0.0.0'.
131206  7:38:39 [Note] Event Scheduler: Loaded 0 events
131206  7:38:39 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-cll'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
root@les [/var/lib/mysql]#

服务器似乎在随机时间崩溃并重启，尽管它似乎更多发生在上午7:30左右（± 30分钟）。此外，在这个时间附近没有任何cron作业在运行。

如果有任何帮助，将不胜感激！

ss

- brokenspoke

---

在您下面的评论中，您说过“最终删除并从旧备份中恢复”。那样做是否阻止了您的MySQL再次崩溃？您有什么线索可以解释这些崩溃是由什么引起的吗？ - Vincent

也许相关，systemd没有正确关闭MariaDB？ 这些是该问题应在适当网站上提出的原因。这个答案缺乏对问题潜在根本原因的分析。你只会得到一个开发者找到适用于他们的轶事。 - jww

Answer 1

删除ib_log文件并在配置文件中加入innodb_force_recovery=6，然后重新启动mysql即可解决问题。

- user3048109

---

这些步骤非常激进，根据目前关于此处遇到的具体问题的信息量来看，完全没有理由进行这样的操作。 - Michael - sqlbot

在这个网站上，如果这个答案有效，点击旁边的勾选标记将其标记为“已接受”是正确的“编辑礼仪”（相当于“已解决”）。 :) - Anonymous Penguin

它从未得到解决，所以我没有检查它...我认为它已经修复了，但是问题仍然不断出现。最终，我只能从旧备份中删除并恢复。尽管如此，还是感谢你的明显提示。 - brokenspoke

我应该在哪里丢弃 "ib_log files"？ - User

请注意，innodb_force_recovery=5或6可能会对数据库造成破坏！请先尝试使用1，再逐步升级到2、3和4。每个选项都比前一个更强大，但5和6可能会导致数据损坏。请谨慎使用5/6！ - lowly_junior_sysadmin

Answer 2

原来，有时内存使用会剧增（我猜测这是由于 Apache 的子进程数量急剧增加导致的），这会导致内存压力。系统的 OOM 杀手会选择关闭 MySQL，因为它通常是系统中占用最多内存的程序。

所以，我：

• 增加了更多内存
• 对 Apache 进行了优化，控制了生成的子进程数量。

- Eric Jiang

---

此外，您可以将 /proc/$(pidof mysqld)/oom_adj 设置为 -17，以便 oom-killer 不会杀死 mysqld。最好杀死 apache 而不是 mysqld。请参见 https://twindb.com/oom-killer-or-how-to-fail-at-start/。 - akuzminsky