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:
mysql -uUSER -pPASSWORD DATABASE
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:
sed ‘s/ \*\/;/\*\//’ ORIGDUMP.sql > NEWDUMP.sql
With that accomplished, I was able to run the mysql command as exampled earlier:
mysql -uUSER -pPASSWORD DATABASE As my voice teacher in undergrad used to tell me “practice doesn’t make perfect, rather
perfect practice makes perfect.”
“Whatever you have learned or received or heard from me, or seen in me–put it into practice” –
Philippians 4:9
One of the free tools I find priceless in helping me backup and restore my sites is a little Telnet/SSH terminal emulator program known as PuTTY: a free Win32 telnet/ssh client.
Written and maintained by Simon Tatham, this tool allows you to login to your host computer … provided your host provider allows such acess.
Why not use something else? Because more and more host providers are switching from providing Telnet access to SSH. Why … because one of the ‘S’s in SSH stands for secure … as in encrypted.
Using this tool, I can get to the linux … from there, I can play with other fun commands I’ll talk about in some very near future posts, like TAR and GREP.