[wp-hackers] Incremental backup and restore

[Roy Schestowitz]

_____/ On Thu 26 Jan 2006 02:09:36 GMT, [Chetan Kunte] wrote : \_____

> Hi all -
>
> My blog with less than a 1000 posts weighs slightly over 0.5MB in a
> gzip format and is over 3MB (bare minimum, without any extra tables
> like stats and other optional stuff). I am worried that sooner or
> later the database is going to be a big fat pig that will refuse to
> fly in one piece (backup and restore). On a lowest common denominator
> level, this could get a lot worse for a lot of users: dial-up, hosts
> that do not offer a command line, etc. As a user myself (with a decent
> DSL connection and host) still dread the idea of squashing the db and
> uploading afresh from the backup for the fear of not completing the
> upload.


You have raised a good point, particularly when it comes to slow connections
and restrictive Web hosts. Nevertheless, many hosts continue to permit the
cron job functionalities while denying command-line access. My host is
among them, so one can set up a cron job to sqldump the database onto the
Web server automatically.

If you fear the idea of restoring a database, then your fear is in vain. We
are all victims of paranoia at times. There were only two situations I can
recall where database restoration went awry or was lossy. With WordPress, a
restoration I once attempted led to an issue with a table that's associated
with PDF creation settings. Settings were easy to restore manually though.
With phpWiki, I once uploaded a database with excess verbage. Everything
else was fine.

Remember that a failed upload can be superseded by a second attempt. If
needed, you can confer somebody else for help, provided you keep the raw
product (sqldump).


> Are you guys thinking of something that could ease this pain and keep
> the pig always in air? :)


If you habitually save your database off-server, then you could just store
the 'diff' between the current database state and yesterday's state. For
example, consider the following cron job:

0 0 * * 0,2,4,6 mysqldump --user <username> --password=<your_pass>
<your_database>  > ~/tmp/blog.dump;

0 0 * * 1,3,5 mysqldump --user <username> --password=<your_pass>
<your_database>  > ~/tmp/blog2.dump;

5 0 * * * diff  ~/tmp/blog.dump ~/tmp/blog2.dump >>
~/public_html/<your_hidden_dir>/recent_additions

This does not obviate the need for full backups, but it keeps you on the
'safe side' more frequently.


> In a layman's terms, I was thinking of an idea: how about if the
> database can be backed-up and restored incrementally like "per year"
> or "per number of posts" in addition to the default option of a full
> backup and restore. I seriously think the "per year" or "per number of
> posts" concept is a good idea (provided of course the core contents of
> a database are not modified in between versions. Do you think I'm
> totally off?


I think that Scott (skippy) might wish to consider this. He's the WP 'backup
man'. Light backups ought to help scalability, yet without potential clutter
(maybe "advanced" tab).


> As always, a huge WordPress fan.
>
> Cheers,
> --
> Chetan, ckunte.com