GitHub's Online Schema Migrations for MySQL

gh-ost

build status downloads release

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.

gh-ost general flow

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.9 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 tar.gz artifacts in /tmp/gh-ost

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:

Similar Resources

GitHub's Online Schema Migrations for MySQL

GitHub's Online Schema Migrations for MySQL

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 paus

Apr 3, 2020

Simple MySQL migrations in Go

gomigrator This package contains a very small MySQL migration solution for go written in 1 file. The aim of this package is to be very small but highl

Nov 29, 2021

A tool to compare if terraform provider migration schema snapshot is equal to schema defined in resource code

migration schema comparer for Terraform When develop Terraform provider sometimes we need do some state migration(not schema migration) via StateUpgra

Nov 18, 2021

Schema - JSON Schema rules plugin

This plugin allows to configure JSON Schema validations rules ensuring user-submitted records adhere to a pre-defined data schema.

Feb 16, 2022

Go-mysql-orm - Golang mysql orm,dedicated to easy use of mysql

golang mysql orm 个人学习项目, 一个易于使用的mysql-orm mapping struct to mysql table golang结构

Jan 7, 2023

An online, full fledged bank system - ATM, online bank, transactions, bank cards

Bank An online, full fledged bank system - ATM, online bank, transactions, bank cards Online Bank: The online banking system can be used to send trans

Apr 2, 2022

Schema management CLI for MySQL

Schema management CLI for MySQL

Skeema is a tool for managing MySQL tables and schema changes in a declarative fashion using pure SQL. It provides a CLI tool allowing you to: Export

Dec 27, 2022

A Go package to help write migrations with go-pg/pg.

go-pg-migrations A Go package to help write migrations with go-pg/pg. Usage Installation Because go-pg now has Go modules support, go-pg-migrations al

Oct 16, 2022

Database migrations. CLI and Golang library.

migrate Database migrations written in Go. Use as CLI or import as library. Migrate reads migrations from sources and applies them in correct order to

Dec 31, 2022

Database migrations. CLI and Golang library.

Database migrations written in Go. Use as CLI or import as library.

May 30, 2021

Database migrations. CLI and Golang library.

migrate Database migrations written in Go. Use as CLI or import as library. Migrate reads migrations from sources and applies them in correct order to

Jan 9, 2023

Database migrations written in Go

migrate Database migrations written in Go. Use as CLI or import as library. Migrate reads migrations from sources and applies them in correct order to

Oct 3, 2021

Opinionated tool for database structure management and migrations

trek Requirements At least version 13 of postgres is needed. Installation go install . Setup Create config.yaml: model_name: model_name db_name: db

Dec 14, 2022

Tool to handle versioned migrations with gorm

GORM Migrations About Gorm Migrations Gorm Migrations is a tool designed for go-gorm. Gorm Migration takes the pain out of development of migrations v

Mar 14, 2022

Go MySQL Driver is a MySQL driver for Go's (golang) database/sql package

Go-MySQL-Driver A MySQL-Driver for Go's database/sql package Features Requirements Installation Usage DSN (Data Source Name) Password Protocol Address

Jan 4, 2023

mysql to mysql 轻量级多线程的库表数据同步

goMysqlSync golang mysql to mysql 轻量级多线程库表级数据同步 测试运行 设置当前binlog位置并且开始运行 go run main.go -position mysql-bin.000001 1 1619431429 查询当前binlog位置,参数n为秒数,查询结

Nov 15, 2022

Quick and dirty test to compare MySQL perf between ARM64 & Rosetta MySQL on M1Pro mac

Quick and dirty test to compare MySQL perf between ARM64 & Rosetta MySQL on M1Pro mac

Nov 5, 2021

Beerus-DB: a database operation framework, currently only supports Mysql, Use [go-sql-driver/mysql] to do database connection and basic operations

Beerus-DB · Beerus-DB is a database operation framework, currently only supports Mysql, Use [go-sql-driver/mysql] to do database connection and basic

Oct 29, 2022

Golang mysql orm, a personal learning project, dedicated to easy use of mysql

golang mysql orm 个人学习项目, 一个易于使用的mysql-orm mapping struct to mysql table golang结构

Dec 30, 2021
Schema management CLI for MySQL
Schema management CLI for MySQL

Skeema is a tool for managing MySQL tables and schema changes in a declarative fashion using pure SQL. It provides a CLI tool allowing you to: Export

Dec 27, 2022
A Go package to help write migrations with go-pg/pg.

go-pg-migrations A Go package to help write migrations with go-pg/pg. Usage Installation Because go-pg now has Go modules support, go-pg-migrations al

Oct 16, 2022
Database migrations. CLI and Golang library.

migrate Database migrations written in Go. Use as CLI or import as library. Migrate reads migrations from sources and applies them in correct order to

Dec 31, 2022
Database migrations. CLI and Golang library.

Database migrations written in Go. Use as CLI or import as library.

May 30, 2021
Opinionated tool for database structure management and migrations

trek Requirements At least version 13 of postgres is needed. Installation go install . Setup Create config.yaml: model_name: <model_name> db_name: <db

Dec 14, 2022
Tool to handle versioned migrations with gorm

GORM Migrations About Gorm Migrations Gorm Migrations is a tool designed for go-gorm. Gorm Migration takes the pain out of development of migrations v

Mar 14, 2022
Database schema evolution library for Go

Try browsing the code on Sourcegraph! Darwin Database schema evolution library for Go Example package main import ( "database/sql" "log" "github.

Dec 5, 2022
SQL schema migration tool for Go.

sql-migrate SQL Schema migration tool for Go. Based on gorp and goose. Using modl? Check out modl-migrate. Features Usable as a CLI tool or as a libra

Jan 2, 2023
Dbmate is a database migration tool, to keep your database schema in sync across multiple developers and your production servers.

Dbmate is a database migration tool, to keep your database schema in sync across multiple developers and your production servers. It is a stand

Jan 1, 2023
Simple migration tool for MySQL

prrn Simple migration tool for MySQL This is a CLI that helps you create a DB migration file. There is no need to write up and down files from scratch

Nov 10, 2021