On Tue, Feb 3, 2026 at 1:38 PM Gordan Bobic via discuss <[email protected]> wrote: > > On Tue, 3 Feb 2026 at 13:10, Simon Avery via discuss > <[email protected]> wrote: > > > I am trying to draw a concept of a MariaDB Backup Strategy. > > I am aware of full Backups (via Restic as outlined here: > > https://archive.fosdem.org/2022/schedule/event/mariadb_backup_restic/attachments/slides/5135/export/events/attachments/mariadb_backup_restic/slides/5135/mariabackup_restic.pdf) > > If your database is beyond a certain size, in my experience the > overheads and inefficiencies will add up to the point where the only > workable solution is snapshot based backups. > - mysqldump: backup/restore times make it unusable in reasonable time > once your backups get beyond maybe 100GB on a top of the line current > generation bare metal server optimised for single-thread performance. > - mydumper: massively parallel mysqldump, workable if disk I/O isn't > the bottleneck (~2-3x the writes relative to the size of the data) > - mariabackup: good option, but only usable up to the point where you > can complete the backup before the redo log rolls over > - LVM snapshot based backups: optimal option, provided you have enough > snapshot space reserved to complete the backup > - ZFS snapshot based backups: none of the limitations of any of the > above - but you have to run on ZFS. As a bonus with ZFS backup target > you can have huper-efficient incrementals as well.
When it comes to this limitation of mariabackup a.k.a. mariadb-backup, I am working on implementing a format option innodb_log_archive=ON (MDEV-37949), which should facilitate much more efficient backup, because InnoDB would not overwrite any log records. The idea is that you could efficiently clone the log files that are between the latest checkpoint (of a previous incremental backup, or as of the current full backup) and the end of the backup. I am also planning a server-side backup that would either clone the files to a mounted file system, or make use of a script that would invoke a file system snapshot. In https://jira.mariadb.org/browse/MDEV-38362 I posted some notes about this. I imagine that when a file system snapshot is supported, one would invoke a new BACKUP SERVER statement, invoking a script that quickly creates the file system snapshot, while blocking some operations to make it consistent. (For example, DROP DATABASE or some DDL on partitioned tables are not atomic.) There are also file systems such as XFS that do not support snapshot but do support block cloning, a.k.a. copy-on-write files. Making use of that, we could rather quickly copy the entire data directory to a new backup directory. On a file system that lacks block cloning and snapshots, we must copy files, but the copying can make use of Linux system calls. Once the file system snapshot or local backup has been created, the BACKUP SERVER statement would finish and normal operation on the server could resume. After that, you would then invoke another command to transfer the backup to a remote location. By the "ZFS backup target", are you referring to the "zfs send" command? I also found some documentation about "btrfs send" and the Microsoft Windows "refsutil streamsnapshot". These would stream a file system snapshot to another system. I have also been thinking of implementing a live streaming backup in the tar format. Perhaps, for performance reasons, there should be an option to create multiple streams in parallel. I am yet to experiment with this. > For ZFS servers with object storage targets, you don't get > hyper-efficient incrementals, but it is usually reasonably workable, > as long as you have enough disk I/O and network bandwidth. > We use this tool that we developed in-house, as a counterpart to sanoid: > https://github.com/shatteredsilicon/backoid How would you use this tool with ZFS? Could it also be used with btrfs, or to copy any set of files in any file system? > > I am searching through the net and could not find a good solution for > > Binlogs. Postgres f.e. has WAL Archiving to a remote Storage or onto a > > Master. > > How can I do something like this with MariaDB (without MaxScale Binlog > > Router). > > You can set log_slave_updates on the slave, which will make the slave > generate binlogs. The file/offset coordinates won't match the master, > but this isn't a problem if you use GTID. With binlog_storage_engine=innodb (https://jira.mariadb.org/browse/MDEV-34705) and a future development of innodb_log_archive=ON, it should eventually be possible to write only one copy of the binlog information and have something similar to the Postgres WAL archiving. Best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
