Edited at

【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が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


参考