Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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がMASTErサーバに接続できない場合、このオプションで明示する

実際に、実行した後の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

参考

suzuki_sh
Windowsでコンピュータの世界が広がります
https://www.s2terminal.com
finergy-a-tm
大阪府大阪市北区角田町8番1号 梅田阪急ビル オフィスタワー35F
https://finergy.a-tm.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away