MySQL Read Replica + Prisma: 2.7s → 0.8s Page Loads for $0
Business Problem: One Website, Two Speeds
Our site uses DNS-based traffic splitting based on visitor geography: mainland users resolve to Alibaba Cloud (Beijing), while overseas users resolve to a VPS in Los Angeles. The application is stateless Next.js, running the same image on both sides—but there's only one database: Alibaba Cloud RDS MySQL in Beijing.
This results in a typical "split traffic, unsplit data" architecture:
- Mainland users: app and database in the same region, single query in milliseconds, page in hundreds of milliseconds
- Overseas users: app in Los Angeles, every SQL query has to cross the Pacific round-trip, RTT about 150-200ms
SSR pages typically run multiple queries serially (article + related recommendations + tags + categories), and latency is amplified per query. Measured on the same article page: domestic server 0.1-0.5 seconds, overseas server 2.7-4.2 seconds. The intuitive user experience is "this website is unusable abroad due to lag."
Selection: Why Native binlog Replication
Write traffic is minimal (content site, high read-write ratio), and the only issue to solve is the physical distance for reads. Comparison of alternative solutions:
| Solution | Cost | Problem |
|---|---|---|
| Cloud provider read-only instance | Per-instance fee | Can only be built in the same provider's region, cannot cross borders, cannot solve transoceanic issues |
| DTS / data synchronization service | Pay-per-use | Not cost-effective to pay subscription fees for a 30MB database |
| Application-layer cache (Redis) | New component | Cache invalidation logic invades business, and first query still crosses the ocean |
| MySQL native binlog/GTID replication | 0 cost | Requires self-handling operational pitfalls (this article's value) |
MySQL replication is a standard capability, and RDS as the source incurs no fees. The replica is placed on a 2C2G small VPS in the same data center as the overseas app (Docker running MySQL 8), reducing read latency from 150ms to within 1ms locally.
Before starting, perform a prerequisite check on the source database with five must-check items (execute on RDS):
SELECT @@version; -- 副本版本必须 ≥ 源库（我们是 8.0.36）
SELECT @@gtid_mode; -- 必须 ON，才能用 SOURCE_AUTO_POSITION
SELECT @@binlog_format; -- 期望 ROW
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'; -- 断链重建窗口（我们是 30 天）
SHOW GRANTS FOR CURRENT_USER; -- 有没有 REPLICATION SLAVE 权限
Unexpected discovery: Alibaba Cloud RDS's high-privilege account comes with REPLICATION SLAVE by default, no need to create a separate replication account.
Architecture: Write to Beijing, Read Locally
阿里云 RDS 北京（主库，唯一写入点）
│ binlog/GTID 原生复制，SSL 加密，SOURCE_AUTO_POSITION=1
▼
洛杉矶 VPS：MySQL 8 只读副本（Docker，与海外 app 同机房）
▲ 读 ~1ms
海外 app ──写───────────────────→ 仍直写北京主库
国内 app ──读写──→ 北京主库（完全不变）
The application layer uses the Prisma official extension @prisma/extension-read-replicas for routing, core is dual exports:
// src/lib/db.ts（简化）
export const prismaPrimary = new PrismaClient({ /* 主库 */ });
export const prisma = process.env.DATABASE_REPLICA_URL
? prismaPrimary.$extends(readReplicas({ url: process.env.DATABASE_REPLICA_URL }))
as unknown as PrismaClient // cast 保型，全站 200+ 调用点零改动
: prismaPrimary; // 没配副本 = 同一实例，行为与改造前一字不差
The extension's routing rules: find*/count/aggregate/groupBy go to the replica; all writes, $transaction, $queryRaw automatically go to the primary. Most code doesn't need changes, but two scenarios must manually pin to the primary (import prismaPrimary):
- Login sessions: NextAuth's database session is "write session then immediately read back on next request"—even a half-second delay on the replica causes login flickering
- Read-after-write: Interfaces that
findManyimmediately aftercreateon the same table—replication delay makes the newly written record "disappear"
Conversely, the pattern of "update return value as read result" is naturally safe—update itself goes to the primary, so what's returned is primary data.
Want Only One Machine Enabled: Host Whitelist Access Control
Two servers share the same .env file (rendered from the same CI Secret), so cannot differentiate by "sending different configurations." The approach is three-tiered:
- At deployment,
docker run -e DEPLOY_HOST=<local IP>, so the container knows which machine it's on - The entry script uses whitelist + TCP probe to decide variable retention (fail-safe direction is always downgrading to direct primary connection):
if [ -n "$DATABASE_REPLICA_URL" ]; then
if host_in_whitelist "$DATABASE_REPLICA_HOST"; then
node -e "TCP 探测副本 3 秒" || unset DATABASE_REPLICA_URL # 副本挂了→回主库
else
unset DATABASE_REPLICA_URL # 国内机→行为零变化
fi
fi
- Application code only checks if the variable exists
This design also serves as a degradation switch: if the replica goes down, restarting the app container automatically falls back to the primary; to completely remove it, just delete two lines from the Secret.
Five Real-World Pitfalls (with Original Error Messages)
Pitfall 1: lower_case_table_names can only be set during initialization
Alibaba Cloud RDS defaults to lower_case_table_names=1, while MySQL on Linux defaults to 0. ORM creates table names with uppercase (Article, User), inconsistency breaks replication. This parameter only takes effect during datadir initialization—it must be written into my.cnf before starting the replica container; changing it afterward = deleting the data directory and restarting.
Pitfall 2: mysqldump requires RELOAD privilege, and RDS heartbeat prevents GTID from ever being quiescent
The standard initial full sync mysqldump --set-gtid-purged=ON directly errors:
mysqldump: Couldn't execute 'FLUSH TABLES': Access denied;
you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s)
RDS regular accounts don't have and can't be granted RELOAD. Falling back to "take gtid_executed before and after dump, trust if consistent"—retried 8 times all failed: Alibaba Cloud RDS internal heartbeat table (mysql.ha_health_check) writes GTID per second, quiescent window doesn't exist.
Final solution: --set-gtid-purged=OFF + manual SET GLOBAL gtid_purged='<GTID before dump>', with loud failure verification—the gap between pre-dump GTID and snapshot point is only 1-2 seconds; if a business write falls exactly in there, replay will trigger 1062/1032 errors (loud, detectable, never silent data corruption); on error, rebuild and retry. Heartbeat transactions are blocked by the next filter rule, replay harmless.
Pitfall 3: RDS binlog carries named timezone, official image timezone tables are empty
Replication breaks immediately after starting:
Error 1298: Unknown or incorrect time zone: 'Asia/Shanghai'
RDS session timezone is named and written into binlog transaction headers; MySQL official Docker image's mysql.time_zone* tables are empty by default. Writing +08:00 in my.cnf only aligns server default, cannot prevent named timezone in binlog. Solution in one line:
docker exec mysql-replica sh -c "mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot mysql"
After loading timezone tables, no rebuild needed, START REPLICA resumes in place (failed transactions are atomically rolled back).
Incidentally, the replica must configure replicate-wild-do-table=业务库.% to filter, otherwise RDS internal heartbeat table ROW events find no corresponding table on the replica, also breaking replication.
Pitfall 4: Docker-published ports don't go through INPUT chain
Restricting 3306 to only allow app machine access, iptables -A INPUT --dport 3306 written has no effect—Docker-published port traffic goes through PREROUTING DNAT → FORWARD, not into INPUT at all. Correct approach is DOCKER-USER chain + matching original port before DNAT:
iptables -I DOCKER-USER -i eth0 -p tcp -m conntrack \
--ctorigdstport 3306 --ctdir ORIGINAL ! -s <app机IP> -j DROP
Pitfall 5: Under MYSQL_ROOT_HOST=localhost, root has empty password
To avoid exposing root to public internet, set MYSQL_ROOT_HOST=localhost, but after initialization, mysql.user has root@localhost with authentication_string empty (no password, direct socket access). Exposure is only within the container, but still need to manually add an ALTER USER and verify the field is non-empty.
Additionally, two safeguards worth adding to any replica's my.cnf: super_read_only=ON (application-layer routing bugs will error directly instead of writing divergent data) and performance_schema=OFF (saves hundreds of MB memory on a 2GB small machine).
Results: 3.3x, and Got Clean A/B Data
During post-launch degradation testing, solved the "no baseline before modification" issue—stopped the replica container, app automatically fell back to direct primary connection, this state is pre-modification:
| State | Same article page duration (overseas server local measurement) |
|---|---|
| Direct to Beijing primary (= pre-modification) | 2.7 - 4.2 s |
| Read local replica (= post-modification) | 0.81 - 0.95 s |
Approximately 3.3x. The remaining 0.8 seconds includes one transoceanic write (page view count) and rendering itself; read path is no longer the bottleneck.
Testing also verified fault path: stop replica → app restart auto-degrades (site remained 200 throughout, no interruption) → after replica recovery, replication auto-resumes and delay resets to zero. Daily cron checks replication thread and delay every 5 minutes, sends email alerts on anomalies.
Who Is This Solution For?
Suitable for: Content sites/tool sites with high read-write ratio; small database (ours is only 30MB, full rebuild is minutes); already deployed multi-region, just missing the data layer.
Not suitable for: Write-heavy or business with strong consistency requirements for "read-after-write" (either tolerate marking everywhere for primary reads, or directly use distributed database); large database where full rebuild is painful—calculate binlog retention window first.
Common Questions FAQ
Will the website go down if the replica fails?
No. The entry script performs TCP probe on startup; probe failure auto-degrades to direct primary connection; if replica goes down during operation, restarting the app container falls back. Fail-safe direction is always "back to primary"—worst case is overseas users experience slower speed, not downtime.
What if replication breaks?
First check errors: timezone/table structure issues fix by START REPLICA to resume in place; if unfixable or break exceeds binlog retention (ours is 30 days), delete data directory and full rebuild—whole process takes only minutes for a small database.
Why not use Redis cache to solve?
Cache solves "repeated reads of hot data," here the problem is "all reads must cross the ocean." Cache requires invalidation logic invading business code, and cold data first query still slow; replica solution is almost transparent to business code, one-time setup accelerates everything.
Is Prisma's read-write splitting extension stable?
@prisma/extension-read-replicas is an official Prisma extension, but it relies on internal fields of Prisma Client, with conservative peer version declarations. We pinned both extension and prisma to exact versions and left a 7-assertion routing verification script, must rerun before upgrading prisma.
Reference Links
- MySQL 8.0 Reference: Replication with GTIDs
- Prisma official read-write splitting extension @prisma/extension-read-replicas
- MySQL official Docker image documentation (timezone tables, MYSQL_ROOT_HOST)
- Docker official documentation: Packet filtering and firewalls (DOCKER-USER chain)
- Alibaba Cloud RDS MySQL Help Documentation