MySQL replication breaks when taking backups.

[Reio Remma]

Since february I've been encountering issues with replication staying in sync.

I've a long running script that I run once a week that updates data in the database. It has taken a little longer to run lately and it has happened that taking my daily backups with either ZFS snapshots or mysqldump are overlapping with the script being run and this is resulting in some events not reaching my 2 slaves.

I've narrowed it down to using --single-transaction in mysqldump and I suspect the locks used before ZFS snapshots (LOCK INSTANCE FOR BACKUP; FLUSH TABLES WITH READ LOCK;)

I see no errors in the client or master, but both slaves are missing a few identical row updates from the time when --single-transaction or instance locking happens. As if some events were not written to the binary log when locking happened.

MySQL Community Server 8.0.33 running on Almalinux 8 & 9, in a LXC container on Proxmox with ZFS.

I've had the same setup running without issues for a few years and the problem appeared when the script and backups overlapped.

Any ideas?