Hi,
First mount the snapshot somewhere (eg /mnt/backup).
Now, just off hand you have two options:
Option 1: rsync (look into --link-dest) for speeding this up (what we do).
Option 2: tar into a pipe, something like: tar -C /mnt/backup -zf - |
ssh user@remote dd of=targetfile.tar.gz bs=4096 - other variations using
cat has also been provided, I find dd verall just works better here,
some ssh servers just don't like the "stuff > file" structure, openssh
is fine in this respect.
Either option is going to take a while, mall files if you've got many
thousands of tables you'll probably win out with tar in raw speed if
you've got network capacity, but transfer more data, rsync wins on large
files with small(ish) deltas if your disk IO beats your network
capacity. Best is to test both options and decide what'll work better
for you. On rsync you should ignore the first run as you don't have the
benefit of --link-dest - but in that case tar option will win every day
of the year.
You should probably make sure to use thin pools and thin snaps for LVM
here in order to maintain performance during backup periods.
Thick/traditional snapshots are just too heavy for anything but a light
IO load here. Especially if copying to off-host takes several hours.
Using thin snapshots you can also keep on-host rotational snapshots, say
every hour, and full off-host less frequently, without drastically
impacting on performance (whether you have 1 or 100 snapshots, the IO
overhead remans the same - obviously that's ignoring the physical space
requirements).
Kind regards,
Jaco
On 2026/02/16 17:51, Zoë Kron via discuss wrote:
Hello List,
Hello Gordan
thank you all for your answer.
To conclude a good working solution:
The data dir is on an LVM. Binlog location is set to the same dir but
with ".bin" as subfolder.
There you can do LVM Snapshots.
- innodb_flush_log_at_trx_commit=1
- sync_binlog = 1 (if binlog is enabled)
- sync_master_info = 1 (if slave)
These are the settings that have to be done to have that aswell.
In a future version binlogs will be part automatically.
===
Now for the last puzzle. If I have a e.g. 2 TB MariaDB Folder. I take
a Snapshot with LVM.
This is only local. How can this be synced onto a remote?
If I tar.gz the whole Snapshot I effectively need about the same size
to be able to sync it off.
Which for one or two TB is not the end of the world. For a 100 TB
Datadir this is not good
anymore.
What is the last puzzle-piece? Do you use restic? rsync? rclone?
Incremental sync?
Thank you all for your great and in detail answers. I think a
Master-Slave Replication with
a good Full Backup via Snapshot is sufficient
Zoë
Am 10.02.2026 um 14:49 schrieb Gordan Bobic via discuss:
On Tue, 10 Feb 2026 at 15:34, Kristian Nielsen<[email protected]> wrote:
Gordan Bobic via discuss<[email protected]> writes:
The problem is that unless you are running with:
- innodb_flush_log_at_trx_commit=1
- sync_binlog = 1 (if binlog is enabled)
- sync_master_info = 1 (if slave)
writes can be out of order which causes a problem.
It should be enough to run with innodb_flush_log_at_trx_commit=2 and
sync_binlog=0 to have consistent snapshots. In this case, the data is
written to the filesystem/OS (write(2) system call), but the fsync() is
skipped. And this gives most of the performance benefit.
Right, but what happens in the following scenario:
1) Transaction arrives
2) Transaction gets written to the redo log
3) Transaction gets written to the binlog
4) Before the flush is issued a second later on the innodb log, the
OS' disk I/O scheduler decides to write the binlog before flushing the
redo log to stable storage
5) Power outage / hard kernel crash happens
Without write ordering guarantees, this will lead to an inconsistent
state on disk at a lower layer.
What you are describing would hold true only in cases where the file
system and disk I/O scheduler specifically don't try to optimise
unflushed writes by reordering them (and some do).
_______________________________________________
discuss mailing list [email protected]
To unsubscribe send an email [email protected]
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
