EC2 MySQL crashing continuously

Question

I have an EC2 instance set on the x64 amazon linux ami.

I am using PHP & Wordpress with W3 total cache & php-apc backed by MySQL to test a blog which can handle a decent number of connections relatively cheaply.

However, my mysql keeps crashing out.

Taken from the /var/log/mysqld.log

120912  8:44:24 InnoDB: Completed initialization of buffer pool
120912  8:44:24 InnoDB: Fatal error: cannot allocate memory for the buffer pool
120912  8:44:24 [ERROR] Plugin 'InnoDB' init function returned error.
120912  8:44:24 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
120912  8:44:24 [ERROR] Unknown/unsupported storage engine: InnoDB

Anyone know the reason this could be happening?

Current memory usage (below)

[root@ip-obscure mysql]# free -m
             total       used       free     shared    buffers     cached
Mem:           594        363        230          0          3         67
-/+ buffers/cache:        293        301
Swap:            0          0          0

Answer 1

Be wary of concluding you do not have enough memory , yes that is the error you see but that is also a symptom not the cause. Wait before paying for a bigger instance, the problem will only go away for some time until the memory fills up again.

Be wary of creating SWAP files, again you are only bandaging the symptoms.

Be wary too of changing config settings (and limiting the performance of your apache or mysql) which has been working just fine for some time but only now suddenly the server will not stay up for long at all.

Think how could that really be settings? if there was a badly optimised setting or a memory leak in PHP it would have failed consistently after the same sort of time period. So assuming you have not just recently installed new modules and have had had a pretty static environment for some time it is unlikely to be a memory leak or settings. Obviously if you have just been installing new modules disabling them should always be a first step

Be wary of splitting the database to another server, this will not solve the problem in the same way as buying a bigger server doesnt solve the problem. Yes each function will get more memory initially but then.....

Be wary of migrating from apache to another http server such as NginX, a drastic step, which may solve the problem..... but for the wrong reasons

I was guilty of most of these and raised many a false hope, until I looked at the apache /var/log/httpd/ACCESS_LOG file and saw that my site was being hit several times a second from the same IP looking for a file called XMLRPC.PHP, some kind of DDOS attack well known in Wordpress circles.

example access_log entry ....

191.96.249.80 - - [21/Nov/2016:20:27:53 +0000] "POST /xmlrpc.php HTTP/1.0" 200 370

each time it is received apache tries to instantiate a child process to service that request. Before long you run out of memory and apache starts failing to fork a new process and mysql gives up trying to allocate memory space to the buffer pool. You basically run out of memory and all these requests grind your server to a halt.

to solve ths I changed the .htaccess file to deny access to the file from that IP, and my server returned to normal operation immediatly.

example .htaccess

<Files xmlrpc.php>

order allow,deny

deny from 191.96.249.80

allow from all

</Files>"

Hope my hard won findings help someone else!

Obviously if your access log is not showing a DDOS effect, it might be something else, try all the above! ;-) but I have now seen several a wordpress/apache site affliicted with this attack. ... same IP too! Its a pity Amazon AWS does not allow blacklists in its security groups. [sigh]

Answer 2

I imagine your instance is lacking memory needed to do what you are wanting to do.

Have you considered using RDS for MySQL? That is really the preferred methodology in the AWS world (at least for DB's taht don't require a high degree of custom configuration) and will give you much better performance than running MySQL on EBS storage (which I assume you are doing as otherwise you have no way of persisting your DB content).

Answer 3

Well, it's December 2016 and apparently this is still around.

A client reported that one of his sites (not managed by my company) was down and asked for support. When we started looking for the problem it became apparent that his webserver was being DDoS'ed due to this vulnerability.

The mitigation procedures are pretty much covered in the other answers, so I just want to add my 2 cents: besides .htaccess rules, you can also block the IPs originating the requests with iptables. See here for a quick overview. Basically what you get from this is:

