Posts
gh-ost

gh-ost

October 31, 2021

gh-ost

gh-ost is an online migration tool for MySQL developed by GitHub.

There is a video to describe what issue gh-ost is used to solve.

Getting Started

There are documents that are helpful for this article.

Prerequisite

  • When gh-ost connects to a replica and migrates on master
    • If a replication is SBR
      • log_bin and log_slave_updates must be enabled on a read replica server
      • binlog_format=ROW
    • If a replication is RBR
  • When gh-ost connects to a main DB and migrates on master
    • A replication must be RBR

Build a binary from source code 

> wget https://github.com/github/gh-ost/releases/download/v1.1.2/gh-ost-binary-linux-20210617134741.tar.gz
--2021-07-28 15:40:14--  https://github.com/github/gh-ost/releases/download/v1.1.2/gh-ost-binary-linux-20210617134741.tar.gz
Resolving github.com (github.com)... 192.30.255.112
Connecting to github.com (github.com)|192.30.255.112|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://github-releases.githubusercontent.com/54378638/0a93f200-cf85-11eb-8870-035762e21f3a?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20210728%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20210728T063905Z&X-Amz-Expires=300&X-Amz-Signature=993eae9ee5e3b05bc105d62feb28ffe1d76ee2dd3c1ef7c2a77d7e16c72b3ccb&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=54378638&response-content-disposition=attachment%3B%20filename%3Dgh-ost-binary-linux-20210617134741.tar.gz&response-content-type=application%2Foctet-stream [following]
--2021-07-28 15:40:14--  https://github-releases.githubusercontent.com/54378638/0a93f200-cf85-11eb-8870-035762e21f3a?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20210728%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20210728T063905Z&X-Amz-Expires=300&X-Amz-Signature=993eae9ee5e3b05bc105d62feb28ffe1d76ee2dd3c1ef7c2a77d7e16c72b3ccb&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=54378638&response-content-disposition=attachment%3B%20filename%3Dgh-ost-binary-linux-20210617134741.tar.gz&response-content-type=application%2Foctet-stream
Resolving github-releases.githubusercontent.com (github-releases.githubusercontent.com)... 185.199.108.154, 185.199.110.154, 185.199.111.154, ...
Connecting to github-releases.githubusercontent.com (github-releases.githubusercontent.com)|185.199.108.154|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 4710185 (4.5M) [application/octet-stream]
Saving to: ‘gh-ost-binary-linux-20210617134741.tar.gz’

gh-ost-binary-linux-202106171 100%[=================================================>]   4.49M  4.18MB/s    in 1.1s

2021-07-28 15:40:15 (4.18 MB/s) - ‘gh-ost-binary-linux-20210617134741.tar.gz’ saved [4710185/4710185]

> tar -xzvf gh-ost-binary-linux-20210617134741.tar.gz
gh-ost
> ./gh-ost --version
1.1.2
> mv gh-ost /path/to/bin/

Dry run a migration without a real update

Without an --execute option, gh-ost runs DB migration for testing. Next CLI runs to add new column email on my_table in my_schema DB.

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="ALTER TABLE users ADD COLUMN email VARCHAR(255)" \
--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 an --execute option, gh-ost runs DB migration and flips tables.

Example

Go to /examples/mysql/cluster and start a DB cluster.

docker-compose up -d

gh-ost is installed in both of main and read-replica containers. Note that data is deleted when a container stops.

Last updated on
jq cheetsheetMySQL Replication