gh-ost Documentation

GitHub's Online Schema-migration Tool for MySQL

13,421 stars Go Go 1 file · ~16,974 tokens #mysql#schema-migrations
RAW Doc

Docker & Self-Hosted Deployment Verified OCI

Deploy gh-ost on any server, homelab, or cloud container platform.

1-Click Terminal Command
bash (docker run) 
docker run -d --name gh-ost -p 8474:8474 --restart always ghcr.io/shopify/toxiproxy:latest
Configured Services & Ports
⚙️ mysql-toxiproxy
image: ghcr.io/shopify/toxiproxy:latest
🔌 Ports: 8474:8474, 23308:23308
docker-compose.yml
yaml (docker-compose.yml) 
services:
  mysql-toxiproxy:
    image: "ghcr.io/shopify/toxiproxy:latest"
    container_name: mysql-toxiproxy
    ports:
      - '8474:8474'
      - '23308:23308'

1. Project Overview & Quickstart (github/gh-ost)

File: README.md

gh-ost

GitHub's online schema migration for MySQL

gh-ost is a triggerless online schema migration solution for MySQL. It is testable and provides pausability, dynamic control/reconfiguration, auditing, and many operational perks.

gh-ost produces a light workload on the master throughout the migration, decoupled from the existing workload on the migrated table.

It has been designed based on years of experience with existing solutions, and changes the paradigm of table migrations.

How?

All existing online-schema-change tools operate in similar manner: they create a ghost table in the likeness of your original table, migrate that table while empty, slowly and incrementally copy data from your original table to the ghost table, meanwhile propagating ongoing changes (any INSERT, DELETE, UPDATE applied to your table) to the ghost table. Finally, at the right time, they replace your original table with the ghost table.

gh-ost uses the same pattern. However it differs from all existing tools by not using triggers. We have recognized the triggers to be the source of many limitations and risks.

Instead, gh-ost uses the binary log stream to capture table changes, and asynchronously applies them onto the ghost table. gh-ost takes upon itself some tasks that other tools leave for the database to perform. As result, gh-ost has greater control over the migration process; can truly suspend it; can truly decouple the migration's write load from the master's workload.

In addition, it offers many operational perks that make it safer, trustworthy and fun to use.

Highlights

  • Build your trust in gh-ost by testing it on replicas. gh-ost will issue same flow as it would have on the master, to migrate a table on a replica, without actually replacing the original table, leaving the replica with two tables you can then compare and satisfy yourself that the tool operates correctly. This is how we continuously test gh-ost in production.
  • True pause: when gh-ost throttles, it truly ceases writes on master: no row copies and no ongoing events processing. By throttling, you return your master to its original workload
  • Dynamic control: you can interactively reconfigure gh-ost, even as migration still runs. You may forcibly initiate throttling.
  • Auditing: you may query gh-ost for status. gh-ost listens on unix socket or TCP.
  • Control over cut-over phase: gh-ost can be instructed to postpone what is probably the most critical step: the swap of tables, until such time that you're comfortably available. No need to worry about ETA being outside office hours.
  • External hooks can couple gh-ost with your particular environment.

Please refer to the docs for more information. No, really, read the docs.

Usage

The cheatsheet has it all. You may be interested in invoking gh-ost in various modes:

  • a noop migration (merely testing that the migration is valid and good to go)
  • a real migration, utilizing a replica (the migration runs on the master; gh-ost figures out identities of servers involved. Required mode if your master uses Statement Based Replication)
  • a real migration, run directly on the master (but gh-ost prefers the former)
  • a real migration on a replica (master untouched)
  • a test migration on a replica, the way for you to build trust with gh-ost's operation.

Our tips:

  • Testing above all, try out --test-on-replica first few times. Better yet, make it continuous. We have multiple replicas where we iterate our entire fleet of production tables, migrating them one by one, checksumming the results, verifying migration is good.
  • For each master migration, first issue a noop
  • Then issue the real thing via --execute.

More tips:

  • Use --exact-rowcount for accurate progress indication
  • Use --postpone-cut-over-flag-file to gain control over cut-over timing
  • Get familiar with the interactive commands

Also see:

What's in a name?

Originally this was named gh-osc: GitHub Online Schema Change, in the likes of Facebook online schema change and pt-online-schema-change.

But then a rare genetic mutation happened, and the c transformed into t. And that sent us down the path of trying to figure out a new acronym. gh-ost (pronounce: Ghost), stands for GitHub's Online Schema Transmogrifier/Translator/Transformer/Transfigurator

License

gh-ost is licensed under the MIT license

gh-ost uses 3rd party libraries, each with their own license. These are found here.

Community

gh-ost is released at a stable state, but with mileage to go. We are open to pull requests. Please first discuss your intentions via Issues.

We develop gh-ost at GitHub and for the community. We may have different priorities than others. From time to time we may suggest a contribution that is not on our immediate roadmap but which may appeal to others.

Please see Coding gh-ost for a guide to getting started developing with gh-ost.

Download/binaries/source

gh-ost is now GA and stable.

gh-ost is available in binary format for Linux and Mac OS/X

Download latest release here

gh-ost is a Go project; it is built with Go 1.15 and above. To build on your own, use either:

  • script/build - this is the same build script used by CI hence the authoritative; artifact is ./bin/gh-ost binary.
  • build.sh for building the release artifacts (tar.gz archives plus .rpm/.deb packages and a SHA256SUMS manifest) in /tmp/gh-ost-release

Generally speaking, master branch is stable, but only releases are to be used in production.

Authors

gh-ost is designed, authored, reviewed and tested by the database infrastructure team at GitHub:

File: doc/azure.md

gh-ost has been updated to work with Azure Database for MySQL however due to GitHub does not use it, this documentation is community driven so if you find a bug please open an issue!

Azure Database for MySQL

Limitations

  • gh-ost runs should be setup use --assume-rbr and use binlog_row_image=FULL.
  • Azure Database for MySQL does not use same user name suffix for master and replica, so master host, user and password need to be pointed out.

Step

  1. Change the replica server's binlog_row_image from MINIMAL to FULL. See guide on Azure document.
  2. Use your gh-ost always with additional 5 parameter
{bash}
gh-ost \
--azure \
--assume-master-host=master-server-dns-name \
--master-user="master-user-name" \
--master-password="master-password" \
--assume-rbr \
[-- other parameters you need]

File: doc/cheatsheet.md

Cheatsheet

Operation modes

gh-ost operates by connecting to potentially multiple servers, as well as imposing itself as a replica in order to streamline binary log events directly from one of those servers. There are various operation modes, which depend on your setup, configuration, and where you want to run the migration.

a. Connect to replica, migrate on master

