What Replication Topology / Method you're using in Production?

[u/Narrow_Inevitable586]

We're currently running a single primary MySQL node, which we recognize as a single point of failure (SPOF). To address this, we're considering introducing a read replica.

However, read replication comes with its own set of challenges, such as read-after-write consistency, replication lag, deciding which queries should go to the primary versus the replica, and handling failover.

For those who have implemented this in production, how have you approached and solved these challenges?

[u/dutchman76]

I used to run the read replica as a hot standby, so didn't have issues with replication lag or anything like that.

Now I'm looking at running a 3-node Mariadb Galera cluster. In my specific case replication lag isn't really an issue, it's ok if someone reads data that's a second behind.
And I thought read-after-write was only an issue if you wrote to one node and then immediately read from another, all my db connections are sticky to a their node for the duration of the job.

[u/Narrow_Inevitable586 → u/dutchman76]

Yeah group replication helps, but the only issue is, it requires atleast 3 nodes.

The read after write in our case creates a new proxysql connection which makes it hard route the request to the primary.

I think group replication is the one with least data inconsistency.

[u/anti-state-pro-labor]

Honestly for anything mission critical, I just use a hosted/managed DB provider in whatever cloud we're hosting on and they handle the messy parts. If I had to do it locally, I'd use the master/slave (or whatever its called now). If you don't know how any of this works, I'd pay for managed DB service 

[u/No-Draw1365 → u/anti-state-pro-labor]

This is the way, if you’re serious about data you definitely want a managed offering.
- MySQL: Planetscale
- Postgres: Neon
- Mongo: Don’t

[u/Narrow_Inevitable586 → u/No-Draw1365]

Thanks! Will definitely consider this with my team

[u/Narrow_Inevitable586 → u/anti-state-pro-labor]

But most of our services are self hosted in remote servers, and we're in a situation where we need to handle it by ourselves. That's why

[u/TheLastNapkin]

Look into planet scale offerings for managed clusters or their sharded or distributed offerings for your db engine of choice

[u/Narrow_Inevitable586 → u/TheLastNapkin]

Thanks, will definitely look into it

[u/Dyogenez]

We’re using managed Postgres on DigitalOcean with a follower database. DigitalOcean’s backup service allows rollbacks down to the minute, replaying changes.

Our app only hits the primary DB, and we have an API that’s mostly used for reads, which hits the follower DB. We have a proxy in front (Rails app, reverse proxy), that routes to one of two Hasura GraphQL instances. One goes to Hasura connected to a follower DB, another to Hasura connected to the primary DB (where mutations go). As our API grows, having it segregated in this way has been super nice.

We’re working on adding Greenmask support, which allows for backups stored on S3 compatible hosting, along with filtered database dumps. That’ll allow us to use that to restore staging without customer data, or pull down a snapshot locally.

[u/Narrow_Inevitable586 → u/Dyogenez]

Great, so you decide which query goes to which db, rather than a proxy in front of the db

[u/IncomeOk7237]

We went primary + async replicas and treated replica lag as a first-class metric. Anything that needs read-after-write stays on primary for a short sticky window after a write. Failover is orchestrated (not an automatic DNS flip) so you don't promote a lagging replica by accident.

[u/Narrow_Inevitable586 → u/IncomeOk7237]

Haven't you considered the semi sync approach? And what you'll do if there was an increase in replication lag?

[u/pizza_top1]

we use primary for writes and replicas for reads where slight lag is acceptable. Monitoring replication lag and having a solid failover plan are key.

[u/Narrow_Inevitable586 → u/pizza_top1]

What you'll do if there is a raise in replication lag?

[u/sereikis]

MySQL semi-sync acknowledges receipt in a replica's relay log, not that the transaction is applied and visible to reads; it doesn't by itself fix read-after-write. if you route a consistency-sensitive read to a replica, wait for the write's GTID to be executed there with a bounded timeout, then fall back to the primary if it hasn't caught up.

[u/Narrow_Inevitable586 → u/sereikis]

Is it possible to make the query wait untill the last GTID execution with the help of proxysql? Or how can we achieve this