How to fix Mysql server stops very frequently

I have had a problem with my WordPress site, the connection with MySql has gone away and to get it back I had to restart the server. This been an annoying issue as I do not have that high traffic on the site.

I tried to check if MySQL is up and make the cronjob to restart it but that does not work that well. I have been looking at the log, using.

tail /var/log/mysql/error.log

In the log, I looked for shutdown messages or errors. I identified that the MySQL connection crashed when the server run out of memory, so why did it run out of memory. I started to optimize Mysql resources by using the optimization tool from Percona tools.

I also checked when my WordPress is running smoothly or what is causing heavy traffic peaks to your DB. One way is to increase the size of the server, I did not have that much traffic, so I installed the New Relic tool on my server to monitor the server resources to find out which process are consuming memory.

I found out that the server did not have enough memory and MySQL can’t allocate what it needs so it crashes, what I did was to add swap space to help this.

On the command line

mysql@myserver:~# dd if=/dev/zero of=/swap.dat bs=1024 count=512k
mysql@myserver:~# mkswap /swap.dat
mysql@myserver:~# swapon /swap.dat
mysql@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

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

And add this under [mysqld]

innodb_buffer_pool_size=64M

Finally, reload MySQL

mysql@myserver:~#  service mysqld reload

From the New Relic account, I also noticed that the PHP was taken up memory, so I upgraded my server version of PHP from 5.5 to version 7. That helped my issue and after these changes to the server, I have not had an issue with MySql connection going away.