Tuesday 16 July 2013

MySQL Server won't restart

MySQL Server won't restart

Today I went to restart MySQL from my SSH console with the following command:

/etc/init.d/mysql restart

However even though the database server stopped it wouldn't restart.

I tried opening another console and running the status command.

/etc/init.d/mysql status

But this just told me it was stopped and a start command kept failing.

Even when I went into my VirtualMin website that manages my virtual server the service wouldn't restart.

I dug into the services and databases and tried accessing a database from VMIN and saw a message saying the system couldn't retrieve a list of databases. Further digging gave me this error:

can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

Now I knew I had changed some settings in the my.cnf configuration file the other day for performance tuning. So I searched the web and found that I had added a command that wasn't supported by my older version i.e MySQL 5.0.51.

The command in question was:

skip-external-locking

Because Java had crashed and I couldn't access the file to edit it easily.

I quickly ran a:

CHMOD 777 /etc/mysql/my.cnf 

command to allow me to edit the file from FTP and then I swapped the new command with the older one which my version of MySQL supported:

skip-locking

I copied the file back and then hey presto a start command got the server back and running:

/etc/init.d/mysql status

I then made sure to CHMOD the file back so it couldn't be written by the website.

However on checking my website I only got to see the theme but NO articles.

It was a Wordpress site and the problem was probably WP-Super-Cache caching a page without data. I needed to run a REPAIR command on my wp_posts table to ensure all posts were visible again.

This is something I have seen many times before with hard reboots. The system comes back up but no articles appear. I always REPAIR and OPTIMIZE my wp_posts and wp_posts_meta table to rectify this.

This obviously locked the database up, as well as consuming 99% CPU whilst it ran - something which really annoys me, but afterwards the site was working.

So if you have been performance tuning your own MySQL database make sure you are not adding commands that your server doesn't support. A failed restart is a sure sign of unsupported configuration commands.

No comments: