## 1. Project Overview & Quickstart (github/gh-ost) ## File: README.md # gh-ost [](https://github.com/github/gh-ost/actions/workflows/ci.yml) [](https://github.com/github/gh-ost/actions/workflows/replica-tests.yml) [](https://github.com/github/gh-ost/releases) [](https://github.com/github/gh-ost/releases) #### 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](doc/why-triggerless.md). Instead, `gh-ost` [uses the binary log stream](doc/triggerless-design.md) 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](doc/perks.md) 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](doc/throttle.md), 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](doc/interactive-commands.md) 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](doc/hooks.md) can couple `gh-ost` with your particular environment. Please refer to the [docs](doc) for more information. No, really, read the [docs](doc). ## Usage The [cheatsheet](doc/cheatsheet.md) 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](doc/testing-on-replica.md), 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](doc/interactive-commands.md) Also see: - [requirements and limitations](doc/requirements-and-limitations.md) - [common questions](doc/questions.md) - [what if?](doc/what-if.md) - [the fine print](doc/the-fine-print.md) - [Community questions](https://github.com/github/gh-ost/issues?q=label%3Aquestion) - [Using `gh-ost` on AWS RDS](doc/rds.md) - [Using `gh-ost` on Azure Database for MySQL](doc/azure.md) ## What's in a name? Originally this was named `gh-osc`: GitHub Online Schema Change, in the likes of [Facebook online schema change](https://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932/) and [pt-online-schema-change](https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html). 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](https://github.com/github/gh-ost/blob/master/LICENSE) `gh-ost` uses 3rd party libraries, each with their own license. These are found [here](https://github.com/github/gh-ost/tree/master/vendor). ## Community `gh-ost` is released at a stable state, but with mileage to go. We are [open to pull requests](https://github.com/github/gh-ost/blob/master/.github/CONTRIBUTING.md). Please first discuss your intentions via [Issues](https://github.com/github/gh-ost/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](doc/coding-ghost.md) 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](https://github.com/github/gh-ost/releases/latest) `gh-ost` is a Go project; it is built with Go `1.15` and above. To build on your own, use either: - [script/build](https://github.com/github/gh-ost/blob/master/script/build) - this is the same build script used by CI hence the authoritative; artifact is `./bin/gh-ost` binary. - [build.sh](https://github.com/github/gh-ost/blob/master/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](https://github.com/github/gh-ost/releases) are to be used in production. ## Authors `gh-ost` is designed, authored, reviewed and tested by the database infrastructure team at GitHub: - [@jonahberquist](https://github.com/jonahberquist) - [@ggunson](https://github.com/ggunson) - [@tomkrouper](https://github.com/tomkrouper) - [@shlomi-noach](https://github.com/shlomi-noach) - [@jessbreckenridge](https://github.com/jessbreckenridge) - [@gtowey](https://github.com/gtowey) - [@timvaillancourt](https://github.com/timvaillancourt) --- ## 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][new_issue]! # Azure Database for MySQL ## Limitations - `gh-ost` runs should be setup use [`--assume-rbr`][assume_rbr_docs] 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](https://docs.microsoft.com/en-us/azure/mysql/howto-server-parameters) 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] ``` [new_issue]: https://github.com/github/gh-ost/issues/new [assume_rbr_docs]: https://github.com/github/gh-ost/blob/master/doc/command-line-flags.md#assume-rbr [migrate_test_on_replica_docs]: https://github.com/github/gh-ost/blob/master/doc/cheatsheet.md#c-migratetest-on-replica --- ## 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: ``` [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: ``` 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: ``` 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: ``` 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`](https://golangci-lint.run/). The linter config is located at [`.golangci.yml`](https://github.com/github/gh-ost/blob/master/.golangci.yml) and the `golangci-lint` GitHub Action is located at [`.github/workflows/golangci-lint.yml`](https://github.com/github/gh-ost/blob/master/.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`](#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`](#database) and [`--table`](#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`](#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`](https://dev.mysql.com/doc/refman/8.0/en/performance-schema-metadata-locks-table.html) 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`](#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`](#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`](#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`](#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`](#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`](resume.md) ### 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: ``` [client] user=gromit password=123456 ``` ### concurrent-rowcount Defaults to `true`. See [`exact-rowcount`](#exact-rowcount) ### critical-load Comma delimited status-name=threshold, same format as [`--max-load`](#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.md) ### cut-over-exponential-backoff Wait exponentially longer intervals between failed cut-over attempts. The maximum wait interval is controlled by [`--exponential-backoff-max-interval`](#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`](#alter) statement. ### debug Enables debug logging. This is very verbose. See also [`--verbose`](#verbose), [`--quiet`](#quiet), and [`--stack`](#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`](#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`](https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html). `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`](#concurrent-rowcount) is also specified, this runs in parallel to row copy. Note: [`--concurrent-rowcount`](#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](understanding-output.md) 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`](#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](https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html) for replication positioning. On MySQL this requires `gtid_mode` and `enforce_gtid_consistency` to be set to `ON`. [MariaDB GTIDs](https://mariadb.com/kb/en/gtid/) 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`](subsecond-lag.md) 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`](hooks.md) 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-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`](#throttle-http). ### include-triggers When true, existing triggers on the original table are created on the new table. See also [`--trigger-suffix`](#trigger-suffix) and [`--remove-trigger-suffix-if-exists`](#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`](#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`](#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](cheatsheet.md#a-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`](#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](subsecond-lag.md) ### max-load List of metrics and threshold values; topping the threshold of any will cause throttler to kick in. See also: [`throttling`](throttle.md#status-thresholds) ### 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`](#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`](#ask-pass) or [`--conf`](#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](cut-over.md) 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](interactive-commands.md). ### quiet Quiet mode. Forces logging to errors only, overriding [`--verbose`](#verbose) or [`--debug`](#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`](cheatsheet.md#concurrent-migrations) on the cheatsheet. ### remove-trigger-suffix-if-exists Remove the given suffix from trigger names. Requires [`--include-triggers`](#include-triggers) and [`--trigger-suffix`](#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`](resume.md) ### revert Attempts to revert a completed migration. Must be used with [`--old-table`](#old-table). `--revert` cannot be used together with [`--resume`](#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`](#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`](#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`](#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`](#alter) statement. See also [`--database`](#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`](testing-on-replica.md) ### 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`](#max-lag-millis). The list can be queried and updated dynamically via [interactive commands](interactive-commands.md) ### 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](interactive-commands.md). 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`](#include-triggers). ### tungsten See [`tungsten`](cheatsheet.md#tungsten) on the cheatsheet. ### user MySQL user. ### verbose Enables info-level logging. See also [`--debug`](#debug) and [`--quiet`](#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](https://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932/) 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](https://github.com/github/gh-ost/issues/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](https://github.com/github/gh-ost/blob/master/go/logic/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](https://github.com/github/gh-ost/tree/master/resources/hooks-sample), 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`](https://pkg.go.dev/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=`: modify the `chunk-size`; applies on next running copy-iteration - `dml-batch-size=`: modify the `dml-batch-size`; applies on next applying of binary log events - `max-lag-millis=`: modify the maximum replication lag threshold (milliseconds, minimum value is `100`, i.e. `0.1` second) - `max-load=`: modify the `max-load` config; applies on next running copy-iteration - The `max-load` format must be: `some_status=[,some_status=...]`' - 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=`: modify the `critical-load` config (exceeding these thresholds aborts the operation) - The `critical-load` format must be: `some_status=[,some_status=...]`' - 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=`: 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](cut-over.md) phase, writing a cut over flag file to the given path - `unpostpone`: at a time where `gh-ost` is postponing the [cut-over](cut-over.md) 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](https://github.com/github/gh-ost/tree/master/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](https://github.com/github/gh-ost/tree/master/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](https://docs.github.com/en/contributing). ## Quick links by contributor type * **Hubbers (GitHub employees):** See [CONTRIBUTING.md](https://github.com/github/docs-content/blob/main/CONTRIBUTING.md) in the `docs-content` repository for GitHub-specific processes. * **Open source contributors:** See [CONTRIBUTING.md](https://github.com/github/docs/blob/main/.github/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: * [Finding ways to contribute to open source on GitHub](https://docs.github.com/en/get-started/exploring-projects-on-github/finding-ways-to-contribute-to-open-source-on-github) * [Set up Git](https://docs.github.com/en/get-started/git-basics/set-up-git) * [GitHub flow](https://docs.github.com/en/get-started/using-github/github-flow) * [Collaborating with pull requests](https://docs.github.com/en/github/collaborating-with-pull-requests) ## 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](LICENSE)) * **MIT License** - for code (see [LICENSE-CODE](LICENSE-CODE))