经常出现“连接数据库时出错”

longlian90

1G1核阿里云ECS，wordpress程序，做了静态缓存，经常出现网站打不开，提示“连接数据库时出错”
日志如下
2016-03-05 04:20:05 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-03-05 04:20:05 1338 [Note] Plugin 'FEDERATED' is disabled.
2016-03-05 04:20:05 7f9bfbbcd720 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2016-03-05 04:20:05 1338 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-03-05 04:20:05 1338 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-05 04:20:05 1338 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-05 04:20:05 1338 [Note] InnoDB: Memory barrier is not used
2016-03-05 04:20:05 1338 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-03-05 04:20:05 1338 [Note] InnoDB: Using CPU crc32 instructions
2016-03-05 04:20:05 1338 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2016-03-05 04:20:05 1338 [Note] InnoDB: Completed initialization of buffer pool
2016-03-05 04:20:05 1338 [Note] InnoDB: Highest supported file format is Barracuda.
2016-03-05 04:20:05 1338 [Note] InnoDB: The log sequence numbers 1626024 and 1626024 in ibdata files do not match the log sequence number 1626054 in the ib_logfiles!
2016-03-05 04:20:05 1338 [Note] InnoDB: Database was not shutdown normally!
2016-03-05 04:20:05 1338 [Note] InnoDB: Starting crash recovery.
2016-03-05 04:20:05 1338 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-03-05 04:20:05 1338 [Note] InnoDB: Restoring possible half-written data pages
2016-03-05 04:20:05 1338 [Note] InnoDB: from the doublewrite buffer...
2016-03-05 04:20:06 1338 [Note] InnoDB: 128 rollback segment(s) are active.
2016-03-05 04:20:06 1338 [Note] InnoDB: Waiting for purge to start
2016-03-05 04:20:06 1338 [Note] InnoDB: 5.6.23 started; log sequence number 1626054
2016-03-05 04:20:06 1338 [Note] Recovering after a crash using mysql-bin
2016-03-05 04:20:06 1338 [Note] Starting crash recovery...
2016-03-05 04:20:06 1338 [Note] Crash recovery finished.
2016-03-05 04:20:06 1338 [Note] Server hostname (bind-address): '*'; port: 3306
2016-03-05 04:20:06 1338 [Note] IPv6 is not available.
2016-03-05 04:20:06 1338 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2016-03-05 04:20:06 1338 [Note] Server socket created on IP: '0.0.0.0'.
2016-03-05 04:20:07 1338 [Note] Event Scheduler: Loaded 0 events
2016-03-05 04:20:07 1338 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.23-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
160305 07:32:44 mysqld_safe Number of processes running now: 0
160305 07:32:44 mysqld_safe mysqld restarted
2016-03-05 07:32:45 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-03-05 07:32:45 1577 [Note] Plugin 'FEDERATED' is disabled.
2016-03-05 07:32:45 7f1670e27720 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2016-03-05 07:32:45 1577 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-03-05 07:32:45 1577 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-05 07:32:45 1577 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-05 07:32:45 1577 [Note] InnoDB: Memory barrier is not used
2016-03-05 07:32:45 1577 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-03-05 07:32:45 1577 [Note] InnoDB: Using CPU crc32 instructions
2016-03-05 07:32:45 1577 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2016-03-05 07:32:45 1577 [Note] InnoDB: Completed initialization of buffer pool
2016-03-05 07:32:45 1577 [Note] InnoDB: Highest supported file format is Barracuda.
2016-03-05 07:32:45 1577 [Note] InnoDB: The log sequence numbers 1626024 and 1626024 in ibdata files do not match the log sequence number 1626064 in the ib_logfiles!
2016-03-05 07:32:45 1577 [Note] InnoDB: Database was not shutdown normally!
2016-03-05 07:32:45 1577 [Note] InnoDB: Starting crash recovery.
2016-03-05 07:32:45 1577 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-03-05 07:32:45 1577 [Note] InnoDB: Restoring possible half-written data pages
2016-03-05 07:32:45 1577 [Note] InnoDB: from the doublewrite buffer...
2016-03-05 07:32:46 1577 [Note] InnoDB: 128 rollback segment(s) are active.
2016-03-05 07:32:46 1577 [Note] InnoDB: Waiting for purge to start
2016-03-05 07:32:46 1577 [Note] InnoDB: 5.6.23 started; log sequence number 1626064
2016-03-05 07:32:46 1577 [Note] Recovering after a crash using mysql-bin
2016-03-05 07:32:46 1577 [Note] Starting crash recovery...
2016-03-05 07:32:46 1577 [Note] Crash recovery finished.
2016-03-05 07:32:47 1577 [Note] Server hostname (bind-address): '*'; port: 3306
2016-03-05 07:32:47 1577 [Note] IPv6 is not available.
2016-03-05 07:32:47 1577 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2016-03-05 07:32:47 1577 [Note] Server socket created on IP: '0.0.0.0'.
2016-03-05 07:32:48 1577 [Note] Event Scheduler: Loaded 0 events
2016-03-05 07:32:48 1577 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.23-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
160305 07:32:51 mysqld_safe Number of processes running now: 0
160305 07:32:51 mysqld_safe mysqld restarted
2016-03-05 07:32:52 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-03-05 07:32:52 1618 [Note] Plugin 'FEDERATED' is disabled.
2016-03-05 07:32:52 7f96395ad720 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2016-03-05 07:32:52 1618 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-03-05 07:32:52 1618 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-05 07:32:52 1618 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-05 07:32:52 1618 [Note] InnoDB: Memory barrier is not used
2016-03-05 07:32:52 1618 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-03-05 07:32:52 1618 [Note] InnoDB: Using CPU crc32 instructions
2016-03-05 07:32:52 1618 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2016-03-05 07:32:52 1618 [Note] InnoDB: Completed initialization of buffer pool
2016-03-05 07:32:52 1618 [Note] InnoDB: Highest supported file format is Barracuda.
2016-03-05 07:32:52 1618 [Note] InnoDB: The log sequence numbers 1626024 and 1626024 in ibdata files do not match the log sequence number 1626074 in the ib_logfiles!
2016-03-05 07:32:52 1618 [Note] InnoDB: Database was not shutdown normally!
2016-03-05 07:32:52 1618 [Note] InnoDB: Starting crash recovery.
2016-03-05 07:32:52 1618 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-03-05 07:32:52 1618 [Note] InnoDB: Restoring possible half-written data pages
2016-03-05 07:32:52 1618 [Note] InnoDB: from the doublewrite buffer...
2016-03-05 07:32:52 1618 [Note] InnoDB: 128 rollback segment(s) are active.
2016-03-05 07:32:52 1618 [Note] InnoDB: Waiting for purge to start
2016-03-05 07:32:52 1618 [Note] InnoDB: 5.6.23 started; log sequence number 1626074
2016-03-05 07:32:52 1618 [Note] Recovering after a crash using mysql-bin
2016-03-05 07:32:52 1618 [Note] Starting crash recovery...
2016-03-05 07:32:52 1618 [Note] Crash recovery finished.
2016-03-05 07:32:52 1618 [Note] Server hostname (bind-address): '*'; port: 3306
2016-03-05 07:32:52 1618 [Note] IPv6 is not available.
2016-03-05 07:32:52 1618 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2016-03-05 07:32:52 1618 [Note] Server socket created on IP: '0.0.0.0'.
2016-03-05 07:32:53 1618 [Note] Event Scheduler: Loaded 0 events
2016-03-05 07:32:53 1618 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.6.23-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

