Skip to content

migrate.md

Latest commit

 

History

History
903 lines (632 loc) · 86.8 KB

migrate.md

File metadata and controls

903 lines (632 loc) · 86.8 KB
Raw

Migrate subcommand

The migrate command applies ALTER TABLE statements to large tables without blocking reads or writes. It creates a shadow copy, streams binlog changes, and performs an atomic cutover via RENAME TABLE.

Basic usage:

spirit migrate --host mydb:3306 --username root --password secret \
               --database mydb --statement "ALTER TABLE users ADD COLUMN email VARCHAR(255)"

Configuration

checkpoint-max-age

  • Type: Duration
  • Default value: 168h (7 days)

The maximum age of a checkpoint before Spirit refuses to resume from it. When Spirit starts and finds an existing checkpoint from a previous run, it checks how old the checkpoint is. If the checkpoint is older than this value, Spirit will discard it and start a fresh migration instead of attempting to resume.

This protects against resuming from very stale checkpoints where replaying the accumulated binary log changes would take longer than starting the migration from scratch.

Resuming across Spirit binary versions

⚠️ Resuming a migration with a different Spirit binary version than the one that wrote the checkpoint is not supported and may produce incorrect results.

When Spirit reads a checkpoint, it relies on the columns of the checkpoint table matching the columns the current binary expects:

  • If the checkpoint table is missing a column the current binary expects (typically, a newer binary resuming a checkpoint written by an older one), the resume read fails, and Spirit logs a warning and starts a fresh migration. Progress from the previous binary version is discarded.
  • If the checkpoint table only has extra or reordered columns (typically, an older binary resuming a checkpoint written by a newer one), the read succeeds, because Spirit selects the columns it knows by name. Spirit cannot detect the mismatch, and resumes from a checkpoint whose extra fields it ignores.
  • If the checkpoint table schema is unchanged but the meaning of stored values has changed between versions (for example, a watermark format change, a routing-policy change, or a new applier behavior), Spirit cannot detect the mismatch. The resume will silently succeed and the new binary will reinterpret the old checkpoint, which can produce incorrect results.

Operationally, this means:

  • Do not upgrade or downgrade the Spirit binary while a migration is in progress.
  • If you must change Spirit versions, let the in-flight migration finish first, or accept the lost progress and start fresh with the new version.
  • For long-running migrations that span planned binary upgrades, plan to drain the migration before the upgrade window.
Upgrading from a version with --table/--alter

Resume requires the statement text to match the checkpoint exactly. Versions that still had --table and --alter stored the statement they composed from that pair (ALTER TABLE `t` <alter>, with the table name back-quoted), so a migration started on such a version will not resume once you upgrade: the mismatch is treated as definitive and Spirit starts a fresh copy, discarding the checkpoint and the _new table.

Drain any in-flight migrations before upgrading past the release that removed those flags.

conf

  • Type: String
  • Default value: ``

Optional path to INI file containing host, port, username, password, database and tls settings to be used when connecting to MySQL. Spirit will only interpret the [client] section within the INI file and ignore all other sections. Values for --host, --username, --password, --database, --tls-ca and tls-mode provided via command line arguments to Spirit take precedence over what is provided in file.

Expected INI file format:

[client]
user=$username
password=$password
host=$hostname
port=$port
tls-ca=$tls-ca
tls-mode=$tls-mode

database

  • Type: String
  • Default value: test

The database that the schema change will be performed in.

defer-cutover

  • Type: Boolean
  • Default value: false

The "defer cutover" feature makes spirit wait to perform the final cutover until the "sentinel" table has been dropped. This is similar to the --postpone-cut-over-flag-file feature of gh-ost.

The defer cutover feature will not be used and the sentinel table will not be created if the schema migration can be successfully executed using ALGORITHM=INSTANT (see "Attempt Instant DDL" in the project README).

Deferring a cutover is decided when Spirit starts, and the sentinel table only holds a run that was started with defer-cutover:

  1. Pass defer-cutover at startup. It cannot be turned on for a run that is already in progress.
  2. Spirit creates the sentinel table. When a migration starts fresh with defer-cutover, Spirit creates _spirit_sentinel in the same schema as the table being altered, before the row copy begins.
  3. The cutover is held for as long as the table exists. Spirit blocks before the cutover until the operator drops the sentinel table, then proceeds. Spirit never drops the sentinel table itself. After 720 hours (30 days) of waiting it exits with an error.

A run started without defer-cutover never waits on the sentinel table. Creating _spirit_sentinel by hand while it runs has no effect, and neither does a sentinel table left behind by an earlier deferred migration: the run cuts over without waiting.

A migration resumed from checkpoint with defer-cutover waits again if the sentinel table still exists. A resume does not create the table, so dropping it before restarting Spirit lets the resumed migration complete the cutover without waiting. A migration resumed without defer-cutover does not wait, even if the sentinel table still exists.

If you start a migration and realize you forgot to set defer-cutover, cancel it, create _spirit_sentinel in the schema, and restart with defer-cutover. The migration resumes from its checkpoint and waits for you to drop the table.

Initial and continuous verification

Spirit uses the same configured checker in two phases:

  1. The initial checksum runs after copy-rows completes. Cutover requires a successful, clean verification.
  2. Continuous verification runs while Spirit waits for the sentinel to be dropped. Dropping the sentinel interrupts background verification; it does not require another complete pass. Verification errors and cancellation during a snapshot pass that observed mismatches abort cutover.
copy rows → initial checksum → wait on sentinel (continuous verification) → cutover

By default both phases use the lockless checksum: optimistic reads with hot-range splitting and bounded retries, the configured thread count, throttling, and autoscaling. With --legacy-checksum, both phases use the legacy snapshot checksum, including its brief setup locks. Repair policy is the same either way: a confirmed stable divergence is repaired from the source and re-verified on a later pass, and a range that never verifies clean fails the migration rather than cutting over. Background lockless passes may defer changing ranges; the initial gate must verify a complete clean pass.

The first background pass starts one hour after continuous verification begins; subsequent passes start at least one hour apart. Replication continues flushing between passes. Continuous verification runs automatically whenever a sentinel causes Spirit to wait. While a pass is active, the status block includes checksum progress and reports load throttling. Interval waits are not reported as throttled.

Once continuous verification starts, checksum resume progress is discarded. After an interruption, Spirit keeps its copy checkpoint but repeats the full initial checksum, even if background verification found no differences. Background walker positions are never treated as proof of completed verification.

Before continuous verification starts, an interrupted initial checksum does resume from where it got to, under either algorithm. The persisted watermark covers only the prefix that was read on both sides and observed equal: a range that had to be repaired, or that was still unresolved, parks the watermark below itself so the resumed run re-verifies it.

enable-experimental-foreign-keys

  • Type: Boolean
  • Default value: false

Experimental. Requires MySQL 9.7 or later.

Use --enable-experimental-foreign-keys to alter a table that has foreign keys to other tables (a child table). Without the flag, Spirit refuses any table with foreign keys.

Spirit copies changes from the binary log. Before MySQL 9.6, InnoDB applied foreign key cascades (ON DELETE CASCADE, ON UPDATE CASCADE, SET NULL) internally, and they were not written to the binary log. Spirit could not see them, so the new table would miss them. MySQL 9.6 moved foreign key enforcement to the SQL layer, which writes cascaded changes to the binary log as row events of the child table. Spirit requires 9.7 or later, and refuses the table when the server was started with innodb_native_foreign_keys=ON, which restores the old behavior.

What the flag supports:

  • ALTERs of a child table. The table keeps its foreign keys, under the same names.
  • DROP FOREIGN KEY in the ALTER, and renaming a column a foreign key is on.

What it still refuses:

  • A table that another table's foreign key references (a parent table), including a table that references itself. The cutover's RENAME TABLE would move the other table's foreign key to the old table.
  • ADD FOREIGN KEY (or a column with an inline REFERENCES). Spirit adds the foreign keys to the new table without checking its rows, which is only safe for foreign keys the rows already satisfy.
  • DROP CONSTRAINT of a foreign key. Use DROP FOREIGN KEY.

