On Fri, Jul 7, 2023 at 9:42 AM Tom Worster <fsb@thefsb.org> wrote:

Hi Daniel,

That part is looking good for now but I gotta do fire drills validate the whole shebang.

There's something almost blog worthy about this. mysqldump started as a handy import export utility and ended up being an exquisite point-in-time logical SQL snapshot tool. Now xtrabackup and mariabackup are so astonishingly fast at restoration/recovery they can't be ignored. But they are terrifying! They operate directly on the datadir. mysqldump is a nice friendly SQL client like any other. Does that make sense? An SQL client can ruin the DB as well as anything else if it has the right permissions (pun!). Hence subtracting permission to write datadir from the mariabackup process feels like a step to parity.

For confidence look at strace -fe trace=openat mariabackup and you'll see the datadir files are opened O_RDONLY. https://www.freedesktop.org/software/systemd/man/systemd.exec.html#BindPaths... It's also possible to make the /var/lib/mysql readonly for this service without affecting mariadbd. Selinux rules can make a tighter constraint, though would impede the copyback functionality when a restore occurs. Though could be enforeced on the backup context - https://www.freedesktop.org/software/systemd/man/systemd.exec.html#SELinuxCo...

Tom

On 7/6/2023 6:52:17 PM, "Daniel Black" <daniel@mariadb.org> wrote:

...

Glad you worked it out.

I did mean to write this onlist a while ago. Got saved a draft message and I forgot about it.

On Thu, Jul 6, 2023 at 10:47 PM Tom Worster <fsb@thefsb.org> wrote:

...

On 7/6/2023 7:14:50 AM, "Daniel Black" <daniel@mariadb.org> wrote:

...

...

I prefer to sandbox systemd services with ProtectSystem=strict. Especially so when User=root.

Should be able to use User=mysql (or whatever the mariadb.service is running as), provided the backup directory is also mysql: owned.

Thanks Daniel.

Yes. I discovered that in subsequent testing. spin And I found that only target-dir and tmpdir need to be read-write. I forget what made me think that wasn't enough.

To run as mariabackup as mysql user but with read-only access to datadir I separated the mariabackup part of the overall hourly backup script into its own service unit: [Unit] Description=mariabackup DBs OnFailure=unit-status-mail@%n.service unit-status-pushover@%n.service [Service] User=mysql Type=oneshot ProtectSystem=strict ReadWritePaths=/pool2/mariabackup /pool2/mariabackup_tmpdir ExecStart=/path/to/backup_maria.sh

This is /path/to/backup_maria.sh. It's on a replica and the test of prev and new size are a check (among others) that the replica isn't stuck. #!/bin/bash prev_backup_size=`du -s /pool2/mariabackup` rm -rf /pool2/mariabackup/* mariabackup --backup --target-dir=/pool2/mariabackup/ --tmpdir=/pool2/mariabackup_tmpdir --user=mariabackup --password=__ mariabackup --prepare --target-dir=/pool2/mariabackup/ --tmpdir=/pool2/mariabackup_tmpdir new_backup_size=`du -s /pool2/mariabackup` if [ "$prev_backup_size" = "$new_backup_size" ] then echo WARNING: Consecutive mariabackup sizes unchanged # FIXME send alerts fi