打印

lnmp的mysql服务无辜挂掉

lnmp的mysql服务无辜挂掉

军哥你好:
安装好lnmp 1.3之后,运行两个网站,但是mysql会挂掉,也没有时间的规律,突然就挂掉了,每次重启lnmp restart,就恢复了。

err的日志:
复制内容到剪贴板
代码:
170311 11:06:04 mysqld_safe mysqld from pid file /usr/local/mysql/var/iZbp1h44q15c9j13hfqfdeZ.pid ended
170311 12:01:26 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
2017-03-11 12:01:26 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.29-log) starting as process 14585 ...
2017-03-11 12:01:26 14585 [Note] Plugin 'FEDERATED' is disabled.
2017-03-11 12:01:26 14585 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-03-11 12:01:26 14585 [Note] InnoDB: The InnoDB memory heap is disabled
2017-03-11 12:01:26 14585 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-03-11 12:01:26 14585 [Note] InnoDB: Memory barrier is not used
2017-03-11 12:01:26 14585 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-03-11 12:01:26 14585 [Note] InnoDB: Using CPU crc32 instructions
2017-03-11 12:01:26 14585 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2017-03-11 12:01:26 14585 [Note] InnoDB: Completed initialization of buffer pool
2017-03-11 12:01:26 14585 [Note] InnoDB: Highest supported file format is Barracuda.
2017-03-11 12:01:26 14585 [Note] InnoDB: The log sequence numbers 1626130 and 1626130 in ibdata files do not match the log sequence number 1626140 in the ib_logfiles!
2017-03-11 12:01:26 14585 [Note] InnoDB: Database was not shutdown normally!
2017-03-11 12:01:26 14585 [Note] InnoDB: Starting crash recovery.
2017-03-11 12:01:26 14585 [Note] InnoDB: Reading tablespace information from the .ibd files...
2017-03-11 12:01:26 14585 [Note] InnoDB: Restoring possible half-written data pages
2017-03-11 12:01:26 14585 [Note] InnoDB: from the doublewrite buffer...
2017-03-11 12:01:26 14585 [Note] InnoDB: 128 rollback segment(s) are active.
2017-03-11 12:01:26 14585 [Note] InnoDB: Waiting for purge to start
2017-03-11 12:01:26 14585 [Note] InnoDB: 5.6.29 started; log sequence number 1626140
2017-03-11 12:01:26 14585 [Note] Recovering after a crash using mysql-bin
2017-03-11 12:01:27 14585 [Note] Starting crash recovery...
2017-03-11 12:01:27 14585 [Note] Crash recovery finished.
2017-03-11 12:01:27 14585 [Note] Server hostname (bind-address): '*'; port: 3306
2017-03-11 12:01:27 14585 [Note] IPv6 is available.
2017-03-11 12:01:27 14585 [Note]   - '::' resolves to '::';
2017-03-11 12:01:27 14585 [Note] Server socket created on IP: '::'.
2017-03-11 12:01:27 14585 [Note] Event Scheduler: Loaded 0 events
2017-03-11 12:01:27 14585 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.29-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
麻烦军哥看看,谢谢。

TOP

回复 1# 的帖子

这个日子上没有具体的信息,可以往前再翻翻看看

TOP