How it works:

  • The new table has no foreign keys while Spirit copies rows into it. With a foreign key on it, MySQL would check the application's changes to a parent table against the copy's rows too. With ON DELETE RESTRICT (the default), the application could not delete a parent row while the copy still had one of its child rows, even after deleting that child row from the table.

  • Before the copy, Spirit adds the foreign keys to the empty new table, applies the ALTER, and drops them again. MySQL then checks the ALTER against the foreign keys as it checks a native ALTER: for example, it refuses a column type change a foreign key cannot take. The indexes the foreign keys use stay.

  • At cutover, with the table lock held and every change applied, Spirit adds the foreign keys to the new table with foreign_key_checks off. MySQL adds them as a metadata change, without checking the rows, because the rows are a copy of rows that satisfy the same foreign keys. That is only a metadata change when the new table has an index each foreign key can use. Without one, MySQL builds the index under the lock, even with LOCK=NONE. Setup leaves those indexes in place, but Spirit does not rely on predicting MySQL's choice. Before it takes the lock, it creates an empty copy of the new table and empty copies of the parent tables, adds the same foreign keys to the copy in the same way, and refuses the cutover if that changed the copy's indexes. Under the lock, it refuses unless the foreign keys and the new table's columns and indexes are the ones it probed, and refuses if adding them changed the indexes. Spirit checks that the new table's foreign keys match the original's, and runs the RENAME TABLE. Still under the lock, Spirit drops the old table's foreign keys and gives the table's foreign keys their original names back. Foreign key names are unique per schema, so the copies have temporary names until then: _<name>_new, or _<table>_new_ibfk_<n> for a name MySQL generated, which the RENAME TABLE restores by itself. With skip-drop-after-cutover, the old table is kept without foreign keys, so it does not hold back changes to the parent tables.

  • LOCK TABLES also locks the parent tables for reading, so the application cannot write to a parent table during the cutover. Adding the foreign keys, the RENAME TABLE and the renames wait for every open transaction that has read or written a parent table. Spirit's force-kill covers the parent tables as well as the table: it kills those transactions once a statement has waited the force-kill-after delay. The same applies to the DDL on the new table before the copy.

  • If Spirit cannot drop the old table's foreign keys or restore the names under the lock, it tries again after the lock is released, and logs an error if that also fails. The foreign keys are enforced under either name. If Spirit stops in between, for example because it is killed, the old table keeps its foreign keys and the table keeps the temporary names. To fix that, drop the old table (or its foreign keys) and rename each foreign key: drop it and add it back under the original name in one ALTER, with the checks off, which MySQL applies in place without checking the rows:

    SET SESSION foreign_key_checks = 0;
ALTER TABLE child DROP FOREIGN KEY _fk_child_parent_new,
  ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE,
  ALGORITHM=INPLACE, LOCK=NONE;
SET SESSION foreign_key_checks = 1;

  • If a cutover attempt fails, Spirit drops the foreign keys it added to the new table before it releases the lock. A resumed migration drops any it finds.

  • An index that MySQL creates for a foreign key keeps its name, but can move to the end of the table's index list.

force-kill-after

How long Spirit waits before it starts killing the connections that are blocking a metadata lock. The default of 0s keeps the historical behavior of waiting for 90% of the lock-wait-timeout, which leaves very little time for the killed connections to release their locks when the timeout is short. For example, a 10s timeout only starts killing at 9 seconds, leaving about one second before the statement itself times out. Setting this flag decouples the two:

spirit migrate --lock-wait-timeout=10s --force-kill-after=5s \
       --host mydb:3306 --database mydb \
       --statement "ALTER TABLE users ADD COLUMN email VARCHAR(255)"

Here MySQL still waits up to 10 seconds for each metadata lock, but Spirit starts killing blockers after 5 seconds. The delay applies to both the native DDL path and the table locks used for cutover (and for the checksum with --legacy-checksum). Which connections are eligible to be killed does not change; see lock-wait-timeout for the selection rules.

An explicit value must be positive and less than the lock_wait_timeout that MySQL receives, which is truncated to whole seconds. Spirit rejects the value at startup otherwise.

Killing a connection is asynchronous, so the extra time is not a guarantee that rollback has finished before the lock wait expires. Killing earlier also interrupts application transactions sooner, so prefer investigating long-running transactions before lowering this value.

host

  • Type: String
  • Default value: 127.0.0.1:3306
  • Examples: mydbhost, mydbhost:3307

The host (and optional port) to use when connecting to MySQL. If no port is provided, 3306 is used.

legacy-checksum

  • Type: Boolean
  • Default value: false

By default Spirit verifies with the lockless checksum, for both the initial checksum and continuous sentinel checks. Use --legacy-checksum to verify with the legacy snapshot checksum instead, which was the default in earlier versions.

spirit migrate --legacy-checksum \
       --host mydb:3306 --database mydb \
       --statement "ALTER TABLE users ADD COLUMN email VARCHAR(255)"

Both checkers use the same column mappings (including renamed columns), the same repair policy, the same checkpoint and resume, checksum load throttling and autoscaling. Cutover locking is the same for both. They differ in how they read:

Lockless (default) Legacy (--legacy-checksum)
Setup lock None A brief table lock (LOCK TABLES) at the start of each pass, yield and retry
Reads Optimistic reads with bounded retries One consistent REPEATABLE READ snapshot per pass segment
InnoDB history list No growth Grows while the snapshot is open; bounded by --legacy-checksum-yield-timeout
Hot rows Split, then settled against the change stream (below) Compared at one snapshot, so concurrent writes do not matter

Lockless checksum

The lockless checksum reads the source and shadow tables with ordinary reads and bounded retries. It takes no checksum setup lock (FTWRL or table lock) and opens no long-lived REPEATABLE READ snapshots.

Cutover still requires a complete clean pass. Hot ranges are split. Small unresolved ranges then use a finite per-row snapshot drain: read target keys first, freeze source PK/CRC32 images once, and retry target reads until each frozen image has matched and every observed target-only key is absent. Later inserts do not expand the frozen work set, so an append-heavy tail can converge.

Each side is limited to 128 rows, with a combined 64 KiB key-data budget; oversized ranges stay on normal splitting/retries. Snapshot reads have a 30-second timeout per capture or target-check call, not for the entire drain. Snapshot retries use the ordinary retry delay and bounded hot-attempt count, then defer without authorizing cutover. Unresolved ranges are revisited in another pass. Completing a scan or deferring a hot range does not authorize cutover. As with the legacy checksum, a stable divergence found by the initial checksum is repaired from the source and re-verified on a later pass, while one found by the continuous checksum during the sentinel wait aborts the migration. The initial checksum gives up after 10 passes without a clean one and fails the migration; resuming with --legacy-checksum is the fallback.

Rows that are updated continuously are verified against the change stream rather than by reading the source again. A range that keeps changing is settled one row at a time: Spirit waits for the row's next change event, applies it to the shadow table, and compares the shadow row to that event's row image. The more often a row is written, the sooner its next event arrives. A row is deferred to the next pass instead when, for example, no change arrives within the range's 5-second settling budget, it is written again before the shadow row can be read, or the buffered changes cannot be fully applied. See Continuously updated hot rows for the full list.

This is optimistic verification, not a comparison at one common source/target snapshot. The final replication drain and cutover locking are the same as with the legacy checksum.

Initial checksum progress is checkpointed and resumed the same way as with the legacy checksum, including when resuming a checkpoint created by the legacy checksum. --legacy-checksum-yield-timeout applies only to the legacy checksum; the lockless gate is bounded by its 10-pass limit instead. The status line's deferred count covers only the current pass, not the lifetime of the run; use the timestamped hot-range and pass-completion logs to investigate repeated deferrals.

legacy-checksum-yield-timeout

  • Type: Duration
  • Default value: 24h

Applies only with --legacy-checksum. The default lockless checksum holds no snapshot, so it has nothing to yield and ignores this flag.

The maximum duration for a single legacy checksum pass before Spirit yields to release long-running REPEATABLE READ transactions. This helps control InnoDB History List Length (HLL) growth on large tables where the checksum phase can take many hours or even days.

During the legacy checksum, Spirit holds open REPEATABLE READ transactions to get a consistent snapshot of the source and target tables. These long-running read views prevent InnoDB from purging old row versions, causing the history list length to grow. On busy systems this can degrade performance for all workloads.

When the yield timeout fires, Spirit:

  1. Closes the current transactions (releasing the read views)
  2. Records the current checksum progress (low watermark)
  3. Re-acquires a table lock and creates fresh REPEATABLE READ transactions
  4. Resumes checksumming from where it left off

The checksum will complete correctly regardless of how many yields occur. However, each yield requires re-acquiring a table lock, which has the same impact as the initial checksum lock acquisition — it may conflict with running transactions, and Spirit may kill blocking transactions to acquire the lock (see lock-wait-timeout).

For most migrations the default of 24h is appropriate. You may want to lower this value if your system is sensitive to HLL growth (e.g. many concurrent writers generating undo log entries).

# Yield every 4 hours to limit HLL growth
spirit migrate --legacy-checksum --legacy-checksum-yield-timeout=4h \
       --host mydb:3306 --database mydb \
       --statement "ALTER TABLE large_table ADD INDEX idx_foo (foo)"

lock-wait-timeout

  • Type: Duration
  • Default value: 30s

