Skip to content
Build 9234610

Database Migration: Diff's MySQL → New Managed MySQL 8.0

How we move OMS's data from Diff's MySQL 8.0 onto the new managed MySQL 8.0 (Azure Database for MySQL Flexible Server, or AWS RDS depending on cloud target).

Production facts (verified)

These were captured directly against the running production DB on 2026-05-18.
Engine MySQL 8.0.44
Database name popsocket
Total size 279 GB (185 GB after LHM ghost cleanup)
Tables 137 active + 5 ghost = 142
Charset / collation utf8mb4 / utf8mb4_0900_ai_ci
sql_mode NO_AUTO_VALUE_ON_ZERO, STRICT_ALL_TABLES, NO_ENGINE_SUBSTITUTION
Time zone UTC
binlog_format MIXED (consider switching to ROW pre-migration)
binlog retention 30 days (excellent for replication setup)
max_connections 2619
innodb_buffer_pool_size ~23 GB
Stored procedures 0
Triggers 0
Views 0

Top 10 largest tables (production):

Table Size
demandware_orders 53.56 GB
lhma_2025_12_02_..._demandware_orders (ghost) 47.70 GB
lhma_2025_12_02_..._shopify_app_admin_shopify_orders (ghost) 46.04 GB
shopify_app_admin_shopify_orders 44.71 GB
shopify_app_admin_shopify_line_items 13.08 GB
order_audits 10.15 GB
shipstation_orders 9.52 GB
images 5.25 GB
line_item_assets 4.42 GB
line_items 3.89 GB

What is and isn't a concern