This is the mode gh-ost expects by default. gh-ost will investigate the replica, crawl up to find the topology's master, and will hook onto it as well. Migration will:

  • Read and write row-data on master
  • Read binary logs events on the replica, apply the changes onto the master
  • Investigates table format, columns & keys, count rows on the replica
  • Read internal changelog events (such as heartbeat) from the replica
  • Cut-over (switch tables) on the master

If your master works with SBR, this is the mode to work with. The replica must be configured with binary logs enabled (log_bin, log_slave_updates) and should have binlog_format=ROW (gh-ost can apply the latter for you).

However even with RBR we suggest this is the least master-intrusive operation mode.

shell
gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--throttle-control-replicas="myreplica.1.com,myreplica.2.com" \
--max-lag-millis=1500 \
--user="gh-ost" \
--password="123456" \
--host=replica.with.rbr.com \
--database="my_schema" \
--table="my_table" \
--verbose \
--alter="engine=innodb" \
--switch-to-rbr \
--allow-master-master \
--cut-over=default \
--exact-rowcount \
--concurrent-rowcount \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
[--execute]

With --execute, migration actually copies data and flips tables. Without it this is a noop run.

b. Connect to master

If you don't have replicas, or do not wish to use them, you are still able to operate directly on the master. gh-ost will do all operations directly on the master. You may still ask it to be considerate of replication lag.

  • Your master must produce binary logs in RBR format.
  • You must approve this mode via --allow-on-master.
shell
gh-ost \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--throttle-control-replicas="myreplica.1.com,myreplica.2.com" \
--max-lag-millis=1500 \
--user="gh-ost" \
--password="123456" \
--host=master.with.rbr.com \
--allow-on-master \
--database="my_schema" \
--table="my_table" \
--verbose \
--alter="engine=innodb" \
--switch-to-rbr \
--allow-master-master \
--cut-over=default \
--exact-rowcount \
--concurrent-rowcount \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
[--execute]

c. Migrate/test on replica

This will perform a migration on the replica. gh-ost will briefly connect to the master but will thereafter perform all operations on the replica without modifying anything on the master.
Throughout the operation, gh-ost will throttle such that the replica is up to date.

  • --migrate-on-replica indicates to gh-ost that it must migrate the table directly on the replica. It will perform the cut-over phase even while replication is running.
  • --test-on-replica indicates the migration is for purpose of testing only. Before cut-over takes place, replication is stopped. Tables are swapped and then swapped back: your original table returns to its original place.
    Both tables are left with replication stopped. You may examine the two and compare data.

Test on replica cheatsheet:

shell
gh-ost \
  --user="gh-ost" \
  --password="123456" \
  --host=replica.with.rbr.com \
  --test-on-replica \
  --database="my_schema" \
  --table="my_table" \
  --verbose \
  --alter="engine=innodb" \
  --initially-drop-ghost-table \
  --initially-drop-old-table \
  --max-load=Threads_running=30 \
  --switch-to-rbr \
  --chunk-size=500 \
  --cut-over=default \
  --exact-rowcount \
  --concurrent-rowcount \
  --serve-socket-file=/tmp/gh-ost.test.sock \
  --panic-flag-file=/tmp/gh-ost.panic.flag \
  --execute

cnf file

You may use a cnf file in the following format:

text
[client]
user=gh-ost
password=123456

You may then remove --user=gh-ost --password=123456 and specify --conf=/path/to/config/file.cnf

Special configurations

Master-master

Master-master setups are supported, but at this time only active-passive. An active-active setup, where both masters write to the migrated table, is not supported at this stage. gh-ost requires you to acknowledge master-master via:

text
gh-ost --allow-master-master

gh-ost will pick one of the masters to work on. You may additionally force gh-ost to pick a particular master of your choice:

text
gh-ost --allow-master-master --assume-master-host=a.specific.master.com

Tungsten

Topologies using tungsten replicator are peculiar in that the participating servers are not actually aware they are replicating. The tungsten replicator looks just like another app issuing queries on those hosts. gh-ost is unable to identify that a server participates in a tungsten topology.

If you choose to migrate directly on master (see above), there's nothing special you need to do.