Spirit requires an exclusive metadata lock for cutover (and for checksum operations with --legacy-checksum). The MySQL default for waiting for a metadata lock is 1 year(!), which means that if there are any long running transactions holding a shared lock on the table that prevent the exclusive lock from being acquired, new lock requests will effectively queue forever behind Spirit's exclusive lock request. To prevent Spirit causing such outages, Spirit sets the lock_wait_timeout to 30s by default.

At 90% of the lock-wait-timeout (i.e. after 27 seconds with the default of 30 seconds), Spirit will also start killing connections that are blocking the lock acquisition. Use force-kill-after to set this delay independently of the timeout. It does this in a semi-intelligent way:

  • It reads performance_schema to find only connections that are blocking a metadata lock being acquired on the migrating table.
  • It refuses to kill connections if they have a transaction open that has modified a large number of rows (>1 million).
  • It refuses to kill connections that hold an explicit LOCK TABLE, since unlike transactions these are not always retryable.

Spirit sets wait_timeout to 600 seconds on all of its connections. If Spirit stops responding while it holds a table lock (the process is frozen, or its network to the server is lost without the connection being closed), MySQL closes the idle session and releases the lock after at most 10 minutes, rather than after the server's wait_timeout (8 hours by default).

This force-kill behavior is always enabled and cannot be disabled. Attempting to acquire MDL locks over and over while they are being blocked is not safe — it can bring down production systems. The force-kill behavior of targeted killing is safer for real systems.

If you cannot tolerate a potential 30s stall during cutover, consider lowering the lock_wait_timeout. The main downside of doing this, is the potential for more connections to be killed by the force kill operation. Before considering increasing the lock-wait-timeout, it is almost always better to investigate why you have long running transactions that are preventing Spirit from acquiring the metadata lock. A good starting point is select * from information_schema.INNODB_TRX.

password

  • Type: String
  • Default value: spirit

The password to use when connecting to MySQL. To connect to MySQL without any password, pass the empty string.

replica-dsn

  • Type: String
  • Default value: ``
  • Example: root:mypassword@tcp(localhost:3307)/test
  • Multiple replicas: root:pass@tcp(replica1:3306)/db,root:pass@tcp(replica2:3306)/db

Used in combination with replica-max-lag. This is the host (or hosts) which Spirit will connect to to determine if the copy should be throttled to ensure replica health.

Multiple replica DSNs can be specified as a comma-separated list. When multiple replicas are configured, Spirit monitors all of them and throttles based on the slowest replica (i.e., the one with the highest lag). This is useful for environments with multiple read replicas where you want to ensure none of them fall too far behind.

Replica TLS Behavior

Spirit automatically applies the main database TLS configuration to replica connections when:

  • The replica DSN does not already contain TLS configuration
  • The main database TLS mode is not DISABLED

TLS Inheritance Rules:

  • If replica DSN contains tls= parameter (any case), that setting is preserved (even if main TLS mode is DISABLED)
  • If main TLS mode is DISABLED, no TLS inheritance occurs but existing replica TLS settings remain untouched
  • Otherwise, replica inherits main DB TLS mode and certificate configuration
  • RDS replicas automatically use RDS certificate bundle when appropriate

Examples and Test Matrix:

For comprehensive examples of replica TLS behavior, including all possible combinations of main DB TLS modes and replica DSN configurations, see:

📋 Replica TLS Testing Matrix

replica-max-lag

  • Type: Duration
  • Default value: 120s
  • Range: 10s-1hr

Used in combination with replica-dsn. This is the maximum lag that the replica is allowed to have before Spirit will throttle the copy phase to ensure that the replica does not fall too far behind. Spirit does not support read-replicas and throttling is only intended to ensure that replicas do not fall so far behind that disaster recovery will be affected. If you require a high fidelity for replicas, you should consider using gh-ost instead of Spirit.

The lag budget can only be enforced while lag is actually being measured, so the throttler fails closed: if lag polling keeps failing (for example, the replica becomes unreachable, or the lag query starts erroring) the migration pauses copying until polling recovers, rather than proceeding at full speed against a lag budget nobody is measuring. Copying resumes automatically once lag can be observed again. If the replica is permanently gone and you want the migration to proceed without lag protection, remove the replica-dsn.

It is recommended that you use Spirit in combination with either parallel replication (which is much better in MySQL 8.0) or non-binary log based replicas such as Aurora. If you are using the default single threaded replication and specifying a replica-dsn + replica-max-lag, you should expect to constantly be throttled.

The replication throttler only affects the copy-rows operation, and does not apply to changes which arrive via the replication client. This is intentional, as if replication changes can not be applied fast enough the migration will never be able to complete. On a busy system (with single-threaded or insufficiently configured parallel replication) it is possible that the changes from the replication applier may be sufficiently high that they cause the copier process to perpetually be throttled. In this case, you may have to do something more drastic for the migration to complete. In approximate order of preference, you may consider:

  • Adjusting the configuration of your replicas to increase the parallel replication threads (see Tuning parallel replication for Spirit workloads below)
  • Temporarily disabling durability on the replica (i.e. SET GLOBAL sync_binlog=0 and SET GLOBAL innodb_flush_log_at_trx_commit=0)
  • Increasing the replica-max-lag or disabling replica lag checking temporarily

Tuning parallel replication for Spirit workloads

Spirit's row-copier runs multiple chunks in parallel (default up to 4 in flight, each dynamically sized against an in-memory byte budget set by --target-chunk-size), and each chunk covers a disjoint primary-key range of the source table. Every chunk lands in the binlog as its own multi-row transaction, and because the ranges are disjoint the chunks have no row-level write conflicts with each other.

This workload is exactly the right shape to execute in parallel on replicas, but under MySQL 8.0 defaults (COMMIT_ORDER scheduling) there is minimum parallelism, and it requires the following configuration changes:

  • binlog_transaction_dependency_tracking = WRITESET on the source — typically 3–10× replica apply boost. The replica coordinator can schedule non-conflicting transactions in parallel regardless of source commit order. This is the single largest unlock for Spirit workloads on busy systems.
    • MySQL 8.0.x (8.0.26 – 8.0.x): set explicitly; the default is COMMIT_ORDER.
    • MySQL 8.4+ (including 9.x): do not set this variable — it was deprecated in 8.0.35 / 8.2.0 and removed in 8.4.0. Attempting to set it raises an Unknown system variable error. The server now always uses writeset-based dependency tracking internally (equivalent to the old WRITESET setting), so no action is needed on the source.
  • replica_preserve_commit_order = OFF on the replica — an additional 1.3–2× on top of writeset dependency tracking. Only safe if no downstream consumer of this replica's binlog depends on source commit order (CDC pipelines, chained replicas, Datawarehouse consumers that tail this replica rather than the source). Verify tolerance before flipping when log_replica_updates = ON. Available on both 8.0 and 8.4+.

On managed engines such as AWS Aurora, many of these parameters are static (pending-reboot) at the parameter-group level — SET GLOBAL works at runtime, but parameter-group changes require an instance reboot to persist.

skip-drop-after-cutover

  • Type: Boolean
  • Default value: false

When set to true, Spirit will keep the old table (renamed to _<table>_old) after completing the cutover instead of dropping it. This can be useful if you want to manually verify the migration before removing the old data.

statement

  • Type: String
  • Required

--statement is the only way to tell Spirit what change to make. It accepts most DDL statements, including CREATE TABLE, ALTER TABLE, CREATE INDEX, RENAME TABLE and DROP TABLE. Others such as DROP INDEX are not supported and should be rewritten as ALTER TABLE statements.

The table name is taken from the statement itself. If it is qualified (`schema`.`table`) the schema must match --database.

--statement replaced the earlier --table and --alter pair, which has been removed. Because resume matches on the exact statement text, migrations started before that removal will not resume — see Upgrading from a version with --table/--alter.

You can also send multiple ALTER TABLE statements at once, for example: --statement="ALTER TABLE t1 CHARSET=utf8mb4; ALTER TABLE t2 CHARSET=utf8mb4;" All of these statements will cutover atomically, which is useful when you are changing charsets or collations since if you were to perform these alters sequentially it may cause performance issues due to datatype mismatches in joins.

There are some restrictions to --statement:

  • Spirit requires that the statements can be parsed by the TiDB parser, so (for example) it is not possible to send CREATE PROCEDURE or CREATE TRIGGER statements to Spirit this way.
  • When sending multiple statements, all statements must be ALTER TABLE statements.
  • When sending multiple statements, the INSTANT and INPLACE optimizations will be skipped. This means that metadata-only changes that would execute instantly if submitted alone will require a full table copy.
  • When sending multiple statements, all statements must operate on tables in the same underlying database (aka schema).
  • CHECK constraint names are not preserved by a table copy. MySQL requires them to be unique per schema rather than per table, so the copy of your table cannot carry your names while your table still exists — it gets server-generated <table>_chk_<n> names instead. Statements that refer to a CHECK constraint by name still work: Spirit rewrites the name to match the copy. That includes dropping a constraint and re-adding it under the same name (widening an allowed-values list, say), but the re-added constraint is server-named too, and Spirit logs a warning saying so.