日志没看出什么错误，翻看了论坛之前您回答的类似情况，做了一个定时检测数据库的脚本，今天没有再出现过。
数据库占用内存过高被系统K掉？1G1核就跑一个wordpress，做了静态缓存，缓存文件生成正常，按说数据库的压力不会很大，如果是这种情况，建议做那些优化，彻底解决这个问题

licess

看看mysql挂掉时间段的mysql二进制日志看看有什么有用的信息，应该是有可能有占用内存很大的语句在执行

longlian90

谢谢军哥答复，我太菜，看不了二进制日志，看了也是搞不定，不过“占用内存很大的语句在执行”很有启发，网上找了方法，做了以后这几天没有再出现数据库挂掉的情况，以下是方法：

1) 在 /etc/my.cnf 的 mysqld 下增加下面一句：
innodb_buffer_pool_size = 64M
默认是128，如果访问量不是非常大，可以适当调小数值。

2) 添加swap分区（类似于Windows的虚拟内存，就是当内存不足的时候，把一部分硬盘空间虚拟成内存使用，从而解决内存容量不足的情况，对于1G内存或更小内存的VPS很有必要）
2.1) dd if=/dev/zero of=/swapfile bs=1M count=1024  （这里虚拟内存为1024*1M=1G，我设置的是2048）
2.2) mkswap /swapfile (格式化成swap格式)
2.3) swapon /swapfile  (激活swap，加入到swap分区中)
2.4) 添加这行： /swapfile swap swap defaults 0 0 到 /etc/fstab  (开机自启动新添加的swap分区)

做完上面步骤，服务器重启一次。
注意：修改my.cnf和fstab文件，能熟练使用shell编辑的，直接编辑，像我一样菜的，可以使用sz 文件名 将文件传到本地，用代价编辑器打开修改以后保存，再使用rm -f 文件名 删除服务器文件，最后用rz上传已经修改的文件。

longlian90

论坛中找答案的时候看到问的人很多，但是没有这个解决方法，贴出来共享，有问题或有更好的解决办法还请指正。
以上方法尽适用于内存不足导致的数据库挂掉，表现为数据库莫名出现无法连接，而日志又无错误。