Sales dashboard stuck on numbers from 03:12
Your task
It is 09:15. The shop's MySQL 8.0 primary, db-primary, has 2 replicas. db-replica-1 serves the sales dashboard, and db-replica-2 serves the API's reads. The sales team pages you:
DashboardStale: newest order on the sales dashboard is 6 h 3 min old (threshold 30 min)
What you see:
- The dashboard's newest order was placed at 03:12.
db-primaryhas orders up to the minute, anddb-replica-2is less than 1 second behind. SHOW REPLICA STATUSondb-replica-1showsReplica_IO_Running: Yes,Replica_SQL_Running: NoandSeconds_Behind_Source: NULL. The replication delay alert, which fires above 60 seconds, stayed quiet all night.- Its error log, at 03:12, has
Could not execute Write_rows event on table shop.refunds; Duplicate entry '88100' for key 'refunds.PRIMARY'. - At 03:10 a support engineer ran a script that inserted 40 refund records the app had failed to save. They took its database settings from the dashboard's config, and they say the script "worked fine".
How do you get the dashboard current this morning, and what has to happen before db-replica-1 serves anyone again?
It covers
- Mysql replication
- Read only replica
- Replica rebuild