1. Apache (or whatever you're using) doesn't consume overhead resources replying with 403 to the attack origin or even log them (saving lots of disk space) - your machine will simply ignore the requests;

2. If you realise that the requests are originating from the same subnet, you can block the requests per subnet origin, hitting many of the compromised machines attacking you all at the same time.

This obviously has the pitfall of not verifying the validity of the requests being made, but that is also a factor in the other solutions, as well as xmlrpc.php still being unaccessible. Also, whatever file requested from those origins will be rejected.

Basically, I grep'ed requests to xmlrpc.php being logged by Apache and counted which were the most offending ones:

cat  /var/log/apache2/access.log | grep xmlrpc.php | awk '{print $1;}' | sort -n | uniq -c | sort -nr | head -20

This will print out a sorted list of the top 20 most offending IPs. I noticed that in my top 5 most offending ones, 4 came from the same subnet.

Then, after you've identified which ones you want to block, assuming they have an IP like 123.123.123.123:

sudo iptables -A INPUT -s 123.123.123.123 -j DROP

Or, if you want to target a certain subnet:

sudo iptables -A INPUT -s 123.123.123.123/24 -j DROP

The /24 indicates you're targeting 123.123.123.XXX where XXX can be whatever combination. Repeat this procedure as much as you see fit. I ended blocking 90%+ of the requests with just a couple of rules, but YMMV.

Also, note that this will stop logging those offending requests unless you remove the iptables rules you set up above.

Hope this helps!

Answer 4

The error says it all - there's not enough memory to hold the pool.

If this is a test instance subject to small loads, then you may try installing the small sample cnf

http://fts.ifac.cnr.it/cgi-bin/dwww/usr/share/doc/mysql-server-5.0/examples/my-small.cnf

(the official one is somewhere on the MySQL site and I can't seem to find it).

Otherwise, for production purposes, I'd seriously consider Mike Brant's solution; otherwise, you need a larger Amazon instance.

Answer 5

I fixed it by tuning apache - it was using up all the memory by trying to launch too many spare servers:

#MinSpareServers    5
#MaxSpareServers   20
MinSpareServers    2
MaxSpareServers   4

Of course, you need a certain amount to run your site, but mine is low traffic.

Answer 6

I had a similar problem with my t2.small EC2 instance. I would login and restart mysql and the website would be ok for about 5 mins before the familiar database error message would reappear.

This was running a Wordpress website with an elastic IP. After following these steps I did not loose any data. I understand that this was due to the EBS storage on this instance.

Steps:

1. Login to AWS console

2. Go to EC2 and select instance

3. Actions -> Instance State -> Stop (took about 3 mins to stop)

4. Actions -> Instance Settings -> Change Instance Type (I went from t2.small to t2.medium)

5. Actions -> Instance State -> Start

The whole process took hardly any time, once the instance had started I reloaded the website and everything was back to normal.

There is obviously a price consideration in upscaling your instance.

More info: http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-instance-resize.html

Answer 7

Following on from answer by Binthere above, the MySQL server crashing on my EC2 instance was also due to a DDOS attack and not to do with the micro instance running out of memory (which is also a very likely possibility). Based on some great links I found online, here are the steps I took to quickly check the issue.

1 - SSH into the instance

2 - sudo tail -200 /var/log/httpd/access_log

Then I saw a lot of POST request from 1 IP address on a Wordpress XMLRPC file. This is was attack.

3 - Screenshot of this to keep for a report to Amazon abuse team if they contact me (they make that move first I have found out after phoning Amazon)

4 - sudo cp /var/lock/subsys/mysqld /root/mysqld

5 - sudo rm /var/lock/subsys/mysqld

6 - sudo service httpd stop

7 - sudo service mysqld restart

8 - Now before restarting the webserver, I put in some changes to a .htaccess file in the web root at /var/www/html (These are specific to my attack issue) sudo nano /var/www/html.htaccess

order allow,deny deny from allow from all

9 - sudo service httpd start

10 - Breath a sign of relief (in my case anyway!)

Hope this is of help to anyone :)