Context & Why You Need It
The clock strikes 2:00 AM. Your phone rings off the hook with PagerDuty alerts reporting a storm of 500 errors across the backend cluster. Right after a late-night release, the checkout API went completely down. Opening the server logs, the culprit is immediately obvious: Unknown column 'discount_rate' in 'field list'.
As it turns out, Kubernetes just pulled a new container image containing code that calculates discount rates. However, the orders table in MySQL was still on the old schema because the developer forgot to run the manual SQL script before merging the pull request.
Hotfixing databases in the middle of the night is extremely dangerous. Pasting SQL scripts via SSH into a production database with hundreds of gigabytes of data can easily cause table locks or data loss. As systems scale across multiple environments (Dev, Staging, Prod), schema drift between application code and the database becomes a leading cause of release downtime.
Flyway solves this risk at its root. It turns all schema changes into versioned script files, automatically validates checksum hashes, and executes migrations right inside your CI/CD pipeline.
# The classic 2:00 AM error when code and database versions are out of sync
ERROR 1054 (42S22): Unknown column 'discount_rate' in 'orders'Installation
Flyway Community Edition is completely free. It supports execution via a standalone CLI, Docker container, or integration with Maven/Gradle. In modern CI/CD pipelines such as GitHub Actions or GitLab CI, using the Docker image or the binary CLI is the most lightweight approach.
Installing Flyway CLI on a Linux Server
Download the official release directly from the Maven repository:
# Download and extract Flyway CLI
cd /tmp
wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/10.10.0/flyway-commandline-10.10.0-linux-x64.tar.gz | tar -xvz
# Move to system directory
sudo mv flyway-10.10.0 /opt/flyway
sudo ln -s /opt/flyway/flyway /usr/local/bin/flyway
# Verify installed version
flyway -vSetting Up the Project Directory Structure
Organize your workspace to hold the configuration file and SQL migration scripts:
mkdir -p ~/mysql-flyway-migration/{sql,config}
cd ~/mysql-flyway-migrationDetailed Configuration
Flyway discovers migration files based on a specific naming convention using prefixes and a double underscore (__):
- V<Version>__<description>.sql: Versioned migration (e.g.,
V1.0__create_users_table.sql,V1.1__add_discount_to_orders.sql). Each file runs exactly once in sequential version order. - U<Version>__<description>.sql: Undo migration used for rollbacks (available only in Teams/Enterprise editions).
- R__<description>.sql: Repeatable migration. This file re-executes whenever its content changes, making it ideal for Views, Stored Procedures, or Functions.
Creating the Flyway Configuration File
Create config/flyway.conf to define the MySQL connection settings:
# config/flyway.conf
flyway.url=jdbc:mysql://127.0.0.1:3306/ecommerce_db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
flyway.user=flyway_user
flyway.password=SuperSecretPassword123!
flyway.locations=filesystem:sql
flyway.table=flyway_schema_history
flyway.baselineOnMigrate=true
flyway.baselineVersion=0.0
flyway.validateOnMigrate=true
flyway.cleanDisabled=trueSafety Note: Always enable flyway.cleanDisabled=true in production. This flag prevents the flyway clean command from accidentally wiping out all tables and data.
Creating Sample Migration Scripts
First, create the initial table schema in sql/V1.0__init_schema.sql:
-- sql/V1.0__init_schema.sql
CREATE TABLE IF NOT EXISTS users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
full_name VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
status VARCHAR(50) NOT NULL DEFAULT 'PENDING',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Next, create a migration file to add the discount column: sql/V1.1__add_discount_to_orders.sql:
-- sql/V1.1__add_discount_to_orders.sql
ALTER TABLE orders
ADD COLUMN discount_rate DECIMAL(5, 2) NOT NULL DEFAULT 0.00 AFTER total_amount,
ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;Integrating Flyway into a CI/CD Pipeline (GitHub Actions)
Instead of manual execution, configure a .github/workflows/db-migration.yml workflow to automatically migrate the schema before rolling out the application:
name: Database Migration Pipeline
on:
push:
branches:
- main
paths:
- 'sql/**'
jobs:
migrate:
runs-on: ubuntu-latest
steps:
- name: Checkout Code
uses: actions/checkout@v4
- name: Run Flyway Migration
uses: docker://flyway/flyway:10.10.0
with:
args: >-
-url=jdbc:mysql://${{ secrets.DB_HOST }}:3306/${{ secrets.DB_NAME }}?useSSL=false&allowPublicKeyRetrieval=true
-user=${{ secrets.DB_USER }}
-password=${{ secrets.DB_PASSWORD }}
-locations=filesystem:sql
-connectRetries=10
migrateTesting & Operations
Before running migrations on live systems, review pending scripts using CLI commands.
Checking Migration Status via CLI
# View detailed history and pending versions
flyway -configFiles=config/flyway.conf info
# Execute migrations
flyway -configFiles=config/flyway.conf migrateOnce the migrate command finishes, Flyway automatically creates the flyway_schema_history table in MySQL. This table records full metadata: the SHA-256 checksum of each script, execution duration in milliseconds, and the success status flag.
Querying the Metadata Table Directly in MySQL
-- Check the schema history tracking table
SELECT installed_rank, version, description, type, script, checksum, installed_on, execution_time, success
FROM flyway_schema_history
ORDER BY installed_rank DESC;Handling Checksum Mismatch Errors
A common scenario occurs when a developer inadvertently modifies an older V1.0__init_schema.sql file after it has already been applied to Staging or Production. On the next run, Flyway detects that the current file hash does not match the database checksum and aborts immediately:
ERROR: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version 1.0
- Applied to database : 1948294012
- Resolved locally : -492810481The golden rule: Never modify a migration script that has already been released. Always create a new version (e.g., V1.2__fix_orders_index.sql). If you only adjusted whitespace or comments and need to update the recorded hash without re-running the SQL statements, use the repair command:
# Resync DB checksums to match files on disk
flyway -configFiles=config/flyway.conf repairIntegrating Flyway into your CI/CD pipeline gives your team 100% control over schema history. No more forgotten manual SQL scripts, and no more midnight emergency calls caused by missing columns.
