Borg: what is a proper way to backup Mysql dbs? tables?

[niksfirefly]

I wonder
what is a proper way to backup Mysql dbs with borg?
dump database tables to files then backup with borg?
or dump entire database?

I assume that only some tables will change between
borgbackups so is this more efficient solution i terms of backup space?

regards

[enkore]

If the tables are not small (dumps larger than a couple dozen MB), then it wouldn't make much difference. When the tables are small, with dumps that are at most a few MB, then separate files likely mean better deduplication if entire dump files don't change.

[infectormp]

I make full backup of mysql with bgbackup and then put mysql backup to borg repository. Save a lot of space for me.

[drzraf]

I'm backuping mysql tables (here a wordpress).
I increment sizes were huge and inspected the following (thx to borg diff):

slash-2018-03-14-slash-2018-03-15.txt:  +2.1 MB   -2.1 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-15-slash-2018-03-16.txt:  +3.1 MB   -2.1 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-16-slash-2018-03-17.txt:  +3.2 MB   -3.1 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-17-slash-2018-03-18.txt:  +3.2 MB   -3.2 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-18-slash-2018-03-19.txt:  +3.2 MB   -3.2 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-19-slash-2018-03-20.txt:  +3.2 MB   -3.2 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-20-slash-2018-03-21.txt:  +3.2 MB   -3.2 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-21-slash-2018-03-22.txt:  +3.3 MB   -3.2 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-22-slash-2018-03-23.txt:  +3.5 MB   -3.3 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-23-slash-2018-03-24.txt:  +3.5 MB   -3.5 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-24-slash-2018-03-25.txt:  +3.5 MB   -3.5 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-25-slash-2018-03-26.txt:  +3.5 MB   -3.5 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-26-slash-2018-03-27.txt:  +3.5 MB   -3.5 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-27-slash-2018-03-28.txt:  +3.5 MB   -3.5 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-28-slash-2018-03-29.txt:  +3.5 MB   -3.5 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-29-slash-2018-03-30.txt:  +3.5 MB   -3.5 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-30-slash-2018-03-31.txt:  +3.5 MB   -3.5 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-03-31-slash-2018-04-01.txt:  +3.5 MB   -3.5 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-04-01-slash-2018-04-02.txt:  +3.5 MB   -3.5 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-04-02-slash-2018-04-03.txt:  +3.5 MB   -3.5 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-04-03-slash-2018-04-04.txt:  +3.5 MB   -3.5 MB var/lib/mysql/vhost1/wp_options.MYD
slash-2018-04-04-slash-2018-04-05.txt:  +3.5 MB   -3.5 MB var/lib/mysql/vhost1/wp_options.MYD

(cf note 1)

If that borg diff output line means that the whole file is uploaded/backuped every night, then that'd explain the ~1 or 2GB daily diff (which is a lot for a simple WordPress/LAMP stack on a 20GB SSD drive)
Many other files seems to follow the"replaced-every-night" pattern (see note 2) mainly divided in two categories:

• mysql files (tables data + logfiles)
• logfiles (before/after log rotation/compression).

--
Note 1:
Files generated with: borg list $REPOSITORY | tail -25 | awk '{print $1}' | while read f; do [[ -z "$g" ]] && g="$f" && continue; borg diff "host:backups::$g" "$f" > $g-$f.txt; g=$f; done

Note 2:

var/lib/mysql/ib_logfile0
var/lib/mysql/ibdata1
var/lib/mysql/vhost1/wp_options.MYD
var/lib/mysql/vhost1/wp_postmeta.MYD
var/lib/mysql/vhost1/wp_postmeta.MYI
var/lib/mysql/vhost1/wp_redirection_404.ibd
var/lib/mysql/vhost1/wp_redirection_logs.ibd
var/lib/mysql/vhost2/wp_commentmeta.MYD
var/lib/mysql/vhost2/wp_options.MYD
var/lib/mysql/vhost2/wp_postmeta.MYD
var/lib/mysql/vhost2/wp_postmeta.MYI
var/lib/mysql/vhost2/wp_ratings.MYD
var/log/apache2/vhost1-access.log
var/log/apache2/vhost2-access.log
var/log/mysql/mysql-slow.log
var/log/php5-fpm-vhost1.access.log
var/log/php5-fpm-vhost2.access.log

[mlbarrow]

You should think about backups from the restore perspective. In other words, think about how much pain you want to endure when it comes time to recover the system after an adverse event.

As a general rule, backing up the database files provides the fastest recovery. So, if you have a catastrophic failure, you can just restore the files and start up the database. If you only back up an export of the database, you will have to recreate the tables and reimport the data. If you have a small database, this won't be too bad. On the other hand, a large database with many rows and lots of indexes will take a while to get going. All the while, your application will be down.

Exports aren't all bad, though. If you need to be able to just insert a few deleted rows because someone or something punted them, or replace some rows because someone or something erroneously modified them, that's easier by just yanking them out of the export file.

If you do end up backing up the database files, don't forget to quiesce the database first either by shutting it down, putting it into "backup" mode, or locking the tables from being written. Best practices dictate using filesystem snapshots (ex: LVM) so that the database is only momentarily locked. Then, you can mount the snapshot and let borg suck up the data from there.

[drzraf]

Still,
Here is /var/log/auth.log backuped at 24h interval:

# ls -lh slash-2018-04-18-auth.log slash-2018-04-19-auth.log

-rw-r--r-- 1 root root 3.2M Apr 19 17:32 slash-2018-04-18-auth.log
-rw-r--r-- 1 root root 3.4M Apr 19 17:33 slash-2018-04-19-auth.log

# wc -l slash-2018-04-18-auth.log slash-2018-04-19-auth.log

  29266 slash-2018-04-18-auth.log
  30964 slash-2018-04-19-auth.log
  60230 total

==> Only 1698 lines added.

# diff slash-2018-04-18-auth.log slash-2018-04-19-auth.log | wc -cl

   1699  196323

==> 192 kb change

# borg115 diff $REPOSITORY::slash-2018-04-18 slash-2018-04-19 var/log/auth.log

  +3.5 MB   -3.3 MB var/log/auth.log

==> How to interpret this?

What I would expect:

   +192 kB    (+1 chunk diff)  var/log/auth.log

Aside from information/format. I'd expect a much smaller diff.

Why is it important and related? Because for a couple of tuples added I see things like:
+137.4 MB -137.4 MB var/lib/mysql/db/my_table.ibd
and I would like to know how to interpret it too.

[ThomasWaldmann]

@drzraf Borg's deduplication has a default granularity of about 2MiB, so don't expect it to work out the smallest possible difference on a line-by-line (or even below) basis as tools like diff are capable to do.

[onnerby]

Found this while searching for similar question and have some input for other who might end up here.
When using mysqldump, also add --order-by-primary so that mysqldump will always output in same sort order and therefore will make it more likely for a better diff (and likely smaller increment sizes for borg)