A huge headache! But I got it all back!
On Monday July 27, I came home to discover that both of my WordPress sites were offline due to a power incident. Browser connections to the server returned “Error establishing a database connection” for both sites. I also found the MySQL server was not running, and could not be restarted. In addition, apcupsd was reporting “Unacceptable Line Voltage Changes” as the reason for the most recent UPS event. I’d had my first database crash.
Three other machines in the shack were found running, but I could see they had all restarted. This is nothing new in Norwalk CA. Even though each machine has a dedicated UPS, I continue to have service problems with the local utility company. In this case it looks like it was a surge, not a loss of power, and the bounce was enough to corrupt the database.
I’m lucky no hardware was affected this time.
No Problem…I have backups of everything.
My sites use the UpdraftPlus Plugin for creating backups. I create a backup whenever any changes are made to a site. It’s fair to say I have ‘backups up the wazoo’. I can beat this database crash!
So UpdraftPlus did it’s job of creating backups. but I soon realized that you can only restore from a working WP site, that has the Updraft plugin installed. In addition to UpDraft backups, I had a copy of the mysql data directory containing the .ibd files of the individual database tables, so I felt my chances were pretty good I would get everything back.
So, I reinstalled WordPress with a fresh database, using the same database name as the site being restored, installed Updraft, and let rip on the most recent backup…(July 20, 2020)
Updraft Restore Failed (missing files?)
This was a major setback.
My mother used to say, “Don’t keep all your eggs in one basket”, and that was exactly what I had done with the Updraft backups. I had failed to realize the need for mysqldump .sql backup files, and hadn’t created any outside of the UpDraft plugin. Big mistakes. Like it or not I have a ‘learning opportunity’ of how to recover from a WordPress database crash.
Another problem was the InnoDB ibdata1, ib_logfile0, ib_logfile1 files were also corrupt, so I could not repair the Inno database in the usual way. This left me with only the .ibd files for the database tables. If they were not also corrupt, they might just be my last chance at recovery. A somewhat scary position to be in when years of work are on the line. I learned my lesson right there!
Recover mysql database from .ibd files?
What do I know about extracting sql from an InnoDB file? Absolutely nothing!
But my research showed it could be done.
After trying various methods found online, I zeroed in on the info contained in this article. It quickly became apparent that the article was written for folks with a lot more SQL knowledge than I currently possess, so there was much I didn’t understand, and had to research my way through.
Soldiering on, I was able to successfully extract the 12 base WP database tables into table.sql backup files using the DBRecover software. I learned a lot in the process. I was now ready to import the 12 base WP database tables.
They imported just fine, but were not enough to get the site back online. There were a total of 52 .ibd files. 12 ‘base’ files, and 40 .ibd files supporting the installed plugins. The remaining 40 .ibd files would all need to go through the same extraction process. So I figured I’d be at it for a least another week or so.
Not the whole problem…
As research continued and I went on to carry out some other tasks, I discovered there had also been some damage to the filesystem, the damage was not limited to the database crash. So I decided to rebuild the server with a clean installation of Ubuntu Server 20.04.
After copying everything I would need to reinstall, I wiped the old server and did the clean install of Ubuntu 20.04.
My big break!
Once the clean new OS, and fresh install of WordPress were complete, I figured it was worthwhile to try the UpDraft restore one more time. What if something in a corrupted area of the filesystem had been preventing UpDraft from operating properly? So I installed the UpDraft plugin, chose the July 20, 2020 backup, and let it rip…
BOOM! Just like that!
Much to my delight, that was exactly the case! The restore was successful! Holy smoke! I almost couldn’t believe it! Everything restored perfectly! What a relief! OK UpDraft, you have a new brand ambassador! Hi!
LESSONS LEARNED: NEW BACKUP PLAN
- Create a mysqldump ‘database.sql’ backup file after making any site changes.
- Save a copy of the /html directory (or at least the wp-content directory)
- Copy of the most recent ‘UpDraft’ backup.
- Store backups across all available storage devices. NAS, Cloud etc.
Minimum Files Required for a WP Restore
I learned that all one really needs to completely restore a WordPress website, is the database.sql file and a backup copy of the wp-content directory and all its files. Of course, if there are any other files, such as html files that reside in the root /html directory for use outside WP, those must be backed-up as well.
I won’t get my shorts caught up like this again! GUARANTEED!
BONUS RECOVERY 08/22/20
I just got around to recovering my AWStats web traffic logs. Today I was able to restore all my AWStats web traffic files dating from 12/10/17 thru 08/2/20. The current AWS report page is not displaying the traffic stats from 08/02/20 through 08/21/20. I can live with that. The data not lost, I have it, but it’s not worth the effort to try to combine the stats into the current installation (merging log files is not my thing) Before I reloaded the history the site had 4044 visits between 8/2/20 and 8/21/20. So that’s all I’m really missing. NBD.
