Amazon EC2 MySQL Failed to Start

Question

I'm having issues starting MySQL after it randomly stopped working a few minutes ago. I'm getting this error while trying to connect:

Connect failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

So I tried to restart MySQL (this had worked for me before) and I got this:

Stopping mysqld:                                           [  OK  ]
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]

Here's my error log:

130414 20:03:45 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130414 20:03:45 [Note] Plugin 'FEDERATED' is disabled.
130414 20:03:45 InnoDB: The InnoDB memory heap is disabled
130414 20:03:45 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130414 20:03:45 InnoDB: Compressed tables use zlib 1.2.5
130414 20:03:45 InnoDB: Using Linux native AIO
130414 20:03:45 InnoDB: Initializing buffer pool, size = 128.0M
130414 20:03:45 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
130414 20:03:45  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
130414 20:03:46  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
130414 20:03:46  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: 127 rollback segment(s) active.
InnoDB: Creating foreign key constraint system tables

Can anyone offer some tips? I'm pretty noobish at this server stuff :P

Many thanks!

Answer 1

Restarting the instance helps, but it doesn't resolve it permanently.

This is what I used to resolve it: http://www.prowebdev.us/2012/05/amazon-ec2-linux-micro-swap-space.html

Amazon EC2 Micro Instance Swap Space - Linux

I have a Amazon EC2 Linux Micro instance. Since Micro instances have only 613MB of memory, MySQL crashed every now and then. After a long search about MySQL, Micro Instance and Memory Managment I found out there is no default SWAP space for Micro instance. So if you want to avoid the crash you may need to setup a swap space for your micro instance. Actually performance wise is better to enable swap.

Steps below show how to make a swap space for your Micro instance. I assume you have AWS Account with a Micro instance running.

1.) Run dd if=/dev/zero of=/swapfile bs=1M count=1024
2.) Run mkswap /swapfile
3.) Run swapon /swapfile
4.) Add this line /swapfile swap swap defaults 0 0 to /etc/fstab  

Step 4 is needed if you would like to automatically enable swap file after each reboot.

Some useful command related to SWAP space:

$ swapon -s $ free -k

$ swapoff -a $ swapon -a

References:

http://www.thegeekstuff.com/2010/08/how-to-add-swap-space/

http://cloudstory.in/2012/02/getting-the-best-out-of-amazon-ec2-micro-instances/

http://cloudstory.in/2012/02/adding-swap-space-to-amazon-ec2-linux-micro-instance-to-increase-the-performance/

http://aws.amazon.com/ec2/instance-types/

Answer 2

I also had faced the same challenge this is what I explored, the reason for restarting can be one of the below:

1. There have been updates applied at your EC2 instance, causing the MySql service to stop and not able to restart again.
2. There may be other processes running along which cause a crunch in memory, thus not allowing the MySql to restart.

To Tackle this you can either use one of the below:

1. If your MySql service is going down because of memory issue -- Upgrade your instance.

2. If your mysqld restart command fails, try to restart the httpd service first and then your mysql service. Here are the commands:

   • sudo service httpd restart
   • sudo service mysqld restart

3. If none of the above work restart your EC2 Instance. Not a permanent fix, but help if you want your services to be up and running, and later want to do an RCA of the issue

If you want you can create a script shown below, and execute it after via a cron-job every 5-10 mins depending on your requirement :

#!/bin/bash
dateFormat=`date "+%Y-%m-%d %T"`
log_file_path="/home/ec2-user/mysql_restart_log.dat"

sudo service mysqld status | grep "is running"
if [ $? -ne 0 ]; then
   echo "HTTPD restart attempted ${dateFormat}" >> ${log_file_path}

   sudo service httpd restart
   if [ $? -ne 0 ]; then
     echo "HTTPD restart failed... ${dateFormat}" >> ${log_file_path}
   else
     echo "HTTPD restart success... ${dateFormat}" >> ${log_file_path}
   fi

   echo "MYSQL restart attempted ${dateFormat}" >> ${log_file_path}
   sudo service mysqld restart
   if [ $? -ne 0 ]; then
     echo "MYSQL restart failed... ${dateFormat}" >> ${log_file_path}
   else
     echo "MYSQL restart success... ${dateFormat}" >> ${log_file_path}
   fi
fi

Also found this link helpful: mysql on amazon linux - MySQL Daemon failed to start

Answer 3

I have seen this issue multiple times on multiple AWS servers, main root cause in my cases was lack of space. I deleted some unused files from the server which were taking space and problem was resolved.

This issue of lack of space will also stop your apache from running.

Answer 4

In My case i solved it by clearing some space in server.

I was taking backup of database on daily basis which results in consumption of lots of space. I deleted all those backup and started mysql.

Answer 5

I had this issue too on an EC2 micro instance I was running WordPress on. Turned out that Apache was being a memory hog, which was not allowing mysqld to start/re-start. Once I edited httpd.conf to tame apache's memory use, httpd started with no issues.

Answer 6

I fixed this by restarting my EC2 instance.