target-chunk-size

  • Type: Integer (bytes)
  • Default value: 16777216 (16 MiB)

The in-memory byte budget the copier sizes each copy chunk against. This is a byte target, not a time target: the copier reads full rows into memory and estimates each chunk's size from the values it read (their length once rendered as SQL, which approximates their size in memory), and its measured chunk time is a poor sizing signal (it includes the wait behind the write queue, which inflates under load independently of chunk size). Bytes-per-row is a stable property of the data, so a byte budget keeps chunks convergent under load and large enough to engage InnoDB/Aurora read-ahead.

The chunker adjusts the row count per chunk so that the estimated size of each chunk trends toward this budget, using a 90th-percentile servo over the last 10 chunks (with a 100,000-row ceiling and a 10-row floor). The default of 16 MiB is roughly 1024 16KB InnoDB pages per chunk; most users should not need to change it. Small-instance mode (see skip-autoscaling) lowers it to 1 MiB.

threads

  • Type: Integer
  • Default value: 4
  • Range: 1-64

Spirit uses threads to set the parallelism of:

  • The copier task
  • The checksum task

The write side of the copy — the applier's write workers — is controlled separately by write-threads.

This flag is replaced on Aurora unless --skip-autoscaling is set (see skip-autoscaling): with 4 or more vCPUs the autoscaler sizes both pools from the instance, and with fewer, small-instance mode sets it to 1.

threads does not size the connection pool. The pool is max-connections and nothing else, and everything below shares it concurrently:

  • the copier and checksum reads, threads of them.
  • the applier's write workers, write-threads of them, or up to twice that under autoscaling.
  • the change feed's drain, 8 concurrent REPLACE batches by default and up to 32 under autoscaling. A periodic flush overlaps the copy, so these really do add rather than borrow.
  • the control plane, 2 + one per changed table: the checkpoint INSERT, the replication-flush poll, and the per-table statistics updater.
  • the checksum's off-pool queries, a fixed 2: repairing a mismatched chunk, and the chunker's LIMIT 1 OFFSET n boundary prefetch. Both are serialized, so one connection each. With --legacy-checksum the checksum's REPEATABLE READ transaction pool is separate, and every one of its transactions pins a connection for the whole phase, so there is no incidental slack for these two to borrow.

Added up, those can exceed max-connections on a large instance under autoscaling. For the copier and the applier that is expected and harmless: they queue on connection checkout instead of each being guaranteed one, which costs throughput and nothing else — a worker waiting on checkout is a worker that will eventually run, and the work it was going to do is still there when it does.

Two of them are not like that, and both are protected:

  • Read workers, because the legacy checksum pins a connection per transaction for a whole phase. Bounds the pool cannot hold would block on checkout with a table lock held, so threads is validated against the pool at startup and any ceiling derived later is fitted to it. The same validation applies with the default lockless checksum.
  • The drain, because its work expires. A flush batch queueing behind a saturated copy is spending the binlog retention window, and running out of that window ends the migration rather than slowing it. The drain therefore keeps a reserved connection through the checksum instead of contending for one.

Throttler polling is not counted: it runs on a dedicated monitoring pool.

You may want to wrap threads in automation and set it to a percentage of the cores of your database server. For example, if you have a 32-core machine you may choose to set this to 8. Approximately 25% is a good starting point, making sure you always leave plenty of free cores for regular database operations. If your migration is IO bound and/or your IO latency is high (such as Aurora) you may even go higher than 25%.

On Aurora with 4 or more vCPUs, Spirit scales the read, write and checksum threads dynamically by default, driven by throttler feedback; below 4 vCPUs it fixes them at 1 (see skip-autoscaling). On other servers, and with --skip-autoscaling, Spirit does not adjust the number of threads while running, but it does support automatically resuming from a checkpoint if it is killed. This means that if you find that you've misjudged the number of threads, you can simply kill the Spirit process and start it again with different values.

One piece of pacing does not depend on autoscaling: the checksum phase waits on the throttler before dispatching a chunk, with or without it. It reacts to load signals only — see checksum scaling for why replica lag deliberately does not pause a checksum. What autoscaling adds is movement of the checksum's own worker count.

write-threads

  • Type: Integer
  • Default value: 4

Sets the parallelism of the applier's write workers — the pool that lands copied rows into the new table during the copy phase. Copier read and checksum parallelism are controlled separately by threads.

Replication (binlog) apply parallelism is not controlled by this flag. Buffered replication changes for tables with a memory-comparable primary key are drained with up to 8 applier batches in flight (a flush snapshot holds one image per key, so batches are disjoint and commute); other drains — non-memory-comparable keys post-copy, and the final under-lock flush at cutover — apply serially. The concurrency is a library-level setting (ClientConfig.FlushConcurrency) with no CLI flag today; raising write-threads does not speed up binlog catch-up.

This flag is replaced on Aurora unless --skip-autoscaling is set (see skip-autoscaling): with 4 or more vCPUs the autoscaler sizes the write pool from the instance (vCPU count minus 2) and treats that as its starting point, and with fewer, small-instance mode sets it to 1.

skip-autoscaling

  • Type: Boolean
  • Default value: false

Disables autoscaling. Autoscaling is on by default. On Aurora, Spirit chooses the thread counts from the instance size, and threads and write-threads are replaced:

Target Thread counts
Aurora, 4 or more vCPUs Autoscaling: derived from the instance and adjusted on throttler feedback (described below).
Aurora, fewer than 4 vCPUs Small-instance mode: 1 read thread, 1 write thread, 1 MiB chunks, nothing scales (described below).
Not Aurora, or --skip-autoscaling Fixed at threads and write-threads for the whole run.

On a server that is not Aurora this flag has no effect. Three Aurora cases fall back to the configured counts, each with a logged reason: the Aurora probe failed (for example an under-granted user), spirit move has a target that is not Aurora, or the connection pool (max-connections) cannot hold the derived bounds (spirit sync only). A target below 4 vCPUs selects small-instance mode even then.

When autoscaling engages, Spirit dynamically adjusts the number of copy read threads, the number of applier write threads, and the number of checksum threads, based on feedback from the throttlers. The copy phase is described first; see checksum scaling below for how the checksum differs. Each throttler reports a continuous utilization signal (0 = idle, 1.0 = the point at which it would hard-stop the copy); the controller takes the highest signal across all throttlers and steers the thread counts to keep it in a comfortable band. Because both pools contribute to the same signal, utilization alone cannot tell which side to move — the buffer queue between the readers and the writers arbitrates: a near-empty queue that writers drain instantly means the read side is the limiting (or, under load, the responsible) pool; a near-full queue where chunks wait as long as they take to write means the write side is.

  • Below 40% utilization it grows the limiting pool by one thread at a time (cautiously, with a ~15s cooldown between increases). If the pipeline is balanced — neither side limiting — it holds instead of growing either pool.
  • At or above 70% utilization it sheds one thread at a time from the side the queue blames (immediately on the first breach, then at most once per ~15s so the signal can reflect each cut).
  • At or above 100% utilization — the smoothed signal has reached the vCPU count, at or beyond where the raw per-sample hard-stop throttle trips — it halves both thread counts instead, so the copy resumes gently once the overload clears.
  • In between it holds steady.

The band has hysteresis, so where it settles depends on which side it approaches from. The write pool's starting point (vCPUs - 2) sits above the band, so on an otherwise idle server the controller sheds it downward and parks just under the 70% watermark — the first band edge it reaches — and holds there. It does not continue down to the 40% floor; that lower watermark is only the level it would climb up to had it started below the band. The read pool starts well below the band and does climb, one thread per ~15s, until either the signal reaches 40% or it hits its ceiling. Either way the remaining headroom is reserved for the primary workload, and responsiveness to genuine overload comes from the hard-stop throttle, not from thread scaling — so on a fully idle instance some capacity is deliberately left unused. The threads-running utilization signal is smoothed (an exponentially weighted moving average over ~3 samples) so one-off spikes — a checkpoint write, a brief flurry of OLTP — do not trigger scaling; the binary hard-stop throttle always acts on the raw per-sample value.

Autoscaling takes over the thread counts: threads and write-threads are ignored when it engages. Set --skip-autoscaling to use them instead. A value other than the default (4) is most likely a deliberate cap, so replacing one logs a warning that names both the configured and the derived counts. A program that embeds Spirit and passes its own counts as a baseline for servers autoscaling does not engage on, not as a cap, can set ThreadsAreBaseline on the Migration (or Move/Sync) struct: the counts are still replaced, without the warning. A controller whose job is to find the right size should not also be told where to stop. Both pools are sized from the instance instead, as start → ceiling:

