TROUBLE SHOOTING TIPS TO FIGURE OUT WHY WORDPRESS AND MY SQL KEEP CRASHING
So you are using Digital Ocean (this could apply to any VPS service) to host your WordPress blog and every few days your site mysteriously goes down. Don’t worry you’re not alone, there are hundreds if not thousands of bloggers sitting in the same seat that you are in right now. Take a deep breath, grab some coffee or tea, and get ready to embark on a troubleshooting journey (which hopefully will be a short one :p ). I also was experiencing this problem at one point and after finding the solution I felt really silly because it was a simple fix. In this article I am going to talk about some of the actions I took to try and resolve the problem, what actually worked for me, and I will point you towards some great resources to help you out.
WHAT I TRIED
At first I figured that my server was simply just running out of memory since I was using a cheaper / lower end hosting plan. Now if that was actually the case creating a large swap file and calling it a day should have been the end of the story, it was not. I allocated a significant amount of space for swap (using this wonderful tutorial found here) but only a few hours later after doing so my site went down again.
My next step in troubleshooting was to disable all of my plug-ins and reactive them one at a time to see if any were causing issues. Even with all of them disabled MySQL crashed only thirty minutes later so I was pretty sure that my plug-ins were not the culprits. I also attempted to roll back my version of WordPress to see if any recent updates were causing any problems. I thought this was the solution at first since WP stayed up for three whole days after reverting to an earlier version but sure enough on the third night the site went down again.
WHAT ACTUALLY WORKED FOR ME
I was about to give up and just upgrade to a higher end VPS but then I realized I am a dirt poor college student and could not afford to do that. So instead I embarked on a night long investigation and came to several realizations. First off, I noticed that my CPU usage and Bandwidth usage had been abnormally high for the following few weeks. I thought this was just a result of my site’s increasing traffic but I soon realized that it was not. After checking my apache2 access logs I was greeted with a record that was filled with the same IP address accessing xmlrpc.php, this was a brute force attack on xmlrpc.php which I thought Jetpack took care of but apparently not in this case.
Check your apache2 log in Ubuntu 14.04:
Preventing an attack like this in Ubuntu is incredibly simple. Using IP tables we can block the IP address from accessing our site.
Ban the offending IP address:
iptables -A INPUT -s 188.8.131.52 -j DROP
In the place of the IP address above, yes that was the one attacking my site, place the IP address of the attacker.
To view banned IPs:
iptables -L INPUT -v -n
This solution completely solved my problem, MySQL and WordPress have not crashed since.
I hope this gave you some insight into solving your problem. The goal of this post was to consolidate everything I tried into one article to make the troubleshooting and research process much easier for who ever is reading this. Thanks for reading!