mysql55 spontaneous malfunction [duplicate]

Question

MySQL runs fine for weeks or days, and then suddenly and without any user action, all attempts to connect begin failing. This is /var/log/mysqld.log:

160916 14:42:01 [Note] /usr/libexec/mysql55/mysqld: ready for connections.
Version: '5.5.51'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
160930 03:05:51 mysqld_safe Number of processes running now: 0
160930 03:05:52 mysqld_safe mysqld restarted
160930  3:05:53 [Note] /usr/libexec/mysql55/mysqld (mysqld 5.5.51) starting as process 8961 ...
160930  3:05:53 [Note] Plugin 'FEDERATED' is disabled.
160930  3:05:54 InnoDB: The InnoDB memory heap is disabled
160930  3:05:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160930  3:05:54 InnoDB: Compressed tables use zlib 1.2.8
160930  3:05:54 InnoDB: Using Linux native AIO
160930  3:05:54 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160930  3:05:54 InnoDB: Completed initialization of buffer pool
160930  3:05:54 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160930  3:05:54 [ERROR] Plugin 'InnoDB' init function returned error.
160930  3:05:54 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160930  3:05:54 [ERROR] Unknown/unsupported storage engine: InnoDB
160930  3:05:54 [ERROR] Aborting

160930  3:05:54 [Note] /usr/libexec/mysql55/mysqld: Shutdown complete

160930 03:05:54 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

What causes this, and how can I deal with it?

Answer 1

Did you see this in the log file?

160930  3:05:54 InnoDB: Fatal error: cannot allocate memory for the buffer pool

You do not have enough memory available on your host. Either decrease the amount of memory MySQL is configured to use, or increase the amount of memory the host/VM has.

The database cannot start because there is not enough memory.

Speculating as to why the database restarted in the first place, it may well have been that the operating systems OOM Killer chose the MySQL process as "the one" when the OS was exhausted of memory. See How OOM killer decides which process to kill first? on Unix.SE.

The "Unknown/unsupported storage engine" message appears because MySQL has asked the storage engine (InnoDB) to initialize itself, which it cannot do due to lack of memory.

160930  3:05:54 [ERROR] Plugin 'InnoDB' init function returned error.
160930  3:05:54 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160930  3:05:54 [ERROR] Unknown/unsupported storage engine: InnoDB

Answer 2

As Phil already wrote - You don't have enough memory on Your database server.

Search syslog for Out of memory error and search what processes were running, before OOM killer stopped MySQL. This way You can find culprit responsible for restart. Or check mysql logs for queries that could take more memory than usual.

Depending on what is Your server's purpose (database only, LAMP stack, other services) You can:

• add more RAM (that's the best option, but I'd still follow it with finding process responsible for restarting MySQL)
• change MySQL configuration to decrease RAM usage
• configure OOM killer to give higher priority to MySQL (example taken from dba square's blog:

mysql-server-01 ~ # cat /proc/$(pidof mysqld)/oom_score
24
mysql-server-01 ~ # echo '-20' > /proc/$(pidof mysqld)/oom_score_adj 
mysql-server-01 ~ # cat /proc/$(pidof mysqld)/oom_score 

Other useful resources:

• How OOM killer decides which process to kill first
• MySQL InnoDB crash post mortem