Troubleshooting extremely high MySQL CPU Usage

MySQL CPU usage was spiking upwards of 1000%. Load average was around 50-60. I could not SSH into the machine though, not immediately.

Since I could not actually get into the machine I had it restarted. Just as soon as the machine came back up MySQL CPU usage jumped right back up to 1000%.

Once I was able to finally shut MySQL down I had to discover _why_ the load was so ridiculously high.

MySQL slow query log showed that it was a WordPress plugin that was making a SHIT TON of queries, and MySQL could not keep up. The plugin in question was the WP Security & Audit Log. The plugin logs activity on a WordPress sites, failed logins, successful logins, new user accounts, etc. The queries in question were all related to failed logins. Checking the Apache access logs showed me that there was a massive spike in users attempting to brute force passwords. Thankfully none of them got through.

Gathered a list of offending IPs and blocked them. Also changed all user passwords, just in case. Also disabled the plugin until a resolution can be found and an alternative put in place.

A few things here..

1. MySQL is probably not tuned well, and should be tuned for this particular site.
2. The audit plugin makes too many unnecessary queries and I do not think it is meant to handle the amount of traffic the site was getting. I really should look into that further.
3. Our Nagios configuration does not monitor traffic spikes on websites. If it did I could have caught this _before_ it become so bad.

Securing Git repository from accidental exposure using Chef

It was brought to my attention at the office that a few of our recently launched websites had publicly exposed .git repository information. Unscrupulous users could use the exposed data to pull down the entire commit history, giving them unfiltered access to what is basically the blueprint for the website.

What if someone accidentally uploaded a config file to the repository with sensitive information in it? Or what if the user was able to discover a major security vulnerability in the code that would have otherwise remained “safe”? Scary.

There is definitely too much risk in allowing public access to your .git directory.

I created a Chef recipe called
gitsec.rb to deploy a very easy and quick fix to 20 web servers.

I was not sure the best way to determine if a service exists using Chef, so I just check for the appropriate init script. All of our servers are RHEL or CentOS, so I do not do any detection of platform here. It would be trivial to do so, so I will leave that to you!

You will also want to create a couple of templates:



At first I was returning a 403 status code, but realized that it was still announcing that a file did exist at that location. 404 is better, it does not expose the existence of a file.

This recipe is part of my initial web server setup now.