Re: Fresh snapshot

Hey Alvin,

Yes I've looked in to this.  The problem with Percona XtraBackup
incremental backups as I understand it, is that it's all or nothing in
terms of schema.  The incremental backup option is great but it backs up
everything - and you'd therefore lose the ability to easily restore one
schema.  Since our mysql server instance also hosts around 7 other sites
that presents a problem.  We don't want to back up all the schema here as
that would consume roughly 60GB of disk space.  The only way around this I
can think of is to spin up separate mysql server instances - but this then
leads to a shortage of ram.

On Sat, Oct 16, 2021 at 9:07 PM Alvin Starr via Any discussion of
rdiff-backup <[email protected]> wrote:

> Have you thought about using the Percona XtraBackup or one of its
> derivatives?
>
> It has the advantage of being able to take a live database backup
> without having to lock everything for potentially a long time.
> It also has the ability to do incremental backups.
>
> The backup is stored as a /var/lib/mysql file tree which can be used as
> the DB for a different mysql server without having to extract tables
> from a full sql dump.
>
> The downside is that is the backup needs to take place on the system
> with the MySQL server because it directly accesses the files in the DB
> tree.
> Where MySQL dump can be run  from a separate machine.
>
>
> On 2021-10-13 19:03, billy noah wrote:
> > Hello,
> >
> > The older snapshots in my backup scheme take very long to restore as they
> > are database dumps over 15GB and stored on a rotational drive (which is
> > more cost effective as a backup volume).  I noticed that this is
> partially
> > due to the fact the rdiff-backup needs to apply each increment as a
> patch,
> > stepping backwards until the desired state is achieved.
> >
> > Is there a simple way around this?  I wouldn't mind taking up the space
> > needed for a full snapshot if there is some argument that can accomplish
> > this, but I'm trying to avoid the need to build some complex snapshot
> > directory rotation in to my script logic.  Currently I'm simply doing:
> >
> > mysqldump --single-transaction --lock-tables=false my_database >
> > /mnt/mirror/daily/dump/my_database.sql
> > rdiff-backup  --no-eas --no-acls --no-file-statistics
> > /mnt/mirror/daily/dump/ /mnt/mirror/daily/diff/
> > rm /mnt/mirror/daily/dump/*sql
> > rdiff-backup --force --remove-older-than 10D /mnt/mirror/daily/diff/
> >> /dev/null 2>&1
> > We'd like to keep up to 30 days of increments here but the time needed to
> > restore something that old would be a bit absurd.  I'm thinking something
> > like 4 full backups with 6 increments each.
> >
> > I hope this makes sense.  Thanks!
>
> --
> Alvin Starr                   ||   land:  (647)478-6285
> Netvel Inc.                   ||   Cell:  (416)806-0133
> [email protected]              ||
>
>
>