gihyo.jpサイトのロゴ

MySQL道普請便り

第138回オンラインスキーママイグレーションツール gh-ostを使ってみよう［その1］

2021-01-12

今回から3回に渡って、GitHub社がOSSとして公開しているオンラインスキーママイグレーションツール gh-ostについて紹介したいと思います。

はじめに、MySQLのオンラインスキーママイグレーションというとMySQL 5.6からオンラインDDLがあります。これにより、並列でDMLが実行されてもロックすることなくスキーマ変更が可能です。特に、MySQL 8.0からのInstance Add Columnは、テーブルをリビルドすることなく即時でカラム追加が完了するといううれしい機能です。

しかし、int型からbigint型へなどの型変更を伴うALTERステートメントなど、いくつかの操作は並列のDMLが許可されない、つまりそのテーブルが全体ロックされるような動作になります。加えて、レプリケーションの遅延が発生する可能性もあります。このように、操作の種類によってAlter中にできる動作が異なるのです。これらについては第30回　InnoDBオンラインDDLについてをご参照ください。

gh-ostを使用すれば、いくつか制限はありますが、ALTERステートメントをレプリケーションの遅延や負荷をコントロールしつつ、かならず並列のDMLが許可されるので、サービス稼働中であっても意識することなくスキーママイグレーションが可能です。

アーキテクチャ

gh-ostは基本的に以下のように動作します。

  1. 変更したいテーブル（基テーブル）の空のコピーテーブル（ゴーストテーブル）を作成
  2. ゴーストテーブルに対して指定したALTERステートメント実行
  3. 基テーブルの既存データをゴーストテーブルにコピー
  4. マイグレーション実行中、基テーブルへの新規DMLはバイナリログから抽出し、ゴーストテーブルへ適用
    ※ 3と4は並列で稼働します
  5. 3と4が終わると、基テーブルとゴーストテーブルを入替（カットオーバー）

オンラインスキーママイグレーションツールとして有名なツールに、Percona社が公開しているpt-online-schema-change（以降、pt-osc）があります。pt-oscとgh-ostのアーキテクチャーは似ていますが、違いは上記4.の新規DMLの適用方法になります。pt-oscはトリガーを基テーブルに仕掛けて適用させます。gh-ostは自身をレプリカのように振る舞いバイナリログを転送してもらい、そこから抽出して適用します。

よって、トリガーによるオーバヘッドがない分gh-ostのほうが負荷を抑えつつ、柔軟に負荷をハンドリング（スロットル）できるようになっています。

また、gh-ostは極力マスターの負荷を抑えるためにレプリカを活用するように設計されています。つまり、デフォルトではバイナリログを取得してくる先はレプリカです。よって、1、2、3、5はマスター上で実行され、4のバイナリログの取得はレプリカから、適用はマスターへというようになっています。もちろん、設定によってマスターからバイナリログを取得することも可能ですが、以降の説明はレプリカから取得する設定で進めていきます。

必須要件

権限の作成

gh-ostを実行するために以下の権限が必要です。

  1. ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATEの権限を対象のデータベース権限で作成
  2. SUPER,REPLICATION CLIENT, REPLICATION SLAVE on *.*が必要な場合あり

2.の権限が必要な場合は--switch-to-rbrオプションがTrueのときです。このオプションは、もしレプリカのbinlog_formatがROWでない場合、gh-ostはbinlog_formatをROWに変更してSTOP SLAVEとSTART SLAVEします。確実にレプリカのbinlog_formatがROWであるならば、--assume-rbrオプションをつけて実行することでSTOP SLAVEとSTART SLAVEは実行されませんので、権限は不要です。

レプリケーション設定

gh-ostがレプリカからバイナリログを取得するためのMySQLの設定になります。

  1. レプリカでlog_binが設定されていること
  2. レプリカでlog_slave_updates=ONであること
  3. レプリカでbinlog_format=ROWであること
  4. レプリカでbinlog_row_image=FULLであること

前述の--switch-to-rbrオプションは、binlog_formatをROWに変更しますが、log_slave_updatesをONへ、binlog_row_imageをFULLにはしてくれません。よって、先にレプリカで設定してgh-ostを実行するほうが良いでしょう。

また、マスターがステートメントベースレプリケーション（binlog_format=STATEMENT or MIXED）で運用されていたとしても問題ありません。そのレプリカを上記の設定に変更すれば、gh-ostを実行することができます。マスターとレプリカ間で設定が異なるのが嫌だという方は、gh-ost実行後に設定を戻せば大丈夫です。

ちなみに、1、2はMySQLの再起動が必要で、3、4はオンラインで設定変更可能ですが、設定変更後にレプリケーションの再起動（STOP SLAVE、START SLAVE）が必要です。

制限事項

