How to Migrate Legacy Systems to a Distributed MySQL Architecture: 8 Practical Strategies

1. Architecture Background and Evolution Strategy

The business is growing rapidly, requiring proactive planning for scalability and high availability. MySQL, while not a universal solution, serves as a typical choice for evolving the architecture and building a distributed storage platform.

2. System Migration Background

The existing system consists of two major domains: data services (transactional data) and billing services (state‑change history). The pre‑migration architecture contains tens of thousands of tables and extensive stored procedures that tightly couple business logic.

The monolithic design makes it difficult to split into a distributed architecture for three main reasons:

Limited understanding of distributed systems among developers and operations, leading to hesitation.

Expectation of a seamless, transparent migration of stored procedures, which is unrealistic.

Distributed architectures introduce maintenance complexity that must be balanced against benefits.

3. Evolution Phases

The migration follows four stages, but the article focuses on the first three:

Functional Phase : Identify requirements and transfer stored procedures to MySQL.

Architecture Phase : Redesign system and business architecture to support distributed scaling.

Performance Phase : Conduct incremental and full‑scale stress tests and optimize performance.

Migration Phase : Move production traffic to the distributed MySQL environment.

4. Strategy 1 – Functional Transfer

For a stable commercial database, a gradual, iterative migration is adopted. The goal is to keep application‑layer changes minimal while moving stored procedures to MySQL.

Key steps:

Check if a record exists: select value from user where id=100; If it exists, update: update user set value=value+10 where id=100; If it does not exist, insert:

insert into user(id,value) values(100,10);

5. Strategy 2 – System Architecture Splitting

The system is divided into data and billing domains. The billing database is split into two instances, decoupling stored‑procedure dependencies and converting real‑time writes to asynchronous operations.

6. Strategy 3 – Horizontal Write Scaling

Billing stored procedures are replaced with simple INSERT statements, and a middleware layer provides a unified data entry point. Example:

Insert into acc_data(id,value,mod_date) values(100,10,now()) on duplicate key update value=value+10,mod_date=now();

This approach reduces write latency and enables transparent scaling for applications.

7. Strategy 4 – Read/Write Separation

Multiple replica nodes are introduced to balance read traffic. Although stored procedures remain for queries, the read‑write split offloads roughly 50 % of query load.

8. Strategy 5 – Business Splitting

Data services are further divided into platform and application services, reducing the load on the application layer and simplifying verification.

9. Strategy 6 – Transaction Dimensionality Reduction

Complex stored‑procedure logic is replaced by a series of SQL calls. The main concern—additional round‑trips—is mitigated after validation, and the architecture becomes fully application‑driven.

10. Strategy 7 – Distributed Business Architecture

Two critical issues are addressed:

Primary‑key conflicts under high concurrency – solved with INSERT ... ON DUPLICATE KEY UPDATE.

Huge number of business tables – unified data entry points (e.g., app_group1_data, app_group2_data) reduce management complexity.

11. Strategy 8 – Business Sharding Logic

Large tables are re‑partitioned across shards, decreasing per‑node file size from ~200 MB to ~70 MB and improving latency (read < 1 ms, write < 4 ms). Overall QPS increased by less than 15 % after cache removal.

12. Performance Optimization Stage

IO‑bound testing with sysbench (cfq scheduler, oltp_read_write.lua, 10 tables, 50 M rows each, 1 h) shows that SATA‑SSD meets the pressure scenario. Detailed benchmark tables and graphs illustrate CPU, IO, and QPS metrics.

13. Architecture Milestone and High‑Availability Design

The final distributed cluster comprises three sets of instances (≈30 instances on 10 physical servers). High‑availability is achieved with LVS + keepalived, providing near‑zero‑perception failover.

Hardware evaluation shows CPU usage is modest while IO demand dominates; SATA‑SSD and PCIe‑SSD configurations comfortably satisfy the workload.

14. Summary

The migration demonstrates that a legacy monolithic database can be transformed into a scalable, distributed MySQL architecture through incremental functional migration, systematic system splitting, horizontal scaling, read/write separation, and targeted performance tuning, achieving stable QPS around 5 k per shard and significant latency reductions.