This is a same-version migration (8.0 → 8.0). The "5.7 → 8.0 upgrade" risk we originally planned for does not exist. Specifically:

  • No reserved-word collisions (same version on both sides)
  • No sql_mode reconciliation (we'll match exactly)
  • No charset migration (already on utf8mb4)
  • No auth-plugin compatibility issues (same caching_sha2_password)
  • No test-suite-against-8.0 pass needed for cutover

What remains:

  • Data volume — 279 GB. Replication is the only realistic path.
  • LHM ghost tables — 95 GB of dead data. See "Pre-migration cleanup" below.
  • Two ~50 GB tables — demandware_orders and shopify_app_admin_shopify_orders dominate transfer time. Plan around them.

Pre-migration cleanup (ask Diff first)

Diff did an LHM batch on 2025-12-02 and didn't drop the shadow tables.

lhma_2025_12_02_13_02_35_612_demandware_orders                     47.70 GB
lhma_2025_12_02_13_35_52_814_shopify_app_admin_shopify_orders      46.04 GB
lhma_2025_12_02_14_19_07_713_marketplace_orders                     1.37 GB
lhma_2025_12_02_15_25_47_807_amazon_orders                          0.02 GB
lhma_2025_12_02_15_25_53_141_influencer_orders                      0.00 GB
                                                                   ───────
                                                                    95 GB

Action item: confirm with Diff that these are safe to drop. If yes, have them drop the tables before we start replication. The migration window then transfers 185 GB instead of 279 GB — a third less data.

Strategy: native MySQL binlog replication

This is the recommended approach for a 279 GB database. mysqldump and restore alone would take many hours of downtime — unacceptable for OMS.

Azure has shifted away from Azure DMS for MySQL in favor of native replication; on AWS, AWS DMS is still recommended for MySQL → MySQL. Either way the shape is the same: seed with a consistent dump, then catch up via binlog replication, then flip traffic during a short cutover window.

   ┌─────────────────┐                       ┌─────────────────────┐
   │ Diff prod MySQL │  ───── binlog ──────► │ New managed MySQL    │
   │   (8.0.44)      │     replication        │   8.0 (replica mode) │
   └─────────────────┘                       └─────────────────────┘
                                                       │
                                                       │ catches up over hours/days
                                                       ▼
                                              "lag = 0" (caught up)
                                                       │
                              cutover window           ▼
        1. Pause Sidekiq, put Rails in maintenance mode
        2. Wait for replica to catch up final transactions (seconds)
        3. Promote new DB to primary (stop replication)
        4. Update DATABASE_URL Secret, roll Deployments
        5. Resume traffic
                                                       │
                                                       ▼
                                              Total downtime: ~15 min

Sequence of work

Order matters. Don't skip steps.

Phase A — Pre-work (1 week before cutover)

  1. Get LHM ghost tables dropped (Diff action). Shrinks dataset to ~185 GB.
  2. Provision new managed MySQL 8.0 (Senith). Sizing:
  3. Engine: MySQL 8.0 (match prod's 8.0.44 or nearest available patch level)
  4. Instance class: comparable to current AWS instance. The current prod has 23 GB InnoDB buffer pool, so target ~32 GB RAM with innodb_buffer_pool_size ~24 GB.
  5. Storage: 500 GB (185 GB current + headroom). Provisioned IOPS sized for write throughput.
  6. Multi-AZ for high availability.
  7. character_set_server = utf8mb4, collation_server = utf8mb4_0900_ai_ci.
  8. sql_mode = NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION (match prod).
  9. time_zone = +00:00 (UTC).
  10. TLS required on connections.
  11. Set up replication user on Diff prod (Diff action). Grant REPLICATION SLAVE, REPLICATION CLIENT.
  12. Confirm network path (Senith + Diff). Either: AWS public endpoint with TLS + IP allowlist, or VPC ↔ VNet peering. Public endpoint is sufficient for a 1–2 week migration window.
  13. Switch Diff's binlog_format to ROW (Diff action, if currently MIXED). Required for reliable replication across platforms.
  14. Confirm binlog_expire_logs_seconds ≥ 604800 (7 days, Diff action). Currently 30 days — plenty.

Phase B — Seed the new database

  1. Take a consistent dump from Diff prod (Senith): 
    mysqldump --single-transaction --master-data=2 --gtid \
          --set-gtid-purged=ON \
          -h <diff-host> -u <user> -p \
          popsocket > popsocket-seed.sql
    Expected time: 2–4 hours for 185 GB. Compress as you go (| gzip > popsocket-seed.sql.gz).
  2. Record the binlog position from the dump's header. Looks like CHANGE MASTER TO MASTER_LOG_FILE='...', MASTER_LOG_POS=...;.
  3. Restore the dump on the new managed DB (Senith). Expected time: 4–8 hours. Use mysql --init-command="SET FOREIGN_KEY_CHECKS=0;" for speed.
  4. Verify row counts match for the top 10 largest tables (joint).

Phase C — Run replication and soak

  1. Configure new DB as a replica of Diff prod (Senith): 
    CHANGE MASTER TO
  MASTER_HOST='<diff-prod-host>',
  MASTER_USER='<repl-user>',
  MASTER_PASSWORD='<password>',
  MASTER_LOG_FILE='<from dump>',
  MASTER_LOG_POS=<from dump>,
  MASTER_SSL=1;
START SLAVE;
  2. Monitor Seconds_Behind_Master for ~1 week. Should drop to 0 within 24 hours of starting replication, then stay near 0.
  3. Set up a Datadog dashboard showing replication lag, replica error count, replica state. Watch daily.

Phase D — Pre-cutover validation (1 week before)

  1. Freeze schema migrations. No new files in db/migrate/ until cutover + 1 week after.
  2. Spot-check data consistency between old and new (joint). Pick 5–10 known rows, compare on both sides.
  3. Run the Rails app in staging pointed at a replica (Rails team). Smoke test the critical paths.
  4. Communicate maintenance window to internal users + Shopify/SFCC partners (manager).
  5. Prepare the rollback DATABASE_URL pointing back at Diff prod, ready to flip if cutover fails.

Phase E — Cutover (15-minute maintenance window)

Detailed timing:

Time Step Owner
T+0 Ingress returns 503 for all paths except /up Senith
T+1m Scale Sidekiq Deployments to 0; wait for in-flight jobs to drain Senith
T+3m Confirm Redis queues are empty (LLEN queue:* = 0) Joint
T+5m Verify replication Seconds_Behind_Master = 0 Senith
T+6m STOP SLAVE; RESET SLAVE ALL; on new DB — it's now standalone primary Senith
T+7m Spot-check AUTO_INCREMENT counters on hot tables match expectations Joint
T+8m Update DATABASE_URL Secret to point at new DB Senith
T+9m Rolling restart all Deployments: kubectl rollout restart deployment/{web,sidekiq-*} Senith
T+11m Run smoke test: hit /up, exercise critical endpoints, enqueue test Sidekiq job Rails team
T+13m Scale Sidekiq Deployments back to normal replica counts Senith
T+14m Drop maintenance mode — traffic resumes Senith

Phase F — Post-cutover (first week)

  1. T+1h: verify error rate is at baseline (Datadog).
  2. T+24h: keep Diff's prod DB read-only as rollback. SET GLOBAL read_only = 1; on the old DB.
  3. T+1w: decommission Diff's prod DB. Take a final backup, then shut down.
  4. T+1w: unfreeze schema migrations.

Rollback plan

If we hit a problem during cutover (smoke test fails, app errors, etc.), the escape hatch is to point back at Diff prod within ~1 hour of cutover:

  1. Maintenance mode on
  2. Drain Sidekiq
  3. Update DATABASE_URL back to Diff prod
  4. Rolling restart Deployments
  5. Smoke test
  6. Drop maintenance mode

The rollback window closes after ~1 hour because by then the new DB has accumulated writes that the old DB doesn't have, and reverting would lose data.

Critical gotchas

Gotcha Mitigation
AWS auto-purges binlogs Step 6 — confirm 30-day retention before starting (already verified)
Azure MySQL Flex enforces TLS Verify sslmode: required in DATABASE_URL, or ?sslmode=verify_ca
Connection limit on smaller tiers New DB needs ≥3000 connection budget to match prod's 2619 max_connections + headroom
sql_mode defaults differ Set explicitly to match prod — don't rely on defaults
AUTO_INCREMENT drift Verify post-cutover before traffic resumes
Replication lag spikes during peak write Acceptable while soaking; must be 0 at cutover
binlog_format = MIXED on prod Switch to ROW before starting replication

What changes in the app — nothing

Rails reads DATABASE_URL from environment. Switching DBs is updating one Kubernetes Secret + rolling the Deployments. No code change at cutover. The env-var refactor done earlier on the docker-setup-chnages branch is what makes that possible.

Effort estimate

Phase Calendar time Active engineering
A (Pre-work) 1 week A few hours total
B (Seed dump+restore) 12–24 hours Mostly waiting
C (Soak) 1 week Daily lag checks
D (Pre-cutover validation) 1 week A day of smoke testing
E (Cutover) 15 minutes High-attention war room
F (Post-cutover) 1 week Monitoring, then decommission

Total elapsed: ~3 weeks from "drop LHM ghosts" to "old DB shut down." Most of that is replication soak time, not active engineering.

Open items

These still need confirmation from Diff:

  • Confirm the 5 LHM ghost tables are safe to drop (asked)
  • Get the production MySQL endpoint and replication user credentials
  • Confirm the network path option (public endpoint vs VPC peering)
  • Confirm any other services connect directly to the DB (BI, analytics, ETL) — these need re-pointing post-cutover

See decisions-needed.md and the most recent Diff email thread for status.