Question

Mysql server stops very frequently

  • Posted January 3, 2015

Recently I installed Zpanel on an Unbuntu 12.04.5 x64 droplet. All gose web, but i have a problem. Mysql stops working and I have no idea why. Can someone help me with this problem? I tried to check if mysql is up and make cronjob to restart it but that does not work. I need to restart manually from command line. That is not a problem, but if i’m not at home, how can i restart the mysql?

Subscribe
Share

I found the solution, seems that our droplets doesnt have enogh memory some times and mysql cant allocate what it needs and crash, so we need to create a swap space to help this. this is what I did: On the command line

root@myserver:~# dd if=/dev/zero of=/swap.dat bs=1024 count=512k
root@myserver:~# mkswap /swap.dat
root@myserver:~# swapon /swap.dat
root@myserver:~# vim /etc/fstab

vim is started to edit, add the following to the end of /etc/fstab file

/swap.dat      none    swap    sw      0       0

Then, edit the mysql config file

root@myserver:~# vim /etc/mysql/my.cnf

And add this under [mysqld]

innodb_buffer_pool_size=64M

Finally reload mysql

root@myserver:~#  service mysqld reload

I found the solution, seems that our droplets doesnt have enogh memory some times and mysql cant allocate what it needs and crash, so we need to create a swap space to help this. this is what I did: On the command line

root@myserver:~# dd if=/dev/zero of=/swap.dat bs=1024 count=512k
root@myserver:~# mkswap /swap.dat
root@myserver:~# swapon /swap.dat
root@myserver:~# vim /etc/fstab

vim is started to edit, add the following to the end of /etc/fstab file

/swap.dat      none    swap    sw      0       0

Then, edit the mysql config file

root@myserver:~# vim /etc/mysql/my.cnf

And add this under [mysqld]

innodb_buffer_pool_size=64M

Finally reload mysql

root@myserver:~#  service mysqld reload

I found the solution, seems that our droplets doesnt have enogh memory some times and mysql cant allocate what it needs and crash, so we need to create a swap space to help this. this is what I did: On the command line

root@myserver:~# dd if=/dev/zero of=/swap.dat bs=1024 count=512k
root@myserver:~# mkswap /swap.dat
root@myserver:~# swapon /swap.dat
root@myserver:~# vim /etc/fstab

vim is started to edit, add the following to the end of /etc/fstab file

/swap.dat      none    swap    sw      0       0

Then, edit the mysql config file

root@myserver:~# vim /etc/mysql/my.cnf

And add this under [mysqld]

innodb_buffer_pool_size=64M

Finally reload mysql

root@myserver:~#  service mysqld reload

I found the solution, seems that our droplets doesnt have enogh memory some times and mysql cant allocate what it needs and crash, so we need to create a swap space to help this. this is what I did: On the command line

root@myserver:~# dd if=/dev/zero of=/swap.dat bs=1024 count=512k
root@myserver:~# mkswap /swap.dat
root@myserver:~# swapon /swap.dat
root@myserver:~# vim /etc/fstab

vim is started to edit, add the following to the end of /etc/fstab file

/swap.dat      none    swap    sw      0       0

Then, edit the mysql config file

root@myserver:~# vim /etc/mysql/my.cnf

And add this under [mysqld]

innodb_buffer_pool_size=64M

Finally reload mysql

root@myserver:~#  service mysqld reload

I found the solution, seems that our droplets doesnt have enogh memory some times and mysql cant allocate what it needs and crash, so we need to create a swap space to help this. this is what I did: On the command line

root@myserver:~# dd if=/dev/zero of=/swap.dat bs=1024 count=512k
root@myserver:~# mkswap /swap.dat
root@myserver:~# swapon /swap.dat
root@myserver:~# vim /etc/fstab

vim is started to edit, add the following to the end of /etc/fstab file

/swap.dat      none    swap    sw      0       0

Then, edit the mysql config file

root@myserver:~# vim /etc/mysql/my.cnf

And add this under [mysqld]

innodb_buffer_pool_size=64M

Finally reload mysql

root@myserver:~#  service mysqld reload

I found the solution, seems that our droplets doesnt have enogh memory some times and mysql cant allocate what it needs and crash, so we need to create a swap space to help this. this is what I did: On the command line

root@myserver:~# dd if=/dev/zero of=/swap.dat bs=1024 count=512k
root@myserver:~# mkswap /swap.dat
root@myserver:~# swapon /swap.dat
root@myserver:~# vim /etc/fstab

vim is started to edit, add the following to the end of /etc/fstab file

/swap.dat      none    swap    sw      0       0

Then, edit the mysql config file

root@myserver:~# vim /etc/mysql/my.cnf

And add this under [mysqld]

innodb_buffer_pool_size=64M

Finally reload mysql

root@myserver:~#  service mysqld reload

I found the solution, seems that our droplets doesnt have enogh memory some times and mysql cant allocate what it needs and crash, so we need to create a swap space to help this. this is what I did: On the command line

