# MySQL Schema Migration Best Practice > A guide to MySQL schema migration best practice. Adela | 2025-12-09 | Source: https://www.bytebase.com/blog/mysql-schema-migration-best-practice/ --- Schema changes are a normal part of software development. Columns get added, data types change, indexes appear or disappear. But in MySQL, even a simple `ALTER TABLE` can block queries, slow down replicas, or cause downtime if not planned well. This guide explains a practical way to manage MySQL schema migrations - simple to follow and focused on the MySQL behaviors that matter in real production environments. ## Core Best Practices These ideas apply broadly to database migrations. They form a reliable foundation before considering MySQL-specific behavior. ### Keep schema changes traceable Teams need a clear record of how the database evolves. Whether you use Git, a migration tool, or a structured changelog, the key is: > **Every schema change should be recorded, reviewable, and reproducible.** This prevents hidden production changes and environment drift. ### Use small, incremental, backward-compatible steps A safer approach is the known pattern: 1. **Expand** - add new columns or tables without removing anything 2. **Migrate** - backfill data and update application logic 3. **Contract** - remove old fields only after everything is stable The contract step usually happens days later because dropping a column is irreversible. Teams wait until all parts of the system clearly use the new structure and real traffic shows no issues. ### Automate checks and workflows Automation reduces mistakes and increases consistency: - **SQL review rules:** catch unsafe SQL before execution. - **CI checks:** run automated tests and static checks on migration scripts. - **Staging verification:** confirm the migration behaves correctly with realistic data. - **Consistent environment promotion:** apply migrations in the same order across Dev -> Test -> Prod. These steps create a predictable path from development to production. ## Key MySQL Behaviors to Know MySQL has several characteristics that directly affect schema migrations. Understanding these early avoids surprises. - **Some schema changes rebuild the whole table** - Certain `ALTER TABLE` operations create a new copy of the table, which can slow things down or block writes. - **Metadata locks can block queries** - A schema change must wait for ongoing queries to finish. While waiting, it can block new queries behind it. - **Large changes can cause replica lag** - Big ALTERs or heavy backfills often make replicas fall behind, affecting reads and failover. - **Some MySQL features are harder to modify** - Examples include ENUM changes, JSON indexes, and FOREIGN KEYS on large tables. ## A Practical Migration Workflow for MySQL This workflow reflects what developers actually do, with MySQL’s behavior in mind. ### 1 Plan the change carefully Before writing SQL, understand the impact: - Will the operation **rebuild** the table? - How **large** is the table in production? - Are there **long-running queries** that could block the migration? - How will **replicas** respond? - Can the work be **split** into smaller, safer steps? A few minutes of planning prevents many issues later. ### 2 Write simple and predictable migration scripts Good migration scripts are easy to understand and review: - One logical change at a time - Clear and descriptive naming - Avoid mixing schema and heavy data updates - Break changes into steps when needed (for example, add column -> backfill -> update code) Older MySQL versions often rebuild tables when adding a column with a default value, so separating operations reduces risk. ### 3 Test the migration with real impact in mind Testing should consider both correctness and performance: - Run first in Dev, then in Staging - Use staging data that matches production size - Measure how long the migration takes - Observe whether replicas fall behind - Test application behavior on the changed tables A change that seems instant on a local machine may take minutes - or more - on real data. ### 4 Deploy with caution and observability During deployment: - Promote the migration across environments in order - Check for blocking queries before running DDL - Monitor: - metadata lock waits - slow queries - replica lag Because MySQL DDL is not transactional, rolling back a schema change is usually not possible. To reduce risk: - Use roll-forward migrations to fix issues quickly - Keep reliable backups and test restore procedures - Make data migrations reversible when possible A careful rollout with a clear recovery plan makes deployments much safer. ## Tooling and Zero-Downtime Options Some tools exist specifically to work around MySQL’s migration challenges. ### [pt-online-schema-change](https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html) Creates a shadow table and copies data gradually, reducing locking. Useful for large and busy tables. ### [gh-ost](https://github.com/github/gh-ost) Uses binary logs instead of triggers and works well on high-write workloads. Often safer for large-scale online schema changes. ### [Native MySQL Online DDL](https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html) MySQL 8.0 supports more fast operations, but testing is still required to avoid unexpected behavior. ### Migration frameworks and workflow tools These tools help manage versioning, ordering, and execution of schema changes: - [Flyway](https://flywaydb.org/) Tracks versions and applies schema changes in order. - [Liquibase](https://www.liquibase.org/) Uses declarative change sets and supports rollback logic and more structured migration workflows. - [Bytebase](/) Provides a workflow for schema changes, SQL review, and environment promotion, supporting both GUI-based changelog and GitOps modes. Also integrates with gh-ost for safer online schema changes on large tables. ## Conclusion Safe MySQL schema migration comes from understanding how MySQL behaves and following a clear, consistent workflow. With small changes, proper testing, and basic awareness of locking and replication, teams can update their schemas with far less risk and uncertainty.