【MySQL】gh-ostでオンラインマイグレーション

github/gh-ostは、GitHubが公開しているMySQL用オンラインDBスキーママイグレーションのためのツールです。

gh-ost：GitHubのMySQL向けオンライン・スキーマ・マイグレーションツール | インフラ・ミドルウェア | POSTD

MySQL5.6にはInnoDB用のオンラインDDL機能が追加されたりしていますが、それら類似の方法との違いは バイナリログでテーブルの変更を補足しながらマイグレーションする 点です。
そのため、動作が軽量で非同期的なのが特徴だそうです。

文献が少ないので、実際に使って試してみました。

環境

• Windows Subsystem for Linux / Ubuntu 16.04.4 LTS
• gh-ost v1.0.47
• MySQL 5.6.41
• Docker 17.12.0-ce

準備

MySQLのMaster-Slave環境を用意します。
すでにある場合は、このセクションはスキップしてください。

今回はテストなので、Dockerを使って構築します。Dockerfileとmy.cnfをmaster/slaveの2つずつ、およびdocker-compose.ymlを作成します。

なお構築した結果はGitHubにも上げているため、こちらをcloneしてもかまいません。
https://github.com/s2terminal/mysql-repl

Dockerfile-master

FROM mysql:5.6
ADD ./mysql-master.cnf /etc/mysql/my.cnf
RUN chmod 644 /etc/mysql/my.cnf

Dockerfile-slave

FROM mysql:5.6
ADD ./mysql-slave.cnf /etc/mysql/my.cnf
RUN chmod 644 /etc/mysql/my.cnf

mysql-master.cnf

[mysqld]
server-id=1
log-bin

mysql-slave.cnf

[mysqld]
server-id=2
log-bin
log_slave_updates

gh-ostはSlaveに接続してbinlogを使うという仕様上、Slaveに log_slave_updates オプションが必要になります。
(※log_slave_updatesはread_onlyなので、もし起動中のMySQLで設定する場合はMySQLの再起動が必要になります)

$ mysql -u root -h 127.0.0.1 -P 23306 -e 'SHOW VARIABLES LIKE "log_slave_updates"'のように確認できます。

docker-compose.yml

version: '2'
services:
  db-master:
    build:
      context: "./"
      dockerfile: "Dockerfile-master"
    ports:
      - "13306:3306"
    volumes:
      - mysql-master-data:/var/lib/mysql
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
  db-slave:
    build:
      context: "./"
      dockerfile: "Dockerfile-slave"
    ports:
      - '23306:3306'
    volumes:
      - mysql-slave-data:/var/lib/mysql
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: 1
volumes:
  mysql-master-data:
  mysql-slave-data:

起動します

$ docker-compose up

レプリケーションを開始するために、Masterで準備をします。

$ mysql -u root -h 127.0.0.1 -P 13306 -e 'FLUSH TABLES WITH READ LOCK'
$ mysqldump --all-databases -u root -h 127.0.0.1 -P 13306 --master-data --single-transaction --order-by-primary -r backup.sql
$ MASTER_LOG_FILE=`mysql -u root -h 127.0.0.1 -P 13306 -e 'SHOW MASTER STATUS\G' | grep File | awk '{ print $2 }'`
$ MASTER_LOG_POSITION=`mysql -u root -h 127.0.0.1 -P 13306 -e 'SHOW MASTER STATUS\G' | grep Position | awk '{ print $2 }'`
$ mysql -u root -h 127.0.0.1 -P 13306 -e 'UNLOCK TABLES'

slaveで、レプリケーションを開始します。

$ mysql -u root -h 127.0.0.1 -P 23306 -e 'STOP SLAVE'
$ mysql -u root -h 127.0.0.1 -P 23306 -e 'SOURCE backup.sql'
$ mysql -u root -h 127.0.0.1 -P 23306 -e "CHANGE MASTER TO MASTER_HOST='db-master', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='', MASTER_LOG_FILE='${MASTER_LOG_FILE}', MASTER_LOG_POS=${MASTER_LOG_POSITION};"
$ mysql -u root -h 127.0.0.1 -P 23306 -e 'START SLAVE'
$ mysql -u root -h 127.0.0.1 -P 23306 -e 'SHOW SLAVE STATUS\G'

DBへの接続情報は下記のとおりです。

param	master	slave
host	127.0.0.1	127.0.0.1
port	13306	23306
username	root@localhost	root@localhost
password	無し	無し

gh-ostは、Slave(gh-ostが接続するサーバ)のバイナリログ形式を行ベースに設定しておく必要があります。STATEMENTになっている場合は、変更しておきます。

$ mysql -u root -h 127.0.0.1 -P 23306 -e "SET GLOBAL binlog_format = 'ROW'"
$ mysql -u root -h 127.0.0.1 -P 23306 -e 'SHOW VARIABLES LIKE "%binlog_form%"'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

オンラインマイグレーションの実行

Masterへ接続し、適当にテーブルを作ります。

$ mysql -h 127.0.0.1 -P 13306 -uroot

CREATE DATABASE sushi_ya;
USE sushi_ya;

CREATE TABLE sushi(
  id   INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20),
  INDEX(id)
);

INSERT into sushi_ya.sushi (name) VALUES ('maguro');
INSERT into sushi_ya.sushi (name) VALUES ('tamago');

ここで、上記テーブルに、下記クエリを実行して2つのカラムを追加したいとします。
※まだ実行しません

ALTER TABLE sushi
  ADD COLUMN price INT DEFAULT 100,
  ADD COLUMN created_at DATETIME;

https://github.com/github/gh-ost/releases からgh-ostのバイナリを入手しておきます。

$ wget https://github.com/github/gh-ost/releases/download/v1.0.47/gh-ost-binary-linux-20181016015113.tar.gz
$ tar xzvf ./gh-ost-binary-linux-20181016015113.tar.gz

マイグレーションのテスト(Migrate/test on replica)

まず、 gh-ostの3つの動作モード のうち、「c. Migrate/test on replica」というモードでテスト実行してみます。

公式ドキュメントで使い方として紹介されているチートシートには大量のオプションが付いていますが、とりあえず最低限のオプションで実行してみます。

$ ./gh-ost --user="root" --password="" --host="127.0.0.1" --port="23306" --database="sushi_ya" \
  --table="sushi" \
  --alter="ADD COLUMN price INT DEFAULT 100, ADD COLUMN created_at DATETIME" \
  --test-on-replica \
  --gcp \
  --assume-master-host="127.0.0.1:13306" \
  --execute

option	説明
--user --host --port --database	データベースへの接続情報。普通のMySQLコマンドと同様
--table --alter	ALTER TABLEの対象テーブルと内容を指定する
--execute	このオプションを付けるまでは実行されず、dry-runとなる
--test-on-replica	レプリカでマイグレーションのテストが行われ、テーブルの切り替えまでは実行されない
--gcp	SSHトンネル環境下だとポートチェックに引っかかってしまうが、このオプションを付けると回避できるとGitHub上でやりとりされている。筆者が本番環境で実行した際は不要だったが、今回のテスト環境はDockerのため必要
--assume-master-host	SHOW SLAVE STATUSで確認できる接続情報でgh-ostがMASTEｒサーバに接続できない場合、このオプションで明示する

実際に、実行した後のSlaveに接続して確認してみます。

$ mysql -h 127.0.0.1 -P 23306 -uroot sushi_ya

mysql> SHOW TABLES;
+-------------------+
| Tables_in_sushiya |
+-------------------+
| _sushi_gho        |
| sushi             |
+-------------------+
2 rows in set (0.00 sec)

mysql> DESCRIBE sushi;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> DESCRIBE _sushi_gho;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(20) | YES  |     | NULL    |                |
| price      | int(11)     | YES  |     | 100     |                |
| created_at | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

「_sushi_gho」テーブル が作成され、「price」「created_at」の2カラムが追加されていることが分かります。

一旦「_sushi_gho」を削除しておきます。

DROP TABLE _sushi_gho;

マイグレーションの適用(Connect to replica, migrate on master)

実際に変更を適用するには--test-on-replicaオプションを外して「a. Connect to replica, migrate on master」というモード実行します。

$ ./gh-ost --user="root" --password="" --host="127.0.0.1" --port="23306" --database="sushi_ya" \
  --table="sushi" \
  --alter="ADD COLUMN price INT DEFAULT 100, ADD COLUMN created_at DATETIME" \
  --gcp \
  --assume-master-host="127.0.0.1:13306" \
  --execute

