MySQL Forums
Forum List  »  General

MySQL replication breaks when taking backups.
Posted by: Reio Remma
Date: May 31, 2023 03:40AM

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?

Options: ReplyQuote

Written By
MySQL replication breaks when taking backups.
May 31, 2023 03:40AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.