My External Storage

Mar 20, 2019 - 5 minute read - Comments - mysql docker

クエリの練習用に20以上のテーブルと大量データを事前投入したMySQLイメージをDocker Hubで公開する

以前にDockerを使って使い捨てのMySQLを起動する方法を紹介した。今回はMySQL公式のプリセットデータを使ったMySQLコンテナを作成する。 また、ローカルから簡単に操作するMakefileも作成した。

TL;DR

ある程度複雑なテーブルデータが事前投入された使い捨てのMySQLが欲しい

ある程度SQLに慣れてきたので本や雑誌にある少し難しいクエリを試したくなった。 今まではMySQLが配布しているworld databaseというサンプルデータを含んだDockerイメージを使っていた。

だが、このデータはテーブルが3テーブルしかなく、複雑なクエリを書くことができない。テーブルを連結したりサブクエリなどを使ったクエリを練習をする場合、それなりにリレーションが張られたテーブル群が必要になる。 毎回ローカルで自前コンテナイメージをビルドするのも面倒なので、作成したイメージをDocker Hubに登録しておくことにした。

サンプルデータを用意する

Dockerに詰めるデータはやはりMySQL公式のサンプルデータにすることにした。 MySQLで公開されているサンプルデータにはworld database以外にもあり、今回はテーブル数が多いsakila databaseを使う。

sakira databaseは20以上のテーブルを含んでいる。

Dockerfileを書く

まず、事前データを投入したDockerイメージを作るためのDockerfileを作成する。 コンテナ内のデータベースにデータを事前投入する方法は前述したブログや以前に書いた記事の通りで、/docker-entrypoint-initdb.dディレクトリにSQLのファイルを入れておくだけだ。 データを投入するためのSQL文(sakila-data.sql、sakila-schema.sql)は以下のページにあるsakila-db.zipから取り出した。

ただ、/docker-entrypoint-initdb.dディレクトリに複数SQLファイルがあった場合、SQLの実行順序はファイル名によって決まる。 そのため、テーブルを作成するSQL文が先に実行されるようにリネームしてコピーしている。

FROM mysql:8.0

COPY sakila-db/sakila-schema.sql /docker-entrypoint-initdb.d/00_sakila-schema.sql
COPY sakila-db/sakila-data.sql /docker-entrypoint-initdb.d/01_sakila-data.sql
COPY sakila-db/sakila.mwb /docker-entrypoint-initdb.d/sakila.mwb

上記のDockerfileと配置するSQLファイルをGitHubに公開しておく。

(リポジトリにはMySQLのバージョンを変えた2つのDockerfileが公開されている)

Docker Hubに登録しておく

上記のGitHubのコードからコンテナイメージを公開する。公開したイメージが以下だ。

GitHubに公開しておけば、Docker Hubはコミットプッシュに連動して自動ビルドなども行なってくれる。
上記のDockerfileを以下のビルド設定でDocker Hubに公開した。

build settings

これでGitHubの更新に合わせて、常に最新のDockerイメージを自動でビルドしてくれる。

最低限のコマンドオプションで良ければ、以下のコマンドでDocker Hubで公開されたイメージを利用する事ができる。

$ docker run  -e MYSQL_ALLOW_EMPTY_PASSWORD=yes budougumi0617/mysql-sakila:5.7

コンテナを起動してSQLの練習をするためのMakefileを作成する。

私はすぐコマンドを忘れて毎回ググっていたため、ローカルで実行するときのためにメモ代わりのMakefileも作成しておいた。

.PHONY: help start stop mysql exec run

# 引数がないときはusageを表示する
.DEFAULT_GOAL := help

start: ## Start MySQL Conatainer
	docker container run --rm -d -e MYSQL_ALLOW_EMPTY_PASSWORD=yes \
		-p 43306:3306 --name mysql_til budougumi0617/mysql-sakila:5.7

stop: ## Terminate MySQL Container
	docker container stop mysql_til

mysql: ## Connect to MySQL
	mysql -h 127.0.0.1 --port 43306 -uroot -D sakila

CMD=show tables;
exec: ## Execute query on MySQL ex: make exec CMD="show columns from country"
	mysql -h 127.0.0.1 --port 43306 -uroot -D sakila -e "${CMD}"

FILE=''
run: ## Run quey from file on MySQL ex: make run FILE=./count_city.sql
	mysql -h 127.0.0.1 --port 43306 -uroot -D sakila < ${FILE}

# 各コマンドについたコメントを表示する
help: ## Show options
	@grep -E '^[a-zA-Z_-]+:.*?## .*$$' $(MAKEFILE_LIST) | awk 'BEGIN {FS = ":.*?## "}; {printf "\033[36m%-15s\033[0m %s\n", $$1, $$2}'

3306ポートは開発で利用しているMySQLでふさがっていることが多いので43306ポートで接続するようにしてある。
用意したmakeコマンドの概要を書くと以下になる。

  • make start dockerでMySQLを起動する
  • make mysql mysqlクライアントを接続する
  • make exec インスタントにワンコマンドだけ実行したいときに使う
    • make exec CMD="show columns from country"のように実行する
  • make run ホストPC上のSQLファイルを実行する
    • make run FILE=./count_city.sqlのように実行する
  • make stop コンテナを停止する。

以下のようにコマンドを使うと一連のSQLの練習ができる。

$ make start
docker container run --rm -d -e MYSQL_ALLOW_EMPTY_PASSWORD=yes \
		-p 43306:3306 --name mysql_til budougumi0617/mysql-sakila:5.7
e5bd2a3b9edd512e95f2f7b65d422129be81a5b61808b32cb0c7268af4505f9c

$ make mysql
mysql -h 127.0.0.1 --port 43306 -uroot -D sakila
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT MAX(country.country) AS name, COUNT(city.city_id) AS count
    -> FROM country
    ->   INNER JOIN city
    ->     ON country.country_id = city.country_id
    -> GROUP BY city.country_id
    -> ORDER BY count DESC
    -> LIMIT 10;
+--------------------+-------+
| name               | count |
+--------------------+-------+
| India              |    60 |
| China              |    53 |
| United States      |    35 |
| Japan              |    31 |
| Mexico             |    30 |
| Russian Federation |    28 |
| Brazil             |    28 |
| Philippines        |    20 |
| Turkey             |    15 |
| Indonesia          |    14 |
+--------------------+-------+
10 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM film_text;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

これでクエリを作る練習が捗りそうだ。

終わりに

今回はSQLの練習用のDockerコンテナを作成し、Docker Hubにも登録した。Makefileで引数を読み込む方法やDocker Hubの使い方の勉強にもなった。
SQL関係の積読本が溜まっているので、このコンテナを使って勉強を進めたい。

参考

関連記事