Managing MySQL Schemas with Skeema

Your MySQL schema lives in Skeema’s repository format — one CREATE TABLE file per table, one directory per schema — and skeema push has been working fine on small tables. Then someone adds an index to the events table, which is 900 GB, and skeema push runs a direct ALTER TABLE. Even with InnoDB online DDL, the build takes hours, holds undo, and lags every replica by the full duration, because the replica applies the same ALTER single-threaded after the primary finishes. Skeema is doing exactly what it was configured to do; it just was not configured for tables of that size. This guide sets Skeema up for production MySQL: how to bring an existing schema under management, how to read skeema diff, and how to route large changes through an online schema change tool so replicas stay current. It applies the approach in Declarative Schema Management to MySQL.

How Skeema Should Execute Each ALTER Decision tree. If the change is supported by ALGORITHM=INSTANT, run it directly. If not, and the table is below the size threshold, run it directly with ALGORITHM=INPLACE, LOCK=NONE where possible. If the table is above the threshold, run it through alter-wrapper with gh-ost. How Skeema Should Execute Each ALTER Can the change run as ALGORITHM=INSTANT? yes no Direct ALTER, INSTANT Table smaller than the wrapper threshold? yes no Direct ALTER, INPLACE, LOCK=NONE alter-wrapper runs gh-ost
Skeema can make this decision per table with alter-wrapper-min-size — small tables get a direct ALTER, large ones get gh-ost.

Symptom / Error Signatures

These signals show Skeema is running changes in a way that will hurt a busy MySQL fleet:

  • skeema push output shows a plain ALTER TABLE against a table in the tens or hundreds of gigabytes.
  • Replicas report rising Seconds_Behind_Source (or Seconds_Behind_Master on older versions) for the duration of a push.
  • The processlist on the primary shows altering table for many minutes, and on replicas the SQL thread is stuck on the same statement.
  • skeema diff produces changes on a freshly initialised repository — typically from AUTO_INCREMENT values, or from tables altered manually after skeema init.
  • skeema push stops with a message that unsafe changes were detected and --allow-unsafe is required.

That last one is not a problem: it is Skeema’s own guardrail against destructive changes, covered in preventing destructive changes in declarative diffs.

Root Cause Analysis

Skeema computes differences between the CREATE statements in the repository and the live schema, then generates ALTER TABLE statements to converge them. By default it executes those statements directly on the target. That is ideal for the common case and wrong for the heavy one, for reasons rooted in MySQL replication rather than in Skeema:

Execution mode Primary impact Replica impact
ALGORITHM=INSTANT (8.0+) metadata only, milliseconds replays in milliseconds
ALGORITHM=INPLACE, LOCK=NONE concurrent DML allowed; heavy I/O; online log must hold concurrent changes replays the whole ALTER on the SQL thread, lag equals build time
ALGORITHM=COPY writes blocked for the copy same, plus the block
gh-ost via alter-wrapper row copy in small chunks, throttled replicates as ordinary row changes, lag stays low

The replica column is the decisive one. An online INPLACE ALTER does not block writes on the primary, but it arrives at the replica as a single statement that the replica must execute before applying anything that came after it. A three-hour build becomes three hours of replica lag, which breaks read-your-writes on any read traffic you route to replicas. gh-ost avoids that by performing the change as a stream of small row copies plus binlog-driven catch-up, then a brief cut-over. Skeema supports this directly: its alter-wrapper option replaces the direct ALTER with an external command, and alter-wrapper-min-size restricts the wrapper to tables above a size threshold.

Replica Lag: Direct INPLACE ALTER vs gh-ost Timeline over 180 minutes. With a direct INPLACE ALTER, the primary builds for 90 minutes; the replica only starts replaying after the primary commits and lags for another 90 minutes. With gh-ost, row copy runs throttled on the primary for about 172 minutes while the replica applies ordinary row changes and stays current, followed by a seconds-long cut-over. Replica Lag: Direct INPLACE ALTER vs gh-ost Primary, direct INPLACE build Replica, direct replaying the ALTER · lag grows Primary, gh-ost throttled chunked copy Replica, gh-ost current, applying row changes as they come 0 30 min 60 min 90 min 120 min 150 min 180 min work on primary replica lag cut-over
Online on the primary is not online on the replica: a direct ALTER replays as one long statement, while gh-ost's copies replicate as normal traffic.

Immediate Mitigation

1. Stop pushing large tables directly. If a large push is running now, do not kill the replica’s SQL thread — let the ALTER finish and route reads to the primary or to replicas that have caught up. Before the next push, confirm what would run:

# Shell · operator workstation · read-only credentials are sufficient for diff
# skeema diff exits 1 when differences exist, 0 when in sync
skeema diff production --allow-unsafe=0

2. Configure the wrapper for large tables. Put gh-ost behind alter-wrapper with a size threshold so small tables still use fast direct ALTERs. Skeema substitutes variables such as {HOST}, {SCHEMA}, {TABLE} and {CLAUSES} into the command.

# INI · .skeema at the repository root, production section
# WARNING: gh-ost needs a user with REPLICATION CLIENT, REPLICATION SLAVE and ALTER privileges.
[production]
host=prod-mysql-primary.internal
alter-wrapper="/usr/local/bin/gh-ost --execute --alter {CLAUSES} --database={SCHEMA} --table={TABLE} --host={HOST} --user=ghost --password=$GHOST_PASSWORD --max-lag-millis=1500 --chunk-size=1000 --cut-over=default --postpone-cut-over-flag-file=/tmp/ghost-postpone-{TABLE}"
alter-wrapper-min-size=5G
alter-algorithm=inplace
alter-lock=none

3. Bring drifted tables back under management. If skeema diff shows changes nobody intended, decide which side is right. If production is right, run skeema pull production to rewrite the repository files from the live schema, commit the result, and review it like any other change.

4. Push in a controlled window. Run skeema push production with the wrapper configured; for tables above the threshold, gh-ost starts and can be throttled or postponed while it runs. The operational details of gh-ost throttling are covered in throttling gh-ost on replica lag.

Permanent Fix / Long-Term Pattern

A production-grade Skeema setup has four parts. The repository is the source of truth, initialised once with skeema init and kept honest by skeema pull whenever an out-of-band change must be adopted. Every pull request runs skeema lint and skeema diff against a staging environment in CI, and the diff output is posted for review. Execution policy lives in .skeema: alter-algorithm and alter-lock for direct ALTERs, alter-wrapper plus alter-wrapper-min-size for large tables, and allow-unsafe=0 so destructive changes require a one-off command-line override. And pushes run from the pipeline, not from laptops, with the same lock-wait discipline as any migration — lock_wait_timeout lowered for the session so an instant ALTER cannot queue behind a long transaction, per diagnosing “Waiting for table metadata lock”.

-- MySQL 8.0 · repository file shop/orders.sql · Skeema compares this, it is never run as-is in production
-- WARNING: removing a column here produces an unsafe change that push refuses without --allow-unsafe.
CREATE TABLE `orders` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint unsigned NOT NULL,
  `region` varchar(32) DEFAULT NULL,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `idx_orders_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ROLLBACK PATH: revert this file and review the resulting skeema diff before pushing.

For sequencing across deploys — adding a column the code needs before the code ships, removing one only after the code stops using it — Skeema follows the same rule as every declarative tool: stage the repository across pull requests so each push is one safe expand or contract step. The decision between the wrapper tools is discussed in gh-ost vs pt-online-schema-change; tables with foreign keys generally need pt-online-schema-change, since gh-ost does not support them.

Where Push Time Goes on a Mixed Change Set Stacked bars comparing total push time for a change set touching three small tables and one 900 GB table. Direct ALTERs for everything: small tables take 1 minute, the large table 90 minutes, and replicas lag 90 minutes after. With alter-wrapper on the large table: small tables 1 minute, gh-ost copy 175 minutes, replica lag near zero. Where Push Time Goes on a Mixed Change Set all direct 90 min 90 min wrapper ≥ 5G 175 min small tables large table on primary replica lag after
The wrapper actually makes the big change slower — but it moves the cost off the replicas, which is what users of read replicas actually feel.

Verification Checklist

Frequently Asked Questions

Does Skeema run gh-ost for every change once alter-wrapper is set? Only for tables at or above alter-wrapper-min-size when that option is set. Smaller tables continue to receive direct ALTER TABLE statements, using the alter-algorithm and alter-lock clauses you configured.

What does skeema pull do? It rewrites the repository’s CREATE files from the live database, adopting whatever is currently in production. Use it to bring an out-of-band change under management, then review and commit the resulting file changes like any other diff.

Can Skeema handle tables with foreign keys? Skeema itself can diff and alter them, but if you route large tables through gh-ost, foreign keys are a problem because gh-ost does not support them. Use pt-online-schema-change as the wrapper for such tables, or avoid foreign keys on very large, frequently altered tables.

Why does skeema diff show AUTO_INCREMENT changes? It normally ignores table-level AUTO_INCREMENT counters unless configured otherwise. If you see them, check your configuration and Skeema version; the counter is runtime state, not schema, and should not be managed.

Related Articles