Instance vCPUs Apply (write) Copy read / checksum Change-feed flush
db.t4g.medium 2 1 † 1 † 1 × 1000 †
db.r6g.large 2 1 † 1 † 1 × 1000 †
db.r6g.xlarge 4 2 → 4 2 → 2 8 × 1000
db.r6g.2xlarge 8 6 → 12 2 → 4 8 × 1000
db.r6g.4xlarge 16 14 → 28 4 → 8 14 × 571
db.r6g.8xlarge 32 30 → 60 8 → 16 30 × 266
db.r6g.12xlarge 48 46 → 92 12 → 24 32 × 250
db.r6g.16xlarge 64 62 → 124 16 → 32 32 × 250
db.r8g.24xlarge 96 94 → 188 24 → 48 32 × 250
db.r8g.48xlarge 192 190 → 380 48 → 96 32 × 250

† Small-instance mode: fixed counts, no scaling, and 1 MiB copy chunks. See below.

Small-instance mode. An Aurora target with fewer than 4 vCPUs gets small-instance mode. Two reasons: the smallest instances (db.t4g.medium) were observed to run out of memory at the defaults (4 readers, 4 writers, 16 MiB chunks, 8 concurrent change-feed flushes), and below 4 vCPUs one thread is too large a share of the instance for the controllers to steer. In small-instance mode the copy and the checksum use 1 read thread, the applier uses 1 write thread, the change feed flushes 1 statement at a time (1 × 1000), and target-chunk-size is lowered to 1 MiB (a smaller configured value is kept). Nothing scales. The change feed drains serially, one 1000-row statement at a time, so on a write-heavy table the binlog backlog can grow faster than it drains and outrun binlog retention. For a write-heavy table on a 2-vCPU instance with ample memory (for example db.r6g.large), use --skip-autoscaling to keep the configured counts and the default drain (8 × 1000). It overrides threads, write-threads and target-chunk-size and is logged once at startup; replacing a non-default threads or write-threads logs a warning. Set --skip-autoscaling to keep the configured values instead.

For a size not listed: write threads start at vCPUs - 2 (minimum 1) and may reach twice that; read threads start at ceil((vCPUs - 2) / 4) (minimum 2) and may reach ceil(vCPUs / 2). vCPUs is read from @@innodb_buffer_pool_instances, and the resolved counts are logged once at startup. The lower bound is always 1 — the controller may shed below the starting value. Note xlarge, the smallest size that engages: its read bounds meet at 2, so the read side can shed but not grow there.

The flush column is a pair — concurrent REPLACE statements × rows per statement — and reads differently from the other two. It is not a range, because the flush is not steered by the utilization band: what the instance size sets is only where its own controllers start. And the product is constant at 8000 on every size: a larger instance buys more concurrent statements, each holding proportionally fewer row locks, not more rows in flight at once.

Two controllers move it from there, on different signals and with different intent:

  • Lock contention narrows both terms on a deadlock or lock-wait — a 4× cut to rows in flight — and widens them back after a run of clean drains. This is spirit policing its own fan-out, and no load signal can see it: an oversubscribed flush deadlocks against itself on secondary-index next-key locks while the server still looks idle.
  • Server load narrows the concurrency only, and re-pairs the batch upward so the same rows stay in flight across fewer, larger statements. It floors at 8 — the width every flush ran at before the widening above existed — so it can give back the widening and nothing more.

Both move one step per drain, which sets how fast they can respond: the signal is sampled once when a drain begins, and drains under load have been observed tens of seconds apart. A drain that starts with the signal clear runs at full width for its whole duration even if load arrives immediately after, and reaching the floor takes a drain or two. These controllers damp sustained load; they do not catch transients, and are not meant to.

The second exists because the flush is deliberately never throttled: the binlog position has to keep advancing or the migration loses its retention window. The original reasoning was that the throttler would slow the copier on the flush's behalf, which held while the flush was a fixed 8 batches wide. Once it became instance-derived, the two write paths were sized independently from the same vCPU count, and under sustained load the copier would shed to a handful of workers while the flush held full width — the side that could yield did all the yielding, and the total load barely moved. Narrowing the flush is cheap in a way that shedding a copier thread is not, precisely because the rows-in-flight product is constant: 8 × 1000 moves the same rows as 32 × 250 through a quarter of the server threads.

It reads the same load signals as the write-thread autoscaler and ignores replica lag, for the reason given under checksum scaling: lag is an SLO budget rather than a load gauge, and narrowing the drain cannot reduce it while the position it stops advancing is a deadline. On a non-Aurora target there is no continuous load signal, so this controller never engages and only the contention one does.

That trade is what makes it safe to go past the previous fixed width of 8. A flush batch takes a next-key lock per row per UNIQUE secondary index, so two batches collide when any of their rows land in adjacent slots of any such index — a risk set by how many slots each statement claims, not by how many siblings it has. 32 × 250 and 8 × 1000 therefore push the same rows per unit time, but the wide-and-narrow form holds a quarter of the locks per statement and collides correspondingly less often. Sizes at or below 8 derived concurrency get exactly the previous 8 × 1000, so nothing changes below 4xlarge.

The two shapes are deliberately different. Write threads spend most of their life parked on a redo-log flush, so a count above the vCPU count is not oversubscription; it is what keeps the log busy, and it is why the redo-aware signal excludes those waiters. A read thread scanning a table that is already in the buffer pool is pure CPU, so the same count really does compete with the application for cores — oversubscribing readers is how a checksum ends up degrading the workload it was supposed to be invisible to. The read side therefore starts at about a quarter of the instance and earns its way up through the utilization band.

Its ceiling is a share of the instance rather than a multiple of its starting size, and stops at half the box, because for the legacy checksum the ceiling is not a hypothesis — it is a cost paid up front. Every snapshot transaction must take its read view at the same instant, so the entire pool is created serially while the brief table lock is held, whether or not scaling ever reaches it (see checksum scaling). Half the instance is the most that is worth holding a cutover-class lock to reserve.

Every count above — starting sizes and ceilings both — is capped by the CPU available to spirit itself, at 16 threads per core (GOMAXPROCS, so a container CPU limit is respected). The table assumes spirit has enough CPU to drive the target; where it does not, the derived counts are reduced and a warning names both numbers.

This exists because the sizes above are derived entirely from the target, on the premise that a worker is mostly waiting on the server — and a worker also builds its INSERT client-side, which is pure local CPU. Measured against a 96-vCPU target, roughly 60% of a write worker's cycle was client-side even on a 16-core host. The failure mode is quiet and hard to read: spirit on a 4-core pod derives 94 write threads, only about 7 threads' worth of work progresses, and the extra 87 add queueing and latency while the target's CPU and commit latency both report idle. Nothing on the server side can see it. If throughput does not improve as the thread count climbs, check spirit's own CPU before the database's.

16 per core is permissive by design: it is there to catch the order-of-magnitude mismatch, not to size the pool. The ratio has to sit well above the healthy operating point — that same 16-core host ran ~99 write workers, 6.2 per core, without saturating local CPU, so a cap of 8 per core would have started binding on a host that was keeping up fine. At 16, a host with 16 cores is unconstrained for every instance size up to 128 vCPUs, growth ceilings included; the largest size in the table (192 vCPUs, whose write pool may grow to 380) needs 24 cores to be fully unconstrained.

A --threads/--write-threads you set yourself is not capped — an explicitly named number is yours — but the same mismatch is warned about once.

There is a second cap, on the target side: these are thread ceilings, not connections. The connection pool is max-connections regardless, and from 12xlarge upward the ceilings above add up to more than its default of 128 (at 48 vCPUs: 92 + 24 + 32 plus headroom). Past that point the scaled-up workers contend for connections rather than each being guaranteed one — which is the intended behaviour, since the alternative on a busy server is Error 1040: Too many connections. Raise --max-connections if the server has the headroom to give.

When budgeting proxy or server connection limits, use max-connections and do not try to reconstruct it from the ceilings: it is one number for the whole migration, by design. (One exception to the ceilings themselves: when the threads signal is running in its redo-aware mode and max-commit-latency is disabled with --max-commit-latency=0, the write-side upper bound stays at the starting value — see below.)

One consequence to be aware of: on a well-provisioned target where the writers always keep pace, the queue is drained the instant chunks arrive — which is exactly what "read-limited" looks like — so the read pool tends to ramp well above its starting size early in the copy. That ramp is additive (one thread per ~15s), so on a large instance the read side takes a few minutes to reach its ceiling; overall load remains governed by the utilization band and, ultimately, the hard-stop throttle.

