Feel free to skip ahead to the conclusion.
Recently I just set up a new droplet on DigitalOcean for managing client staging sites. Since I already have a droplet setup there for my blog, I figured I’d just create a new droplet from my existing backups.
This worked great for the first 15 minutes until I started messing with MySQL. I chalked up the first issue to a faulty VM creation sequence and proceeded to destroy and recreate the droplet.
Once setup, I went in to start managing my databases like I had done the previous time and received the same error: “ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)”. Disheartened I Googled the issue and after coming up empty, decided to destroy and re-create the droplet a third time only this time I would leave the existing databases and only add new ones.
Success! Or so I thought. That strategy worked well for the first day, but as I went in to add another client site today I again came up with the same error. Now I am not a MySQL wiz and barely know enough to keep my server going. So I was scared. I realized this was an issue that I would have to face.
I eventually landed on this helpful question on StackOverflow. I tried every solution on the page and nothing worked. The MySQL error log had a mostly unhelpful message for me, but it did point me to MySQL’s page on ‘What to Do If MySQL Keeps Crashing‘. I monkeyed around in there for a while and discovered that if I set innodb_force_recovery = 2 in /etc/mysql/my.cnf I could restart MySQL. Whoopee!!
My excitement here was short lived as I continued reading and discovered that database writes are disabled in this mode. I then proceeded to drop every MySQL database that I could suspecting that maybe one of them was corrupted. After dropping them all (including mysql) I tried running sudo service mysql restart again, hoping beyond hope that it would work.
Finally I decided I’d re-install MySQL with the instructions found here. And would you believe… I still got the same error!!! At this point, I am about ready to wipe my droplet and re-provision it from scratch – it would have been faster at this point anyway. But before I did, I tried one last ditch effort.
I finally stumbled upon a post written by someone else with the same experience. The heading says it all: “MySQL Problems – a complete reinstall“. I followed the instructions here not only removing all existing MySQL packages but also removing all configuration files. I then re-installed MySQL and its dependancies using this very helpful article from DigitalOcean.
After restarting php5-fpm everything was back up and working like a dream. Fortunately, I had backed up my databases before dropping them and was back up and running after a few imports.
Frankly, I hope you haven’t read this far because if you have, it probably means you have experienced the same pain that I have. If you have, however, feel free to drop me a line and we can commiserate together.