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

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

[gh\-ost：GitHubのMySQL向けオンライン・スキーマ・マイグレーションツール \| インフラ・ミドルウェア \| POSTD](http://postd.cc/gh-ost-github-s-online-migration-tool-for-mysql/)

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:Dockerfile-master
FROM mysql:5.6
ADD ./mysql-master.cnf /etc/mysql/my.cnf
RUN chmod 644 /etc/mysql/my.cnf
```

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

```config:mysql-master.cnf
[mysqld]
server-id=1
log-bin
```

```config: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:
```
起動します

```bash
$ docker-compose up
```

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

```bash
$ 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で、レプリケーションを開始します。

```bash
$ 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になっている場合は、変更しておきます。

```sql
$ 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
```

```sql
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つのカラムを追加したいとします。
**※まだ実行しません**

```sql
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つの動作モード](https://github.com/github/gh-ost/blob/v1.0.47/doc/cheatsheet.md) のうち、「c. Migrate/test on replica」というモードでテスト実行してみます。

公式ドキュメントで使い方として紹介されている[チートシート](https://github.com/github/gh-ost/blob/master/doc/cheatsheet.md)には大量のオプションが付いていますが、とりあえず最低限のオプションで実行してみます。

```bash
$ ./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上でやりとりされている](https://github.com/github/gh-ost/issues/631)。筆者が本番環境で実行した際は不要だったが、今回のテスト環境はDockerのため必要|
|`--assume-master-host`|`SHOW SLAVE STATUS`で確認できる接続情報でgh-ostがMASTEｒサーバに接続できない場合、このオプションで明示する|

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

```
$ mysql -h 127.0.0.1 -P 23306 -uroot sushi_ya
```

```sql
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」を削除しておきます。

```sql
DROP TABLE _sushi_gho;
```

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

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

```bash
$ ./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に接続して、確認してみます。

```bash
$ mysql -h 127.0.0.1 -P 13306 -uroot sushi_ya
```

```sql
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`テーブルを削除してくれます。

```bash
$ ./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につないで中身を見てみましょう。

```sql
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
```

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

```sql
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](https://github.com/github/gh-ost/blob/v1.0.47/doc/interactive-commands.md)	

## 参考

- [gh\-ost：GitHubのMySQL向けオンライン・スキーマ・マイグレーションツール \| インフラ・ミドルウェア \| POSTD](http://postd.cc/gh-ost-github-s-online-migration-tool-for-mysql/)
- [MySQLのレプリケーションテスト環境の構築方法 \- Qiita](http://qiita.com/kopug/items/a1f51cf0d54a0cecb92c)
- [Dockerを使って一瞬でMySQLのmaster\-slave構成を手に入れる \- Qiita](https://qiita.com/Sho2010@github/items/eb088837018e411095a2)