The signal comes from the Aurora throttlers — the threads signal and commit-latency (see max-commit-latency) — which are auto-enabled on Aurora. The threads signal compares a running-thread count against the instance vCPU count: by default it uses a redo-aware count from performance_schema that excludes threads parked on redo-log flush (letting the copy oversubscribe the redo log for more throughput), and falls back to the server's plain Threads_running count when the account lacks the extra performance_schema grants. commit-latency complements it by watching storage saturation directly — and in redo-aware mode it is also the backstop that permits scaling above the starting value, since the redo-aware count deliberately ignores the redo-log waiters that oversubscription would produce (so with --max-commit-latency=0 the pool can shed but not grow). Replica lag (replica-dsn) deliberately contributes no continuous signal: lag is a budget, not a load gauge, and steering on it would park replicas well behind. Replicas remain protected by the hard-stop throttle only. If no continuous signal is available at all (for example a non-Aurora target), autoscaling does not engage: the reason is logged and both pools run fixed at threads and write-threads. A failed Aurora probe (for example an under-granted user) warns; a target that is simply not Aurora logs at info level. Aurora instances with fewer than 4 vCPUs get small-instance mode instead (see above).

If a signal stops updating mid-migration (for example the monitoring connection is partitioned, or grants are revoked), the controller does not keep scaling on the frozen value: after ~15 seconds without a successful sample the signal reports a neutral utilization inside the hold band, freezing the thread counts in place (a warning is logged). Scaling resumes automatically when sampling recovers.

spirit move and spirit sync use the same flag and the same derivation. With several targets, every target must qualify, one target below 4 vCPUs puts the whole run in small-instance mode, the bounds come from the smallest one, and targets that share a server share its budget; see move's skip-autoscaling.

Checksum scaling

The checksum phase is scaled by the same autoscaler but on different signals, because it is read-only rather than running write transactions. Three behaviors are worth separating:

  • Pausing under load does not depend on autoscaling. The checksum calls the throttler before dispatching each chunk, with or without --skip-autoscaling. Chunks already in flight are never abandoned — an aborted chunk is wasted I/O that has to be redone from the same watermark — so the checksum stops dispatching rather than cancelling work.

    It pauses on load signals only. Replica lag deliberately does not pause a checksum: the phase writes nothing to the binlog, so it cannot be the cause of the lag and pausing it cannot reduce the lag — while the pause extends the pass. With --legacy-checksum a longer pass also holds the REPEATABLE READ snapshot open, retaining undo that the purge thread cannot advance past. (The lag throttler also fails closed when it cannot poll the replica, so an unreachable replica would otherwise stall the checksum, and a legacy checksum until its yield timeout with the snapshot still held.) Load signals come from the Aurora throttlers, so on a non-Aurora server there is no load signal and the checksum is not paced by the throttler at all. Chunk repairs do write, and are deliberately left unpaced too — they are rare and small, and blocking one extends the pass the same way.

  • Shedding happens only when autoscaling engages (an Aurora target with at least 4 vCPUs, and --skip-autoscaling not set). The signal is the change feed's post-flush residual: the feed flushes concurrently with the checksum, and if the residual is growing across flushes then the checksum's reads are winning a race against writes that have to finish (an unbounded backlog can outrun binlog retention and block cut-over). A worker is shed per flush that agrees, with hysteresis in both directions.

  • Growth happens under the same condition, since it uses the same utilization band as the copy phase — and that band comes from the Aurora throttlers. On stock MySQL autoscaling does not engage, so the checksum runs fixed at the configured threads, the same as spirit move and spirit sync.

Without autoscaling (on stock MySQL, or with --skip-autoscaling) the checksum has the hard stop and nothing else: it never moves its own worker count in either direction.

As with the copy phase, a signal that stops updating does not keep being acted on. If the change feed's flushes stop completing — they error, or one takes minutes — the checksum's thread count is frozen where it is (a warning is logged, and another line when flushes resume). It is frozen rather than reduced: a stalled flush says the signal stopped, not which direction it was heading.

One structural constraint shapes the legacy checksum (the lockless checksum has no transaction pool and can resize mid-pass): its snapshot transaction pool cannot grow once the brief table lock is released, because every transaction must take its read view at the same instant. It is therefore provisioned up front at whatever ceiling scaling could actually reach: ceil(vCPUs / 2) where autoscaling engages on Aurora, and plain threads everywhere else — both with --skip-autoscaling, and on a target where autoscaling cannot engage (non-Aurora, or under 4 vCPUs). It reuses the read-side connection budget, since the copier's readers have finished by the time the checksum runs. An idle pooled transaction costs one connection and no extra history retention, so over-provisioning is cheap in resources. What it is not cheap in is lock time: the transactions are started serially under the table lock, so the ceiling directly lengthens that window. This is the reason the read-side ceiling is half the instance rather than all of it.

Chunk sizing is separate from worker count: the checksum's chunks are sized by the dynamic chunker against a fixed 5s time budget (table.ChunkerDefaultTarget, not a flag — and not the copier's --target-chunk-size byte budget), and scaling only changes how many are in flight at once.

max-connections

  • Type: Integer
  • Default value: 128

The size of Spirit's main connection pool. It is set once, verbatim, and never recomputed: no phase ratchets it, and no thread ceiling derived from the instance raises it.

That is deliberate, because the number has to be one an operator can budget against. Spirit's connections come out of the server's max_connections, shared with the production workload, and if the migration user has a max_user_connections the pool has to be a value you can subtract — not a sum that moves when Spirit reaches a particular phase.

Everything shares that one pool — copier reads, applier writes, the change feed's drain, and the periodic control-plane queries (see threads for the full list). Their ceilings can add up to more than the pool, and on a large instance under autoscaling they will. Above the pool size the copier and applier contend for connections instead of each being guaranteed one, which costs throughput and nothing else. The alternative on a busy server is Error 1040: Too many connections, which is not a slower migration but a dead one. Read workers and the drain are the two exceptions, and are given reserved capacity rather than left to contend — see threads.

For a full accounting against max_user_connections, Spirit also opens two connection pools outside this one, both small and both fixed:

Pool Size When
main max-connections always
throttler monitor 2 only on a target with a continuous load signal (currently Aurora)
replica lag checker max-connections only with replica-dsn — and on the replica, not the target

So the worst case on the target is max-connections + 2.

Values that cannot work are rejected at startup rather than discovered mid-migration:

  • At least 5, which the cutover needs for its LOCK TABLES connection, its RENAME TABLE connection and the flush threads. This is also the one case where Spirit will exceed a configured pool size: a CutOver handed a smaller pool raises it to 5, because the alternative is a cutover that cannot run.
  • At least threads + 6, because the checksum's readers each need a connection — with --legacy-checksum, each read transaction pins one for the whole phase whether or not a worker has one checked out. The 6 is what has to keep running alongside them: 2 for the checksum's own chunk repair and boundary prefetch, 3 for the control plane on a single-table migration, and 1 for the drain. A pool that only just fits the transactions is not a slow checksum — it is a checksum during which the drain cannot check out a connection at all.

Both are hard stops rather than slow paths: below them a migration does not run slower, it stalls holding a table lock or an open read view.

Under autoscaling both read bounds come from the instance rather than from threads, so they are not known at startup and cannot be validated there. If they do not fit the pool, Spirit lowers them — it will not grow the pool past the number you set. Both the starting count and the ceiling are lowered, not just the ceiling: the checksum and the copier each floor their ceiling at the starting count, so lowering one without the other would have no effect. This is logged, and only bites if you set max-connections below half the target's vCPU count.

A negative value is rejected. A pool whose size Spirit cannot state is what this flag exists to remove.

max-commit-latency

  • Type: Duration
  • Default value: 100ms

Throttles the copy when the server's average commit latency exceeds this threshold, protecting the primary workload's write latency from the migration's own write volume.

It is currently auto-enabled only on Aurora (auto-detected); on other servers it has no effect. The default of 100ms is intentionally a high upper bound, so it trims only the most extreme tail latencies rather than throttling under normal load. Setting --max-commit-latency=0 disables it, which also removes the storage-saturation backstop that lets autoscaling grow the write-thread pool while the threads signal is redo-aware; in that combination the pool can shed threads but not scale above its starting value. See block/spirit#468.

tls-ca

  • Type: String
  • Default value: ``

Path to a custom TLS CA certificate file (PEM format) used to verify the MySQL server's certificate. This is used in combination with tls-mode when set to VERIFY_CA or VERIFY_IDENTITY.

When not specified, Spirit will attempt to use the embedded RDS certificate bundle as a fallback for AWS RDS/Aurora connections. For non-RDS connections with VERIFY_CA or VERIFY_IDENTITY, you should provide this flag to ensure proper certificate verification.