If you choose to migrate via replica, then you need to make sure Tungsten is configured with log-slave-updates parameter (note this is different from MySQL's own log-slave-updates parameter), otherwise changes will not be in the replica's binlog, causing data to be corrupted after table swap. You must also supply the identity of the master, and indicate this is a tungsten setup, as follows:

text
gh-ost --tungsten --assume-master-host=the.topology.master.com

Also note that --switch-to-rbr does not work for a Tungsten setup as the replication process is external, so you need to make sure binlog_format is set to ROW before Tungsten Replicator connects to the server and starts applying events from the master.

Concurrent migrations

It is possible to run concurrent gh-ost migrations.

  • Never on the exact same table.
  • If running on different replicas, (e.g. table1 on replica1 and table2 on replica2) then no further configuration required.
  • If running from same server (binaries run on same server, regardless of which replica/replicas are used):
    • Make sure not to specify same -serve-socket-file (or let gh-ost pick one for you).
    • You may choose to use same -throttle-flag-file (preferably use -throttle-additional-flag-file, this is exactly the reason there's two, this latter file is for sharing).
    • You may choose to use same -panic-flag-file. This all depends on your flow and how you'd like to control your migrations.
  • If using same inspected box (either master or replica, --host=everyone.uses.this.host) then for each gh-ost process you must also provide a different, unique --replica-server-id. Optionally use process ID ($$ in shell) ; but it's on you to choose a number that does not collide with another gh-ost or another running replica.

File: doc/coding-ghost.md

Getting started with gh-ost development.

Overview

Getting started with gh-ost development is simple!

  • First obtain the repository with git clone or go get.
  • From inside of the repository run script/cibuild.
  • This will bootstrap the environment if needed, format the code, build the code, and then run the unit test.

CI build workflow

script/cibuild performs the following actions will bootstrap the environment to build gh-ost correctly, build, perform syntax checks and run unit tests.

If additional steps are needed, please add them into this workflow so that the workflow remains simple.

`golang-ci` linter

To enforce best-practices, Pull Requests are automatically linted by golang-ci. The linter config is located at .golangci.yml and the golangci-lint GitHub Action is located at .github/workflows/golangci-lint.yml.

To run the golang-ci linters locally (recommended before push), use script/lint.

Notes:

Currently, script/ensure-go-installed will install go for Mac OS X and Linux. We welcome PR's to add other platforms.

File: doc/command-line-flags.md

Command line flags

A more in-depth discussion of various gh-ost command line flags: implementation, implication, use cases.

aliyun-rds

Add this flag when executing on Aliyun RDS.

analyze-ghost-table-before-cutover

Run an explicit ANALYZE TABLE on the ghost table immediately before cut-over — after a postponed cut-over is released, before the atomic swap takes its locks — and abort the migration if the ANALYZE fails, rather than swap in a table with stale InnoDB statistics. Without it, the freshly swapped table can briefly serve traffic with a near-zero row estimate, which the optimizer may cost as a free full scan on hot query paths. This is the same rationale as issue #1418 / PR #1419; this flag is a corrected variant: the ANALYZE runs after the postpone gate releases (so a postponed cut-over still gets fresh statistics) and a failed ANALYZE aborts the migration instead of being ignored. Opt-in; intended for small, non-partitioned tables that are non-empty at copy (ANALYZE TABLE cost grows with partition count, and its statement replicates to replicas).

allow-zero-in-date

Allows the user to make schema changes that include a zero date or zero in date (e.g. adding a datetime default '0000-00-00 00:00:00' column), even if global sql_mode on MySQL has NO_ZERO_IN_DATE,NO_ZERO_DATE.

alter

Mandatory unless using --revert. The schema change to apply to the migrated table. You can pass just the alter options, for example --alter="ADD COLUMN created_at timestamp NULL", together with --database and --table. You can also pass a full ALTER TABLE [database.]table ... statement; an explicit database or table in --alter can be used instead of the corresponding flag.

azure

Add this flag when executing on Azure Database for MySQL.

allow-master-master

See --assume-master-host.

allow-nullable-unique-key

Allows gh-ost to choose a unique key that contains nullable columns. This is dangerous if the chosen key contains NULL values: row identity may be ambiguous and data may be corrupted. Use only when you know the nullable unique key contains no NULL values.

allow-on-master

By default, gh-ost would like you to connect to a replica, from where it figures out the master by itself. This wiring is required should your master execute using binlog_format=STATEMENT.

If, for some reason, you do not wish gh-ost to connect to a replica, you may connect it directly to the master and approve this via --allow-on-master.

allow-setup-metadata-lock-instruments

--allow-setup-metadata-lock-instruments allows gh-ost to enable the metadata_locks table in performance_schema, if it is not already enabled. This is used for a safety check before cut-over.
See also: skip-metadata-lock-check

approve-renamed-columns

When your migration issues a column rename (change column old_name new_name ...) gh-ost analyzes the statement to try and associate the old column name with new column name. Otherwise, the new structure may also look like some column was dropped and another was added.

gh-ost will print out what it thinks the rename implied, but will not issue the migration unless you provide with --approve-renamed-columns.

If you think gh-ost is mistaken and that there's actually no rename involved, you may pass --skip-renamed-columns instead. This will cause gh-ost to disassociate the column values; data will not be copied between those columns.

ask-pass

Prompt for the MySQL password instead of passing it on the command line via --password.

assume-master-host

gh-ost infers the identity of the master server by crawling up the replication topology. You may explicitly tell gh-ost the identity of the master host via --assume-master-host=the.master.com. This is useful in:

  • master-master topologies (together with --allow-master-master), where gh-ost can arbitrarily pick one of the co-masters, and you prefer that it picks a specific one
  • tungsten replicator topologies (together with --tungsten), where gh-ost is unable to crawl and detect the master

assume-rbr

If you happen to know your servers use RBR (Row Based Replication, i.e. binlog_format=ROW), you may specify --assume-rbr. This skips a verification step where gh-ost would issue a STOP SLAVE; START SLAVE.
Skipping this step means gh-ost would not need the SUPER privilege in order to operate.
You may want to use this on Amazon RDS.

attempt-instant-ddl

MySQL 8.0 supports "instant DDL" for some operations. If an alter statement can be completed with instant DDL, only a metadata change is required internally. Instant operations include:

  • Adding a column
  • Dropping a column
  • Dropping an index
  • Extending a varchar column
  • Adding a virtual generated column

It is not reliable to parse the ALTER statement to determine if it is instant or not. This is because the table might be in an older row format, or have some other incompatibility that is difficult to identify.

--attempt-instant-ddl is disabled by default, but the risks of enabling it are relatively minor: gh-ost may need to acquire a metadata lock at the start of the operation. This is not a problem for most scenarios, but it could be a problem for users that start the DDL during a period with long running transactions.

gh-ost will automatically fallback to the normal DDL process if the attempt to use instant DDL is unsuccessful.

binlogsyncer-max-reconnect-attempts

--binlogsyncer-max-reconnect-attempts=0, the maximum number of attempts to re-establish a broken inspector connection for sync binlog. 0 or negative number means infinite retry, default 0

check-flag

Checks whether one or more flags exist in the current gh-ost binary. This is useful for cross-version scripting. Exits with 0 when all flags specified alongside --check-flag exist, non-zero otherwise. Flags that require a value must be given a dummy value.

Example: gh-ost --check-flag --cut-over-lock-timeout-seconds 1 --nice-ratio 0

checkpoint

--checkpoint enables periodic checkpoints of the gh-ost's state so that gh-ost can resume a migration from the checkpoint with --resume. Checkpoints are written to a separate table named _${original_table_name}_ghk. It is recommended to use with --gtid for checkpoints.
See also: resuming-migrations

checkpoint-seconds

--checkpoint-seconds specifies the seconds between checkpoints. Default is 300.

chunk-size

Controls how many rows gh-ost copies in each row-copy iteration. The default is 1000; allowed range is 10 to 100000.

conf

--conf=/path/to/my.cnf: file where credentials are specified. Should be in (or contain) the following format:

text
[client]
user=gromit
password=123456

concurrent-rowcount

Defaults to true. See exact-rowcount

critical-load

Comma delimited status-name=threshold, same format as --max-load.

--critical-load defines a threshold that, when met, gh-ost panics and bails out. The default behavior is to bail out immediately when meeting this threshold.

This may sometimes lead to migrations bailing out on a very short spike, that, while in itself is impacting production and is worth investigating, isn't reason enough to kill a 10-hour migration.

critical-load-hibernate-seconds

When --critical-load-hibernate-seconds is non-zero (e.g. --critical-load-hibernate-seconds=300), critical-load does not panic and bail out; instead, gh-ost goes into hibernation for the specified duration. It will not read/write anything from/to any server during this time. Execution then continues upon waking from hibernation.

If critical-load is met again, gh-ost will repeat this cycle, and never panic and bail out.

critical-load-interval-millis

When --critical-load-interval-millis is specified (e.g. --critical-load-interval-millis=2500), gh-ost gives a second chance: when it meets critical-load threshold, it doesn't bail out. Instead, it starts a timer (in this example: 2.5 seconds) and re-checks critical-load when the timer expires. If critical-load is met again, gh-ost panics and bails out. If not, execution continues.

This is somewhat similar to a Nagios n-times test, where n in our case is always 2.

cut-over

Optional. Default is atomic. Accepted values are atomic, default, and two-step; default is a legacy alias for atomic. atomic performs an atomic table swap where pending connections briefly block and either all see the old table or all see the new table. two-step uses a non-atomic rename sequence where the original table is renamed away before the ghost table takes its place, leaving a brief interval where the table does not exist. See more discussion in cut-over

cut-over-exponential-backoff

Wait exponentially longer intervals between failed cut-over attempts. The maximum wait interval is controlled by --exponential-backoff-max-interval.

cut-over-lock-timeout-seconds

Default 3. Max number of seconds to hold locks on tables while attempting to cut-over (retry attempted when lock exceeds timeout).

database

The database containing the table to migrate. Required unless the database is specified in a full --alter statement.

debug

Enables debug logging. This is very verbose. See also --verbose, --quiet, and --stack.

default-retries

Default number of retries for various operations before panicking. Default is 60.

discard-foreign-keys

Danger: this flag will silently discard any foreign keys existing on your table.

At this time (10-2016) gh-ost does not support foreign keys on migrated tables (it bails out when it notices a FK on the migrated table). However, it is able to support dropping of foreign keys via this flag. If you're trying to get rid of foreign keys in your environment, this is a useful flag.

See also: skip-foreign-key-checks

dml-batch-size

gh-ost reads event from the binary log and applies them onto the ghost table. It does so in batched writes: grouping multiple events to apply in a single transaction. This gives better write throughput as we don't need to sync the transaction log to disk for each event.

The --dml-batch-size flag controls the size of the batched write. Allowed values are 1 - 1000, where 1 means no batching (every event from the binary log is applied onto the ghost table on its own transaction). Default value is 10.

Why is this behavior configurable? Different workloads have different characteristics. Some workloads have very large writes, such that aggregating even 50 writes into a transaction makes for a significant transaction size. On other workloads write rate is high such that one just can't allow for a hundred more syncs to disk per second. The default value of 10 is a modest compromise that should probably work very well for most workloads. Your mileage may vary.

Noteworthy is that setting --dml-batch-size to higher value does not mean gh-ost blocks or waits on writes. The batch size is an upper limit on transaction size, not a minimal one. If gh-ost doesn't have "enough" events in the pipe, it does not wait on the binary log, it just writes what it already has. This conveniently suggests that if write load is light enough for gh-ost to only see a few events in the binary log at a given time, then it is also light enough for gh-ost to apply a fraction of the batch size.

exact-rowcount

A gh-ost execution need to copy whatever rows you have in your existing table onto the ghost table. This can and often will be, a large number. Exactly what that number is?
gh-ost initially estimates the number of rows in your table by issuing an explain select * from your_table. This will use statistics on your table and return with a rough estimate. How rough? It might go as low as half or as high as double the actual number of rows in your table. This is the same method as used in pt-online-schema-change.

gh-ost also supports the --exact-rowcount flag. When this flag is given, two things happen:

  • An initial, authoritative select count(*) from your_table.
    This query may take a long time to complete, but is performed before we begin the massive operations.
    When --concurrent-rowcount is also specified, this runs in parallel to row copy.
    Note: --concurrent-rowcount now defaults to true.
  • A continuous update to the estimate as we make progress applying events.
    We heuristically update the number of rows based on the queries we process from the binlogs.

While the ongoing estimated number of rows is still heuristic, it's almost exact, such that the reported ETA or percentage progress is typically accurate to the second throughout a multiple-hour operation.

execute

Without this parameter, migration is a noop: testing table creation and validity of migration, but not touching data.

exponential-backoff-max-interval

Maximum number of seconds to wait between attempts when exponential backoff is used. Default is 64 seconds. See also --cut-over-exponential-backoff.

force-named-cut-over

If given, a cut-over command must name the migrated table, or else ignored.

force-named-panic

If given, a panic command must name the migrated table, or else ignored.

force-table-names

Table name prefix to be used on the temporary tables.

gcp

Add this flag when executing on a 1st generation Google Cloud Platform (GCP).

gtid

Add this flag to enable support for MySQL replication GTIDs for replication positioning. On MySQL this requires gtid_mode and enforce_gtid_consistency to be set to ON.

MariaDB GTIDs are also supported: gh-ost detects the server flavor automatically and uses the appropriate GTID dialect. MariaDB has no gtid_mode/enforce_gtid_consistency settings — GTIDs are always recorded when binary logging is enabled, so no extra configuration is required beyond --gtid.

heartbeat-interval-millis

Default 100. See subsecond-lag for details.

help

Prints command-line usage and exits.

host

MySQL hostname to connect to. Preferably this is a replica, not the master. Default is 127.0.0.1.

hooks-hint

Arbitrary message injected into hooks via the GH_OST_HOOKS_HINT environment variable.

hooks-hint-owner

Arbitrary owner name injected into hooks via the GH_OST_HOOKS_HINT_OWNER environment variable.

hooks-hint-token

Arbitrary token injected into hooks via the GH_OST_HOOKS_HINT_TOKEN environment variable.

hooks-path

Directory where hook files are found. Empty by default, which disables hooks. Hook files found on this path and conforming to hook naming conventions are executed.

hooks-status-interval

Defaults to 60 seconds. Configures how often the gh-ost-on-status hook is called, see hooks for full details on how to use hooks.

initially-drop-ghost-table

gh-ost maintains two tables while migrating: the ghost table (which is synced from your original table and finally replaces it) and a changelog table, which is used internally for bookkeeping. By default, it panics and aborts if it sees those tables upon startup. Provide --initially-drop-ghost-table and --initially-drop-old-table to let gh-ost know it's OK to drop them beforehand.

We think gh-ost should not take chances or make assumptions about the user's tables. Dropping tables can be a dangerous, locking operation. We let the user explicitly approve such operations.

initially-drop-old-table

See initially-drop-ghost-table

initially-drop-socket-file

Default False. Should gh-ost forcibly delete an existing socket file. Be careful: this might drop the socket file of a running migration!

ignore-http-errors

Ignore HTTP connection errors during checks configured with --throttle-http.

include-triggers

When true, existing triggers on the original table are created on the new table. See also --trigger-suffix and --remove-trigger-suffix-if-exists.

master-password

MySQL password for the master when it differs from the password used for the inspected replica. Requires --assume-master-host.

master-user

MySQL user for the master when it differs from the user used for the inspected replica. Requires --assume-master-host.

max-lag-millis

On a replication topology, this is perhaps the most important migration throttling factor: the maximum lag allowed for migration to work. If lag exceeds this value, migration throttles.

When using Connect to replica, migrate on master, this lag is primarily tested on the very replica gh-ost operates on. Lag is measured by checking the heartbeat events injected by gh-ost itself on the utility changelog table. That is, to measure this replica's lag, gh-ost doesn't need to issue show slave status nor have any external heartbeat mechanism.

When --throttle-control-replicas is provided, throttling also considers lag on specified hosts. Lag measurements on listed hosts is done by querying gh-ost's changelog table, where gh-ost injects a heartbeat.

When using on master or when --allow-on-master is provided, max-lag-millis is also considered a threshold for starting the cutover stage of the migration. If the row copy is complete and the heartbeat lag is less than max-lag-millis cutover phase of the migration will start.

See also: Sub-second replication lag throttling

max-load

List of metrics and threshold values; topping the threshold of any will cause throttler to kick in. See also: throttling

migrate-on-replica

Typically gh-ost is used to migrate tables on a master. If you wish to only perform the migration in full on a replica, connect gh-ost to said replica and pass --migrate-on-replica. gh-ost will briefly connect to the master but otherwise will make no changes on the master. Migration will be fully executed on the replica, while making sure to maintain a small replication lag.

mysql-timeout

Connect, read, and write timeout for MySQL connections, in seconds. The value is applied to the MySQL driver's timeout, readTimeout, and writeTimeout DSN parameters. It is configured on the initial inspector connection and is copied to related gh-ost MySQL connections, including the applier/master connection and throttle-control replica checks. The default 0 uses the driver's default behavior.

nice-ratio

Makes row-copy operations sleep after each chunk, proportional to the time spent copying that chunk. Range is 0.0 to 100.0; default 0 is most aggressive. For example, --nice-ratio=1 sleeps one millisecond for every millisecond spent copying rows.

ok-to-drop-table

Allows gh-ost to drop the old table at the end of a successful migration. This is disabled by default because dropping a table can be a long locking operation.

old-table

The old table name to use when --revert is enabled, for example _mytable_del.

panic-flag-file

When this file is created, gh-ost immediately terminates without cleanup.

panic-on-warnings

When this flag is set, gh-ost will panic when SQL warnings indicating data loss are encountered when copying data. This flag helps prevent data loss scenarios with migrations touching unique keys, column collation and types, as well as NOT NULL constraints, where MySQL will silently drop inserted rows that no longer satisfy the updated constraint (also dependent on the configured sql_mode).

While panic-on-warnings is currently disabled by defaults, it will default to true in a future version of gh-ost.

password

MySQL password. To avoid passing the password on the command line, use --ask-pass or --conf.

port

MySQL port to connect to. Preferably this is a replica, not the master. Default is 3306.

postpone-cut-over-flag-file

Indicate a file name, such that the final cut-over step does not take place as long as the file exists.
When this flag is set, gh-ost expects the file to exist on startup, or else tries to create it. gh-ost exits with error if the file does not exist and gh-ost is unable to create it.
With this flag set, the migration will cut-over upon deletion of the file or upon cut-over interactive command.

quiet

Quiet mode. Forces logging to errors only, overriding --verbose or --debug if they are also provided.

replica-server-id

Defaults to 99999. If you run multiple migrations then you must provide a different, unique --replica-server-id for each gh-ost process.
Optionally involve the process ID, for example: --replica-server-id=$((1000000000+$$)).

It's on you to choose a number that does not collide with another gh-ost or another running replica.
See also: concurrent-migrations on the cheatsheet.

remove-trigger-suffix-if-exists

Remove the given suffix from trigger names. Requires --include-triggers and --trigger-suffix.

replication-lag-query

Deprecated. gh-ost uses an internal subsecond-resolution lag query instead.

resume

--resume attempts to resume a migration that was previously interrupted from the last checkpoint. The first gh-ost invocation must run with --checkpoint and have successfully written a checkpoint in order for --resume to work.
See also: resuming-migrations

revert

Attempts to revert a completed migration. Must be used with --old-table. --revert cannot be used together with --resume, and ignores migration options such as --alter, --attempt-instant-ddl, --include-triggers, and --discard-foreign-keys.

runtime-metrics-interval

Seconds between Go runtime memory/GC gauge samples. Requires --statsd-addr. Default is 10; 0 disables runtime metrics.

serve-socket-file

Defaults to an auto-determined and advertised upon startup file. Defines Unix socket file to serve on.

serve-tcp-port

TCP port for gh-ost to serve its interactive interface on. Default is disabled.

skip-foreign-key-checks

By default gh-ost verifies no foreign keys exist on the migrated table. On servers with large number of tables this check can take a long time. If you're absolutely certain no foreign keys exist (table does not reference other table nor is referenced by other tables) and wish to save the check time, provide with --skip-foreign-key-checks.

skip-metadata-lock-check

By default gh-ost performs a check before the cut-over to ensure the rename session holds the exclusive metadata lock on the table. In case performance_schema.metadata_locks cannot be enabled on your setup, this check can be skipped with --skip-metadata-lock-check.
:warning: Disabling this check involves the small chance of data loss in case a session accesses the ghost table during cut-over. See https://github.com/github/gh-ost/pull/1536 for details.

See also: allow-setup-metadata-lock-instruments

skip-port-validation

Skips port validation for MySQL connections.

skip-strict-mode

By default gh-ost enforces STRICT_ALL_TABLES sql_mode as a safety measure. In some cases this changes the behaviour of other modes (namely ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE) which may lead to errors during migration. Use --skip-strict-mode to explicitly tell gh-ost not to enforce this. Danger This may have some unexpected disastrous side effects.

skip-renamed-columns

See approve-renamed-columns

ssl

By default gh-ost does not use ssl/tls connections to the database servers when performing migrations. This flag instructs gh-ost to use encrypted connections. If enabled, gh-ost will use the system's ca certificate pool for server certificate verification. If a different certificate is needed for server verification, see --ssl-ca. If you wish to skip server verification, but still use encrypted connections, use with --ssl-allow-insecure.

ssl-allow-insecure

Allows gh-ost to connect to the MySQL servers using encrypted connections, but without verifying the validity of the certificate provided by the server during the connection. Requires --ssl.

ssl-ca

--ssl-ca=/path/to/ca-cert.pem: ca certificate file (in PEM format) to use for server certificate verification. If specified, the default system ca cert pool will not be used for verification, only the ca cert provided here. Requires --ssl.

ssl-cert

--ssl-cert=/path/to/ssl-cert.crt: SSL public key certificate file (in PEM format).

ssl-key

--ssl-key=/path/to/ssl-key.key: SSL private key file (in PEM format).

stack

Adds a stack trace when logging errors.

statsd-addr

StatsD endpoint, either host:port or a Unix socket. Empty disables StatsD.

statsd-tags

Global StatsD tags applied to every metric. This flag is repeatable and uses key:value format. Example: --statsd-tags 'env:prod' --statsd-tags 'service:gh-ost'.

storage-engine

Default is innodb, and rocksdb support is currently experimental. InnoDB and RocksDB are both transactional engines, supporting both shared and exclusive row locks.

But RocksDB currently lacks a few features support compared to InnoDB:

  • Gap Locks
  • Foreign Key
  • Generated Columns
  • Spatial
  • Geometry

When --storage-engine=rocksdb, gh-ost will make some changes necessary (e.g. sets isolation level to READ_COMMITTED) to support RocksDB.

charset

The default charset for the database connection is utf8mb4, utf8, latin1. The ability to specify character set and collation is supported, eg: utf8mb4_general_ci,utf8_general_ci,latin1.

switch-to-rbr

Allows gh-ost to automatically switch the replica's binary log format to ROW, if needed. The format is not switched back automatically.

table

The table name to migrate, without the database name. Required unless the table is specified in a full --alter statement. See also --database.

test-on-replica

Issue the migration on a replica; do not modify data on master. Useful for validating, testing and benchmarking. See testing-on-replica

test-on-replica-skip-replica-stop

Default False. When --test-on-replica is enabled, do not issue commands stop replication (requires --test-on-replica).

throttle-additional-flag-file

An additional flag file that pauses operation while it exists. Defaults to /tmp/gh-ost.throttle and is useful for throttling multiple gh-ost operations at once.

throttle-flag-file

A flag file that pauses operation while it exists. Prefer a file name specific to the table being altered.

throttle-query

A custom query issued every second to decide whether the migration should throttle. The query runs on the migrated server and should return 0 for no throttle and a value greater than 0 to throttle. Keep this query lightweight.

throttle-control-replicas

Provide a command delimited list of replicas; gh-ost will throttle when any of the given replicas lag beyond --max-lag-millis. The list can be queried and updated dynamically via interactive commands

throttle-http

Provide an HTTP endpoint; gh-ost will issue HEAD requests on given URL and throttle whenever response status code is not 200. The URL can be queried and updated dynamically via interactive commands. Empty URL disables the HTTP check.

throttle-http-interval-millis

Defaults to 100. Configures the HTTP throttle check interval in milliseconds.

throttle-http-timeout-millis

Defaults to 1000 (1 second). Configures the HTTP throttler check timeout in milliseconds.

timestamp-old-table

Makes the old table include a timestamp value. The old table is what the original table is renamed to at the end of a successful migration. For example, if the table is gh_ost_test, then the old table would normally be _gh_ost_test_del. With --timestamp-old-table it would be, for example, _gh_ost_test_20170221103147_del.

trigger-suffix

Adds a suffix to trigger names, for example _v2. Requires --include-triggers.

tungsten

See tungsten on the cheatsheet.

user

MySQL user.

verbose

Enables info-level logging. See also --debug and --quiet.

version

Prints the gh-ost version and exits.

File: doc/cut-over.md

Cut-over step

The cut-over is the final major step of the migration: it's the moment where your original table is pushed aside, and the ghost table (the one we secretly altered and operated on throughout the process) takes its place.

MySQL poses some limitations on how the table swap can take place. While it supports an atomic swap, it does not allow a connection to swap tables it holds under lock.

The facebook OSC tool documents this nicely. Look for "Cut-over phase". The Facebook solution uses a non-atomic swap: the original table is first renamed and pushed aside, then the ghost table is renamed to take its place. In between the two renames there's a brief period of time where your table just does not exist, and queries will fail.

gh-ost solves this by using an atomic, two-step blocking swap: while one connection holds the lock, another attempts the atomic RENAME. The RENAME is guaranteed to not be executed prematurely by positioning a sentry table which blocks the RENAME operation until gh-ost is satisfied all is in order.

This solution either:

  • executes successfully, in which case the tables are swapped atomically and pending connections are blocked for a brief period of time, proceeding to operate on the newly migrated table
  • or fails, due to timeout or death of some connection, in which case we are naturally returning to pre-cut-over phase, where the original table is still in place and accessible. This releases the pending connections, which are able again to write to the table, and gh-ost is then able to make another attempt at the cut-over.

Also note:

  • With --migrate-on-replica the cut-over is executed in exactly the same way as on master.
  • With --test-on-replica the replication is first stopped; then the cut-over is executed just as on master, but then reverted (tables rename forth then back again).

Internals of the atomic cut-over are discussed in Issue #82.

At this time the command-line argument --cut-over is supported, and defaults to the atomic cut-over algorithm described above. Also supported is --cut-over=two-step, which uses the FB non-atomic algorithm. We recommend using the default cut-over that has been battle tested in our production environments.

File: doc/hooks.md

Hooks

gh-ost supports hooks: external processes which gh-ost executes at particular points of interest.

Use cases include:

  • You wish to be notified by mail when a migration completes/fails
  • You wish to be notified when gh-ost postpones cut-over (at your demand), thus ready to complete (at your leisure)
  • RDS users who wish to --test-on-replica, but who cannot have gh-ost issue a STOP SLAVE, would use a hook to command RDS to stop replication
  • Send a status message to your chatops every hour
  • Perform cleanup on the ghost table (drop/rename/nibble) once migration completes
  • etc.

gh-ost defines certain points of interest (event types), and executes hooks at those points.

Notes:

  • You may have more than one hook per event type.
  • gh-ost will invoke relevant hooks sequentially and synchronously
    • thus, you would generally like the hooks to execute as fast as possible, or otherwise issue tasks in the background
  • A hook returning with error code will propagate the error in gh-ost. Thus, you are able to force gh-ost to fail migration on your conditions.
    • Make sure to only return an error code when you do indeed wish to fail the rest of the migration

Creating hooks

All hooks are expected to reside in a single directory. This directory is indicated by --hooks-path. When not provided, no hooks are executed.

gh-ost will dynamically search for hooks in said directory. You may add and remove hooks to/from this directory as gh-ost makes progress (though likely you don't want to). Hook files are expected to be executable processes.

In an effort to simplify code and to standardize usage, gh-ost expects hooks in explicit naming conventions. As an example, the onStartup hook expects processes named gh-ost-on-startup*. It will match and accept files named:

  • gh-ost-on-startup
  • gh-ost-on-startup--send-notification-mail
  • gh-ost-on-startup12345
  • etc.

The full list of supported hooks is best found in code: hooks.go. Documentation will always be a bit behind. At this time, though, the following are recognized:

  • gh-ost-on-startup
  • gh-ost-on-validated
  • gh-ost-on-rowcount-complete
  • gh-ost-on-before-row-copy
  • gh-ost-on-status
  • gh-ost-on-interactive-command
  • gh-ost-on-row-copy-complete
  • gh-ost-on-stop-replication
  • gh-ost-on-start-replication
  • gh-ost-on-begin-postponed
  • gh-ost-on-before-cut-over
  • gh-ost-on-success
  • gh-ost-on-failure
  • gh-ost-on-batch-copy-retry

Context

gh-ost will set environment variables per hook invocation. Hooks are then able to read those variables, indicating schema name, table name, alter statement, migrated host name etc. Some variables are available on all hooks, and some are available on relevant hooks.

The following variables are available on all hooks:

  • GH_OST_DATABASE_NAME
  • GH_OST_TABLE_NAME
  • GH_OST_GHOST_TABLE_NAME
  • GH_OST_OLD_TABLE_NAME - the name the original table will be renamed to at the end of operation
  • GH_OST_DDL
  • GH_OST_ELAPSED_SECONDS - total runtime
  • GH_OST_ELAPSED_COPY_SECONDS - row-copy time (excluding startup, row-count and postpone time)
  • GH_OST_ESTIMATED_ROWS - estimated total rows in table
  • GH_OST_COPIED_ROWS - number of rows copied by gh-ost
  • GH_OST_INSPECTED_LAG - lag in seconds (floating point) of inspected server
  • GH_OST_HEARTBEAT_LAG - lag in seconds (floating point) of heartbeat
  • GH_OST_PROGRESS - progress pct ([0..100], floating point) of migration
  • GH_OST_ETA_SECONDS - estimated duration until migration finishes in seconds
  • GH_OST_MIGRATED_HOST
  • GH_OST_INSPECTED_HOST
  • GH_OST_EXECUTING_HOST
  • GH_OST_HOOKS_HINT - copy of --hooks-hint value
  • GH_OST_HOOKS_HINT_OWNER - copy of --hooks-hint-owner value
  • GH_OST_HOOKS_HINT_TOKEN - copy of --hooks-hint-token value
  • GH_OST_DRY_RUN - whether or not the gh-ost run is a dry run
  • GH_OST_REVERT - whether or not gh-ost is running in revert mode

The following variable are available on particular hooks:

  • GH_OST_INSTANT_DDL is only available in gh-ost-on-success. The value is true if instant DDL was successful, and false if it was not.
  • GH_OST_COMMAND is only available in gh-ost-on-interactive-command
  • GH_OST_STATUS is only available in gh-ost-on-status
  • GH_OST_LAST_BATCH_COPY_ERROR is only available in gh-ost-on-batch-copy-retry

Examples

See sample hooks, as bash implementation samples.

Embedded usage: registering Go callbacks

When gh-ost is consumed as a library (importing github.com/github/gh-ost/go/logic), callers can register Go functions for any hook event instead of, or in addition to, the on-disk script contract. Implement the base.Hooks interface and assign it to MigrationContext.Hooks before calling logic.NewMigrator:

go
import (
    "github.com/github/gh-ost/go/base"
    "github.com/github/gh-ost/go/logic"
)

const version = "1.1.8"

type myHooks struct{}

func (myHooks) OnSuccess(instantDDL bool) error { return nil }
func (myHooks) OnFailure() error                { return nil }
// ... implement the remaining base.Hooks methods.

ctx := base.NewMigrationContext()
// ... configure ctx (DatabaseName, OriginalTableName, AlterStatement, etc.)

ctx.Hooks = &myHooks{}
m := logic.NewMigrator(ctx, version)
err := m.Migrate()

To run shell hooks from --hooks-path and Go callbacks together, wrap both in logic.CompositeHooks. Each member is invoked in order; the first non-nil error short-circuits, matching the script executor's behavior:

go
ctx.Hooks = logic.CompositeHooks{
    logic.NewHooksExecutor(ctx), // existing scripts under HooksPath
    &myHooks{},                  // additional Go callbacks
}

MigrationContext.Hooks is opt-in. When it is nil, NewMigrator wires the default script executor and behavior is identical to the CLI. Hooks are read once at NewMigrator time, so reassigning the field afterwards has no effect on the running migration.

File: doc/interactive-commands.md

Interactive commands

gh-ost is designed to be operations friendly. To that effect, it allows the user to control its behavior even while it is running.

Interactive interfaces

gh-ost listens on:

  • Unix socket file: either provided via --serve-socket-file or determined by gh-ost, this interface is always up.
    When self-determined, gh-ost will advertise the identify of socket file upon start up and throughout the migration.
  • TCP: if --serve-tcp-port is provided

Both interfaces may serve at the same time. Both respond to simple text command, which makes it easy to interact via shell.

Known commands

  • help: shows a brief list of available commands
  • status: returns a detailed status summary of migration progress and configuration
  • sup: returns a brief status summary of migration progress
  • cpu-profile: returns a base64-encoded runtime/pprof CPU profile using a duration, default: 30s. Comma-separated options gzip and/or block (blocked profile) may follow the profile duration
  • coordinates: returns recent (though not exactly up to date) binary log coordinates of the inspected server
  • applier: returns the hostname of the applier
  • inspector: returns the hostname of the inspector
  • chunk-size=<newsize>: modify the chunk-size; applies on next running copy-iteration
  • dml-batch-size=<newsize>: modify the dml-batch-size; applies on next applying of binary log events
  • max-lag-millis=<max-lag>: modify the maximum replication lag threshold (milliseconds, minimum value is 100, i.e. 0.1 second)
  • max-load=<max-load-thresholds>: modify the max-load config; applies on next running copy-iteration
    • The max-load format must be: some_status=<numeric-threshold>[,some_status=<numeric-threshold>...]'
    • For example: Threads_running=50,threads_connected=1000, and you would then write/echo max-load=Threads_running=50,threads_connected=1000 to the socket.
  • critical-load=<critical-load-thresholds>: modify the critical-load config (exceeding these thresholds aborts the operation)
    • The critical-load format must be: some_status=<numeric-threshold>[,some_status=<numeric-threshold>...]'
    • For example: Threads_running=1000,threads_connected=5000, and you would then write/echo critical-load=Threads_running=1000,threads_connected=5000 to the socket.
  • nice-ratio=<ratio>: change nice ratio: 0 for aggressive (not nice, not sleeping), positive integer n:
    • For any 1ms spent copying rows, spend n*1ms units of time sleeping.
    • Examples: assume a single rows chunk copy takes 100ms to complete.
      • nice-ratio=0.5 will cause gh-ost to sleep for 50ms immediately following.
      • nice-ratio=1 will cause gh-ost to sleep for 100ms, effectively doubling runtime
      • value of 2 will effectively triple the runtime; etc.
  • throttle-http: change throttle HTTP endpoint
  • throttle-query: change throttle query
  • throttle-control-replicas='replica1,replica2': change list of throttle-control replicas, these are replicas gh-ost will check. This takes a comma separated list of replica's to check and replaces the previous list.
  • throttle: force migration suspend
  • no-throttle: cancel forced suspension (though other throttling reasons may still apply)
  • postpone-cut-over-flag-file=: Postpone the cut-over phase, writing a cut over flag file to the given path
  • unpostpone: at a time where gh-ost is postponing the cut-over phase, instruct gh-ost to stop postponing and proceed immediately to cut-over.
  • panic: immediately panic and abort operation

Querying for data

For commands that accept an argument as value, pass ? (question mark) to get current value rather than set a new one.

Examples

While migration is running:

shell
$ echo status | nc -U /tmp/gh-ost.test.sample_data_0.sock
# Migrating `test`.`sample_data_0`; Ghost table is `test`.`_sample_data_0_gst`
# Migration started at Tue Jun 07 11:45:16 +0200 2016
# chunk-size: 200; max lag: 1500ms; dml-batch-size: 10; max-load: map[Threads_connected:20]
# Throttle additional flag file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.test.sample_data_0.sock
# Serving on TCP port: 10001
Copy: 0/2915 0.0%; Applied: 0; Backlog: 0/100; Time: 41s(total), 40s(copy); streamer: mysql-bin.000550:49942; Lag: 0.01s, HeartbeatLag: 0.01s, State: throttled, flag-file; ETA: N/A
shell
$ echo "chunk-size=250" | nc -U /tmp/gh-ost.test.sample_data_0.sock
# Migrating `test`.`sample_data_0`; Ghost table is `test`.`_sample_data_0_gst`
# Migration started at Tue Jun 07 11:56:03 +0200 2016
# chunk-size: 250; max lag: 1500ms; dml-batch-size: 10; max-load: map[Threads_connected:20]
# Throttle additional flag file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.test.sample_data_0.sock
# Serving on TCP port: 10001
shell
$ echo "chunk-size=?" | nc -U /tmp/gh-ost.test.sample_data_0.sock
250
shell
$ echo throttle | nc -U /tmp/gh-ost.test.sample_data_0.sock

$ echo status | nc -U /tmp/gh-ost.test.sample_data_0.sock
# Migrating `test`.`sample_data_0`; Ghost table is `test`.`_sample_data_0_gst`
# Migration started at Tue Jun 07 11:56:03 +0200 2016
# chunk-size: 250; max lag: 1500ms; max-load: map[Threads_connected:20]
# Throttle additional flag file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.test.sample_data_0.sock
# Serving on TCP port: 10001
Copy: 0/2915 0.0%; Applied: 0; Backlog: 0/100; Time: 59s(total), 59s(copy); streamer: mysql-bin.000551:68067; Lag: 0.01s, HeartbeatLag: 0.01s, State: throttled, commanded by user; ETA: N/A

File: doc/local-tests.md

Local tests

gh-ost is continuously tested in production via --test-on-replica alter='engine=innodb'. These tests check the GitHub workload and usage, but not necessarily the general case.

Local tests are an additional layer of tests used for continuous integration tests and local development.

Local tests test explicit use cases, such as column renames, mix of time zones, special types and alters. Traits of a single test:

  • Composed of a single table.
  • A single alter.
  • By default the alter is engine=innodb, but this can be overridden per-test
  • Scheduled DML operations, executed via event_scheduler.
  • gh-ost is set to execute and throttle for 5 seconds, at which time all tested DMLs are expected to operate.
  • The test requires a replication topology and utilizes --test-on-replica
  • The test checksums the two tables (original and ghost) and expects identical checksum
  • By default the test selects all (*) columns, but this can be overridden per-test

Tests are found under localtests. A single test is a subdirectory and tests are iterated alphabetically.

New data-integrity, synchronization issues or otherwise concerns are expected to be tested by new test cases.

Run with docker compose

Local tests can be run locally with docker compose using the helper script script/docker-gh-ost-replica-tests.

Example usage:

shell
# create primary-replica containers with specified mysql image
TEST_MYSQL_IMAGE="mysql-server:8.0.16" ./script/docker-gh-ost-replica-tests up

# run all tests
./script/docker-gh-ost-replica-tests run

# cleanup containers
./script/docker-gh-ost-replica-tests down

Pass the -t flag to run the tests with a toxiproxy between gh-ost and the MySQL replica. This simulates network conditions where MySQL connections are closed unexpectedly.

shell
# run tests with toxiproxy
./script/docker-gh-ost-replica-tests up -t
./script/docker-gh-ost-replica-tests run -t

File: doc/migrating-with-sbr.md

Migrating with Statement Based Replication

Even though gh-ost relies on Row Based Replication (RBR), it does not mean you can't keep your Statement Based Replication (SBR).

gh-ost is happy to, and actually prefers and suggests to, connect to a replica. On this replica, it is happy to:

  • issue the heavyweight INFORMATION_SCHEMA queries that make a table structure analysis
  • issue a select count(*) from mydb.mytable, should --exact-rowcount be provided
  • connect itself as a fake replica to get the binary log stream

All of the above can be executed on the master, but we're more comfortable that they execute on a replica.

Please note the third item: gh-ost connects as a fake replica and pulls the binary logs. This is how gh-ost finds the table's changelog: it looks up entries in the binary log.

The magic is that your master can still produce SBR, but if you have a replica with log-slave-updates, you can also configure it to have binlog_format='ROW'. Such a replica accepts SBR statements from its master, and produces RBR statements onto its binary logs.

gh-ost is happy to modify the binlog_format on the replica for you:

  • If you supply --switch-to-rbr, gh-ost will convert the binlog format for you, and restart replication to make sure this takes effect.
  • If your replica is an intermediate master, i.e. further serves as a master to other replicas, gh-ost will not convert the binlog_format.
  • At any case, gh-ost will not convert back to STATEMENT (SBR). This is because you may be running multiple migrations concurrently. Being able to run concurrent migrations is one of the design goals of this tool. It's your own responsibility to switch back to SBR once all pending migrations are complete.

Summary

  • If you're already using RBR, all is well for you
  • If not, convert one of your replicas to binlog_format='ROW', or let gh-ost do this for you.

2. Official Technical Reference & Guides (github/docs)

GitHub Docs

Welcome to GitHub Docs! GitHub’s documentation is open source, meaning anyone from inside or outside the company can contribute. For full contributing guidelines, visit our contributing guide.

  • Hubbers (GitHub employees): See CONTRIBUTING.md in the docs-content repository for GitHub-specific processes.

  • Open source contributors: See CONTRIBUTING.md in the docs repository for a quick-start summary.

How we sync changes across Docs repositories

There are two GitHub Docs repositories:

  • github/docs (public): Open to external contributions

  • github/docs-internal (private): For GitHub employee contributions.

The two repositories sync frequently. Content changes in one are reflected in the other. Hubbers might prefer to post in docs when working with a customer, but docs has limitations on the types of contributions it accepts to safeguard the site and our workflows. Internal contributions should usually go to docs-internal.

Important: The docs repository accepts contributions to content files (.md files in /content and select /data sections like reusables only). Infrastructure files, workflows, and site-building code are not open for external modification.

New to contributing

Here are some resources to help you get started with open source contributions:

License

This project is dual-licensed under:

  • Creative Commons Attribution 4.0 - for documentation and content in the assets, content, and data folders (see LICENSE)
  • MIT License - for code (see LICENSE-CODE)

Explore Related Technologies & Topics

View All 9,111+ Repositories →

Discover similar high-velocity repositories, agent skills, and OpenAPI specifications across the ecosystem.

#mysql Browse mysql catalog → #schema-migrations Browse schema-migrations catalog → ⚡ MCP Servers Model Context Protocol → 🧠 Agent Skills Prompt & rule files → 🔌 OpenAPI Specs REST & Swagger schemas →