Self-hosting a WordPress blog on a Raspberry Pi 1 Model B has its share of challenges. Setting up your LAMP stack from scratch, updating the OS regularly, and a variety of other issues force you to learn the nuances of web server administration, sometimes on the fly. One of those necessary duties is regularly backing up the MySQL database. Unfortunately, I failed miserably at this final and crucial task.
After running an update on my server, I suddenly found that I could no longer login by SSH, forcing me to essentially pull the plug and move it over to my testing area, and reboot with keyboard/monitor attached. Hoping for signs of life, I was initially optimistic as it provided a login prompt. Then all hopes where dashed when said login entered a failed state after entering my password. All kinds of unrecognizable error message threw up on my screen – I was at a loss. With a cry of desperation, I managed to mount the Pi’s SD card onto my development laptop (my ancient Toshiba NB205-N210). I copied over my entire web root directory as well as the raw MySQL database files. I feared for the worst.
Long story short, I had to reload an archived image of my OS (Raspbian Jessie) onto the Raspberry Pi in order to simulate the last operational state, fire up a fresh MySql server, transfer over the raw database files, then run a mysqldump in order to get a compressed single file database backup of my blog. Of course quite a few problems occurred in doing this and I had to make several modifications to MySQL’s config file in order to allow for InnoDB error correction. Finally I was able to make a stable database backup that I could upload onto a new server. I learned how to do this by reading a great blog post “Restore MySQL databases from raw *.frm files” at ailoo.net. Thanks to Mathias Geat’s excellent walk-through, I was finally able to get my blog database into a single, non-corrupted file I could work with.
If that sounds like complete gibberish to you, well you don’t have my sympathies, because all of this was only the initial portion of my own personalized MySQL/WordPress Self-Hosting 101 course, featuring the hellish professors at StackExchange, LinuxQuestions.org, and every web server troubleshooting blog post that mentioned Raspberry Pi web servers. Finding the info I needed to get this blog up and running, in a healthy state, was like looking for a needle in the haystack.
Which brings me to my next project – building a new web server on a more powerful Raspberry Pi 3, using NGINX (a more lightweight alternative to Apache), focusing on speed optimization and security. I couldn’t have built my new server without the help of Henry Cheung over at E-Tinkers. His post series “Hosting WordPress on Raspberry Pi – A complete approach” has to be the definitive guide to setting up the Pi 3 as a server for a WordPress blog. Everything from FastCGI caching to back-end MySQL administration/security – this guide has it all. Of course I had my issues, as no LEMP (Linix, NGINX, MySQL, PHP) stack installation goes entirely as planned. Just as I reached the height of my debugging desperation, I reached out to Henry via the comments on his post, and he was happy to give me even further guidance through private chat. The Raspberry Pi hobbyist community is unbelievably gracious with their time. Finally, the site was up and secure.
It’s a never ending debugging rabbit hole once you dive deep into the real nitty gritty of MySQL, WordPress optimization, and PHP configuration. I figure the last few weeks did wonders for my preparation for the LPIC-2 (Linux Professional Institute Certification Level 2: Linux Engineer) and accelerating my understanding of WordPress (always good for future employment). And… the site is running smoother than ever, quicker page loads, super secure, and a better blog. Enjoy!