tls-mode

  • Type: Enumeration
  • Default value: PREFERRED

Spirit uses the same TLS/SSL mode options as the MySQL client, making it familiar and intuitive for users.

Spirit applies TLS configuration consistently across all database connections:

Main Database Connection: Uses the specified --tls-mode and --tls-ca settings.

Replica Throttler Connection: Automatically inherits TLS settings from main database unless the replica DSN already contains TLS configuration.

Binary Log Replication: Uses the same TLS configuration as the main database for streaming binary log events.

This ensures security consistency across all database communications during the migration process.

Mode Description Encryption CA Verification Hostname Verification --tls-ca Required?
DISABLED No TLS encryption ❌ No ❌ No ❌ No ❌ Never needed
PREFERRED TLS if server supports it (default) ✅ If available ❌ No ❌ No ❌ Never needed
REQUIRED TLS required, connection fails if unavailable ✅ Required ❌ No ❌ No ❌ Never needed
VERIFY_CA TLS required + verify server certificate ✅ Required ✅ Yes ❌ No ⚠️ Optional*
VERIFY_IDENTITY Full verification including hostname ✅ Required ✅ Yes ✅ Yes ⚠️ Optional*

Configuration Flags:

Flag Description Default
--tls-mode TLS connection mode (see table above) PREFERRED
--tls-ca Path to custom TLS CA certificate file ""

* Optional but recommended: These modes can use the embedded RDS certificate bundle as a fallback, but providing --tls-ca gives you full control over which Certificate Authorities are trusted.

Examples:

PREFERRED

NOTE: This mode is the default behavior

# Add a column with automatic TLS detection (default mode)
spirit migrate --tls-mode PREFERRED \
       --host mydb.us-west-2.rds.amazonaws.com:3306 \
       --username admin \
       --password mypassword \
       --database production \
       --statement "ALTER TABLE users ADD COLUMN last_login_ip VARCHAR(45) AFTER last_login" \
       --threads 8

Result: Automatically uses TLS for RDS hosts with embedded certificates, optional for others.

REQUIRED

Force TLS Without Certificate Verification

# Add a column requiring TLS but not verifying certificates
spirit migrate --tls-mode REQUIRED \
       --host mysql.staging.company.com:3306 \
       --username staging_user \
       --password staging_pass \
       --database inventory \
       --statement "ALTER TABLE products ADD COLUMN supplier_notes JSON AFTER supplier_id" \
       --threads 6

Result: TLS encryption required, but accepts self-signed or invalid certificates.

VERIFY_CA

Certificate Verification Without Hostname Check

# Add a column with CA verification using custom certificate
spirit migrate --tls-mode VERIFY_CA \
       --tls-ca /etc/ssl/certs/company-ca-bundle.pem \
       --host 192.168.1.100:3306 \
       --username app_user \
       --password app_password \
       --database analytics \
       --statement "ALTER TABLE events ADD COLUMN event_metadata JSON AFTER event_type" \
       --threads 4

Result: Verifies certificate against custom CA bundle but allows IP addresses/hostname mismatches.

# Add a column using embedded RDS certificate for non-RDS MySQL server
spirit migrate --tls-mode VERIFY_CA \
       --host mysql.internal.corp:3306 \
       --username internal_user \
       --password internal_pass \
       --database hr_system \
       --statement "ALTER TABLE employees ADD COLUMN emergency_contact VARCHAR(255) AFTER phone_number" \
       --threads 2

Result: Uses embedded RDS certificate bundle as fallback for certificate verification.

VERIFY_IDENTITY

Full Certificate and Hostname Verification

# Add a column with maximum security verification
spirit migrate --tls-mode VERIFY_IDENTITY \
       --tls-ca /opt/certificates/production-ca.pem \
       --host mysql.secure.company.com:3306 \
       --username secure_user \
       --password very_secure_password \
       --database financial \
       --statement "ALTER TABLE transactions ADD COLUMN fraud_score DECIMAL(5,4) AFTER amount" \
       --threads 8

Result: Full TLS verification including hostname matching - maximum security. Custom certificate takes precedence over RDS auto-detection.

# Add a column to RDS with full verification using auto-detected certificate
spirit migrate --tls-mode VERIFY_IDENTITY \
       --host prod-db.cluster-xyz.us-east-1.rds.amazonaws.com:3306 \
       --username rds_admin \
       --password rds_password \
       --database customer_data \
       --statement "ALTER TABLE profiles ADD COLUMN gdpr_consent_date DATETIME AFTER created_at" \
       --threads 10

Result: Uses embedded RDS certificate with full verification for RDS hostname.

username

  • Type: String
  • Default value: spirit

The username to use when connecting to MySQL.

GTID auto-detection

Spirit keeps the new table in sync by following the source's replication stream, and MySQL has two coordinate schemes for positions in that stream. Spirit picks between them automatically at the start of each migration — there is no flag:

  • GTID set (e.g. 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5): used whenever the server has GTIDs enabled (gtid_mode=ON and enforce_gtid_consistency=ON).
  • Binlog file + offset (e.g. binlog.000002:4): used when it does not.

