Guidance on configuring WAL-G for MySQL

[catalogoanatom]

MySQL

MySQL WAL-G Configuration

Guidance on configuring WAL-G for MySQL

Some questions are not obvious from documentation. I'm seeking guidance on configuring WAL-G for MySQL in a master-replica setup with semisynchronous replication for the following objectives:

1. Ensuring guaranteed backups with Point-in-Time Recovery (PITR) in the event of master-to-replica failover.
2. In the event of data loss on the master followed by a fresh reinstall, ensuring all data is preserved in the storage for recovery purposes.

From my understanding, I believe setting WALG_MYSQL_CHECK_GTIDS=1 (and enabling GTID in MySQL itself) is necessary. Should wal-g binlog-push be executed only on the master, possibly scheduled via cron every minute as suggested in the documentation?

Is it necessary to enable log_slave_updates on replica? As I understand it, this would allow the storage to capture any unwritten transactions when the replica takes over as the master in case of master failure.

Are there any other options to enable or best practices to follow in this scenario?

Thank you for your assistance.

[ostinru]

Hi, @catalogoanatom

MySQL configuration
In order to achieve quite reliable PiTR please turn on GTIDs on mysql itself. Also I would recommend to use row-based replication (as more deteministic).
Don't use non-InnoDB engines (e.g. MEMORY is not replication-safe, MyIsam is broken by design).
log_slave_updates = ON - yes, this will help upload transactions after failover.

wal-g configuration
wal-g's option WALG_MYSQL_CHECK_GTIDS is optional - it will only change amount of binlogs uploaded after switchover/failover: it will check binlogg's GTID-set and check whether it uploaded before or not.

other recomendations
Use incremental backups on your own risk - it is not well tested yet.
From time to time try to restore MySQL from backup - just to have practice & check that everything is working.