MySQL在日志文件中无故停止运行。

Question

我正在CentOS6.5上运行MySQL 5.6.17 (2.6.32-042START 090.5 #1 SMP 21 00:15:09 MSK 2014 x86_64 GNU/Linux)。

每隔几周，MySQL就会停止运行，日志没有显示任何显示原因的信息。

磁盘空间很好(使用了50%)，内存使用也可以。

我还能采取什么其他步骤来诊断这个问题呢？

2014-08-13 17:55:18 26960 [Note] InnoDB: 128 rollback segment(s) are active.
2014-08-13 17:55:18 26960 [Note] InnoDB: Waiting for purge to start
2014-08-13 17:55:18 26960 [Note] InnoDB: 5.6.17 started; log sequence number 1307884677
2014-08-13 17:55:18 26960 [Note] Server hostname (bind-address): '*'; port: 3306
2014-08-13 17:55:18 26960 [Note] IPv6 is available.
2014-08-13 17:55:18 26960 [Note]   - '::' resolves to '::';
2014-08-13 17:55:18 26960 [Note] Server socket created on IP: '::'.
2014-08-13 17:55:19 26960 [Note] Event Scheduler: Loaded 0 events
2014-08-13 17:55:19 26960 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.17'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
140813 17:57:10 mysqld_safe Number of processes running now: 0
140813 17:57:10 mysqld_safe mysqld restarted
2014-08-13 17:57:12 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-08-13 17:57:13 27945 [Note] Plugin 'FEDERATED' is disabled.
2014-08-13 17:57:13 27945 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-08-13 17:57:13 27945 [Note] InnoDB: The InnoDB memory heap is disabled
2014-08-13 17:57:13 27945 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-08-13 17:57:13 27945 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-08-13 17:57:13 27945 [Note] InnoDB: Using Linux native AIO
2014-08-13 17:57:13 27945 [Note] InnoDB: Using CPU crc32 instructions
2014-08-13 17:57:13 27945 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-08-13 17:57:13 27945 [Note] InnoDB: Completed initialization of buffer pool
2014-08-13 17:57:13 27945 [Note] InnoDB: Highest supported file format is Barracuda.
2014-08-13 17:57:13 27945 [Note] InnoDB: Log scan progressed past the checkpoint lsn 1307884687
2014-08-13 17:57:13 27945 [Note] InnoDB: Database was not shutdown normally!
2014-08-13 17:57:13 27945 [Note] InnoDB: Starting crash recovery.
2014-08-13 17:57:13 27945 [Note] InnoDB: Reading tablespace information from the .ibd files...
2014-08-13 17:57:13 27945 [Note] InnoDB: Restoring possible half-written data pages
2014-08-13 17:57:13 27945 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 1307954236
2014-08-13 17:57:13 27945 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
2014-08-13 17:57:14 27945 [Note] InnoDB: 128 rollback segment(s) are active.
2014-08-13 17:57:14 27945 [Note] InnoDB: Waiting for purge to start
2014-08-13 17:57:14 27945 [Note] InnoDB: 5.6.17 started; log sequence number 1307954236
2014-08-13 17:57:14 27945 [Note] Server hostname (bind-address): '*'; port: 3306
2014-08-13 17:57:14 27945 [Note] IPv6 is available.
2014-08-13 17:57:14 27945 [Note]   - '::' resolves to '::';
2014-08-13 17:57:14 27945 [Note] Server socket created on IP: '::'.
2014-08-13 17:57:14 27945 [Note] Event Scheduler: Loaded 0 events
2014-08-13 17:57:14 27945 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.17'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
140813 17:57:21 mysqld_safe Number of processes running now: 0
140813 17:57:21 mysqld_safe mysqld restarted
2014-08-13 17:57:21 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-08-13 17:57:21 28100 [Note] Plugin 'FEDERATED' is disabled.
2014-08-13 17:57:21 28100 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-08-13 17:57:21 28100 [Note] InnoDB: The InnoDB memory heap is disabled
2014-08-13 17:57:21 28100 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-08-13 17:57:21 28100 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-08-13 17:57:21 28100 [Note] InnoDB: Using Linux native AIO
2014-08-13 17:57:21 28100 [Note] InnoDB: Using CPU crc32 instructions
2014-08-13 17:57:21 28100 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-08-13 17:57:21 28100 [Note] InnoDB: Completed initialization of buffer pool
2014-08-13 17:57:21 28100 [Note] InnoDB: Highest supported file format is Barracuda.
2014-08-13 17:57:21 28100 [Note] InnoDB: Log scan progressed past the checkpoint lsn 1307954246
2014-08-13 17:57:21 28100 [Note] InnoDB: Database was not shutdown normally!
2014-08-13 17:57:21 28100 [Note] InnoDB: Starting crash recovery.
2014-08-13 17:57:21 28100 [Note] InnoDB: Reading tablespace information from the .ibd files...
2014-08-13 17:57:21 28100 [Note] InnoDB: Restoring possible half-written data pages
2014-08-13 17:57:21 28100 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 1307959447
2014-08-13 17:57:21 28100 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
2014-08-13 17:57:22 28100 [Note] InnoDB: 128 rollback segment(s) are active.
2014-08-13 17:57:22 28100 [Note] InnoDB: Waiting for purge to start
2014-08-13 17:57:22 28100 [Note] InnoDB: 5.6.17 started; log sequence number 1307959447
2014-08-13 17:57:22 28100 [Note] Server hostname (bind-address): '*'; port: 3306
2014-08-13 17:57:22 28100 [Note] IPv6 is available.
2014-08-13 17:57:22 28100 [Note]   - '::' resolves to '::';
2014-08-13 17:57:22 28100 [Note] Server socket created on IP: '::'.
2014-08-13 17:57:22 28100 [Note] Event Scheduler: Loaded 0 events
2014-08-13 17:57:22 28100 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.17'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
140813 17:57:23 mysqld_safe Number of processes running now: 0
140813 17:57:23 mysqld_safe mysqld restarted
2014-08-13 17:57:24 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-08-13 17:57:24 28175 [Note] Plugin 'FEDERATED' is disabled.
2014-08-13 17:57:24 28175 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-08-13 17:57:24 28175 [Note] InnoDB: The InnoDB memory heap is disabled
2014-08-13 17:57:24 28175 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-08-13 17:57:24 28175 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-08-13 17:57:24 28175 [Note] InnoDB: Using Linux native AIO
2014-08-13 17:57:24 28175 [Note] InnoDB: Using CPU crc32 instructions
2014-08-13 17:57:24 28175 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-08-13 17:57:24 28175 [Note] InnoDB: Completed initialization of buffer pool
2014-08-13 17:57:24 28175 [Note] InnoDB: Highest supported file format is Barracuda.
2014-08-13 17:57:24 28175 [Note] InnoDB: Log scan progressed past the checkpoint lsn 1307954246
2014-08-13 17:57:24 28175 [Note] InnoDB: Database was not shutdown normally!
2014-08-13 17:57:24 28175 [Note] InnoDB: Starting crash recovery.
2014-08-13 17:57:24 28175 [Note] InnoDB: Reading tablespace information from the .ibd files...
2014-08-13 17:57:24 28175 [Note] InnoDB: Restoring possible half-written data pages
2014-08-13 17:57:24 28175 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 1307974563
140813 17:57:24 mysqld_safe mysqld from pid file /var/lib/mysql/localhost.local.pid ended
140814 09:25:42 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2014-08-14 09:25:43 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-08-14 09:25:43 22422 [Note] Plugin 'FEDERATED' is disabled.
2014-08-14 09:25:43 22422 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-08-14 09:25:43 22422 [Note] InnoDB: The InnoDB memory heap is disabled
2014-08-14 09:25:43 22422 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-08-14 09:25:43 22422 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-08-14 09:25:43 22422 [Note] InnoDB: Using Linux native AIO
2014-08-14 09:25:43 22422 [Note] InnoDB: Using CPU crc32 instructions
2014-08-14 09:25:43 22422 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-08-14 09:25:43 22422 [Note] InnoDB: Completed initialization of buffer pool
2014-08-14 09:25:43 22422 [Note] InnoDB: Highest supported file format is Barracuda.
2014-08-14 09:25:43 22422 [Note] InnoDB: Log scan progressed past the checkpoint lsn 1307954246
2014-08-14 09:25:43 22422 [Note] InnoDB: Database was not shutdown normally!
2014-08-14 09:25:43 22422 [Note] InnoDB: Starting crash recovery.
2014-08-14 09:25:43 22422 [Note] InnoDB: Reading tablespace information from the .ibd files...
2014-08-14 09:25:50 22422 [Note] InnoDB: Restoring possible half-written data pages
2014-08-14 09:25:50 22422 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 1307974563
2014-08-14 09:25:51 22422 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
2014-08-14 09:25:52 22422 [Note] InnoDB: 128 rollback segment(s) are active.
2014-08-14 09:25:52 22422 [Note] InnoDB: Waiting for purge to start
2014-08-14 09:25:52 22422 [Note] InnoDB: 5.6.17 started; log sequence number 1307974563
2014-08-14 09:25:52 22422 [Note] Server hostname (bind-address): '*'; port: 3306
2014-08-14 09:25:52 22422 [Note] IPv6 is available.
2014-08-14 09:25:52 22422 [Note]   - '::' resolves to '::';
2014-08-14 09:25:52 22422 [Note] Server socket created on IP: '::'.
2014-08-14 09:25:53 22422 [Note] Event Scheduler: Loaded 0 events
2014-08-14 09:25:53 22422 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.17'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

Answer 1

有几件事你可以做：

• 检查dmesg输出的分段故障(可能是分段故障)
• 对mysql和unix进程列表进行详细转储
• 图形内存、磁盘、缓冲池使用情况和任何其他您可以想到的指标
• 使用系统尝试捕捉任何出错的东西。