Plan and Execute Database Migrations

This skill plans database migrations that minimize downtime, preserve data integrity, and always keep a rollback path available - covering schema migrations, data transformations, cross-platform migrations, and production deployment strategies. Every migration starts with a risk assessment: data-volume analysis to estimate migration time from table sizes and network throughput, dependency mapping across foreign keys, triggers, stored procedures, and application code, downtime-tolerance categorization (online, near-zero-downtime, or requiring a maintenance window), and a rollback strategy planned both forward and backward before execution begins. A pre-migration checklist queries database size and growth rate, checks active connections and transactions, and verifies replication lag where applicable.

Use it when planning a production database migration - schema change, platform move, or large-scale data transformation - that needs a tested rollback path and measurable data-integrity validation, not an ad hoc schema change on a small dev database with no rollback plan. It is not a substitute for its own closing rule: migrations should run during low-traffic periods with full backups and a tested rollback plan ready before execution, never as a live experiment.

Given a source and target database, it applies one of two migration strategy patterns: a blue-green migration (a preparation phase provisioning the target and replicating schema, a data-sync phase running initial bulk copy plus continuous replication with lag monitoring, and a cutover phase - maintenance mode, final sync, DNS switch, validation) or a gradual dual-write migration (a manager that always writes to the old database as primary, attempts a shadow write to the new database, validates consistency once out of shadow mode, and logs rather than fails on shadow-write errors). Schema changes follow a versioned migration script pattern (wrapped in a transaction, with a safety check confirming the correct database, CREATE TABLE IF NOT EXISTS with foreign keys and indexes, verification via SHOW CREATE TABLE, and a recorded entry in a schema_migrations table), and large-table alterations use pt-online-schema-change with tuned chunk size, chunk time, and load thresholds to avoid blocking production traffic.

-- Migration script template with safety checks
-- Migration: 20241201_add_user_preferences
-- Author: Migration Team
-- Description: Add user preferences table with foreign key to users

START TRANSACTION;

-- Safety check: Ensure we're on correct database
SELECT DATABASE() as current_db;

-- Create table with proper constraints
CREATE TABLE IF NOT EXISTS user_preferences (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    preference_key VARCHAR(100) NOT NULL,
    preference_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_preference (user_id, preference_key),
    INDEX idx_user_preferences_user_id (user_id)
);

-- Verify table creation
SHOW CREATE TABLE user_preferences;

-- Record migration
INSERT INTO schema_migrations (version, applied_at) 
VALUES ('20241201_add_user_preferences', NOW());

COMMIT;

Validation runs a dedicated framework that compares row counts between source and target per table and performs sample-based checksum validation (MD5 hashes over a random sample of up to 1,000 rows, flagging any mismatched rows). Rollback is scripted end-to-end: a checkpoint step takes a full mysqldump backup plus a Kubernetes ConfigMap snapshot of the app configuration, and an emergency rollback scales the application to zero replicas, restores the database from the checkpoint, reapplies the saved configuration, and scales the application back up. Migration performance is tuned via MySQL parameters (an 8G InnoDB buffer pool, relaxed commit and binlog flushing, a larger bulk-insert buffer, interleaved auto-increment locking, and parallel read threads), and progress is tracked in real time - percent complete, rows per second, and ETA - with an alert firing if the migration rate falls below half the expected rate.

Database and platform engineers running production migrations who need a tested, measurable, reversible process - not a one-way script - covering everything from risk assessment and strategy selection through data validation, rollback automation, and live progress monitoring.