Mysql server keeps stopping unexpectedly

Question

Currently running an apache server with wordpress. Keeps bringing up database error connection after a few minutes of doing anything. Attempted to get into mysql through command line. Keep getting error: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) Error log dump: 140124 17:29:01 [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. 140124 17:29:01 [Note] Plugin 'FEDERATED' is disabled. 140124 17:29:01 InnoDB: The InnoDB memory heap is disabled 140124 17:29:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140124 17:29:01 InnoDB: Compressed tables use zlib 1.2.3.4 140124 17:29:01 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 140124 17:29:01 InnoDB: Completed initialization of buffer pool 140124 17:29:01 InnoDB: Fatal error: cannot allocate memory for the buffer pool 140124 17:29:01 [ERROR] Plugin 'InnoDB' init function returned error. 140124 17:29:01 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140124 17:29:01 [ERROR] Unknown/unsupported storage engine: InnoDB 140124 17:29:01 [ERROR] Aborting 140124 17:29:01 [Note] /usr/sbin/mysqld: Shutdown complete 140124 17:29:02 [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. 140124 17:29:02 [Note] Plugin 'FEDERATED' is disabled. 140124 17:29:02 InnoDB: The InnoDB memory heap is disabled 140124 17:29:02 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140124 17:29:02 InnoDB: Compressed tables use zlib 1.2.3.4 140124 17:29:02 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 140124 17:29:02 InnoDB: Completed initialization of buffer pool 140124 17:29:02 InnoDB: Fatal error: cannot allocate memory for the buffer pool 140124 17:29:02 [ERROR] Plugin 'InnoDB' init function returned error. 140124 17:29:02 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140124 17:29:02 [ERROR] Unknown/unsupported storage engine: InnoDB 140124 17:29:02 [ERROR] Aborting 140124 17:29:02 [Note] /usr/sbin/mysqld: Shutdown complete 140124 17:29:50 [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. 140124 17:29:50 [Note] Plugin 'FEDERATED' is disabled. 140124 17:29:50 InnoDB: The InnoDB memory heap is disabled 140124 17:29:50 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140124 17:29:50 InnoDB: Compressed tables use zlib 1.2.3.4 140124 17:29:50 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 140124 17:29:50 InnoDB: Completed initialization of buffer pool 140124 17:29:50 InnoDB: Fatal error: cannot allocate memory for the buffer pool 140124 17:29:50 [ERROR] Plugin 'InnoDB' init function returned error. 140124 17:29:50 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140124 17:29:50 [ERROR] Unknown/unsupported storage engine: InnoDB 140124 17:29:50 [ERROR] Aborting 140124 17:29:50 [Note] /usr/sbin/mysqld: Shutdown complete 140124 17:29:51 [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. 140124 17:29:51 [Note] Plugin 'FEDERATED' is disabled. 140124 17:29:51 InnoDB: The InnoDB memory heap is disabled 140124 17:29:51 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140124 17:29:51 InnoDB: Compressed tables use zlib 1.2.3.4 140124 17:29:51 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 140124 17:29:51 InnoDB: Completed initialization of buffer pool 140124 17:29:51 InnoDB: Fatal error: cannot allocate memory for the buffer pool 140124 17:29:51 [ERROR] Plugin 'InnoDB' init function returned error. 140124 17:29:51 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140124 17:29:51 [ERROR] Unknown/unsupported storage engine: InnoDB 140124 17:29:51 [ERROR] Aborting 140124 17:29:51 [Note] /usr/sbin/mysqld: Shutdown complete 140124 17:29:52 [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. 140124 17:29:52 [Note] Plugin 'FEDERATED' is disabled. 140124 17:29:52 InnoDB: The InnoDB memory heap is disabled 140124 17:29:52 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140124 17:29:52 InnoDB: Compressed tables use zlib 1.2.3.4 140124 17:29:52 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 140124 17:29:52 InnoDB: Completed initialization of buffer pool 140124 17:29:52 InnoDB: Fatal error: cannot allocate memory for the buffer pool 140124 17:29:52 [ERROR] Plugin 'InnoDB' init function returned error. 140124 17:29:52 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140124 17:29:52 [ERROR] Unknown/unsupported storage engine: InnoDB 140124 17:29:52 [ERROR] Aborting 140124 17:29:52 [Note] /usr/sbin/mysqld: Shutdown complete 140124 17:30:16 [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. 140124 17:30:16 [Note] Plugin 'FEDERATED' is disabled. 140124 17:30:16 InnoDB: The InnoDB memory heap is disabled 140124 17:30:16 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140124 17:30:16 InnoDB: Compressed tables use zlib 1.2.3.4 140124 17:30:16 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 140124 17:30:16 InnoDB: Completed initialization of buffer pool 140124 17:30:16 InnoDB: Fatal error: cannot allocate memory for the buffer pool 140124 17:30:16 [ERROR] Plugin 'InnoDB' init function returned error. 140124 17:30:16 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140124 17:30:16 [ERROR] Unknown/unsupported storage engine: InnoDB 140124 17:30:16 [ERROR] Aborting 140124 17:30:16 [Note] /usr/sbin/mysqld: Shutdown complete 140124 17:30:17 [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. 140124 17:30:17 [Note] Plugin 'FEDERATED' is disabled. 140124 17:30:17 InnoDB: The InnoDB memory heap is disabled 140124 17:30:17 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140124 17:30:17 InnoDB: Compressed tables use zlib 1.2.3.4 140124 17:30:17 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 140124 17:30:17 InnoDB: Completed initialization of buffer pool 140124 17:30:17 InnoDB: Fatal error: cannot allocate memory for the buffer pool 140124 17:30:17 [ERROR] Plugin 'InnoDB' init function returned error. 140124 17:30:17 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140124 17:30:17 [ERROR] Unknown/unsupported storage engine: InnoDB 140124 17:30:17 [ERROR] Aborting 140124 17:30:17 [Note] /usr/sbin/mysqld: Shutdown complete 140124 17:30:18 [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. 140124 17:30:18 [Note] Plugin 'FEDERATED' is disabled. 140124 17:30:18 InnoDB: The InnoDB memory heap is disabled 140124 17:30:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140124 17:30:18 InnoDB: Compressed tables use zlib 1.2.3.4 140124 17:30:18 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 140124 17:30:18 InnoDB: Completed initialization of buffer pool 140124 17:30:18 InnoDB: Fatal error: cannot allocate memory for the buffer pool 140124 17:30:18 [ERROR] Plugin 'InnoDB' init function returned error. 140124 17:30:18 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140124 17:30:18 [ERROR] Unknown/unsupported storage engine: InnoDB 140124 17:30:18 [ERROR] Aborting 140124 17:30:18 [Note] /usr/sbin/mysqld: Shutdown complete 140124 17:30:58 [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. 140124 17:30:58 [Note] Plugin 'FEDERATED' is disabled. 140124 17:30:58 InnoDB: The InnoDB memory heap is disabled 140124 17:30:58 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140124 17:30:58 InnoDB: Compressed tables use zlib 1.2.3.4 140124 17:30:58 InnoDB: Initializing buffer pool, size = 128.0M 140124 17:30:58 InnoDB: Completed initialization of buffer pool 140124 17:30:58 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 140124 17:30:58 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 140124 17:30:59 InnoDB: Waiting for the background threads to start 140124 17:31:00 InnoDB: 5.5.34 started; log sequence number 20123319 140124 17:31:00 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306 140124 17:31:00 [Note] - '127.0.0.1' resolves to '127.0.0.1'; 140124 17:31:00 [Note] Server socket created on IP: '127.0.0.1'. 140124 17:31:00 [Note] Event Scheduler: Loaded 0 events 140124 17:31:00 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.34-0ubuntu0.12.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu) 140124 17:35:14 [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. 140124 17:35:14 [Note] Plugin 'FEDERATED' is disabled. 140124 17:35:14 InnoDB: The InnoDB memory heap is disabled 140124 17:35:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140124 17:35:14 InnoDB: Compressed tables use zlib 1.2.3.4 140124 17:35:14 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 140124 17:35:14 InnoDB: Completed initialization of buffer pool 140124 17:35:14 InnoDB: Fatal error: cannot allocate memory for the buffer pool 140124 17:35:14 [ERROR] Plugin 'InnoDB' init function returned error. 140124 17:35:14 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140124 17:35:14 [ERROR] Unknown/unsupported storage engine: InnoDB 140124 17:35:14 [ERROR] Aborting 140124 17:35:14 [Note] /usr/sbin/mysqld: Shutdown complete 140124 17:35:15 [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. 140124 17:35:15 [Note] Plugin 'FEDERATED' is disabled. 140124 17:35:15 InnoDB: The InnoDB memory heap is disabled 140124 17:35:15 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140124 17:35:15 InnoDB: Compressed tables use zlib 1.2.3.4 140124 17:35:15 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137363456 bytes) failed; errno 12 140124 17:35:15 InnoDB: Completed initialization of buffer pool 140124 17:35:15 InnoDB: Fatal error: cannot allocate memory for the buffer pool 140124 17:35:15 [ERROR] Plugin 'InnoDB' init function returned error. 140124 17:35:15 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140124 17:35:15 [ERROR] Unknown/unsupported storage engine: InnoDB 140124 17:35:15 [ERROR] Aborting 140124 17:35:15 [Note] /usr/sbin/mysqld: Shutdown complete root@Nostalgia:/var/log/mysql# tail /var/log/mysql/error.log InnoDB: mmap(137363456 bytes) failed; errno 12 140124 17:35:15 InnoDB: Completed initialization of buffer pool 140124 17:35:15 InnoDB: Fatal error: cannot allocate memory for the buffer pool 140124 17:35:15 [ERROR] Plugin 'InnoDB' init function returned error. 140124 17:35:15 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140124 17:35:15 [ERROR] Unknown/unsupported storage engine: InnoDB 140124 17:35:15 [ERROR] Aborting 140124 17:35:15 [Note] /usr/sbin/mysqld: Shutdown complete root@Nostalgia:/var/log/mysql# mysql -u root -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111) root@Nostalgia:/var/log/mysql# tail /var/log/mysql/error.log InnoDB: mmap(137363456 bytes) failed; errno 12 140124 17:35:15 InnoDB: Completed initialization of buffer pool 140124 17:35:15 InnoDB: Fatal error: cannot allocate memory for the buffer pool 140124 17:35:15 [ERROR] Plugin 'InnoDB' init function returned error. 140124 17:35:15 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140124 17:35:15 [ERROR] Unknown/unsupported storage engine: InnoDB 140124 17:35:15 [ERROR] Aborting 140124 17:35:15 [Note] /usr/sbin/mysqld: Shutdown complete Help would be nice :)

Answer 1

rreboto January 21, 2016

This is happening to me on a WordPress droplet. I’ve done two things, one suggested by another answer here. In the [mysqld] section I’ve added innodb_buffer_pool_size = 16M. Note, this is really small, but this is on a small droplet, so it is what it is. Additionally, when mysql eventually crashed I noticed apache having a LOT of processes. It’s likely hogging up all the system memory. Again, this is for a small site, so I tuned down apache by editing /etc/apache2/mods-enabled/mpm_prefork.conf to this:

<IfModule mpm_prefork_module>
    StartServers        3
    MinSpareServers     3
    MaxSpareServers     5
    MaxRequestWorkers   25
    MaxConnectionsPerChild  0
</IfModule>

Updating innodb_buffer_pool_size stabilized the system for a lot longer than before, but apache eventually hogged all the memory. I’m hoping my problem goes away limiting the number of Apache processes that can exist.

Reply Report

alexprodvig January 23, 2016

Yeah. Upvote for Apache tweaks.
Reply Report
boyang March 24, 2016

Great answer. I have same use case and your solution solved my problem
Reply Report
jjordan July 21, 2016

[deleted]
Reply Report
jeffmcneill August 11, 2017

MaxConnectionsPerChild 0 means that child threads will never restart. This can cause memory leaks. Perhaps a large enough number such as 2048 or 4096.
Reply Report
- rreboto August 25, 2017
  
  yes, you are right, thanks for pointing this out!
  
  my site is very a low-traffic, so i want child processes to cycle quickly. i’m updating my config to use MaxConnectionsPerChild 512. i used chrome’s inspector and my site’s main page makes 45 (!!) requests. with 512 requests per child, each process will render ~10 pages and exit.
  
  for reference, here is a link to the official docs: https://httpd.apache.org/docs/trunk/mod/mpm_common.html#maxconnectionsperchild
  
  Reply Report

Answer 2

alexprodvig January 23, 2016

Yeah. Upvote for Apache tweaks.
Reply Report
boyang March 24, 2016

Great answer. I have same use case and your solution solved my problem
Reply Report
jjordan July 21, 2016

[deleted]
Reply Report
jeffmcneill August 11, 2017

MaxConnectionsPerChild 0 means that child threads will never restart. This can cause memory leaks. Perhaps a large enough number such as 2048 or 4096.
Reply Report
- rreboto August 25, 2017
  
  yes, you are right, thanks for pointing this out!
  
  my site is very a low-traffic, so i want child processes to cycle quickly. i’m updating my config to use MaxConnectionsPerChild 512. i used chrome’s inspector and my site’s main page makes 45 (!!) requests. with 512 requests per child, each process will render ~10 pages and exit.
  
  for reference, here is a link to the official docs: https://httpd.apache.org/docs/trunk/mod/mpm_common.html#maxconnectionsperchild
  
  Reply Report

Answer 3

me122977 January 25, 2014

Can you backup your existing /etc/mysql/my.cnf and replace it with the this one

http://pastie.org/8665237

This is what I'm currently using on 512 droplet to test.

Reply Report

mendonca2709 July 6, 2015

Worked for me!
Reply Report
warpaintmedia December 28, 2015

This also worked for me. Remember to backup your my.cnf file before replacing it with the new one, just incase something is missing or different in the pastebin version.
Reply Report

Answer 4

mendonca2709 July 6, 2015

Worked for me!
Reply Report
warpaintmedia December 28, 2015

This also worked for me. Remember to backup your my.cnf file before replacing it with the new one, just incase something is missing or different in the pastebin version.
Reply Report

Answer 5

ipv315 September 15, 2014

I’ve encountered similar issue, and adding 1GB swap solved my problem. See this:
http://www.webtrafficexchange.com/solved-mysql-crash-fatal-error-cannot-allocate-memory-buffer-pool

Reply Report

Answer 6

rreboto January 21, 2016

After applying the mysql and apache tweaks in my previous post I stressed the system using ab. Below are my results. Long story short, the system was able to successfully process 5000 requests across 20 concurrent connections. The system remained stable and no mysql issues.

I’m running this on a 1G droplet, might be tight, but this config seems like it would work on a 512MB as well.

Hope this helps!

root@KavanaKava:~# time ab -l -n 5000 -c 20 http://example.com/
This is ApacheBench, Version 2.3 <$Revision: 1528965 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/

Benchmarking 45.55.159.118 (be patient)
Completed 500 requests
Completed 1000 requests
Completed 1500 requests
Completed 2000 requests
Completed 2500 requests
Completed 3000 requests
Completed 3500 requests
Completed 4000 requests
Completed 4500 requests
Completed 5000 requests
Finished 5000 requests


Server Software:        Apache/2.4.7
Server Hostname:        example.com
Server Port:            80

Document Path:          /
Document Length:        Variable

Concurrency Level:      20
Time taken for tests:   508.491 seconds
Complete requests:      5000
Failed requests:        0
Total transferred:      115833214 bytes
HTML transferred:       113373214 bytes
Requests per second:    9.83 [#/sec] (mean)
Time per request:       2033.964 [ms] (mean)
Time per request:       101.698 [ms] (mean, across all concurrent requests)
Transfer rate:          222.46 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.3      0       6
Processing:   957 2033 184.2   1966    3450
Waiting:      544  897 142.7    858    2746
Total:        957 2033 184.2   1966    3450

Percentage of the requests served within a certain time (ms)
  50%   1966
  66%   2012
  75%   2082
  80%   2163
  90%   2270
  95%   2421
  98%   2566
  99%   2675
 100%   3450 (longest request)

real    8m28.506s
user    0m0.319s
sys 0m1.003s
root@KavanaKava:~#

Reply Report

bestofmic August 17, 2016

Can you upload your config to pastebin and share?
Reply Report
- rreboto August 25, 2017
  
  I didn’t see this until now, sorry!
  
  https://gist.github.com/rca/4b3553e0797c98ea0a15fdce95e2033a
  
  Reply Report

Answer 7

bestofmic August 17, 2016

Can you upload your config to pastebin and share?
Reply Report
- rreboto August 25, 2017
  
  I didn’t see this until now, sorry!
  
  https://gist.github.com/rca/4b3553e0797c98ea0a15fdce95e2033a
  
  Reply Report

Answer 8

gparent January 24, 2014

140124 17:35:15 InnoDB: Fatal error: cannot allocate memory for the buffer pool

Reply Report

Answer 9

hawk January 24, 2014

Also, try a pastebin, bro

http://pastie.org

Reply Report

Answer 10

danbarrow257 January 24, 2014

So what can I do to actually fix this problem Im having?

Reply Report

Answer 11

me122977 January 24, 2014

I'm assuming this is MySQL 5.5 or 5.6?
You can try on turning of the performance schema in the my.cnf

More info here: http://dev.mysql.com/doc/refman/5.6/en/performance-schema-startup-configuration.html
If this works you'll then need to decide if you need the performance schema and tweak your my.cnf so everything fits in the memory you have

Create or add the following to your /etc/mysql/my.cnf

[mysqld]
performance_schema=off

Reply Report

Answer 12

danbarrow257 January 25, 2014

The full version title is '5.5.34-0ubuntu0.12.04.1;, I have turned the performance_schema off and it has repeated the fault. My next line of inquiry is that does there have to be a minimum amount of memory to run a mysql server. I am currently at 512MB, will I have to increase this?

Reply Report

Answer 13

me122977 January 25, 2014

Can you run the command: free -m

I had mysql + php-fpm + nginx + memcache running on a 512mb droplet as a test and got everything working.

Reply Report

Answer 14

danbarrow257 January 25, 2014

total used free shared buffers cached
Mem: 490 225 265 0 10 103
-/+ buffers/cache: 111 379
Swap: 0 0 0

That is the result from free -m, Anything out of the unordinary?

Reply Report

Answer 15

danbarrow257 January 25, 2014

Copied the pastie file, failed to start upon 'service mysql restart'.

Pastie to original my.cnf = http://pastie.org/8665255 if you need it.

Reply Report

Answer 16

me122977 January 25, 2014

In your config can you move the line
performance_schema=off

to be under the [mysqld] section?
I'm not the greatest at mysql so hopefully someone else will come along with more ideas if that doesn't work.

Also can you check that you don't have any abandoned mysql processes that could be throwing off the startup script

ps -ef | grep mysqld

Reply Report

Answer 17

gparent January 25, 2014

You'll want more memory. The error message clearly says so. You can tune MySQL later.

Reply Report

Answer 18

danbarrow257 January 25, 2014

That is the message produced by the command.

mysql 1933 1 0 Jan24 ? 00:00:14 /usr/sbin/mysqld
root 4265 4196 0 12:09 pts/0 00:00:00 grep --color=auto mysqld

Is that correct? Or expected anyway?

Reply Report

Answer 19

yazeed.aloyoun July 27, 2014

I got this when I used the config file provided for 512MB droplet.

140727 12:12:59 [ERROR] Unknown/unsupported storage engine: InnoDB

Reply Report

yazeed.aloyoun July 27, 2014

I fixed it by using these before restarting mysql to the new config provided in the comments.

rm /var/lib/mysql/iblogfile0
rm /var/lib/mysql/iblogfile1
Reply Report

Answer 20

yazeed.aloyoun July 27, 2014

I fixed it by using these before restarting mysql to the new config provided in the comments.

rm /var/lib/mysql/iblogfile0
rm /var/lib/mysql/iblogfile1
Reply Report

Answer 21

dave328394 October 15, 2014

Just my two cents:

We were/are getting this error periodically when we notice that our server is getting hammered with web/sql requests, usually from a european or asian IP. Dropping the IP in IPTABLES seems to help for long periods of time; at least until the next bot or whatever starts again.

Dave

Reply Report

Answer 22

starduster October 16, 2014

edit my.cnf and set “innodbusesys_malloc =1”

Reply Report

Answer 23

ebuzi July 9, 2015

I am on 2GB RAM with apache+php-fpm+nginx as reverse proxy..running latest mariaDB server on centos 6.6..i get this error…

150709 14:06:20 mysqld_safe mysqld from pid file /var/lib/mysql/mysql.pid ended
150709 14:08:06 mysqld_safe Starting mysqld daemon with databases from /va             r/lib/mysql/
150709 14:08:06 [Note] /usr/sbin/mysqld (mysqld 10.0.19-MariaDB-log) starting as process 5758 ...
150709 14:08:06 [Note] InnoDB: Using mutexes to ref count buffer pool pages
150709 14:08:06 [Note] InnoDB: The InnoDB memory heap is disabled
150709 14:08:06 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
150709 14:08:06 [Note] InnoDB: Memory barrier is not used
150709 14:08:06 [Note] InnoDB: Compressed tables use zlib 1.2.3
150709 14:08:06 [Note] InnoDB: Using Linux native AIO
150709 14:08:06 [Note] InnoDB: Using CPU crc32 instructions
150709 14:08:06 [Note] InnoDB: Initializing buffer pool, size = 624.0M
InnoDB: mmap(669646848 bytes) failed; errno 12
150709 14:08:06 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
150709 14:08:06 [ERROR] Plugin 'InnoDB' init function returned error.
150709 14:08:06 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150709 14:08:06 [ERROR] Failed to initialize plugins.
150709 14:08:06 [ERROR] Aborting
150709 14:08:06 [Note] /usr/sbin/mysqld: Shutdown complete

150709 14:08:06 mysqld_safe mysqld from pid file /var/lib/mysql/mysql.pid ended

Can somebody help and suggest my.cnf configuration file?

my.cnf..
[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

performance_schema=ON
# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
#default-table-type      =innodb
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 1
query-cache-size               = 1000000
max-connections                = 100
thread-cache-size              = 16
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 64M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 256M

# MYISAM ONLYURS#
long_query_time = 1
log-queries-not-using-indexes = 1
#big-tables

#thread_concurrency = 8
myisam_sort_buffer_size = 1G
read_rnd_buffer_size = 2M

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

edited by kamaln7

Reply Report

Answer 24

juliocg112 July 16, 2015

if your on centos to any rhel distro place your new config here : /etc/my.cnf.d/ also a great tool : https://tools.percona.com

Reply Report

Answer 25

danivijay March 14, 2018

I am using Docker, the --innodb-buffer-pool-size param saved my day!

sudo docker run --name test-mysql -e MYSQL_ROOT_PASSWORD=testpass -d mysql --innodb-buffer-pool-size=200000

Reply Report

Question

Mysql server keeps stopping unexpectedly

Leave a comment

Looking for something else?

Sign up for our newsletter

Question

Mysql server keeps stopping unexpectedly

Leave a comment

Looking for something else?