Error establishing a database connection AWS EC2 WordPress MYSQL

Views: 18613

Error establishing a database connection while using WordPress installed on AWS EC2

Using the Free tier EC2 server on Amazon with an active wordpress server requires a bit of work, namely that you need a script that will restart the mysqld service. However, it really help to add a swap file, not installed by default with Amazon Linux.

This be caused by some Jerks doing DDOS attack on your site. Be sure to check the apache access_log to see which IP addresses  are trying to access your xmlrpc.php and wp-login.php files. You can block these addresses. We have a preconfigured server that has a pro-active firewall script, fail2ban, already installed. 

You might have seen the dreaded “Error Establishing a database connection” when opening your site.  As a person who keeps the Jewish  Shabbat, of course, this message usually arrived between Sundown Friday night and when the stars come out on Saturday night.

A quick fix is to log into your shell, using the handy dandy key that you should have saved for shell access. At the prompt type:

service mysqld restart

and like magic your site will return to life.

In order to prevent this from happening I have changed the /etc/mycnf – Under Section labeled

[mysqld]

innodb_log_file_size = 220M

This needs to be done after closing and removing the innodb files from the /var/lib/mysql

Additionally and I have added Virtual memory – Swap file

I followed some instruction on the net, but perhaps these will do to set up the swap:

https://www.digitalocean.com/community/tutorials/how-to-configure-virtual-memory-swap-file-on-a-vps

For a while I simply restarted the mysqld service whenever Jetpack was kind enough to inform me that my site was down. using:

service mysqld restart

Clients like stuff to be done automatically, so I set up 2 scripts following the Linux rules for starting a service at startup. You might find a cleaner way to do this.

You see the dreaded message when opening your site, or even worse a client who paid for the site sees it

Error Establishing a database connection

The first script monitors the mysqld log for the string “Fatal error” then restarts the server, sends me a local email that my charming site has been restarted, then it sleeps for 15 seconds in order to let the server actually restart.

I named the script restartmysql.sh for the client. Put it into your /usr/sbin directory

#!/bin/bash
tail -Fn0 /var/log/mysqld.log | \
while read line ; do
echo “$line” | grep “Fatal error”
if [ $? = 0 ]
then
service mysqld restart && mail -s ‘charming mysqld * put in startup *restarted’ steve && sleep 15
fi
done

Then you need to make sure it gets added to the boot start of boot level 3. This file needs to go into the /etc/init.d directory

I called the script:

restartmysql.d

#!/bin/sh
### BEGIN INIT INFO
# Provides: monitor and restart mysql for Fatal Error
# Required-Start: $remote_fs $syslog
# Required-Stop: $remote_fs $syslog
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: Start daemon at boot time
# chkconfig: 345 80 20
# Description: Enable service restart-mysqld-Fatal-Error.

### END INIT INFO

dir=”/usr/sbin/”
cmd=”/usr/sbin/restart-mysql.sh”
user=”root”

name=`basename $0`
pid_file=”/var/run/$name.pid”
stdout_log=”/var/log/$name.log”
stderr_log=”/var/log/$name.err”

get_pid() {
cat “$pid_file”
}

is_running() {
[ -f “$pid_file” ] && ps `get_pid` > /dev/null 2>&1
}

case “$1” in
start)
if is_running; then
echo “Already started”
else
echo “Starting $name”
cd “$dir”
if [ -z “$user” ]; then
sudo $cmd >> “$stdout_log” 2>> “$stderr_log” &
else
sudo -u “$user” $cmd >> “$stdout_log” 2>> “$stderr_log” &
fi
echo $! > “$pid_file”
if ! is_running; then
echo “Unable to start, see $stdout_log and $stderr_log”
exit 1
fi
fi
;;
stop)
if is_running; then
echo -n “Stopping $name..”
kill `get_pid`
pkill tail
pkill restart-mysql.sh
for i in {1..10}
do
if ! is_running; then
break
fi

echo -n “.”
sleep 1
done
echo

if is_running; then
echo “Not stopped; may still be shutting down or shutdown may have failed”
exit 1
else
echo “Stopped”
if [ -f “$pid_file” ]; then
rm “$pid_file”
fi
fi
else
echo “Not running”
fi
;;
restart)
$0 stop
if is_running; then
echo “Unable to stop, will not attempt to start”
exit 1
fi
$0 start
;;
status)
if is_running; then
echo “Running”
else
echo “Stopped”
exit 1
fi
;;
*)
echo “Usage: $0 {start|stop|restart|status}”
exit 1
;;
esac

exit 0

In order to check whether the script will run at startup run

chkconfig –list

to  fire up the script after all this enter

service restartmysql.d start

to check if it’s ok type

service restartmysql.d status

you won’t want to type:

service restartmysql.d stop since this will prevent your server from being protected

If all else fails I will be happy to fix it for you for a reasonable fee. call 516-595-1713 or email me to steve@charming.co.il