On Fri, Jul 7, 2023 at 9:42 AM Tom Worster <[email protected]> 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#SELinuxContext= > > Tom > > On 7/6/2023 6:52:17 PM, "Daniel Black" <[email protected]> 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 <[email protected]> wrote: > >> > >> On 7/6/2023 7:14:50 AM, "Daniel Black" <[email protected]> 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 > >> > _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