root@myserver:~# dd if=/dev/zero of=/swap.dat bs=1024 count=512k
root@myserver:~# mkswap /swap.dat
root@myserver:~# swapon /swap.dat
root@myserver:~# vim /etc/fstab

vim is started to edit, add the following to the end of /etc/fstab file

/swap.dat      none    swap    sw      0       0

Then, edit the mysql config file

root@myserver:~# vim /etc/mysql/my.cnf

And add this under [mysqld]

innodb_buffer_pool_size=64M

Finally reload mysql

root@myserver:~#  service mysqld reload

I found the solution, seems that our droplets doesnt have enogh memory some times and mysql cant allocate what it needs and crash, so we need to create a swap space to help this. this is what I did: On the command line

root@myserver:~# dd if=/dev/zero of=/swap.dat bs=1024 count=512k
root@myserver:~# mkswap /swap.dat
root@myserver:~# swapon /swap.dat
root@myserver:~# vim /etc/fstab

vim is started to edit, add the following to the end of /etc/fstab file

/swap.dat      none    swap    sw      0       0

Then, edit the mysql config file

root@myserver:~# vim /etc/mysql/my.cnf

And add this under [mysqld]

innodb_buffer_pool_size=64M

Finally reload mysql

root@myserver:~#  service mysqld reload

I found the solution, seems that our droplets doesnt have enogh memory some times and mysql cant allocate what it needs and crash, so we need to create a swap space to help this. this is what I did: On the command line

root@myserver:~# dd if=/dev/zero of=/swap.dat bs=1024 count=512k
root@myserver:~# mkswap /swap.dat
root@myserver:~# swapon /swap.dat
root@myserver:~# vim /etc/fstab

vim is started to edit, add the following to the end of /etc/fstab file

/swap.dat      none    swap    sw      0       0

Then, edit the mysql config file

root@myserver:~# vim /etc/mysql/my.cnf

And add this under [mysqld]

innodb_buffer_pool_size=64M

Finally reload mysql

root@myserver:~#  service mysqld reload

I found the solution, seems that our droplets doesnt have enogh memory some times and mysql cant allocate what it needs and crash, so we need to create a swap space to help this. this is what I did: On the command line

root@myserver:~# dd if=/dev/zero of=/swap.dat bs=1024 count=512k
root@myserver:~# mkswap /swap.dat
root@myserver:~# swapon /swap.dat
root@myserver:~# vim /etc/fstab

vim is started to edit, add the following to the end of /etc/fstab file

/swap.dat      none    swap    sw      0       0

Then, edit the mysql config file

root@myserver:~# vim /etc/mysql/my.cnf

And add this under [mysqld]

innodb_buffer_pool_size=64M

Finally reload mysql

root@myserver:~#  service mysqld reload

Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

A better solution is to check why your MySQL needs so much memory, try to optimize its resources before you invest on more, use this to optimize: Percona tools. Also check if your site/application/whatever is running smoothly or is causing heavy traffic peaks to your db.

If your mysql and application/website are running on the same droplet maybe you should consider something bigger but again check if is something you can solve before you invest on resources.

use “top” or better yet get a free newrelic account and monitor your server resources so you can be sure which process are consuming memory.

I found that MySQL gets restarted due to automatic unattended upgrades that seems to be included in the later (I believe) Ubuntu releases.

Can be stopped by adding a blacklist entry to: /etc/apt/apt.conf.d/50unattended-upgrades

This comment has been deleted

The key error here is:

Fatal error: cannot allocate memory for the buffer pool

This shows that MySql has tried to use more memory than is available. As a result, it is crashing. Adding swap file can give you some flexibility when it comes to memory issues. Check out this tutorial for all the information:

This is my error.log after crash:

root@AdultZone:~# tail /var/log/mysql/error.log
InnoDB: mmap(137363456 bytes) failed; errno 12
150106 18:04:02 InnoDB: Completed initialization of buffer pool
150106 18:04:02 InnoDB: Fatal error: cannot allocate memory for the buffer pool
150106 18:04:02 [ERROR] Plugin 'InnoDB' init function returned error.
150106 18:04:02 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150106 18:04:02 [ERROR] Unknown/unsupported storage engine: InnoDB
150106 18:04:02 [ERROR] Aborting

150106 18:04:02 [Note] /usr/sbin/mysqld: Shutdown complete

This is my error.log:

150103 16:15:26 InnoDB: Completed initialization of buffer pool
150103 16:15:26 InnoDB: highest supported file format is Barracuda.
150103 16:15:26  InnoDB: Waiting for the background threads to start
150103 16:15:27 InnoDB: 5.5.40 started; log sequence number 2230560
150103 16:15:27 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
150103 16:15:27 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
150103 16:15:27 [Note] Server socket created on IP: '127.0.0.1'.
150103 16:15:27 [Note] Event Scheduler: Loaded 0 events
150103 16:15:27 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.40-0ubuntu0.12.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)