Individual commands for local (snapshot) and remote transfer (sync)

[lkraav]

I am running a MySQL subvolume backup, and must lock the database for run command duration.

Unfortunately, as database size increases, run is now taking 2 minutes and locked database is becoming increasingly uncomfortable to work with.

It'd be a lot smoother, if I could lock the database only for proposed new snapshot command (estimated to be significantly faster to unlock from), and transfer separately afterwards. Your thoughts?

[masc3d]

I understand your problem, but possible solutions don't integrate well into push / pull concept.
eg possible solutions would be:

• pre / post local snapshot scripts (post script may not execute if there's a connection problem leaving database locked forever)
• split run operation into snapshot (local) and sync (transfer pending snapshots to remote, if possible incrementally) (doesn't support pull at all)

second solution would make most sense despite limitation.

have you tried dedicated btrfs volume for mysql db, manually snapshot with appropriate locking and backup using separate job for that volume?

[lkraav]

have you tried dedicated btrfs volume for mysql db,

I already made a dedicated btrfs db volume from the start, so this operation at least is async from the global fs backup jobs.

manually snapshot with appropriate locking, and backup using separate job for that volume?

I'm not sure I comprehend the details of how to set this up in a sane way. Initial question (if I'm even reading things right): are we talking some sort of snapshot duplication scenario then (my manual snapshots + btrfs-sxbackup-strategy snapshots), or? Having trouble visualizing the outcome.

[masc3d]

I'm not sure I comprehend the details of how to set this up in a sane way.

you're right.
probably most sane way is to split run into snapshot for local operation and sync for transferring to remote.

[masc3d]

however, no time to implement this anytime soon.

did you try replicate for backup? https://dev.mysql.com/doc/refman/8.0/en/replication-solutions-backups.html