1

I am exploring possibilities to minimize the size of the database dump (for backing up the WordPress content automatically) of several WordPress instances.

What I am wondering about most of all is if there is any generated data stored in the database which I can safely discard during backups because it will be re-generated by WordPress (or some action that can be manually performed after a backup). For example for phpBB3 it's usually safe to discard search terms and such, because you can easily re-index after disaster recovery ...

Oh, I should probably add that I've read this and didn't deduce any method to minimize dump size from that (aside from --compact and --skip-comments command line switches to mysqldump).

I investigated a little further on my own. Previously I had tried Git and Mercurial to store pretty much only the difference between two snapshots, but these tools aren't very good for that particular purpose. I also tried rdiff-backup, but the results were ... uhm, mediocre.

Anyway, I think I may have found an alternative to shrinking the individual (DB) snapshot size. I have experimented with repackaging the SQL dump files. Numbers:

  • 24 hours worth of (hourly) backups amount to ~1500 MiB for one of the blogs I use this for.
    • Individually compressed (gzip -9) these clock in at ~540 MiB altogether (per 24 hour period)
  • Repackaging the 24 hours worth of backup into a single archive with xz I came up with the following numbers:
    • xz -6: still > 300 MiB
    • xz -7: still > 300 MiB
    • xz -9: depending on the day somewhere between 10 and 30 MiB ... I found the variance surprising, but it may be due to the spam comments, so this needs more experimenting.

Anyway for now - and unless there are some further answers providing more insight it will remain that way - I am going with:

  • repackaging my backups from the last 24 hour period
  • adding the latest files from that last 24 hour period

And I will see whether this scales for me for monthly backups as well or whether I need to thin out the backups prior to repackaging.

On the DB dump shrinking front the --where command line switch to mysqldump seemed promising, but I have yet to be able to express where comment_approved not in ('spam', 'trash') in a way that works on all the tables in the blog-specific database, which makes it kinda impractical to use:

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `wp_commentmeta` WHERE comment_approved not in ('spam', 'trash');': Unknown column 'comment_approved' in 'where clause' (1054)

(referring to the column name like wp_comments.comment_approved doesn't help either)

Improve this question
2
  • If this question is just about how to do an efficient incremental backup of a MySQL database, or how to compress database backups, then this isn't a WordPress question at all. If you don't want to back up spam and trashed comments, just delete them.
    – Tom J Nowell ♦
    Commented Oct 19, 2019 at 23:34
  • @TomJNowell actually, I have to disagree here. Because it matters a lot to know about the subject matter when you do a backup. And no, deletion is not an option, because that would be an automated process. That is: it is perfectly acceptable to lose a comment or two which have been misidentified as spam/trash for backup purposes, but it's not okay to lose them during normal operation. Because the backup will only be used under exceptional circumstances, so losing a handful comments that were flagged (i.e. false positives) is okay under these circumstances. But not otherwise ...
    – 0xC0000022L
    Commented Oct 19, 2019 at 23:39

1 Answer 1

Reset to default
2

The only things you could clear out like this are transients, temporary options in the database. WP already does this on a cron job, and it's unlikely to have a big impact. If you don't mind data loss, revisions might reduce the database size, but that's highly dependent on what you've been doing with the database.

A lot of the generated data WP uses is generated in realtime, e.g. post type data is registered on every page load as it's cheap and fast. Cached data lives in memory and gets erased when the page has finished loading, or it persists in an object cache where it has a limited lifespan but doesn't touch the database.

Eitherway, this is not the approach to take, and is a dead end not worth exploring. You'll get a lot more out of zipping up your backups, or investigating incremental backups instead, but that's a database question not a WP question.

Improve this answer
9
  • Awesome and comprehensive answer. Thanks. I am already compressing them, but the real savings would only occur once you start unpacking the hourly backups of the day and then repackage them ... because of how compression works. For incremental backups it's similar. I have actually tried doing that with both Git and Mercurial, but the savings were not at the level I expected.
    – 0xC0000022L
    Commented Oct 17, 2019 at 8:33
  • I'm sure there are more effective methods of doing incremental database backups than git or hg, that would be a good dev ops question for serverfault :)
    – Tom J Nowell ♦
    Commented Oct 17, 2019 at 12:45
  • there probably are, but sometimes one needs to learn about what's best and that's why I was posing the question in the first place (the documentation is patently bad in that regard).
    – 0xC0000022L
    Commented Oct 19, 2019 at 20:36
  • That's because incremental backups aren't a WP thing, they're a general database administration thing, you'd be better asking at serverfault about how to back up databases efficiently
    – Tom J Nowell ♦
    Commented Oct 19, 2019 at 23:32
  • I think that finding out that the better part of my WP comment tables is junk and how to exclude that is rather WP-specific. It's why I mentioned the phpBB3 analogy, because I knew my way around better there. You're right that "how to wield mysqldump" is probably no question for this site, but that wasn't (and still isn't) my question. And I included my findings for the benefit of anyone trying a similar thing, not because I wanted to somehow "shift" the question into a DB/ops/whatever direction ...
    – 0xC0000022L
    Commented Oct 19, 2019 at 23:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.