My MySQL Server Died

Question

Approximately 1 time per week my MySQL server is shutting down.

This is what the log looks like:

2016-03-18 13:46:25 13166 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)
2016-03-18 13:46:25 13166 [Warning] Buffered warning: Changed limits: table_open_cache: 431 (requested 2000)
2016-03-18 13:46:25 13166 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2016-03-18 13:46:25 13166 [Note] Plugin 'FEDERATED' is disabled.
2016-03-18 13:46:25 13166 [ERROR] Function 'innodb' already exists
2016-03-18 13:46:25 13166 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2016-03-18 13:46:25 13166 [ERROR] Function 'federated' already exists
2016-03-18 13:46:25 13166 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2016-03-18 13:46:25 13166 [ERROR] Function 'blackhole' already exists
2016-03-18 13:46:25 13166 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2016-03-18 13:46:25 13166 [ERROR] Function 'archive' already exists
2016-03-18 13:46:25 13166 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2016-03-18 13:46:25 13166 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-03-18 13:46:25 13166 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-18 13:46:25 13166 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-18 13:46:25 13166 [Note] InnoDB: Memory barrier is not used
2016-03-18 13:46:25 13166 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-03-18 13:46:25 13166 [Note] InnoDB: Using Linux native AIO
2016-03-18 13:46:25 13166 [Note] InnoDB: Using CPU crc32 instructions
2016-03-18 13:46:25 13166 [Note] InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12
2016-03-18 13:46:25 13166 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2016-03-18 13:46:25 13166 [ERROR] Plugin 'InnoDB' init function returned error.
2016-03-18 13:46:25 13166 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-03-18 13:46:25 13166 [ERROR] Unknown/unsupported storage engine: InnoDB
2016-03-18 13:46:25 13166 [ERROR] Aborting
2016-03-18 13:46:25 13166 [Note] Binlog end
2016-03-18 13:46:25 13166 [Note] Shutting down plugin 'partition'
2016-03-18 13:46:25 13166 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-03-18 13:46:25 13166 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2016-03-18 13:46:25 13166 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2016-03-18 13:46:25 13166 [Note] /usr/sbin/mysqld: Shutdown complete

160318 13:46:25 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

What do you think? Maybe there is not enough memory?

This is running on a Virtual Private Server running Ubuntu 15.10 with 512MB of memory.

Now I can't even restart my MySQL server. My my.cnf is empty, What can I do?

Answer 1

Honestly, I don't think mysqld is dying.

The message you posted reminds me of someone or something trying to restart mysqld in the absence of the PID file. When there is no PID file, doing service mysql stop does not work. I wrote about this in Percona-server time out on /etc/init.d/mysql start back on Dec 14, 2012 and MySQL Start/Stop 3 days later.

What's happening is that mysqld is already running and a mysqld start is being issued. This destroys the PID file (See MySQL Start/Stop under the heading PROCESSES (Shutdown)). The messages you are seeing is the second attempt to start mysqld only to find out that the plugins are already loaded.

What makes this more obvious is something I wrote back on April 24, 2014 in Two MySQL Server accessing same database over NFS:

• Only one mysqld instance can attain an exclusive log buffer thread on a system tablespace
• Only one mysqld instance can attain an exclusive insert buffer thread on a system tablespace

It only makes sense that the Buffer Pool cannot be allocated because a Buffer Pool already exists

SUGGESTION

Try shutting down mysqld with

mysqladmin -h127.0.0.1 -P3306 --protocol=tcp -uroot -p shutdown

This will prompt for password. After entering the password, if this returns no output, then mysqld performed a proper shutdown. (BTW this is handled in MySQL 5.7 because you can run the SHUTDOWN command from within a mysql client session). Then, you can run service mysql start with the PID file situation straightened out.

I hope you have the user root@127.0.0.1 or mysqld is smart enough to use root@localhost credentials (See my old post MySQL error: Access denied for user 'a'@'localhost' (using password: YES) on how mysqld authenticates users)