Automatic weekly forum backups

[Ari Rahikkala]

... should be working now. Maybe. Ask me again in a week .

[Nick Foghorn Leghorn]

How?

Just curious, planning on implementing a periodic backup system on another MySQL based system, and wanting to compare notes.

Delphi Augustus used to have a backup on the server, plus have a copy emailed to him. The Republic used to do manual backups whenever the steward (read: I) felt like it, and I think I found a PHP script that runs every time index.php is viewed and does it to a "backup" directory as long as its a certain time after the last one, a variable stored in a text file.

[Ari Rahikkala]

By putting this in my crontab (actually, of a separate user account I set up only for backup duty), after setting up private key authentication:

@ 1w ssh shire@shireroth.kuroshiro.net "mysqldump shire_db --single-transaction -q -u shire_dbuser -p<our database password> | bzip2 -c" > shireroth-"$(date -u)".sql.bz2

[Ari Rahikkala]

Ok, the database dump for last wednesday has been created as it should, and seems to be complete (at a quick glance)... we have automatic off-site backups!

[Nick Foghorn Leghorn]

About goddamned time. Now where did I put my copy of Goldeneye...

[dr-spangle]

It'ld be handy for me to be able to implement something like that for MCS, currently I just back it up every month or so in case Pete loses another domain I could have it all backed up and then temp running somewhere...


But I doubt it would be possible to set something like that up to backup the whole site files, databases and all every week or so without it eating bandwidth of someone.

[Ari Rahikkala]

The latest backup weighs ~30 megabytes. 30 megabytes once a week is very little, but then again on the other hand the server apparently does have rsync, so I could try increasing the efficiency of the backups with that (and documenting how to do it so others can do it)... I'm not sure how much rsync would help with SQL dumps, though, especially since it certainly would *not* help if the dumps were compressed first.