Skip to content
Qzentra

Case fileData Engineering

5 TB Zero-Downtime MySQL Migration

A 24/7 production system on legacy MySQL 5.7 with roughly 5 TB of data, moved to a modern MySQL environment by replication with no service interruption at cutover.

5 TBof production data moved with zero service interruption

Enters through
02Data Engineering
System type
Production database migration
Also demonstrates
infrastructure · replication · SQL optimization · production risk management
Source and replica through the migration.The route follows the traffic: on the MySQL 5.7 source through audit, remediation, build, and synchronization, then across to the replica at promotion. The dashed line is the replica being built and kept in sync before it serves.SourceMySQL 5.7, serving 24/7Replicamodern MySQL123456
  1. 1Compatibility auditon the source
  2. 2Variable remediationon the source
  3. 3Replica buildsource serving · replica building
  4. 4Synchronizationsource serving · replica building
  5. 5Promotioncutoveron the replica
  6. 6Query modernizationon the replica

01 Context

The challenge

A production database of approximately 5 TB was running on legacy MySQL 5.7 under a 24/7 workload. It had to move to a modern MySQL environment, and there was no maintenance window in which the system could stop.

A migration of that size cannot be a dump and restore. Data keeps changing during the copy, the newer MySQL version rejects configuration the old one accepted, and the moment of cutover is the moment the business is exposed.

What made it non-trivial

  • Approximately 5 TB of data that kept changing under a 24/7 workload
  • No maintenance window: zero service interruption was the requirement
  • Deprecated variables and compatibility differences between MySQL 5.7 and the target
  • A single cutover moment with the whole system depending on it

02 The system

What Qzentra engineered

Qzentra planned and ran a replication-based migration. The MySQL 5.7 workload was audited for compatibility with the target environment, and deprecated variables were remediated before any data moved.

A replica was built on the modern environment and synchronized with the live source while it continued to serve traffic. At cutover the replica was promoted, with no service interruption. Afterwards, queries were modernized using common table expressions.

What Qzentra was responsible for

  1. Migration planning and production risk management
  2. Compatibility audit and deprecated-variable remediation
  3. Replica build and synchronization
  4. Promotion at cutover
  5. Post-migration query modernization with CTEs

03 The route

How the system works

Five steps, all in the two foundation layers. Nothing above Infrastructure was touched, and nothing needed to be.

The migration on the system section.Data and Infrastructure carry the whole route; the other four layers are unused. The numbered ports follow the steps below.06  Application05  Automation04  AI03  APIs02  Infrastructure01  Data12345
  1. 1Compatibility audit of MySQL 5.7 workloadData
  2. 2Deprecated variable remediationInfrastructure
  3. 3Replica build and synchronizationInfrastructure
  4. 4Replica promotion at cutoverInfrastructure
  5. 5Query modernization with CTEsData

  1. Compatibility audit of MySQL 5.7 workload

    Data

    The existing workload was audited against the target MySQL environment before anything moved, to find what would break.

  2. Deprecated variable remediation

    Infrastructure

    Configuration variables deprecated in the target version were remediated on the source side, so the target would accept the workload.

  3. Replica build and synchronization

    Infrastructure

    A replica was built on the modern environment and kept synchronized with the live source, which continued to serve traffic.

  4. Replica promotion at cutover

    Infrastructure

    Cutover was the promotion of the synchronized replica to primary. There was no service interruption.

  5. Query modernization with CTEs

    Data

    After the migration, queries were modernized using common table expressions, as a separate step from the cutover.

04 Decisions

Engineering decisions

  1. Replication, not dump and restore

    With roughly 5 TB changing under a 24/7 workload, the way to move without stopping is to build a replica and keep it synchronized until the switch.

  2. Compatibility first

    The 5.7 workload was audited against the target before migration and deprecated variables were remediated, so incompatibilities were found in the audit rather than at cutover.

  3. Cutover as promotion

    Cutover was the promotion of an already synchronized replica. That reduces the switch to a controlled step rather than a data move.

  4. Modernize after, as a separate step

    Query modernization with CTEs was done after the migration, not folded into the cutover.

05 Verified outcomes

What was achieved

  • Zero service interruption at cutover
  • Approximately 5 TB of production data migrated from MySQL 5.7 to a modern MySQL environment
  • Compatibility issues and deprecated variables remediated before cutover
  • Queries modernized with CTEs after migration
5 TBof production data moved with zero service interruption

06 Technology and capabilities

Technology, after the architecture

Named last, with the role each played. The capabilities are what the project demonstrates beyond its primary service.

  • MySQL 5.7Source environment, 24/7 production
  • MySQL, modern environmentTarget environment
  • MySQL replicationReplica build, synchronization, and promotion
  • SQL and CTEsPost-migration query modernization

Capabilities demonstrated

  • database architecture
  • migration planning
  • MySQL replication
  • compatibility assessment
  • SQL optimization
  • production risk management
  • high-availability thinking

07 Related work

Other case files

Systems that share a layer, a capability, or a decision with this one.

  1. 04Case fileData Engineering

    Enterprise Data Query Architecture

    Query architectures for analytical workloads across data platforms and data lakes, using distributed and embedded engines over Azure Data Lake storage.

    Enters through02Data Engineering

    Read the case file
    Enterprise Data Query Architecture: route through the six layers.06  Application05  Automation04  AI03  APIs02  Infrastructure01  Data1234
    1. 1lake storageData
    2. 2query enginesInfrastructure
    3. 3federationAPIs
    4. 4analyticsApplication

  2. 01Case fileAI Systems & Automation

    Multimodal AI Sales Funnel

    An end-to-end sales funnel for a crypto-mining hardware enterprise: Instagram and WhatsApp leads qualified, answered from a catalog of 500+ ASIC miners, scored, and handed to sales.

    Verified factAirtable-backed catalog of 500+ ASIC miners

    Enters through01AI Systems & Automation

    Read the case file
    Multimodal AI Sales Funnel: route through the six layers.06  Application05  Automation04  AI03  APIs02  Infrastructure01  Data12345678
    1. 1Instagram DMsAPIs
    2. 2Lead qualificationAutomation
    3. 3WhatsAppAPIs
    4. 4Multimodal AI (text, voice notes, images)AI
    5. 5Product knowledge retrievalData
    6. 6Lead scoringAutomation
    7. 7Human sales handoffApplication
    8. 8Automated follow-up or cold-lead archivingAutomation

08 Discuss a project

Running a production database you cannot afford to stop?

Tell us the version, the size, the workload, and what it has to move to. Migrations are planned from the risk backwards.

Discuss a ProjectAll work