谢谢军哥,今天4点收到短息说挂了,我把4点多的日志截取出来,您看看:
复制内容到剪贴板
代码:
170311 23:43:55 mysqld_safe mysqld from pid file /usr/local/mysql/var/iZbp1h44q15c9j13hfqfdeZ.pid ended
170311 23:47:14 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
2017-03-11 23:47:14 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.29-log) starting as process 22570 ...
2017-03-11 23:47:14 22570 [Note] Plugin 'FEDERATED' is disabled.
2017-03-11 23:47:14 22570 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-03-11 23:47:14 22570 [Note] InnoDB: The InnoDB memory heap is disabled
2017-03-11 23:47:14 22570 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-03-11 23:47:14 22570 [Note] InnoDB: Memory barrier is not used
2017-03-11 23:47:14 22570 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-03-11 23:47:14 22570 [Note] InnoDB: Using CPU crc32 instructions
2017-03-11 23:47:14 22570 [Note] InnoDB: Initializing buffer pool, size = 64.0M
2017-03-11 23:47:14 22570 [Note] InnoDB: Completed initialization of buffer pool
2017-03-11 23:47:14 22570 [Note] InnoDB: Highest supported file format is Barracuda.
2017-03-11 23:47:14 22570 [Note] InnoDB: 128 rollback segment(s) are active.
2017-03-11 23:47:14 22570 [Note] InnoDB: Waiting for purge to start
2017-03-11 23:47:14 22570 [Note] InnoDB: 5.6.29 started; log sequence number 1626250
2017-03-11 23:47:14 22570 [Note] Server hostname (bind-address): '*'; port: 3306
2017-03-11 23:47:14 22570 [Note] IPv6 is available.
2017-03-11 23:47:14 22570 [Note]   - '::' resolves to '::';
2017-03-11 23:47:14 22570 [Note] Server socket created on IP: '::'.
2017-03-11 23:47:15 22570 [Note] Event Scheduler: Loaded 0 events
2017-03-11 23:47:15 22570 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.29-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
170312 04:27:57 mysqld_safe Number of processes running now: 0
170312 04:27:57 mysqld_safe mysqld restarted
2017-03-12 04:27:57 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.29-log) starting as process 24426 ...
2017-03-12 04:27:57 24426 [Note] Plugin 'FEDERATED' is disabled.
2017-03-12 04:27:57 24426 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-03-12 04:27:57 24426 [Note] InnoDB: The InnoDB memory heap is disabled
2017-03-12 04:27:57 24426 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-03-12 04:27:57 24426 [Note] InnoDB: Memory barrier is not used
2017-03-12 04:27:57 24426 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-03-12 04:27:57 24426 [Note] InnoDB: Using CPU crc32 instructions
2017-03-12 04:27:57 24426 [Note] InnoDB: Initializing buffer pool, size = 64.0M
2017-03-12 04:27:57 24426 [Note] InnoDB: Completed initialization of buffer pool
2017-03-12 04:27:57 24426 [Note] InnoDB: Highest supported file format is Barracuda.
2017-03-12 04:27:57 24426 [Note] InnoDB: The log sequence numbers 1626250 and 1626250 in ibdata files do not match the log sequence number 1626260 in the ib_logfiles!
2017-03-12 04:27:57 24426 [Note] InnoDB: Database was not shutdown normally!
2017-03-12 04:27:57 24426 [Note] InnoDB: Starting crash recovery.
2017-03-12 04:27:57 24426 [Note] InnoDB: Reading tablespace information from the .ibd files...
2017-03-12 04:27:58 24426 [Note] InnoDB: Restoring possible half-written data pages
2017-03-12 04:27:58 24426 [Note] InnoDB: from the doublewrite buffer...
2017-03-12 04:27:58 24426 [Note] InnoDB: 128 rollback segment(s) are active.
2017-03-12 04:27:58 24426 [Note] InnoDB: Waiting for purge to start
2017-03-12 04:27:58 24426 [Note] InnoDB: 5.6.29 started; log sequence number 1626260
2017-03-12 04:27:58 24426 [Note] Recovering after a crash using mysql-bin
2017-03-12 04:27:58 24426 [Note] Starting crash recovery...
2017-03-12 04:27:58 24426 [Note] Crash recovery finished.
2017-03-12 04:27:58 24426 [Note] Server hostname (bind-address): '*'; port: 3306
2017-03-12 04:27:58 24426 [Note] IPv6 is available.
2017-03-12 04:27:58 24426 [Note]   - '::' resolves to '::';
2017-03-12 04:27:58 24426 [Note] Server socket created on IP: '::'.
2017-03-12 04:27:58 24426 [Note] Event Scheduler: Loaded 0 events
2017-03-12 04:27:58 24426 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.29-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
170312 04:28:38 mysqld_safe Number of processes running now: 0
170312 04:28:38 mysqld_safe mysqld restarted
2017-03-12 04:28:39 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.29-log) starting as process 24471 ...
2017-03-12 04:28:39 24471 [Note] Plugin 'FEDERATED' is disabled.
2017-03-12 04:28:39 24471 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-03-12 04:28:39 24471 [Note] InnoDB: The InnoDB memory heap is disabled
2017-03-12 04:28:39 24471 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-03-12 04:28:39 24471 [Note] InnoDB: Memory barrier is not used
2017-03-12 04:28:39 24471 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-03-12 04:28:39 24471 [Note] InnoDB: Using CPU crc32 instructions
2017-03-12 04:28:39 24471 [Note] InnoDB: Initializing buffer pool, size = 64.0M
InnoDB: mmap(68681728 bytes) failed; errno 12
2017-03-12 04:28:39 24471 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2017-03-12 04:28:39 24471 [ERROR] Plugin 'InnoDB' init function returned error.
2017-03-12 04:28:39 24471 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-03-12 04:28:39 24471 [ERROR] Unknown/unsupported storage engine: InnoDB
2017-03-12 04:28:39 24471 [ERROR] Aborting
再次感谢军哥。

