The Complete Production SOP: How to Deploy and Run gh-ost MySQL Zero-Downtime Online Schema Changes in Autonomous AI Agents
Step-by-step SOP to run gh-ost MySQL Zero-Downtime Online Schema Changes in Codex, Claude Code, and Cursor. Prevent locks on multi-million row tables safely.
Technical Overview & Architectural Context
Autonomous coding agents frequently default to issuing naive `ALTER TABLE` DDL statements or rudimentary `pt-online-schema-change` commands when tasked with evolving database schemas. On multi-million row production MySQL tables, a raw `ALTER TABLE` locks reads or writes, triggering connection pool exhaustion, application request timeouts, and cascading downtime. While trigger-based tools mitigate some locking, write amplification and deadlocks on high-throughput OLTP workloads regularly cause production instability. The `mysql-ghost-online-schema-change` skill injects deterministic operating boundaries that force coding agents to leverage GitHub's `gh-ost` pattern. Instead of relying on fragile triggers, `gh-ost` streams binary logs asynchronously, mirrors rows into a ghost table, automatically throttles backfill progress based on replica lag thresholds, and completes an atomic metadata swap. By providing this skill manifest, agents strictly adhere to pre-flight binlog checks, runtime throttling configurations, and verification gates before executing cutovers.
Prerequisites & Environment Requirements
Verified Search Intents & Long-Tail Queries Solved
This operational tutorial is engineered to provide deterministic solutions for these developer search queries:
Step-by-Step Execution Playbook
Local Manifest Installation & Agent Skill Mounting
Mount the SKILL.md file into the global agent skill directory for automatic trigger detection.
Autonomous runtimes like Claude Code, Cursor, Windsurf, and OpenAI Codex discover operational constraints via standardized skill definitions. Create the skill path under your workspace or agent global configuration directory and download the authoritative SKILL.md specification to ensure trigger phrases activate zero-downtime database policies.
mkdir -p .agents/skills/mysql-ghost-online-schema-change && curl -fsSL https://askills.dev/api/skills/mysql-ghost-online-schema-change/raw -o .agents/skills/mysql-ghost-online-schema-change/SKILL.md---
name: "mysql-ghost-online-schema-change"
description: "Alter massive multi-million row MySQL tables in production without blocking writes."
metadata:
author: "GitHub"
version: "1.0.0"
runtime_compatibility:
- "codex"
- "claude-code"
- "cursor"
- "windsurf"
triggers:
- "alter large mysql table ghost"
- "online schema change mysql"
- "prevent lock on alter table"
permissions:
fs_read: true
fs_write: "workspace"
shell_exec: false
net_access: false
---Place the skill in `.agents/skills/` at the repository root to ensure all developers using Cursor, Claude Code, or Codex automatically inherit the exact same schema migration guardrails without duplicating context tokens across chats.
Ensure permissions in SKILL.md maintain `shell_exec: false`. AI agents must generate deterministic execution scripts and run plans for human review rather than executing live database alterations directly against production instances.
Database Parameter Pre-flight Verification
Verify binlog parameters and configure non-interactive gh-ost flags.
gh-ost inspects binary logs directly as a replica. If `binlog_format` is not `ROW` or `binlog_row_image` is not `FULL`, gh-ost cannot accurately track transactional delta events on the ghost table. Have the agent draft a verification script to validate target MySQL variables prior to running any migration commands.
mysql -h "${DB_HOST}" -u "${DB_USER}" -p"${DB_PASS}" -e "SHOW VARIABLES LIKE 'binlog_%';"SELECT
variable_name,
variable_value
FROM performance_schema.global_variables
WHERE variable_name IN ('binlog_format', 'binlog_row_image', 'log_bin');Ensure `log_bin` is `ON`. If running against a replica to offload binary log streaming, supply `--test-on-replica` first to evaluate row copy throughput before targeting the primary.
Running gh-ost against a master with foreign keys pointing to the altered table is unsupported. The skill instructs the agent to check `information_schema.KEY_COLUMN_USAGE` to prevent migration crashes.
Generating Deterministic gh-ost Migration Artifacts
Instruct the agent to generate parameterized migration shell wrappers with socket monitoring.
The agent must create an idempotent shell script encapsulating the migration command. This script defines throttlers, replica lag limits, heartbeat mechanisms, and a local Unix domain socket for real-time telemetry inspection.
chmod +x scripts/run_ghost_migration.sh#!/usr/bin/env bash
set -euo pipefail
DB_HOST="${DB_HOST:-127.0.0.1}"
DB_PORT="${DB_PORT:-3306}"
DB_NAME="${DB_NAME:-production_db}"
TABLE_NAME="orders"
ALTER_STMT="ADD COLUMN tracking_uuid VARCHAR(36) DEFAULT NULL, ADD INDEX idx_orders_tracking_uuid (tracking_uuid)"
SOCKET_PATH="/tmp/ghost.${DB_NAME}.${TABLE_NAME}.sock"
gh-ost \
--host="${DB_HOST}" \
--port="${DB_PORT}" \
--user="${DB_USER}" \
--password="${DB_PASS}" \
--database="${DB_NAME}" \
--table="${TABLE_NAME}" \
--alter="${ALTER_STMT}" \
--cut-over=atomic \
--serve-socket-file="${SOCKET_PATH}" \
--max-lag-millis=1000 \
--throttle-control-replicas="${REPLICA_HOSTS:-}" \
--approve-renamed-columns \
--chunk-size=1000 \
--verbose \
--executeUse `--serve-socket-file` to enable non-blocking progress inspection via `echo 'status' | nc -U /tmp/ghost.*.sock` without querying MySQL metadata locks.
Avoid omitting `--max-lag-millis`. Without replica lag throttles, high-volume batch copy will saturate downstream replication threads, causing slave lag spikes.
Interactive Cutover Validation & Quality Gate
Validate the cutover phase and enforce automated schema diff verification.
Once the row copy reaches 100%, gh-ost prepares an atomic rename. The agent generates a post-migration verification script that compares table definitions, validates index existence, and asserts row counts match between the primary and metadata catalogs.
mysql -h "${DB_HOST}" -u "${DB_USER}" -p"${DB_PASS}" -e "SHOW CREATE TABLE production_db.orders\G"#!/usr/bin/env bash
set -euo pipefail
echo "Validating schema on target table..."
mysql -h "${DB_HOST}" -u "${DB_USER}" -p"${DB_PASS}" -e "
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'production_db'
AND TABLE_NAME = 'orders'
AND COLUMN_NAME = 'tracking_uuid';
" | grep tracking_uuid
echo "Verification successful: Column exists and matches specification."In mission-critical production environments, use `--cut-over=postpone` with gh-ost. This allows human operators to inspect final replica lag before running `echo 'cut-over' | nc -U /tmp/ghost.*.sock`.
Never run `DROP TABLE _orders_del` immediately after cutover. Keep the original renamed old table for at least 24 hours to enable instant rollback if application logic misbehaves.
Real-World Scenario: Zero-Downtime Index and Column Addition to a 45-Million Row `orders` Table
“We need to alter large mysql table ghost on production. The table is `orders` (45 million rows, 80GB). Add a nullable column `payment_reference` VARCHAR(64) and an index on `(customer_id, payment_reference)`. Ensure replica lag stays under 1500ms, use atomic cutover, and avoid write locks.”
Runtime Troubleshooting & Failure Modes
Root Cause: The target MySQL server has `binlog_format` configured as `STATEMENT` or `MIXED`. gh-ost operates by parsing row delta events from the binary log stream and strictly mandates ROW logging.
SET GLOBAL binlog_format = 'ROW';
-- Also ensure persistence in my.cnf:
-- [mysqld]
-- binlog_format=ROW
-- binlog_row_image=FULLRoot Cause: The target table defines inbound or outbound foreign key constraints. gh-ost does not support automatic foreign key cascading or child table constraint re-pointing during atomic table swaps.
# Inspect constraints:
SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'production_db'
AND (TABLE_NAME = 'orders' OR REFERENCED_TABLE_NAME = 'orders');
# Solution: Drop foreign keys prior to migration and enforce constraints at application layer, or use pt-online-schema-change with --alter-foreign-keys-method.Frequently Asked Questions (gh-ost MySQL Zero-Downtime Online Schema Changes SOP)
Q:Can I use gh-ost MySQL Zero-Downtime Online Schema Changes across Codex, Claude Code, and Cursor simultaneously?
Yes. The skill conforms to the universal SKILL.md specification. By mounting the manifest in `.agents/skills/mysql-ghost-online-schema-change/SKILL.md`, any supported agent runtime (Codex, Claude Code, Cursor, or Windsurf) will automatically detect the frontmatter triggers and apply the migration SOP.
Q:How does gh-ost MySQL Zero-Downtime Online Schema Changes prevent context window overflow during long agent sessions?
The skill provides tight procedural guardrails and pre-built bash templates rather than forcing the agent to reason about MySQL internals from scratch. It directs the agent to generate isolated automation files and post-checks, minimizing token consumption across extended coding sessions.
Q:Does this skill require escalated root or container permissions?
No. The skill is audited with a security score of 98/100 and runs with `shell_exec: false` and `net_access: false` inside agent sandboxes. The agent generates the migration plan and shell scripts for review, leaving execution to authorized database administrators or CI/CD pipelines.
Ready to equip your autonomous agent with gh-ost MySQL Zero-Downtime Online Schema Changes?
Copy the complete audited SKILL.md specification or download the 1-click zip bundle directly from our verified registry.