実行したあとMasterに接続して、確認してみます。

$ mysql -h 127.0.0.1 -P 13306 -uroot sushi_ya

mysql> SHOW TABLES;
+-------------------+
| Tables_in_sushiya |
+-------------------+
| _sushi_del        |
| sushi             |
+-------------------+
2 rows in set (0.00 sec)

mysql> DESCRIBE sushi;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(20) | YES  |     | NULL    |                |
| price      | int(11)     | YES  |     | 100     |                |
| created_at | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> DESCRIBE _sushi_del;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

「price」「created_at」のカラムが追加された「sushi」テーブルに差し替わり、古いテーブルは「_sushi_del」テーブルとなりました。

これで、gh-ostを利用してテーブルの変更ができました。

マイグレーションタイミングの手動制御(Interactive commands)

ここからがgh-ostの強力な機能です。
gh-ostはレプリケーションを使ってデータの複製を非同期に行うため、途中で一時停止・再開したりすることができます。

まず、テーブル切り替えを保留にするフラグファイルを作成します。

$ touch /tmp/ghost.postpone.flag

--postpone-cut-over-flag-fileオプションで先ほど作成したフラグファイルを指定して、gh-ostを実行します。
また--initially-drop-old-tableオプションを付けておくと、先のgh-ost実行で作成された_sushi_delテーブルを削除してくれます。

$ ./gh-ost --user="root" --password="" --host="127.0.0.1" --port="23306" --database="sushi_ya" \
  --table="sushi" \
  --alter="ADD COLUMN updated_at DATETIME" \
  --gcp \
  --assume-master-host="127.0.0.1:13306" \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
  --initially-drop-old-table \
  --execute

下記のようなログが出続けて止まったら、成功です。

Copy: 0/2 0.0%; Applied: 0; Backlog: 0/1000; Time: 1s(total), 0s(copy); streamer: ade432c638e9-bin.000004:3368249; State: migrating; ETA: N/A
Copy: 2/2 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: ade432c638e9-bin.000004:3371966; State: migrating; ETA: due
Copy: 2/2 100.0%; Applied: 0; Backlog: 0/1000; Time: 2s(total), 1s(copy); streamer: ade432c638e9-bin.000004:3372298; State: migrating; ETA: due
...

この状態では、テーブルの複製は100.0%完了していますが、切り替えを保留にしている状況です。

このままMasterにつないで中身を見てみましょう。

mysql> SHOW TABLES;
+--------------------+
| Tables_in_sushi_ya |
+--------------------+
| _sushi_ghc         |
| _sushi_gho         |
| sushi              |
+--------------------+
3 rows in set (0.00 sec)

mysql> DESCRIBE _sushi_gho;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(20) | YES  |     | NULL    |                |
| price      | int(11)     | YES  |     | 100     |                |
| created_at | datetime    | YES  |     | NULL    |                |
| updated_at | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> DESCRIBE sushi;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(20) | YES  |     | NULL    |                |
| price      | int(11)     | YES  |     | 100     |                |
| created_at | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

XXXX_ghcというのはgh-ost内部で使うテーブルのようなので、ここでは触れません。
sushiテーブルには変更が反映されていない事、_sushi_ghoという新しいテーブルが作成されていることが分かります。

テーブルを実際に切り替えるには、フラグファイルを削除します。

$ mv /tmp/ghost.postpone.flag /tmp/ghost.postpone.flag.unpostpone

こうすると、テーブルが切り替わります。

mysql> DESCRIBE sushi;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(20) | YES  |     | NULL    |                |
| price      | int(11)     | YES  |     | 100     |                |
| created_at | datetime    | YES  |     | NULL    |                |
| updated_at | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

このほかにも、動作中に様々な操作を行うことができるのがgh-ostの特徴です。
gh-ost/interactive-commands.md at v1.0.47 · github/gh-ost

参考

• gh-ost：GitHubのMySQL向けオンライン・スキーマ・マイグレーションツール | インフラ・ミドルウェア | POSTD
• MySQLのレプリケーションテスト環境の構築方法 - Qiita
• Dockerを使って一瞬でMySQLのmaster-slave構成を手に入れる - Qiita