TOP

第二段是建中午的时候,疯狂的挂掉,重启之后,不超过5分钟一直挂,我把这部分的日志也截取出来:
复制内容到剪贴板
代码:
170312 11:13:50 mysqld_safe mysqld from pid file /usr/local/mysql/var/iZbp1h44q15c9j13hfqfdeZ.pid ended
170312 11:13:52 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
2017-03-12 11:13:52 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.29-log) starting as process 32613 ...
2017-03-12 11:13:52 32613 [Note] Plugin 'FEDERATED' is disabled.
2017-03-12 11:13:52 32613 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-03-12 11:13:52 32613 [Note] InnoDB: The InnoDB memory heap is disabled
2017-03-12 11:13:52 32613 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-03-12 11:13:52 32613 [Note] InnoDB: Memory barrier is not used
2017-03-12 11:13:52 32613 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-03-12 11:13:52 32613 [Note] InnoDB: Using CPU crc32 instructions
2017-03-12 11:13:52 32613 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2017-03-12 11:13:52 32613 [Note] InnoDB: Completed initialization of buffer pool
2017-03-12 11:13:52 32613 [Note] InnoDB: Highest supported file format is Barracuda.
2017-03-12 11:13:52 32613 [Note] InnoDB: 128 rollback segment(s) are active.
2017-03-12 11:13:52 32613 [Note] InnoDB: Waiting for purge to start
2017-03-12 11:13:52 32613 [Note] InnoDB: 5.6.29 started; log sequence number 1626350
2017-03-12 11:13:52 32613 [Note] Server hostname (bind-address): '*'; port: 3306
2017-03-12 11:13:52 32613 [Note] IPv6 is available.
2017-03-12 11:13:52 32613 [Note] - '::' resolves to '::';
2017-03-12 11:13:52 32613 [Note] Server socket created on IP: '::'.
2017-03-12 11:13:52 32613 [Note] Event Scheduler: Loaded 0 events
2017-03-12 11:13:52 32613 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.29-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
2017-03-12 11:19:47 32613 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown

