Partial Restore of Mysqldump Backup

Just recently I started making backups of my MySQL databases using mysqldump.
I have a small batch/command file running on my Windows based MySQL Server, scheduled to every night backup all of my databases to a file named backup_all_dbs.sql:

“C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump” –all-databases -u username –password=”password” > c:\backup\%DATE%\Backup_MySQL\backup_all_dbs.sql

A few weeks ago I finally upgraded my MySQL server from 4.1 to 5.0. For once I did everything by the book which included backing up all my databases using the backup tool in MySQL Administrator, installing the new server version and then restoring all databases.

Today however I just realized that somehow two tables in one of the databases did not get backed up and hence not restored. Fortunately I still had the nightly backup from the day before upgrading.

So I thought I’d just do a selective restore from the mysqldump file using MySQL Administrator (part if the MySQL GUI Tools) Turns out MySQL Administrator does not read files created with mysqldump.
And worse: a quick Google reveals that mysqldumps cannot be partially restored 🙁

My next couple of hours I spent installing and uninstalling various text editors that could open my 500+MB backup file from mysqldump. I finally found one that could, even though it took a while… (using a Windows Vista machine with 2GB RAM) – NoteTab Light 5.5 (freeware)

Using this I opened the backup file created with mysqldump and sure enough it was a text file containing SQL statements (too bad MySQL Administrator could not open it, hopefully it will in a future version).
I simply deleted the SQL statements for all databases except the one I wanted to partially restore. Then I restored it on my development machine using this command:

“C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe” -uusername -p dbname < c:\backup\2007-12-13\backup_all_dbs.sql

Important note: If you have backed up full databases (not just tables) then it seems the dbname parameter doesn’t matter, it still restores to the original database name – lucky me I did it on my development machine!

After the restore I did a partial backup using MySQL Administrator. Moved that backup file to my production server and did a restore using MySQL Administrator on that machine. Voìla!

So, to sum up:
1) Open your dump file in a large file text editor and delete everything except what you want to restore.
2) On a non-production machine (recommended) do a restore.
3) Make a partial backup using MySQL Administrator from your non-production machine
4) Restore the partial backup using MySQL Administrator on your production server.

Job done, finally I can get back to work!

Of course you can skip step 2 and 3 (and/or just restore the specific tables you want) if you like living dangerously 😉

Happy New Year!