Brought my blog back to life

My blog died some time ago. In order to explain why it died, here’s a little background:

I had 2 servers: a home server and a VPS. The VPS hosted my blog (a multi-site wordpress instance) and my IRC bouncer. My Home server hosted my ownCloud (now hosting NextCloud) and a bunch of media services. They are all configured with Docker using the official images from each project. When I started making this set-up, I didn’t know about docker-compose, so I made my own scripts that would run the docker commands needed to update and run my containers, including the back-up scripts for my blog.

And here comes the problem, the back-up scripts: my script got a DB dump (an SQL file) and an snapshot archive of the wordpress instance, when ever it was called. Inisially I made the script to get those 2 items and put them both in a single archive, but at some point (I can’t for the life of me remember why) I removed that functionality, and thus only remained with separate .sql and .tar.gz files. So, later in life, when I wanted to clean up the old backups, I deleted the old archives and db dumps, and forgot that I don’t have a single archive for each backup, so I deleted ALL the DB dumps… Yeah… I know now… Face palm moment…

Some time passed, and I wanted to give up the VPS, and move everything to my home server. I’m using Docker, so that should be pretty straight forward, right? WRONG!!! MySQL went against me. Now I know that sometime when I upgraded the mysql version, the DB got kind of corrupted. In short, the data was there, „SHOW TABLES;” showed all the tables, but „SELECT * FROM

I decided to give up on the recovering the DB and try to get my blog posts from caches around the world. Most of my blog posts were related to KDE, so I asked the KDE Sysadmins if they could filter the KDE planet for my blog posts, and give them to me, so I may rebuild my blog. When I asked for that I also told them what happened. Imagine the relieve I had when after a short conversation, they told me how to fix the broken database, AND IT WORKED!!

Here are the steps I took:

1. From an older (very outdated) SQL dump, loaded into a new database, I made an DB dump with just the „CREATE TABLE” statements
2. Created a new database to do the recovery in
3. In this database, recreate empty tables with the same name. Appended row_format=compact to each „CREATE TABLE” statement. This is only necessary if MySQL is older than 5.6
4. For each table, run: „ALTER TABLE DISCARD TABLESPACE;”
5. Then copy the *.ibd file for that table into place in /var/lib/mysql/…/ as appropriate, making sure permissions are correct
6. Run „ALTER TABLE IMPORT TABLESPACE;”
7. On many tables I got an error that they actually have a dynamic row_format, so I went back and removed the row_format=compact from the „CREATE TABLE” querries for those specific tables (since it’s dynamic by default), and then did the whole thing again

From here, I just made a DB dump and used it, to recover the website DB, since I did all this in a local copy.

And done. All I had to do next is change my DB password, and my blog is back online.

I’d like to give a big shout out and a warm THANK YOU through this post to the KDE Sysadmin: Ben Cooksley, who helped me solve this issue.

• ← Previous Post
• Next Post →