Mysql Migration

MySQL Migration Safety Review

Quick Reference

| If you need to… | Go to | |----------------------------------------|-----------------------------------------| | Understand what this skill covers | §1 Scope | | Check mandatory prerequisites | §2 Mandatory Gates | | Choose review depth | §3 Depth Selection | | Handle incomplete context | §4 Degradation Modes | | Analyze DDL safety item by item | §5 DDL Safety Checklist | | Design a phased execution plan | §6 Execution Plan | | Avoid common migration mistakes | §7 Anti-Examples | | Score the review result | §8 Scorecard | | Format review output | §9 Output Contract | | Look up DDL algorithm by operation | references/ddl-algorithm-matrix.md | | Plan a large-table (>10M rows) change | references/large-table-migration.md |

---

§1 Scope

In scope — schema migration safety for MySQL 5.7 and 8.0+ (InnoDB):

• ALTER TABLE (add/drop/modify column, add/drop index, rename, convert charset)
• CREATE / DROP INDEX, data backfill and transformation migrations
• Table restructuring (partitioning, splitting, merging), foreign key changes
• Migration file review (Flyway, Liquibase, golang-migrate, raw SQL)
• Rollback planning, verification, and replication impact assessment

Out of scope — delegate to dedicated skills:

• Query optimization, connection pooling, buffer tuning → mysql-best-practise
• Application code changes → go-code-reviewer or language-specific reviewer
• Security hardening, privilege management → security-review

---

§2 Mandatory Gates

Execute gates sequentially. Each gate has a STOP condition — if triggered, do not proceed until resolved.

Gate 1: Context Collection

Collect before giving migration advice:

| Item | Why it matters | If unknown | |------|----------------|------------| | MySQL version (5.7.x / 8.0.x / 8.4.x) | DDL algorithm support differs dramatically | Assume 5.7 (most restrictive) | | Storage engine | Only InnoDB supports online DDL | Assume InnoDB; WARN if MyISAM | | Table row count | Determines safe DDL vs tool-based threshold | Ask, or estimate via SHOW TABLE STATUS | | Table data + index size | Large tables need gh-ost / pt-osc | Ask, or estimate | | Active QPS on table | High-traffic amplifies MDL contention | Assume high-traffic (conservative) | | Replication topology | DDL on source replicates; COPY causes lag | Assume source-replica with GTID | | Maintenance window | Some operations need low-traffic periods | Assume none (zero-downtime required) | | Migration framework | Flyway/Liquibase/golang-migrate affect rollback | Detect from project files |

If database access is available, run:

SELECT VERSION();
SHOW TABLE STATUS WHERE Name = '' \G
SHOW CREATE TABLE  \G

STOP: Cannot determine whether the target is MySQL at all (e.g., migration file has no MySQL-identifiable syntax). Redirect to appropriate skill.

PROCEED: At least MySQL version and table name are known or conservatively assumed. Record all assumptions.

Gate 2: Scope Classification

Classify the request mode:

| Mode | Trigger | Output | |------|---------|--------| | review | User provides existing migration SQL/file | Safety analysis of provided DDL | | generate | User describes desired schema change | Migration SQL + safety analysis | | plan | User describes goal without specifics | Phased migration plan + rationale |

STOP: Request is not migration-related (e.g., query optimization, tuning). Redirect to mysql-best-practise.

PROCEED: Migration intent confirmed. Continue with depth selection.

Gate 3: Risk Classification

For each DDL statement, classify risk:

| Risk | Definition | Required action | |------|------------|-----------------| | SAFE | INSTANT or INPLACE+LOCK=NONE, small table, additive | Session guards sufficient | | WARN | INPLACE with restrictions, or table 1M–10M rows | Off-peak window + monitoring | | UNSAFE | COPY algorithm, >10M rows, or destructive DDL | gh-ost/pt-osc + staged rollout + rollback rehearsal |

STOP: Any UNSAFE item has no mitigation plan. Must provide tool-based alternative or phased approach before proceeding.

PROCEED: Every DDL statement has a risk level and corresponding mitigation.

Gate 4: Output Completeness

Before delivering output, verify all §9 Output Contract sections are present. If any section is missing, add it (even if "N/A — [reason]"). §9.9 Uncovered Risks must never be empty.

---

§3 Depth Selection

| Depth | When to use | Gates | References to load | |-------|-------------|-------|-------------------| | Lite | ≤3 DDL statements, all additive (ADD nullable column, CREATE INDEX) | 1–4 | None | | Standard | 4–15 statements, or any destructive/modifying DDL | 1–4 | ddl-algorithm-matrix.md | | Deep | >15 statements, or table >10M rows, or multi-step data migration | 1–4 | Both reference files |

Force Standard or higher when any signal appears: column type change, NOT NULL addition, PK modification, FK add/remove, charset change, data backfill, partition change, column rename/removal.

---

§4 Degradation Modes

When context is incomplete, degrade gracefully — never fabricate information.

| Available context | Mode | What you can do | What you cannot do | |-------------------|------|-----------------|-------------------| | Full (version, size, QPS, replicas) | Full | All checklist items, precise recommendations | — | | Version + size known, others unknown | Degraded | Full checklist with conservative assumptions | Precise lock-time estimates | | Only migration SQL, no context | Minimal | Static DDL analysis, flag all unknowns | Algorithm version-match, replication assessment | | No SQL (planning request) | Planning | Generate migration plan from requirements | Review existing SQL |

Hard rule: Never claim "SAFE" without evidence. In Degraded/Minimal mode, mark items as "SAFE (assumed — verify against production)" and list all assumptions in §9.9 Uncovered Risks.

---

§5 DDL Safety Checklist

