After last weekend’s disaster, I decided to “practice” a recovery of a huge mysql database of my friends phpBB.
Good thing too. I found a potential problem that could have made an emergency repair a real pain. The fix is easy, when you have the luxury of time to look.
Here’s what I did …
I programmed my friend’s production site to automagically made backup in the wee hours via crontab, which in turn invoked mysqldump with the ‘–opt’ option. The output file was compressed using our buddy tar using the -zcvf options.
About 10 hours later, when the rest of us were awake, using SSH, I logged into another system on a different server that had a mirror of my friends phpBB. I FTP’d the backup. Decompressed using tar -zxvf .
At this point, I was ready to restore the data file. Because I mysqldump’d with the –opt option, it meant that the dump file contained the backup data in SQL languge format. This included deleting tables, then re-creating them, then populating them. The command to restore is simple enough:
Usually, from there it’s VIOLA, we’re done. Not so today. Which is why I’m imploring you to rehearse and practice your restorations from time to time.
Appearently, the MySQL configuration at my friends production site, isn’t exactly the same as it is on the mirror site. Because I had the luxury of time, I was able to poke around until I figured out what had happened.
In this case, ‘C’ like comments were followed with a semi-colon. Which meant MySQL was being instructed to execute a query. Only because the semi-colon was preceede with a comment, there was no query. Boom … an error was raised and the restore failed.
Once I figured out what the problem was, I had to find a way of quickly and easily remedying the situation. Not such an easy task for 4mb of ASCII data ! But since we’re on a linux/unix based system, we do have some powerful text processing tools … in this case, the weapon of choice was SED which is short for ‘stream editor‘.
Using the following command, I ran a search and replace on the entire file in under 2 seconds:
With that accomplished, I was able to run the mysql command as exampled earlier: