MySQL 不断崩溃

Question

我在云中有一台运行 Ubuntu 12.10 的服务器，配备 512Mb 内存，仅用于运行 wordpress 网站(每天访问量约为 1000 次)。

MySQL 总是崩溃然后我启用了 4Gb 交换以查看是否可以工作......但仍然崩溃......我每次都需要重新启动服务......

检查来自 mysql 的错误日志，我注意到 InnoDB 似乎在循环中试图恢复某些东西，但我认为它不能...有人可以帮助我吗？

131009 17:56:57 InnoDB: 5.5.32 started; log sequence number 242183133
131009 17:56:57 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
131009 17:56:57 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
131009 17:56:57 [Note] Server socket created on IP: '127.0.0.1'.
131009 17:56:57 [Note] Event Scheduler: Loaded 0 events
131009 17:56:57 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-0ubuntu0.12.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
131009 17:57:25 [Note] Plugin 'FEDERATED' is disabled.
131009 17:57:25 InnoDB: The InnoDB memory heap is disabled
131009 17:57:25 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131009 17:57:25 InnoDB: Compressed tables use zlib 1.2.7
131009 17:57:25 InnoDB: Using Linux native AIO
131009 17:57:25 InnoDB: Initializing buffer pool, size = 128.0M
131009 17:57:25 InnoDB: Completed initialization of buffer pool
131009 17:57:25 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!
131009 17:57:25  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...
131009 17:57:26  InnoDB: Waiting for the background threads to start
131009 17:57:27 InnoDB: 5.5.32 started; log sequence number 242183133
131009 17:57:27 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
131009 17:57:27 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
131009 17:57:27 [Note] Server socket created on IP: '127.0.0.1'.
131009 17:57:27 [Note] Event Scheduler: Loaded 0 events
131009 17:57:27 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-0ubuntu0.12.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
131009 17:58:05 [Note] Plugin 'FEDERATED' is disabled.
131009 17:58:05 InnoDB: The InnoDB memory heap is disabled
131009 17:58:05 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131009 17:58:05 InnoDB: Compressed tables use zlib 1.2.7
131009 17:58:05 InnoDB: Using Linux native AIO
131009 17:58:05 InnoDB: Initializing buffer pool, size = 128.0M
131009 17:58:05 InnoDB: Completed initialization of buffer pool
131009 17:58:06 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!
131009 17:58:06  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...
131009 17:58:06  InnoDB: Waiting for the background threads to start
131009 17:58:07 InnoDB: 5.5.32 started; log sequence number 242183143
131009 17:58:07 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
131009 17:58:07 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
131009 17:58:07 [Note] Server socket created on IP: '127.0.0.1'.
131009 17:58:07 [Note] Event Scheduler: Loaded 0 events
131009 17:58:07 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-0ubuntu0.12.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
131009 17:58:33 [Note] Plugin 'FEDERATED' is disabled.
131009 17:58:33 InnoDB: The InnoDB memory heap is disabled
131009 17:58:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131009 17:58:33 InnoDB: Compressed tables use zlib 1.2.7
131009 17:58:33 InnoDB: Using Linux native AIO
131009 17:58:33 InnoDB: Initializing buffer pool, size = 128.0M
131009 17:58:34 InnoDB: Completed initialization of buffer pool
131009 17:58:34 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 242183143
131009 17:58:34  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...
InnoDB: Doing recovery: scanned up to log sequence number 242183153
131009 17:58:34  InnoDB: Waiting for the background threads to start
131009 17:58:35 InnoDB: 5.5.32 started; log sequence number 242183153
131009 17:58:35 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
131009 17:58:35 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
131009 17:58:35 [Note] Server socket created on IP: '127.0.0.1'.
131009 17:58:35 [Note] Event Scheduler: Loaded 0 events
131009 17:58:35 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-0ubuntu0.12.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
131009 17:58:44 [Note] Plugin 'FEDERATED' is disabled.
131009 17:58:44 InnoDB: The InnoDB memory heap is disabled
131009 17:58:44 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131009 17:58:44 InnoDB: Compressed tables use zlib 1.2.7
131009 17:58:44 InnoDB: Using Linux native AIO
131009 17:58:45 InnoDB: Initializing buffer pool, size = 128.0M
131009 17:58:45 InnoDB: Completed initialization of buffer pool
131009 17:58:45 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!
131009 17:58:45  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...
131009 17:58:45  InnoDB: Waiting for the background threads to start
131009 17:58:47 [Note] Plugin 'FEDERATED' is disabled.
131009 17:58:47 InnoDB: The InnoDB memory heap is disabled
131009 17:58:47 InnoDB: Mutexes and rw_locks use GCC atomic builtins
131009 17:58:47 InnoDB: Compressed tables use zlib 1.2.7
131009 17:58:47 InnoDB: Using Linux native AIO
131009 17:58:47 InnoDB: Initializing buffer pool, size = 128.0M
131009 17:58:47 InnoDB: Completed initialization of buffer pool
131009 17:58:47 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!
131009 17:58:47  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...
131009 17:58:47  InnoDB: Waiting for the background threads to start
131009 17:58:48 InnoDB: 5.5.32 started; log sequence number 242183153
131009 17:58:48 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
131009 17:58:48 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
131009 17:58:48 [Note] Server socket created on IP: '127.0.0.1'.
131009 17:58:48 [Note] Event Scheduler: Loaded 0 events
131009 17:58:48 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-0ubuntu0.12.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Answer 1

我发现这发生在 MySQL 使用过多 RAM 时。 This question和 this post两者都有助于提供测试方法和解决方案。

对我来说，将这个添加到我的 /etc/mysql/my.cnf 文件中解决了这个问题。

innodb_buffer_pool_size = 12M

这是我决定分配多少内存的方法。启动除 MySQL 之外的所有程序并检查有多少内存可用。将您的 InnoDB 缓冲区设置为该数量的 10%。对我来说是 12M。