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 <rdiff-backup-users@nongnu.org> 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 > al...@netvel.net || > > >