2017-03-12 11:19:47 32613 [Note] Giving 0 client threads a chance to die gracefully
2017-03-12 11:19:47 32613 [Note] Event Scheduler: Purging the queue. 0 events
2017-03-12 11:19:47 32613 [Note] Shutting down slave threads
2017-03-12 11:19:47 32613 [Note] Forcefully disconnecting 0 remaining clients
2017-03-12 11:19:47 32613 [Note] Binlog end
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'partition'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'ARCHIVE'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_METRICS'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_CMPMEM'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_CMP'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_LOCKS'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'INNODB_TRX'
2017-03-12 11:19:47 32613 [Note] Shutting down plugin 'InnoDB'
2017-03-12 11:19:47 32613 [Note] InnoDB: FTS optimize thread exiting.
2017-03-12 11:19:47 32613 [Note] InnoDB: Starting shutdown...
2017-03-12 11:19:48 32613 [Note] InnoDB: Shutdown completed; log sequence number 1626360
2017-03-12 11:19:48 32613 [Note] Shutting down plugin 'BLACKHOLE'
2017-03-12 11:19:48 32613 [Note] Shutting down plugin 'CSV'
2017-03-12 11:19:48 32613 [Note] Shutting down plugin 'MyISAM'
2017-03-12 11:19:48 32613 [Note] Shutting down plugin 'MRG_MYISAM'
2017-03-12 11:19:48 32613 [Note] Shutting down plugin 'MEMORY'
2017-03-12 11:19:48 32613 [Note] Shutting down plugin 'sha256_password'
2017-03-12 11:19:48 32613 [Note] Shutting down plugin 'mysql_old_password'
2017-03-12 11:19:48 32613 [Note] Shutting down plugin 'mysql_native_password'
2017-03-12 11:19:48 32613 [Note] Shutting down plugin 'binlog'
2017-03-12 11:19:48 32613 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

170312 11:19:48 mysqld_safe mysqld from pid file /usr/local/mysql/var/iZbp1h44q15c9j13hfqfdeZ.pid ended
170312 11:19:51 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var
2017-03-12 11:19:51 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.29-log) starting as process 815 ...
2017-03-12 11:19:51 815 [Note] Plugin 'FEDERATED' is disabled.
2017-03-12 11:19:51 815 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-03-12 11:19:51 815 [Note] InnoDB: The InnoDB memory heap is disabled
2017-03-12 11:19:51 815 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-03-12 11:19:51 815 [Note] InnoDB: Memory barrier is not used
2017-03-12 11:19:51 815 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-03-12 11:19:51 815 [Note] InnoDB: Using CPU crc32 instructions
2017-03-12 11:19:51 815 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2017-03-12 11:19:51 815 [Note] InnoDB: Completed initialization of buffer pool
2017-03-12 11:19:51 815 [Note] InnoDB: Highest supported file format is Barracuda.
2017-03-12 11:19:51 815 [Note] InnoDB: 128 rollback segment(s) are active.
2017-03-12 11:19:51 815 [Note] InnoDB: Waiting for purge to start
2017-03-12 11:19:51 815 [Note] InnoDB: 5.6.29 started; log sequence number 1626360
2017-03-12 11:19:51 815 [Note] Server hostname (bind-address): '*'; port: 3306
2017-03-12 11:19:51 815 [Note] IPv6 is available.
2017-03-12 11:19:51 815 [Note] - '::' resolves to '::';
2017-03-12 11:19:51 815 [Note] Server socket created on IP: '::'.
2017-03-12 11:19:51 815 [Note] Event Scheduler: Loaded 0 events
2017-03-12 11:19:51 815 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.29-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution

TOP

引用:
InnoDB: mmap(68681728 bytes) failed; errno 12
2017-03-12 04:28:39 24471 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
内存可能不足了
你内存多大,建议把 innodb_buffer_pool_size 调小一些,再观察看看

TOP

主机的内存是2G,现在这里设置的是:256M.

调整到128或者64M ?

TOP

军哥您好,另外一个阿里云的ECS,是2核4G内存的;

innodb_buffer_pool_size 之前设置的大小是:512M,也会导致数据库挂掉,但是不是很频繁,一般是1~2天才挂一次,是不是也要调整小一点,比如设置成256M ?

谢谢。

TOP

另外,刚才查看了数据库编码,都是mysiam的,这样是不是可以关闭InnoDB这个数据库引擎?

谢谢。

TOP

回复 7# 的帖子

2G的话设置128MB把,4G的256MB

mysql 5.6的话,编辑/etc/my.cnf
innodb=OFF
ignore-builtin-innodb
skip-innodb
default_storage_engine = MyISAM
default_tmp_storage_engine = MyISAM

将#loose-innodb 那些行前面的# 删掉

TOP

感谢军哥,这几天一直观察中,数据库没有再挂过,再次感谢军哥。

TOP