Lo-fi mysql backups
I wanted to upgrade my ubuntu server to 12.04 “precise pangolin”. So I used backup-manager to make backups of the relevant file system items, and to dump my entire database.
I copied the files back to my desktop computer using scp. All the files were now safely on my computer (or so I thought), and I started reinstalling the new Ubuntu distro.
When time came to get the websites back up online, I wanted to install the sql dump to the mysql server, so I unpacked the dump file, copied it back to the server, and ran
mysql < dumpfile.sql
But nothing happened.
By examining the compressed dump file on my local computer I realized that it was empty, something had gone wrong with the backup. Luckily I had the file system backups, and mysql stores all it’s data in one folder on a linux server, namely /var/lib/mysql. So you can just copy everything from this folder back to the server, and it should work (depending if you have very different versions of mysql).
Only thing you should keep in mind is that the permissions and ownerships should remain the same. So if you have tar files (like me) you can do this in your home folder:
This is not a feasible backup solution, but it is a handy failsafe is something bad happens to your regular mysql backups. You are backing up your filesystem, right??
sudo su # become root
tar xvzf archive.tar.gz var/lib/mysql # uncompress the archive, only the files matching the var/lib/mysql path
mv /var/lib/mysql /var/lib/mysql.old # move the old mysql folder out of the way
mv ~/var/lib/mysql /var/lib/mysql # move the decompressed folder over the old one
rm --rf /var/lib/mysql.old # remove the old folder after checking
service mysql restart # restart mysql