You signed in with another tab or window.Reload to refresh your session.You signed out in another tab or window.Reload to refresh your session.You switched accounts on another tab or window.Reload to refresh your session.Dismiss alert
This PR introduces revertible migrations, following the approach suggested by@jonahberquist. 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.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.
`gh-ost` can attempt to revert a previously completed migration if the follow conditions are met:
4
+
- The first`gh-ost` process was invoked with`--checkpoint`
5
+
- The checkpoint table (name ends with`_ghk`) still exists
6
+
- The binlogs from the time of the migration's cut-over still exist on the replica gh-ost is inspecting (specified by`--host`)
7
+
8
+
To revert, find the name of the "old" table from the original migration e.g.`_mytable_del`. Then invoke`gh-ost` with the same arguments and the flags`--revert` and`--old-table="_mytable_del"`.
9
+
gh-ost will read the binlog coordinates of the original cut-over from the checkpoint table and bring the old table up to date. Then it performs another cut-over to complete the reversion.
10
+
Note that the checkpoint table (name ends with_ghk) will not be automatically dropped unless`--ok-to-drop-table` is provided.
11
+
12
+
>[!WARNING]
13
+
>It is recommended use`--checkpoint` with`--gtid` enabled so that checkpoint binlog coordinates store GTID sets rather than file positions. In that case,`gh-ost` can revert using a different replica than it originally attached to.
14
+
15
+
###❗ Note ❗
16
+
Reverting is roughly equivalent to applying the "reverse" migration._Before attempting to revert you should determine if the reverse migration is possible and does not involve any unacceptable data loss._
17
+
18
+
For example: if the original migration drops a`NOT NULL` column that has no`DEFAULT` then the reverse migration adds the column. In this case, the reverse migration is impossible if rows were added after the original cut-over and the revert will fail.
19
+
Another example: if the original migration modifies a`VARCHAR(32)` column to`VARCHAR(64)`, the reverse migration truncates the`VARCHAR(64)` column to`VARCHAR(32)`. If values were inserted with length > 32 after the cut-over then the revert will fail.
20
+
21
+
22
+
##Example
23
+
The migration starts with a`gh-ost` invocation such as:
24
+
```shell
25
+
gh-ost \
26
+
--chunk-size=100 \
27
+
--host=replica1.company.com \
28
+
--database="mydb" \
29
+
--table="mytable" \
30
+
--alter="drop key idx1"
31
+
--gtid \
32
+
--checkpoint \
33
+
--checkpoint-seconds=60 \
34
+
--execute
35
+
```
36
+
37
+
In this example`gh-ost` writes a cut-over checkpoint to`_mytable_ghk` after the cut-over is successful. The original table is renamed to`_mytable_del`.
38
+
39
+
Suppose that dropping the index causes problems, the migration can be revert with:
40
+
```shell
41
+
# revert migration
42
+
gh-ost \
43
+
--chunk-size=100 \
44
+
--host=replica1.company.com \
45
+
--database="mydb" \
46
+
--table="mytable" \
47
+
--old-table="_mytable_del"
48
+
--gtid \
49
+
--checkpoint \
50
+
--checkpoint-seconds=60 \
51
+
--revert \
52
+
--execute
53
+
```
54
+
55
+
gh-ost then reconnects at the binlog coordinates stored in the cut-over checkpoint and applies DMLs until the old table is up-to-date.
56
+
Note that the "reverse" migration is`ADD KEY idx(...)` so there is no potential data loss to consider in this case.
flag.Int64Var(&migrationContext.CheckpointIntervalSeconds,"checkpoint-seconds",300,"The number of seconds between checkpoints")
150
150
flag.BoolVar(&migrationContext.Resume,"resume",false,"Attempt to resume migration from checkpoint")
151
+
flag.BoolVar(&migrationContext.Revert,"revert",false,"Attempt to revert completed migration")
152
+
flag.StringVar(&migrationContext.OldTableName,"old-table","","The name of the old table when using --revert, e.g. '_mytable_del'")
151
153
152
154
maxLoad:=flag.String("max-load","","Comma delimited status-name=threshold. e.g: 'Threads_running=100,Threads_connected=500'. When status exceeds threshold, app throttles writes")
153
155
criticalLoad:=flag.String("critical-load","","Comma delimited status-name=threshold, same format as --max-load. When status exceeds threshold, app panics and quits")