Terraform や Kubernates のマニフェスト等、インフラ界隈ではより安全でバージョン管理可能、かつ自動化に組み込みやすい「宣言型」の方法での構築が流行ですが、MySQLにもサードパーティ製の宣言型スキーマ定義ツールであるSkeemaがありますので、今回試してみました。
検証環境
おなじみの dbdeployer で環境を作成します。
手元にあった 8.0.27 を使いました。
|
1 2 3 4 |
$ dbdeployer deploy single 8.0.27 --remote-access=% -c bind_address=0 --gtid --sandbox-directory=blog Database installed in $HOME/sandboxes/blog run 'dbdeployer usage single' for basic instructions' . sandbox server started |
sakila database を検証用にインポートしておきます。
|
1 2 3 4 5 6 7 |
# RH8系のためpython3を利用 curl -kLsS https://downloads.mysql.com/docs/sakila-db.zip | \ python3 -c "import sys,zipfile,io; zf = zipfile.ZipFile(io.BytesIO(sys.stdin.buffer.read())); print(zf.open('sakila-db/sakila-schema.sql').read().decode('utf-8')); print(zf.open('sakila-db/sakila-data.sql').read().decode('utf-8'))" | \ ~/sandboxes/blog/use -uroot |
Skeema とは
MySQLスキーマオブジェクトをマニフェストファイルで宣言的に管理、変更する事ができるCLIツールです。
宣言型の構築手法が良い点として
- マニフェスト = 実際の構成
という点があります。
スキーマ構成をキレイにドキュメントにまとめても、作ったそばから陳腐化していくことは避けられません。
可読性の高いマニフェストを使用していれば、変更作業とドキュメント化が同じ作業で行えるということで一石二鳥です。
また、Skeemaは内部的にオブジェクトに変更を加える際には pt-online-schema-change を使うというようにサービス影響を考慮して変更が加えられるようになっています。
Skeemaには、無償のCommunity版と、有償のPlus/Premium版があります。
無償と有償の大きな違いとしては、「ビュー、イベント、ルーチン、トリガ等が管理対象外になる」ということのようです。
Skeemaのインストール
Community Edition のページから.tar.gz版をダウンロードしました。
RPMやDEBも用意されています。
|
1 2 3 4 5 6 7 8 |
$ wget https://github.com/skeema/skeema/releases/download/v1.8.2/skeema_1.8.2_linux_amd64.tar.gz $ tar xf skeema_1.8.2_linux_amd64.tar.gz $ ls README.md skeema $ chmod +x skeema $ sudo mv skeema /usr/local/bin $ skeema version skeema version 1.8.2-community, commit 19801326958762b461d339ddd02ce139f3eb79b0, released 2022-09-14T20:51:18Z |
チュートリアル
まずは、 init サブコマンドを使用して、管理対象のデータベースインスタンスの情報をダンプし、任意のディレクトリに格納します。
今回は、skeemaディレクトリを作成し、サブディレクトリの schemas に初期化しました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
$ mkdir -p skeema $ cd skeema $ skeema init -u root -p -P 8027 -h 127.0.0.1 -d schemas Enter password: 2022-10-18 17:48:22 [INFO] Using host dir /home/nari/tmp/skeema/schemas for 127.0.0.1:8027 2022-10-18 17:48:22 [INFO] Populating /home/nari/tmp/skeema/schemas/sakila 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/category.sql (303 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/city.sql (509 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/rewards_report.sql (1547 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/customer.sql (921 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/film.sql (1254 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/language.sql (300 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/film_actor.sql (596 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/inventory_in_stock.sql (640 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/actor.sql (380 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/get_customer_balance.sql (1318 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/inventory_held_by_customer.sql (384 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/inventory.sql (700 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/film_category.sql (637 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/rental.sql (1064 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/staff.sql (971 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/film_in_stock.sql (495 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/film_not_in_stock.sql (507 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/address.sql (745 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/film_text.sql (271 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/country.sql (304 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/payment.sql (972 bytes) 2022-10-18 17:48:22 [INFO] Created /home/nari/tmp/skeema/schemas/sakila/store.sql (718 bytes) |
以下のようにファイルが作成されました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
$ tree -a . └── schemas ├── .skeema └── sakila ├── .skeema ├── actor.sql ├── address.sql ├── category.sql ├── city.sql ├── country.sql ├── customer.sql ├── film.sql ├── film_actor.sql ├── film_category.sql ├── film_in_stock.sql ├── film_not_in_stock.sql ├── film_text.sql ├── get_customer_balance.sql ├── inventory.sql ├── inventory_held_by_customer.sql ├── inventory_in_stock.sql ├── language.sql ├── payment.sql ├── rental.sql ├── rewards_report.sql ├── staff.sql └── store.sql 2 directories, 24 files |
作業は基本的に初期化したschemasディレクトリで行います。
|
1 |
$ cd schemas |
最上位の.skeema ファイルには環境の接続情報が格納されています。
|
1 2 3 4 5 6 7 8 |
$ cat .skeema generator=skeema:1.8.2-community [production] flavor=mysql:8.0 host=127.0.0.1 port=8027 user=root |
skeema add-environment コマンドで、新しい接続先を追加することが可能です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
# development環境(仮)の起動 $ dbdeployer deploy single 8.0.27 --remote-access=% -c bind_address=0 --gtid --sandbox-directory=blog2 $ skeema add-environment -u root --password=msandbox -h 127.0.0.1 -P 8028 development 2022-10-19 09:02:13 [INFO] Added environment [development] to /home/nari/tmp/skeema/schemas/.skeema $ cat .skeema generator=skeema:1.8.2-community [production] flavor=mysql:8.0 host=127.0.0.1 port=8027 user=root [development] flavor=mysql:8.0 host=127.0.0.1 port=8028 user=root |
.skeema を先頭の定義からデフォルトとして利用するため、環境名を指定しないコマンドはこの場合、productionに実行されます。
また、共通のオプションについては各セクションよりも前の先頭に記載することですべての環境に影響します。
.sqlファイルにはCREATE文が記載されています。
ファイルパスは environment/schema/table.sql となっており、目的のファイルを見つけることは容易でしょう。
|
1 2 3 4 5 6 7 8 9 |
$ cat sakila/actor.sql CREATE TABLE <code>actor</code> ( actor_id</code> smallint unsigned NOT NULL AUTO_INCREMENT, first_name</code> varchar(45) NOT NULL, last_name</code> varchar(45) NOT NULL, last_update</code> timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (<code>actor_id</code>), KEY <code>idx_actor_last_name</code> (<code>last_name</code>) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; |
sakila.actor に変更を加えてみようと思います。
MySQLのオブジェクトへ変更を加えるためには、通常 ALTER を実行しますが、Skeema で管理している場合は、ファイル内の CREATE 文を変更することで、ツールが必要なSQLを発行して目的の状態となります。
ですので、とりあえずnick_name列の追加をしてみます。
|
1 2 3 4 5 6 7 8 9 10 |
$ cat schemas/sakila/actor.sql CREATE TABLE <code>actor</code> ( actor_id</code> smallint unsigned NOT NULL AUTO_INCREMENT, first_name</code> varchar(45) NOT NULL, last_name</code> varchar(45) NOT NULL, nick_name</code> varchar(45) NOT NULL, last_update</code> timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (<code>actor_id</code>), KEY <code>idx_actor_last_name</code> (<code>last_name</code>) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; |
変更の差分は diff コマンドで確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
$ skeema diff -p Enter password: 2022-10-18 18:02:13 [INFO] Generating diff of 127.0.0.1:8027 sakila vs /home/nari/tmp/skeema/schemas/sakila/*.sql 2022-10-18 18:02:13 [WARN] Skipping table <code>address</code>: Skeema does not support generating a diff of this table. Use --debug to see which properties of this table are not supported. 2022-10-18 18:02:13 [WARN] /home/nari/tmp/skeema/schemas/sakila/actor.sql:2: Column actor_id of table actor is an auto_increment column using data type smallint unsigned, which is not configured to be permitted. The following data types are listed in option allow-auto-inc: int unsigned, bigint unsigned. In general, auto_increment columns should use larger int types to avoid risk of integer overflow / exhausting the ID space. -- instance: 127.0.0.1:8027 USE <code>sakila</code>; ALTER TABLE <code>actor</code> ADD COLUMN <code>nick_name</code> varchar(45) NOT NULL AFTER <code>last_name</code>; 2022-10-18 18:02:13 [INFO] 127.0.0.1:8027 sakila: diff complete 2022-10-18 18:02:13 [WARN] Skipped 1 operation due to unsupported feature |
address テーブルで警告がでていますが、空間インデックスがサポートされないためのようです。
ドキュメントによれば他の操作に影響するものではないということですので、無視して良さそうです。
|
1 2 3 4 5 6 |
CREATE TABLE <code>address</code> ( address_id</code> smallint unsigned NOT NULL AUTO_INCREMENT, : SPATIAL KEY <code>idx_location</code> (<code>location</code>), -- ★ : ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; |
またその次にactorテーブルにも警告が発生していますが、これは AUTO_INCREMENT 列が smallint で定義されているためのようです(一般的にintやbigintのunsignedが推奨です)。
このように、skeema では望ましくないスキーマ定義の Linter としての機能も持っています。
最後にテーブル定義を変更するための ALTER が自動生成されていることが確認できます。
実際に反映するためには、push サブコマンドを実行します。
|
1 2 3 4 5 6 7 8 |
$ skeema push -p Enter password: 2022-10-18 18:23:28 [INFO] Pushing changes from /home/nari/tmp/skeema/schemas/sakila/*.sql to 127.0.0.1:8027 sakila : -- instance: 127.0.0.1:8027 USE <code>sakila</code>; ALTER TABLE <code>actor</code> ADD COLUMN <code>nick_name</code> varchar(45) NOT NULL AFTER <code>last_name</code>; 2022-10-18 18:23:28 [INFO] 127.0.0.1:8027 sakila: push complete |
以下の通り、反映されました。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$ ~/sandboxes/blog/use -e "show create table sakila.actor\\G" *************************** 1. row *************************** Table: actor Create Table: CREATE TABLE <code>actor</code> ( actor_id</code> smallint unsigned NOT NULL AUTO_INCREMENT, first_name</code> varchar(45) NOT NULL, last_name</code> varchar(45) NOT NULL, nick_name</code> varchar(45) NOT NULL, last_update</code> timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (<code>actor_id</code>), KEY <code>idx_actor_last_name</code> (<code>last_name</code>) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
diff サブコマンドを使用すれば、すべてのテーブルで差分が無いことを確認することができます。
|
1 2 3 4 |
$ skeema --ignore-table=address diff -p Enter password: 2022-10-18 18:27:28 [INFO] Generating diff of 127.0.0.1:8027 sakila vs /home/nari/tmp/skeema/schemas/sakila/*.sql 2022-10-18 18:27:28 [INFO] 127.0.0.1:8027 sakila: No differences found |
なお、CREATE をする場合は、.sqlファイルを目的のスキーマのディレクトリに作成、DROPをする場合は、逆に.sqlファイルを削除して skeema pushすることになります。
DROPは危険な操作とみなされ、--allow-unsafe オプションを付ける必要があります。
これにより、意図せずにファイルが消えてしまったような際のセーフティネットになります。
また、.sqlファイルを変更する際に、人によって順序や書き方が変わったり、望ましくない設定を行う可能性がありますが、lint サブコマンドでそれを検知する事ができます。
|
1 2 3 4 5 6 7 8 9 10 |
$ skeema lint -p Enter password: 2022-10-18 18:32:33 [INFO] Linting /home/nari/tmp/skeema 2022-10-18 18:32:33 [INFO] Linting /home/nari/tmp/skeema/schemas 2022-10-18 18:32:33 [INFO] Linting /home/nari/tmp/skeema/schemas/sakila 2022-10-18 18:32:33 [WARN] /home/nari/tmp/skeema/schemas/sakila/actor.sql:2: Column actor_id of table actor is an auto_increment column using data type smallint unsigned, which is not configured to be permitted. The following data types are listed in option allow-auto-inc: int unsigned, bigint unsigned. In general, auto_increment columns should use larger int types to avoid risk of integer overflow / exhausting the ID space. : 2022-10-18 18:32:33 [WARN] Found 13 warnings |
また、format サブコマンドを実行することで、.sqlファイルを再フォーマットすることができます。
Github ActionsやGitlab CI/CD 等に加えて管理するのがよさそうです。
最後に、先程の空間インデックスのように、Skeemaが変更をサポートできていない機能が一部存在します。
そのような場合は従来どおりコマンドラインで実行することになるのですが、その後スキーマ情報を一致させるためのコマンドとして、 pull サブコマンドがあります。
直接データベース側をいじってみましょう。
|
1 2 3 4 5 6 |
~/sandboxes/blog/use -e ' alter table sakila.film_actor drop constraint fk_film_actor_actor; alter table sakila.actor modify actor_id int unsigned not null; alter table sakila.film_actor modify actor_id int unsigned not null; alter table sakila.film_actor add constraint <code>fk_film_actor_actor</code> foreign key (<code>actor_id</code>) references <code>actor</code> (<code>actor_id</code>) on delete restrict on update cascade; ' |
pull コマンドによって、変更したテーブルの情報が取り込まれました。
|
1 2 3 4 5 |
$ skeema pull -p Enter password: 2022-10-18 19:08:43 [INFO] Updating /home/nari/tmp/skeema/schemas/sakila to reflect 127.0.0.1:8027 sakila 2022-10-18 19:08:43 [INFO] Wrote /home/nari/tmp/skeema/schemas/sakila/film_actor.sql (591 bytes) 2022-10-18 19:08:43 [INFO] Wrote /home/nari/tmp/skeema/schemas/sakila/actor.sql (396 bytes) |
データベースインスタンス側の情報が.sqlに反映されています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
$ cat schemas/sakila/{actor,film_actor}.sql CREATE TABLE <code>actor</code> ( actor_id</code> int unsigned NOT NULL, first_name</code> varchar(45) NOT NULL, last_name</code> varchar(45) NOT NULL, nick_name</code> varchar(45) NOT NULL, last_update</code> timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (<code>actor_id</code>), KEY <code>idx_actor_last_name</code> (<code>last_name</code>) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE <code>film_actor</code> ( actor_id</code> int unsigned NOT NULL, film_id</code> smallint unsigned NOT NULL, last_update</code> timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (<code>actor_id</code>,<code>film_id</code>), KEY <code>idx_fk_film_id</code> (<code>film_id</code>), CONSTRAINT <code>fk_film_actor_actor</code> FOREIGN KEY (<code>actor_id</code>) REFERENCES <code>actor</code> (<code>actor_id</code>) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT <code>fk_film_actor_film</code> FOREIGN KEY (<code>film_id</code>) REFERENCES <code>film</code> (<code>film_id</code>) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
別のインスタンスにも、同じスキーマ構成を反映させたいという場合は、環境を指定して push コマンドを実行します。
|
1 |
$ skeema push -p development |
以下の通り、反映されました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> show tables from sakila; +------------------+ | Tables_in_sakila | +------------------+ | actor | | address | | category | | city | | country | | customer | | film | | film_actor | | film_category | | film_text | | inventory | | language | | payment | | rental | | staff | | store | +------------------+ |
まとめ
現時点では、プレミアム版でも空間インデックス、ロールをサポートしていないなどの制限がありますので、このツールのみで宣言的にMySQLを構成するというのは難しい印象を受けました。
一方で、全ての操作がより安全に宣言的に実行できれば多くのメリットがあると思いますので、今後に期待したい製品だと思います。