The copier, applier, checksum, cutover, and checkpoint contract are identical either way — only the way Spirit asks the source for "everything after position X" differs. The practical differences of the GTID scheme:

  • Spirit never issues FLUSH BINARY LOGS to read or advance its position (the file+offset path runs it to establish the start position and again from BlockWait's stall recovery), so a GTID-mode migration induces no binlog rotations on the source.
  • Resume after a streamer reconnect is naturally transaction-aligned: the GTID client re-asks the server for "everything after GTID set X" rather than rewinding to the start of the current binlog file and re-reading.
  • The opaque resume coordinate written to the checkpoint table is a GTID set string (e.g. uuid:1-5,otheruuid:1-3) rather than <file>:<offset>.

Auto-detection applies to new migrations. A resume from checkpoint always keeps the coordinate scheme the run started with, which is recorded by the format of the checkpointed position itself:

  • A file+offset checkpoint resumes on the file+offset client even if the server has GTIDs enabled — for example a checkpoint written by an older Spirit version, or a server whose GTIDs were switched on mid-migration.
  • A GTID checkpoint requires the server to still have GTIDs enabled. If GTIDs were switched off mid-migration, the resume fails with an error (the file+offset client cannot interpret a GTID coordinate) rather than silently restarting the copy; re-enable GTIDs to resume.

A GTID resume also fails fast if @@GLOBAL.gtid_purged is no longer a subset of the checkpointed GTID set (i.e. the source has dropped binlogs Spirit would need to re-apply). In that case Spirit surfaces change.Source: cannot resume from position, logs the reason, and restarts the migration from scratch.

Reading the status output

While a migration runs, Spirit logs one status report every 30 seconds. It is deliberately the only recurring INFO output: the checkpoint, the binlog flush, the binlog rotations and the reader's own backpressure each used to log on their own schedule, and they are now fields in this report instead (#329). Their detail is still available by running with debug logging.

The report is a header line plus one indented row per subsystem:

2026/08/14 11:47:10 INFO migration status: state=copyRows total-time=2m30s copier-time=2m30s
  copier   46.13%  7550855/16370180  chunk-size=8097  eta=3m29s  throttled=false
  applier queue=128/128  workers=4  wait-p50=1.564s  write-p50=37ms  write-p90=131ms
  binlog  deltas=0  rotations=56 (0 forced)  parks=0 is-parked=false  flush=8x1000  flushed 30s ago (took 9µs, 0 rows)  read=binlog.000047:104861122 (1s behind)
  ckpt    50s ago  binlog.000047:104857600

Which rows appear depends on state; the copy phase (copyRows, above) prints the most. During checksum a checksum row replaces the copier one, and during waitingOnSentinelTable a sentinel row reports the wait.

Note that the whole report is a single log record containing newlines. Spirit's own CLI prints it as written; if you pipe Spirit's output through a handler that quotes log messages (JSON, or slog's TextHandler), the rows will arrive escaped on one line.

Header

Field Meaning
state The migration phase. Runs in order: copyRows → applyChangeset → analyzeTable → checksum → postChecksum → waitingOnSentinelTable → cutOver. The sentinel wait sits immediately before cutover and is entered only with --defer-cutover, where it blocks until you drop the sentinel table.
total-time Wall-clock time since the migration started.
copier-time / checksum-time Time spent in the current phase.

copier row

Field Meaning
% Rows settled out of the estimated total. The total comes from table statistics, so the percentage is approximate and not a row count you should reconcile against. Spirit re-reads those statistics every few minutes for the duration of the copy, so the total moves in both directions while the copy runs: on a table taking deletes the percentage can jump forward and then back across a refresh, and the settled count can overtake the estimate and read past 100%. Neither is a stalled or repeated copy, and the rows settled only ever increase. Rows the binlog applier wrote before the copy reached them are not counted (the copy inserts with INSERT IGNORE), so on a busy table the percentage finishes short of 100%. A resume from checkpoint continues the count from where the previous run left it.
n/m The figures the percentage is derived from: rows settled so far and the estimated table cardinality, the same numbers the status API reports per table and as Copy.
chunk-size Rows in the most recently claimed chunk. The chunker sizes chunks dynamically to hit the --target-chunk-size byte budget, so this number moving is normal and healthy — it is how Spirit adapts to row width. A chunk size that has collapsed to its floor and stayed there means the rows are too wide to fit the budget even at the floor, i.e. lower --target-chunk-size than the data wants, not a struggling server.
eta Remaining copy divided by the recently measured copy rate. On a table with an auto_increment key the remaining copy is distance through the key range, so DUE (past 99.99% of the range) need not coincide with the % column when the ids are sparse; on other keys it is the same rows-against-estimate ratio as %, and the two move together. TBD for the first minute (no rate measured yet). The rate is the average over the most recent 2 minutes of copying, so a throttler pausing the copy for a few seconds moves the ETA a little rather than doubling it, and a real change in speed takes about 2 minutes to show in full. A copy paused for longer than that keeps the last rate it measured: the ETA stops counting down rather than disappearing, and the throttled column says why.
throttled Whether the copy is currently paused by a throttler (replica lag, commit latency, or load). A migration that is throttled is behaving as designed — it is protecting the server, not stalling.

The checksum row that replaces this one during the checksum phase has the same shape — including its own chunk-size, since the checksum sizes chunks dynamically as well, though against a fixed 5s time budget rather than the byte budget — plus threads= and throttled= for the checksum's own pacing.

binlog row

Spirit keeps the new table in sync with writes that land during the copy by subscribing to the source's binary log.

Field Meaning
deltas Changes discovered in the binary log that have not been applied to the new table yet. This is usually low at the start of a migration because of the key above watermark optimization: a change to a row the copier has not reached yet can simply be dropped, since the copier will read the current version when it gets there. It rises as the copy approaches 100%, and the applyChangeset state exists to drain it before cutover.
rotations How many binlog rotations Spirit has followed on the source. High counts usually just indicate a high volume of write activity on the server (from any workload, not only this migration). Worth knowing because binlog retention is what bounds how long a paused or resumable migration can survive.
(n forced) The subset of those rotations Spirit caused itself, by issuing FLUSH BINARY LOGS when it was waiting for the feed to catch up and the position had stalled. A number that climbs here (rather than in rotations) means Spirit's own catch-up waiting is churning through binlogs. Always 0 when the run uses GTID coordinates (see GTID auto-detection) — that feed never issues FLUSH BINARY LOGS.
parks How many times Spirit has throttled its own binlog reader, cumulatively for the run. The reader parks when a subscription's buffer of pending changes reaches a soft limit — either 256MiB of buffered row images or 50,000 pending changes, whichever binds first — and resumes as the next flush applies them. Capacity comes back one applied batch at a time, so one sustained episode of backpressure produces many parks rather than one long one: read the rate between reports, not the absolute number. A count that never moves means the applier is keeping up with the source's write rate.
is-parked Whether the reader is parked right now. Together with parks this separates a reader being briefly throttled and recovering (parks climbing, is-parked=false) from one being held off for minutes at a time (is-parked=true across consecutive reports). The latter is the reading to act on: while the reader is parked Spirit is not consuming the source's binary log, so a long stall eats into binlog_expire_logs_seconds and can make the run unresumable. It usually means the change feed cannot apply as fast as the source writes; check the flushed figures on this row for how long a drain is taking.
flush How wide a flush currently runs: concurrent REPLACE statements × rows per statement. There is no flag for this — it is 8x1000 on servers where autoscaling does not engage, derived from the instance where it does (see skip-autoscaling), and adjusted at runtime by the change feed's lock-contention controller either way, so the status report is where you read it. A second figure in parentheses, as in flush=2x250 (of 8x1000), means that controller has narrowed the flush after deadlocks or lock waits: the first pair is what is running now, the second is what it would run without the back-off. Each step halves both terms, so one step costs 4×. The parenthetical's appearance is the signal, and its disappearance — after enough clean drains — is the recovery. One that stays for the rest of a run means the contention never cleared, and the flushed figures below are where its cost shows up.
flushed X ago (took Y, n rows) When the change feed last flushed its buffered changes to the new table, how long that flush took, and how many buffered changes it started with. Flushes are periodic (every 30 seconds by default), so X reads somewhere between 0s and the interval during a healthy copy; a value that keeps climbing well past it means flushes are not completing. 0 rows is the normal reading for a feed that is keeping up — there was nothing left to write.
read How far the feed has read the binary log, in the run's coordinate scheme. This is not the resume point: it is ahead of the ckpt position by whatever is still buffered and unflushed. Omitted before the feed has read anything.
(X behind) How old the events at that position are — the source's own timestamp on the last event read, against the clock now. This is the field to read as time to catch up: (2s behind) is a healthy feed, and (14h32m behind) is a feed replaying yesterday. It comes from the event headers the reader already has, so it costs no extra query. Omitted before the feed has read an event.

The read field is there to tell "the reader is fine, only publication is blocked" apart from "the feed has stalled" — two situations the ckpt row cannot distinguish, because it shows the flushed position, which is frozen in both. Spirit only advances the flushed position when a flush lands every buffered change, so while any change is held back the checkpoint stops moving by design. If read advances between reports the reader is working normally; if it is also standing still, the feed itself has stopped. The gap between read and the ckpt position is how much re-reading a resume would have to do.

The age next to it answers the question the coordinate cannot: a GTID or file offset looks the same whether the feed is a second or a week behind, and the number of GTIDs still to go is not a time unless you also know the source's commit rate, which nothing here reports. So the age is what tells you whether a resumed migration can converge. A run that resumes from a checkpoint written days ago has to replay those days of binary log before it can cut over, and this is where that shows up — as a large behind figure that shrinks slowly. Watch its rate of decline against the run's remaining time: an age that is falling means the feed is gaining on the source, and one that is flat or growing means it is not, whatever the coordinate is doing. Note it is measured against the Spirit host's clock, so it is only as accurate as the clock skew to the source — which matters not at all at the multi-hour lags that are worth acting on.

ckpt row

Reads <age> ago <position>, or never before the first checkpoint. The position is in the run's coordinate scheme — <file>:<offset> or a GTID set, per GTID auto-detection.

The age is how long ago the resume checkpoint was last written. Checkpoints are attempted every 50 seconds but are skipped while the copier has no resumable watermark yet, so never early in a run is expected. If the age keeps growing, an interrupted migration will resume from further back than you would like. Note that this age says nothing about how stale the position inside it is: a run that resumed from a week-old checkpoint rewrites that row every 50 seconds, so the age reads seconds while the position is a week behind. The (X behind) figure on the binlog row is the one that answers that. A checkpoint that cannot be written at all is fatal — Spirit stops rather than working for hours without being able to record progress.

The position is the binlog coordinate that checkpoint saved: the point a resumed migration would start reading from. Read it against rotations and the server's binlog retention, because resuming only works while this position is still on the server — a run with heavy rotation and a short binlog_expire_logs_seconds can become unresumable long before it fails.

applier row

The applier is the shared write path that both the copier and the replication feed hand work to. Chunks are split into chunklets and queued for a pool of write workers.

Field Meaning
queue Queued chunklets out of the queue capacity. Sitting at capacity (128/128 above) means the writers are the bottleneck and the readers are being backpressured, which is normally the intended state during a copy since it keeps the write side saturated. A queue well below capacity means the copy is read-limited instead.
workers Live write workers. Changes over time when autoscaling engages.
wait-p50 How long a chunklet waits in the queue before a worker picks it up. Far above the write time means the write side is saturated.
write-p50 / write-p90 Time to execute the write against the target. A rising p90 points at the target server rather than at Spirit.

Two more fields appear only when they have something to say, so their presence is itself the signal and their absence means "not the problem":

Field Appears when Meaning
build-p50 Building the statement takes ≥25% of the write time, and at least 1ms Spirit's own CPU is a limit, not the target. No server-side signal reports this, and adding write threads will not help. Build time is contained within write time, not additional to it.
handoff-p50 Handoff reaches 1ms Write workers are backing up behind the single goroutine that publishes completions, rather than behind the target. Adding write threads will not help here either.

Everything Spirit measures about the write path — including the fields not rendered here, such as pending work, mean rows per chunklet, and the remaining p90s — is still emitted to the metrics sink, which is the better source for dashboards.