下記条件のうちいずれかに当てはまると実行エラーとなります。ご留意ください。

  • 対象テーブルにプライマリキーまたはNOT NULL制約がついたユニークキーがない。
  • 対象テーブルに外部キー制約がある。（⁠子テーブル、親テーブルまたは両方）
  • 対象テーブルにトリガーがある。
  • 対象テーブルのプライマリーキーがJSONカラムの一部から生成されたキーである。

使ってみる

さて、前置きが長くなりましたが、ここからはgh-ostを使ってみましょう。

マスターと必須要件を満たしたレプリカのMySQLを用意します。

インストール

今回は、gh-ost実行するためのCentOS 7のサーバを用意して、そこにインストールします。gh-ostのバージョンは1.1.0です。Releasesから最新リリースの確認とダウンロードができます。

# wget https://github.com/github/gh-ost/releases/download/v1.1.0/gh-ost-1.1.0-1.x86_64.rpm
# rpm -i gh-ost-1.1.0-1.x86_64.rpm

実行

まずは、sbtest1テーブルにcol1カラムを追加するgh-ostを最小限のオプションで実行したいと思います。sbtest1テーブルは現在以下のような構成です。

CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
gh-ost \
--host=192.168.0.2 \
--port=3306 \
--user="ghost_user" \
--password="xx" \
--database="sysbenchdb" \
--alter="ALTER TABLE sbtest1 ADD col1 int;" \
--execute
  • --host …レプリカのホスト名。マスターの情報はgh-ostが内部で検索します（default: "127.0.0.1"）
  • --port …レプリカのポート番号（default: 3306）
  • --user …接続するユーザ名
  • --password …パスワード
  • --database …データベース名
  • --alter …実行したいAlterステートメント。
  • --execute …テーブルの変更と移行を実際に実行。指定なしはテストをして終了

標準出力にログが出力されます。

[2020/12/28 10:51:56] [info] binlogsyncer.go:354 begin to sync binlog from position (binary_log.000012, 732101540)
[2020/12/28 10:51:56] [info] binlogsyncer.go:723 rotate to (binary_log.000012, 732101540)
# Migrating `sysbenchdb`.`sbtest1`; Ghost table is `sysbenchdb`.`_sbtest1_gho`
# Migrating 192.168.0.1:3306; inspecting 192.168.0.2:3306; executing on 192.168.0.3
# Migration started at Mon Dec 28 10:51:56 +0900 2020
# chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.sysbenchdb.sbtest1.sock
Copy: 0/9936 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 0s(copy); streamer: binary_log.000012:732104613; Lag: 0.02s, State: migrating; ETA: N/A
Copy: 0/9936 0.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: binary_log.000012:732112272; Lag: 0.01s, State: migrating; ETA: N/A
Copy: 10000/10000 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: binary_log.000012:733837596; Lag: 0.01s, State: migrating; ETA: due
Copy: 10000/10000 100.0%; Applied: 0; Backlog: 1/1000; Time: 3s(total), 1s(copy); streamer: binary_log.000012:734037044; Lag: 0.01s, State: migrating; ETA: due
Copy: 10000/10000 100.0%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 1s(copy); streamer: binary_log.000012:734039306; Lag: 0.01s, State: migrating; ETA: due
[2020/12/28 10:51:59] [info] binlogsyncer.go:164 syncer is closing...
[2020/12/28 10:51:59] [error] binlogstreamer.go:77 close sync with err: sync is been closing...
[2020/12/28 10:51:59] [info] binlogsyncer.go:179 syncer is closed
2020-12-28 10:51:59 ERROR Error 1146: Table 'sysbenchdb._sbtest1_ghc' doesn't exist
# Done

このように、簡単に実行することができます。ログ内容は次回説明しますが、# Doneが出力されていれば正常終了となります。

sbtest1テーブルを確認してみます。

 CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `pad` char(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `col1` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

col1カラムが追加されました。

また、以下のようなテーブルも一緒に残っているのがわかります。

mysql> show tables like '_sbtest1_del';
+-------------------------------------+
| Tables_in_sysbenchdb (_sbtest1_del) |
+-------------------------------------+
| _sbtest1_del                        |
+-------------------------------------+

_sbtest1_delは基テーブルです。基本的に_TABLENAME_delという形で残されています。DROPはコストの高い処理のため、デフォルトでは基テーブルはDROPされずにリネームされます。--ok-to-drop-tableオプションをつけると実行終了時にDROPされます。

gh-ostを実行する手順として、以下の流れが良いと思います。

  1. --executeオプションなしで実行して、エラーがないか確認
  2. --executeオプションをつけて、実際に実行する

まとめ

gh-ostの概要と、簡単に実行した内容を紹介しました。外部キーやトリガーが対象テーブルにあると実行できないのは少し残念ですが、そういう場合はオンラインDDLを使うなど、状況に合わせて使い分ければ良いでしょう。今回は触れていませんが、速度に関してはMySQLのオンラインDDLが速いです。

次回は、もう少し詳しくオプション、ステータスや提供されている機能について紹介したいと思います。

おすすめ記事

記事・ニュース一覧