Description

This PR introduces revertible migrations, following the approach suggested by @jonahberquist and outlined in #302 (comment). In case a migration causes production impact after the cut-over, it can be reverted quickly while preserving the writes that happened after the cut-over.

Usage: When gh-ost is invoked with the --checkpoint flag and the migration completes, the migration can be reverted by invoking gh-ost again with the --revert flag and the --old-table flag specifying the name of the "old" table from the first migration e.g. _mytable_del. Also see docs/revert.md.

Note that the checkpoint table (name ends with _ghk) will not be automatically dropped unless --ok-to-drop-table is provided.

Hooks: gh-ost hook scripts now include the GH_OST_REVERT environment variable with value "true" or "false", indicating if gh-ost is running in revert mode.

Closes #302.

In case this PR introduced Go code changes:

• contributed code is using same conventions as original code
• script/cibuild returns with no formatting errors, build errors or unit test errors.

Details

When gh-ost is invoked with --checkpoint it will write a final checkpoint to the checkpoint (_ghk) table after the cut-over is successful. This checkpoint contains the binlog coordinates of the last inserted AllEventsUpToLockProcessed row in the changelog (_ghc) table. During the cut-over stage, AllEventsUpToLockProcessed is written to the _ghc table after the original table is locked. Therefore no writes after the checkpoint coordinates are applied to the original table.

After the migration completes, the original table is renamed to e.g. _mytable_del. Invoking gh-ost with --revert --old-table="_mytable_del" will read the checkpoint and start application of DML events to _mytable_del starting from the checkpoint coordinates. This is possible as long as the binlogs containing these coordinates still exist. The cut-over then proceeds the same as a regular migration, renaming the "old" table to _mytable_rev_del.

In summary, the original table is now reinstated as mytable and all writes since the first cut-over have been applied to it.

Testing

On a testing MySQL primary-replica cluster, I created a sysbench test table with 5M rows:

CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
)

I ran gh-ost with --checkpoint --gtid --alter="drop index k_1" while under sysbench oltp_write_only workload, with ~3000 qps.

After the cut-over, I waited 60s before starting gh-ost --revert. Before the revert migration finished, I killed the sysbench workload. When the revert was complete, I checksummed the original and _rev_del tables to verify data integrity ✅

Next I repeated the test, but started the revert using a different replica than the original migration. ✅