Wordpress recovery01 August 2020
A while back one of the servers I ran went offline following a network outage at the provider, and I am told it was down for the count. The data itself survived but the VPS instance itself would not restart, and likely as not they probably did not have quite the expertise to troubleshoot a VPS that had been running almost continually since late-2012. All other VPS subscriptions I have had for that long have either been cancelled or have required rebuilds following data-loss, so on the whole it did well to last four months shy of eight years.
The website was a Wordpress-based site which meant that most of the data needed to be extracted from a MySQL database, which is not the easiest of tasks given the age of the server build. This article is half-half of a story and a guide of the process of getting the website back up and running.
Fixing up the databaseOriginally I thought about doing a straight transplant of the data and configuration directories, which on Ubuntu are
/etc/mysqlby default, but I immediately noticed that the files contained were different from those within a fresh install of the newer version of MySQL. It was pretty clear that I would have to transplant the data files into a matching version and then do a database dump, and this dump in turn imported into a more recent version of MySQL. In the process it became clear that some of the database meta-data would have to be recreated, but this would be no big issue in the grand scale of things.
Extracing the dataAt least in the older versions of MySQL there was a file
mysql_upgrade_infowithin the data directory that reveals which version of MySQL the database is from, and in this case it was version
5.5.54. So my next move was to do a tarball build of that version (i.e.
mysql-5.5.54.tar.gz), but that build failed due to some implicit pointer conversion that is a no-no these days. It was a sledgehammer to crack a nut but I then installed a fresh copy of Ubuntu
12.04.5 LTSinto a VirtualBox instance and copied in the data & config files. Once setup and having done a quick eye-ball check of the data it was then onto using
mysqldumpto get a copy of the Wordpress database:
mysqldump -u root -p wordpress > wordpress.sql
Originally I tried dumping all the databases including users, but long story short later versions of MySQL expect a different structure for the system tables, so I was getting warnings about incorrect column counts and a possibly corrupt database. It is far less hassle to recreate the required MySQL user and database with their associated privileges then to dump/restore them.
Setting up the new databaseThe first thing to do is specify a new root password for the MySQL database. Quite honestly this step is often a lot harder than it needs to be, as your typical Google search will bring up different ways of doing this, and whether any given command works is dependent on both the version of MySQL and in cases what mode it is operating in. Firstly start up MySQL in safe mode with various database access controls disabled:
$ mysqld_safe --skip-grant-tables
Then in another window login to MySQL and set a new root password:
$ mysql use mysql; UPDATE user SET authentication_string=PASSWORD("r00t") where User='root'; UPDATE user SET password_expired='N' where User='root'; FLUSH PRIVILEGES; exit
password_expires update avoids a nasty situation where you need a password to get back into mysql but the one you just updated is not accepted. Finally you need to cause MySQL to shut down:
mysqladmin -u root -p shutdown
Once MySQL safe mode has shut down startup the full MySQL and go back in to setup the wordpress user and database:
$ mysql -u root -p CREATE USER 'wordpress'@'localhost' IDENTIFIED BY 'My$QL=acc3ss'; CREATE DATABASE wordpress; GRANT ALL PRIVILEGES on wordpress.* TO 'wordpress'@'localhost'; FLUSH PRIVILEGES; exit;
Importing the dataWith the database setup with an empty wordpress the final step is to populate it with the data dumped from the old server. This is the easy bit, although there is the slight pitfall of the dump not including the database name so it needs to be specified on the command-line:
mysql -u root -p wordpress < wordpress.sql
Setting up the webserverSince the previous server ran virtually continuously since it was built back in 2012 it used Lighttpd (a.k.a Lighty) which was in favour with me at the time. Later on I concluded that Lighty was a one-hit-wonder webserver that was only good as the front-end for an application server on a single domain, and as a result abandoned it long ago along with Apache in favour of Nginx pretty much everywhere else. The author's attitude to per-virutalhost error logs was the final straw. However the one use-case Lighty is good for is what a single Wordpress instance is so I decided to stick with it, particularly as it requires very little configuration:
I suspect there are a few directives in here that could still be removed and there may even be one or two that are actually sub-optimal, but I do not consider it worthwhile digging into the documentation. For me anything beyond trivial changes that are easily found via Google would be the trigger for me to finally ditch Lighttpd completely in favour of Nginx. Once the website is setup I copied in the Wordpress files, fixing up a few permissions on the way, and once again the website was operational.