The need: isolate secondary workloads
Analytical queries can scan large datasets and monopolise CPU, memory or I/O. Backups add their own load. The requirement is to move these tasks away from the instance serving applications while keeping data fresh enough for analysis.
Architecture: one source, dedicated replicas
The production instance publishes changes through binary logs. Replicas apply them on resources dedicated to analytical reads and backups. Separate BI and backup destinations also prevent large exports from affecting dashboards. The exact topology depends on data volume and freshness requirements.
Control BI access
BI tools connect to replicas using accounts limited to reading the necessary data. Network access is restricted and replication protected. Query concurrency and duration limits prevent analytics from exhausting the resources needed to apply incoming changes.
Back up and demonstrate recovery
Backups from a replica must be consistent with the storage engines in use and retain the replication coordinates or GTID positions needed for recovery. Retained logs support point-in-time recovery when designed into the solution. Restore exercises validate data, timings and procedures; replication alone does not replace an independent backup.
Measure the effect on production
The goal is to preserve application performance, but production still bears the cost of generating and transmitting logs. Acceptance testing compares application latency, transaction throughput and I/O before and during secondary workloads. Replication lag, errors and backup freshness are monitored continuously.
Key considerations
- Isolated BI reads
- Replica-based backups
- Replication lag monitoring
- Verified recovery