Execute every item for each DDL statement. Mark SAFE / WARN / UNSAFE with evidence.

5.1 Algorithm & Lock Assessment

1. Algorithm selection — determine ALGORITHM=INSTANT / ALGORITHM=INPLACE / ALGORITHM=COPY for each ALTER TABLE.

Always specify explicitly; never rely on server default. When uncertain → load references/ddl-algorithm-matrix.md.

1. Lock level — specify LOCK=NONE when possible. If server rejects it → operation is not online → escalate to gh-ost/pt-osc.

1. MDL contention — even INSTANT acquires brief metadata lock. Long-running transactions block MDL → DDL queues → all subsequent queries queue. Mitigation: SET SESSION lock_wait_timeout = 3; pre-check: SELECT * FROM information_schema.innodb_trx WHERE trx_started 10M rows requiring COPY, use gh-ost (default) or pt-osc (if inbound FKs). Details in references/large-table-migration.md`.

---

§7 Anti-Examples

AE-1: Implicit algorithm — trusting server default

-- WRONG: server may silently choose COPY → outage on large table
ALTER TABLE users ADD COLUMN age INT;
-- RIGHT: explicit algorithm
ALTER TABLE users ADD COLUMN age INT DEFAULT NULL, ALGORITHM=INSTANT;

AE-2: NOT NULL on populated column without phased approach

-- WRONG: fails if any row has NULL
ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL;
-- RIGHT: add nullable → backfill → enforce NOT NULL (see §6)

AE-3: DDL without session guards

-- WRONG: blocks indefinitely if long transaction holds MDL
ALTER TABLE large_table ADD INDEX idx_date (created_at);
-- RIGHT:
SET SESSION lock_wait_timeout = 3;
ALTER TABLE large_table ADD INDEX idx_date (created_at), ALGORITHM=INPLACE, LOCK=NONE;

AE-4: DROP COLUMN without data backup

-- WRONG: data gone forever
ALTER TABLE users DROP COLUMN legacy_field;
-- RIGHT: backup → wait one release cycle → drop

AE-5: Native COPY on 100M-row table

-- WRONG: hours of exclusive lock
ALTER TABLE events MODIFY COLUMN payload MEDIUMTEXT;
-- RIGHT: use gh-ost (see references/large-table-migration.md)

AE-6: Style nitpick reported as migration risk

-- WRONG: "WARN — column name 'usr_nm' violates naming convention"
-- RIGHT: only flag naming if it causes functional problems

Extended anti-examples (AE-7 through AE-13) in references/migration-anti-examples.md.

---

§8 Migration Scorecard

Critical — any FAIL means overall FAIL

• [ ] Algorithm explicitly specified for every ALTER TABLE (ALGORITHM=INSTANT|INPLACE|COPY)
• [ ] Session guards set before every DDL (lock_wait_timeout, innodb_lock_wait_timeout)
• [ ] Rollback SQL provided for every phase (or irreversibility documented with backup plan)

Standard — 4 of 5 must pass

• [ ] DDL algorithm matches MySQL version capability (no INSTANT on 5.7 where unsupported)
• [ ] Replication impact assessed for each COPY/INPLACE operation
• [ ] Backward-compatible deployment order (additive before app, removal after app)
• [ ] Backfill uses PK-range batching, not LIMIT/OFFSET
• [ ] Validation SQL provided for each phase

Hygiene — 3 of 4 must pass

• [ ] Disk space impact estimated for COPY/gh-ost operations
• [ ] Idempotency guards present (IF NOT EXISTS / IF EXISTS)
• [ ] Post-deploy monitoring checks specified (replication lag, error rate)
• [ ] One DDL per migration file (or grouped ADD COLUMN justified)

Verdict: X/12; Critical: Y/3; Standard: Z/5; Hygiene: W/4. PASS requires: Critical 3/3 AND Standard ≥4/5 AND Hygiene ≥3/4.

---

§9 Output Contract

Every migration review MUST produce these sections. Omit none — write "N/A — [reason]" if inapplicable.

### 9.1 Context Gate
| Item | Value | Source |
| MySQL Version | 8.0.32 | SELECT VERSION() |
| ... | ... | ... |

### 9.2 Depth & Mode
[Lite/Standard/Deep] × [review/generate/plan] — [rationale]

### 9.3 Risk Assessment Table
| # | DDL Statement | Algorithm | Lock | Risk | Notes |

### 9.4 Execution Plan (Standard/Deep; "N/A — Lite" for Lite)

### 9.5 Migration SQL (with session guards, explicit algorithms)

### 9.6 Validation SQL

### 9.7 Rollback Plan (per-phase)

### 9.8 Post-Deploy Checks

### 9.9 Uncovered Risks (MANDATORY — never empty)
| Area | Reason | Impact | Follow-up |

Volume rules:

• UNSAFE: always fully detailed with mitigation
• WARN: up to 10; overflow to §9.9
• SAFE: summary row only
• §9.9 minimum: document all conservative assumptions made

Scorecard summary (append after §9.9):

Scorecard: X/12 — Critical Y/3, Standard Z/5, Hygiene W/4 — PASS/FAIL
Data basis: [full context | degraded | minimal | planning]

---

§10 Reference Loading Guide

| Condition | Load | |-----------|------| | Standard or Deep depth | references/ddl-algorithm-matrix.md | | Deep depth, or table >10M rows | references/large-table-migration.md | | Extended anti-example matching | references/migration-anti-examples.md |

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: johnqtcg
• Source: johnqtcg/awesome-skills
• License: MIT
• Homepage: https://johnqtcg.github.io/awesome-skills/

Install and usage instructions live in the source repository linked above.