Best practices for preventing accidental writes on replication replicas during failover
Farah RDC
Hello MaxScale Community,
I am designing a Master-Slave (Primary-Replica) setup using MariaDB MaxScale's readwritesplit router. I have a question regarding best practices for maintaining data consistency during server crashes.
My Current Setup & Behavior:
Normal state: Master handles all writes. Slaves handle all reads.
Slave crashes: MaxScale correctly routes both reads and writes to the Master.
Master crashes: The application can only perform reads because the slaves are in read-only mode.
The Problem:
On our slave nodes, read_only=ON is enabled. However, users or application connections with SUPER privileges can still bypass this setting and write directly to the slave. This causes data drift and split-brain inconsistency.
My Questions:
What is the recommended MaxScale configuration or database setting to completely block SUPER users from writing to a slave node?
Is there a way to automate a safe failover in case we cannot prevent writes to the slave?
Thank you.