MySQL Replication: Primary-Replica Setup
Fast recall
Quick revision
Core answer
AbstractRoutingDataSource with @Transactional(readOnly=true) to split traffic, while handling replication lag for read-your-writes.Whether you understand the full replication pipeline (binlog, I/O thread, relay log, SQL thread), the consistency trade-offs of async vs semi-sync, and how to implement read/write splitting in a Spring Boot application.
Routing reads to replicas without considering replication lag, producing read-your-writes bugs where users do not see their own changes. Another common mistake is assuming async replication is safe — a primary crash can lose recently committed transactions.
Explain GTID-based replication, how automated failover with Orchestrator or InnoDB Cluster works, and when Aurora or Spanner-style replication is preferable to self-managed binlog replication.
Interview-ready guide
Interview answer
MySQL primary-replica replication is the standard way to scale reads — the primary handles all writes and streams changes to replicas via the binary log.
How it works
- Every write on the primary is recorded in the binary log in commit order — each replica runs an I/O thread that pulls binlog events into a local relay log, and an SQL thread that replays those events
- Replication is asynchronous by default, which means there's always some lag — how much depends on replica hardware and primary write rate
Async vs semi-sync vs Group Replication
- Async: primary acknowledges commit before any replica confirms — lowest latency, but a primary crash can lose recently committed transactions
- Semi-sync: primary waits for at least one replica to ack binlog receipt — bounds data loss at the cost of a small latency bump
- Group Replication: synchronous multi-node consensus — no data loss, higher write latency; used in InnoDB Cluster
Read routing in Spring Boot
- Configure separate DataSource beans for primary and replica, wire them into an
AbstractRoutingDataSourcethat picks based on@Transactional(readOnly=true) - For read-your-writes, remember the user just wrote and route their next reads to the primary for a short window — otherwise they hit a lagging replica and see stale data
Row-based replication is the production default — it replicates exact row changes and avoids the determinism bugs that plague statement-based replication.
MySQL Replication: Primary-Replica Setup
Direct Answer
MySQL replication streams binary log (binlog) events from the primary to one or more replicas asynchronously. On each replica an I/O thread pulls binlog events into a local relay log, and an SQL thread (or multi-threaded workers) replays them. In Spring Boot, you configure separate DataSource beans for writes (primary) and reads (replicas) and use AbstractRoutingDataSource with @Transactional(readOnly=true) to split traffic, while handling replication lag for read-your-writes.
Code Example
// 1. ThreadLocal key holder
public class DataSourceContextHolder {
private static final ThreadLocal<String> context = new ThreadLocal<>();
public static void setDataSourceType(String type) { context.set(type); }
public static String getDataSourceType() { return context.get(); }
public static void clear() { context.remove(); }
}
// 2. Routing DataSource
public class RoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSourceType();
}
}
// 3. DataSource configuration
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties("spring.datasource.primary")
public DataSource primaryDataSource() { return DataSourceBuilder.create().build(); }
@Bean
@ConfigurationProperties("spring.datasource.replica")
public DataSource replicaDataSource() { return DataSourceBuilder.create().build(); }
@Bean @Primary
public DataSource routingDataSource(
@Qualifier("primaryDataSource") DataSource primary,
@Qualifier("replicaDataSource") DataSource replica) {
RoutingDataSource rds = new RoutingDataSource();
Map<Object, Object> sources = new HashMap<>();
sources.put("primary", primary);
sources.put("replica", replica);
rds.setTargetDataSources(sources);
rds.setDefaultTargetDataSource(primary);
return rds;
}
}
// 4. Read-your-writes guard: route reads to primary briefly after a write
@Aspect @Component
public class DataSourceRoutingAspect {
@Around("@annotation(org.springframework.transaction.annotation.Transactional)")
public Object route(ProceedingJoinPoint pjp) throws Throwable {
Transactional tx = ((MethodSignature) pjp.getSignature())
.getMethod().getAnnotation(Transactional.class);
boolean readOnly = tx != null && tx.readOnly();
String key = readOnly && !RecentWriteMarker.isRecent() ? "replica" : "primary";
DataSourceContextHolder.setDataSourceType(key);
try { return pjp.proceed(); }
finally { DataSourceContextHolder.clear(); }
}
}
Common Mistakes & Pitfalls
Routing reads to replicas without considering replication lag, producing read-your-writes bugs where users do not see their own changes. Another common mistake is assuming async replication is safe — a primary crash can lose recently committed transactions.
Check your understanding
Answer these without looking back at the article.
- How would you handle the scenario where a user creates a record and then immediately reads it — and the replica hasn't caught up yet?
- What is GTID replication and what problem does it solve over binlog position-based replication?
- How would you promote a replica to primary in a failover scenario with zero data loss?
- How does Aurora's shared-storage replication differ from traditional binlog replication?
Related interview prep categories
2Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.