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 to [email protected]
