- 投稿日：2020-10-24T23:21:31+09:00
MySQL 8.0.22 で Derived Condition Pushdown Optimization を試してみた
MySQL 8.0.22 がリリースされました。
MySQL 8.0 の薄い本 を発行している身としては新機能や改良を試して 8.0.22 対応版の内容に組み入れていかねば…と思ったのですが、
MySQL 8.0.22 本体には目立った新機能が見当たらない！
（速度に影響する退行バグが修正されている、などの話は聞きますが…）ということで、無理やり地味な改良を取り上げてみました。
Derived Condition Pushdown Optimization とは
- サブクエリがある SQL 文の実行時に
- サブクエリの外側にある
WHERE句などの条件の一部を- サブクエリに（あらかじめ）適用することで処理の効率化を図る
という、オプティマイザの最適化処理のことです。
なるほど。よくわかりません（説明下手で申し訳ない）。
試してみる
公式マニュアルに適用例の説明があるので、それに沿って試してみます。
- 8.2.2.5 Derived Condition Pushdown Optimization（MySQL 8.0 Reference Manual）
なお、先に断っておきますが、2020/10/24 現在、このマニュアル記載の適用例には一部誤りがあります。すでに中の人がドキュメントの不具合報告をあげられているそうなので、そのうち一旦削除されるか修正が行われるはずです。
※その「誤りがある適用例」をこの記事で取り上げて説明するのでした。
テストデータを準備する
公式マニュアルの適用例にはテーブル定義が示されていないのですが、今回は主キーのないテーブルを作成してテストデータを投入してみました。
テーブル定義mysql> CREATE DATABASE subquery_test; Query OK, 1 row affected (0.02 sec) mysql> USE subquery_test; Database changed mysql> CREATE TABLE t1 (i INT NOT NULL, j INT NOT NULL, k INT NOT NULL); Query OK, 0 rows affected (0.12 sec)データ挿入INSERT INTO t1 VALUES(1000 * RAND(), 1000 * RAND(), 1000 * RAND()); ※これで 2 万行挿入後、INSERT INTO ... SELECT を 5 回繰り返して 64 万行に増やした適用例を試してみる（Window 関数を含むケース）
ここでは、いきなりページの最後の例として示されている、「Window 関数を含むケースの適用例」を試してみます。
ただし、前述のとおり誤りがある適用例なので、そのうちマニュアルから消える or 修正されるはずです。2020/10/24 現在は、以下のような記述になっています。
In cases in which the derived table uses a window function, predicates in the outer
WHEREclause can sometimes be pushed down separately according to the rules already given. In the querySELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(k) OVER (PARTITION BY i) AS sum FROM t1 GROUP BY i, j) AS dt WHERE i > 10 AND min < 3, the predicatei > 10references the column used inPARTITION BY, and so can be pushed down directly;min < 3does not reference any columns in either of thePARTITION BYorGROUP BYclauses but can be pushed down as aHAVINGcondition. This means that the query can be rewritten like this:SELECT * FROM ( SELECT i, j, MIN(k) AS min, SUM(k) OVER (PARTITION BY i) AS sum FROM t1 WHERE i > 10 GROUP BY i, j HAVING MIN < 3 ) AS dt;↑は Derived Condition Pushdown Optimization 適用によりこのようにクエリが書き換えられて実行される、という説明ですがこれも間違っています。そもそも別名の
minが大文字でMINになっている時点で不穏ですね…。
さて、書き換え前の SQL 文を実行すると、
ONLY_FULL_GROUP_BYが有効な環境ではいきなりエラーになります。エラーになるmysql> SELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(k) OVER (PARTITION BY i) AS sum FROM t1 GROUP BY i, j) AS dt WHERE i > 10 AND min < 3; ERROR 1055 (42000): Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'subquery_test.t1.k' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by4 列目の値、
SUM(k) OVER (PARTITION BY i) AS sumの k が不定になるためで、ここはSUM(SUM(k)) ...にする必要があります。※余談ですが、
ONLY_FULL_GROUP_BYを無効にして無理やり実行すると、（インデックスを作るなどして）実行計画が変化する度に結果の行数が変わる（場合によってはEmpty set）という、なかなか恐ろしい挙動に…。
さらに、WHERE句の条件を付加する前の SQL 文と比較してみると、PARTITION BY iはPARTITION BY jの、WHERE i > 10はWHERE j > 10の間違いであることが分かります（これらがiのままだと、エラーなく実行できたとしても Derived Condition Pushdown Optimization は働きません）。
2020/10/25 訂正：
ここはPARTITION BY i・WHERE i > 10のままでも Derived Condition Pushdown Optimization が働きました（うっかりPARTITION BY j・WHERE i > 10で実験していました）。ただし、↓の適用例はPARTITION BY j・WHERE j > 10に変更した状態で実行しています。
というわけで、まずは
NO_DERIVED_CONDITION_PUSHDOWNヒントを付けて Derived Condition Pushdown Optimization を無効にして試してみます。プッシュダウン無効mysql> SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3; +-----+-----+------+--------+ | i | j | min | sum | +-----+-----+------+--------+ | 380 | 28 | 1 | 244608 | | 94 | 56 | 0 | 225664 | （中略） | 309 | 986 | 2 | 280480 | | 323 | 994 | 2 | 356160 | +-----+-----+------+--------+ 44 rows in set (0.78 sec) mysql> EXPLAIN SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 638976 filtered: 11.11 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DERIVED table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 638976 filtered: 100.00 Extra: Using temporary; Using filesort 2 rows in set, 2 warnings (0.00 sec)サブクエリを処理する際に 64 万行分読み込んで同じ行数の派生テーブルを作り（
id:2の行）、その全行からフィルタリングして（id:1の行）結果を出すイメージです。TREE形式でEXPLAIN（プッシュダウン無効）mysql> EXPLAIN FORMAT=TREE SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3\G *************************** 1. row *************************** EXPLAIN: -> Filter: ((dt.j > 10) and (dt.min < 3)) -> Table scan on dt (cost=71887.30 rows=638976) -> Materialize -> Window aggregate with buffering: sum(sum(t1.k)) OVER (PARTITION BY t1.j ) -> Sort: t1.j -> Table scan on <temporary> -> Aggregate using temporary table -> Table scan on t1 (cost=65468.60 rows=638976) 1 row in set (0.00 sec)
Filter: ((dt.j > 10) and (dt.min < 3))が一番上に来ています（最後にフィルタリング）。
次に、Derived Condition Pushdown Optimization を有効にして試してみます。
プッシュダウン有効mysql> SELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3; +-----+-----+------+--------+ | i | j | min | sum | +-----+-----+------+--------+ | 380 | 28 | 1 | 244608 | | 94 | 56 | 0 | 225664 | （中略） | 309 | 986 | 2 | 280480 | | 323 | 994 | 2 | 356160 | +-----+-----+------+--------+ 44 rows in set (0.97 sec) mysql> EXPLAIN SELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 212970 filtered: 33.33 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DERIVED table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 638976 filtered: 33.33 Extra: Using where; Using temporary; Using filesort 2 rows in set, 2 warnings (0.00 sec)サブクエリを処理する際に（64 万行分読み込んだものを）フィルタリングして 1/3 程度に絞り込んで派生テーブルを作り（
id:2の行）、20 万行強の派生テーブルからさらにフィルタリングして（id:1の行）結果を出すイメージです。TREE形式でEXPLAIN（プッシュダウン有効）mysql> EXPLAIN FORMAT=TREE SELECT * FROM (SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 GROUP BY i, j) AS dt WHERE j > 10 AND min < 3\G *************************** 1. row *************************** EXPLAIN: -> Filter: (dt.min < 3) -> Table scan on dt (cost=23961.62 rows=212970) -> Materialize -> Window aggregate with buffering: sum(sum(t1.k)) OVER (PARTITION BY t1.j ) -> Sort: t1.j -> Table scan on <temporary> -> Aggregate using temporary table -> Filter: (t1.j > 10) (cost=65468.60 rows=212971) -> Table scan on t1 (cost=65468.60 rows=638976) 1 row in set (0.00 sec)
Filter: (t1.j > 10)が下のほうに来ました（派生テーブルを作る時点でフィルタリング）。Filter: (dt.min < 3)のほうは一番上のままです（最後にフィルタリング）。見た目上、処理が効率化されているので高速化する…かと思うのですが、全然高速化していませんね（かえって遅い？ように見えますが、何度か実行して平均を取ると、結果は「ほぼ同じ」でした）。
この例では（動きを分かりやすく見せる目的で）サブクエリの内容を他テーブルと結合することなくそのまま主クエリで処理しているために、サブクエリによって作られる派生テーブルの行数の多寡が SQL 文全体の処理効率に与える影響が小さいことが原因ではないかと思います。テストに使うテーブルの容量の多少の「嵩増し」や他テーブルとの結合などを試してみましたが、残念ながら速くなることが（誤差の範囲を除いてほぼ）ありませんでした。現状では、「プッシュダウン処理による負荷増≒派生テーブル行数減による負荷減」ということなのでしょうか。派生テーブルがメモリではなくディスクに生成されるサイズになると結果が変わってきそうな気はしますが。
結局、Derived Condition Pushdown Optimization での書き換え後の SQL 文は以下のようになります。
min < 3をサブクエリ側のHAVING句に「移動」させると結果が変わってしまうので、ここは書き換えられずに外側（主クエリ側）に残ります。書き換え後SELECT * FROM ( SELECT i, j, MIN(k) AS min, SUM(SUM(k)) OVER (PARTITION BY j) AS sum FROM t1 WHERE j > 10 GROUP BY i, j ) AS dt WHERE min < 3;
2020/10/25 追記：
もっとシンプルな適用例を試してみる
このままでは、「Derived Condition Pushdown Optimization は効果が出ないのでは？」と誤解されそうなので、普通に考えて効きそうな例を試してみます。
まず最初に、先ほどのテーブルに i 列と j 列 の複合 INDEX を追加します。
INDEX追加mysql> ALTER TABLE t1 ADD INDEX (i, j); Query OK, 0 rows affected (3.52 sec) Records: 0 Duplicates: 0 Warnings: 0この状態で、Derived Condition Pushdown Optimization を無効にして、i と j の範囲のみを条件とする SQL 文を流します。
シンプルな例（プッシュダウン無効）mysql> SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM (SELECT i, j, SUM(k) FROM t1 GROUP BY i, j) AS dt WHERE i < 10 AND j > 990; +---+-----+--------+ | i | j | SUM(k) | +---+-----+--------+ | 9 | 998 | 30784 | +---+-----+--------+ 1 row in set (1.05 sec) mysql> EXPLAIN SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM (SELECT i, j, SUM(k) FROM t1 GROUP BY i, j) AS dt WHERE i < 10 AND j > 990\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 638976 filtered: 11.11 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DERIVED table: t1 partitions: NULL type: index possible_keys: i key: i key_len: 8 ref: NULL rows: 638976 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.00 sec) mysql> EXPLAIN FORMAT=TREE SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM (SELECT i, j, SUM(k) FROM t1 GROUP BY i, j) AS dt WHERE i < 10 AND j > 990\G *************************** 1. row *************************** EXPLAIN: -> Filter: ((dt.i < 10) and (dt.j > 990)) -> Table scan on dt (cost=71887.30 rows=638976) -> Materialize -> Group aggregate: sum(t1.k) -> Index scan on t1 using i (cost=64290.35 rows=638976) 1 row in set (0.00 sec)派生テーブル生成が INDEX に対するフルスキャンになっています。
次に、Derived Condition Pushdown Optimization を有効にして、同じ SQL 文を流してみます。
sqlmysql> SELECT * FROM (SELECT i, j, SUM(k) FROM t1 GROUP BY i, j) AS dt WHERE i < 10 AND j > 990; +---+-----+--------+ | i | j | SUM(k) | +---+-----+--------+ | 9 | 998 | 30784 | +---+-----+--------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM (SELECT i, j, SUM(k) FROM t1 GROUP BY i, j) AS dt WHERE i < 10 AND j > 990\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2058 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 2 select_type: DERIVED table: t1 partitions: NULL type: range possible_keys: i key: i key_len: 4 ref: NULL rows: 6176 filtered: 33.33 Extra: Using index condition 2 rows in set, 1 warning (0.00 sec) mysql> EXPLAIN FORMAT=TREE SELECT * FROM (SELECT i, j, SUM(k) FROM t1 GROUP BY i, j) AS dt WHERE i < 10 AND j > 990\G *************************** 1. row *************************** EXPLAIN: -> Table scan on dt (cost=234.03 rows=2058) -> Materialize -> Group aggregate: sum(t1.k) -> Index range scan on t1 using i, with index condition: ((t1.i < 10) and (t1.j > 990)) (cost=2779.46 rows=6176) 1 row in set (0.00 sec)今度は INDEX の範囲検索によって 1/100 程度に絞り込まれた状態で派生テーブルが生成され、SQL 文も高速に実行されました。
- Qiitaに投稿したMySQL 8.0関連記事
- MySQL 8.0 の薄い本（無料で配布中！）
- 投稿日：2020-10-24T19:49:18+09:00
Docker mysql Quick Refernce
Docker Hub の mysql のクイックリファレンス の日本語訳。
MySQL とは?
MySQL は世界で最も人気のあるオープンソースデータベースです。
実証済みのパフォーマンス、信頼性、使いやすさを備えた MySQL は、個人のプロジェクトや Web サイト、 e コマースや情報サービスなど、全範囲をカバーする Web ベースのアプリケーションの主要なデータベースの選択肢になりました。
Facebook、Twitter、YouTube、Yahoo! などの Web プロパティなどでも使用されています。
MySQL Server およびその他の MySQL 製品の詳細および関連するダウンロードについては www.mysql.com にアクセスしてください。イメージの使い方
mysql サーバーインスタンスの開始
MySQL インスタンスの開始は簡単です。
$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag
some-mysqlはコンテナに割り当てる名前、my-secret-pwは MySQL root ユーザーに設定するパスワード、tagは必要な MySQL バージョンを指定するタグです。
関連するタグについては、上記のリストを参照してください。
MySQL コマンドラインクライアントから MySQL に接続します。
次のコマンドは、別の mysql コンテナインスタンスを起動し、元の mysql コンテナに対して mysql コマンドラインクライアントを実行して、データベースインスタンスに対して SQL ステートメントを実行できるようにします。$ docker run -it --network some-network --rm mysql mysql -hsome-mysql -uexample-user -p
some-mysqlは、元の mysql コンテナ(Docker ネットワークに接続されているsome-network)の名前です。
このイメージは Docker 以外のインスタンスまたはリモートインスタンスのクライアントとしても使用できます。$ docker run -it --rm mysql mysql -hsome.mysql.host -usome-mysql-user -pMySQL コマンドラインクライアントの詳細については MySQL のドキュメントを参照してください。
docker stack deploy または docker-compose を介した使用法
stack.yml記述例# Use root/example as user/password credentials version: '3.1' services: db: image: mysql command: --default-authentication-plugin=mysql_native_password restart: always environment: MYSQL_ROOT_PASSWORD: example adminer: image: adminer restart: always ports: - 8080:8080
docker stack deploy -c stack.yml mysql(またはdocker-compose -f stack.yml up)を実行し、完全に初期化されるのを待って http://swarm-ip:8080, http://localhost:8080, または http://host-ip:8080 へアクセスします。コンテナシェルへのアクセスと MySQL ログの表示
docker execコマンドを使用すると Docker コンテナ内でコマンドを実行できます。
次のコマンドラインは mysql コンテナ内に bash シェルを提供します。$ docker exec -it some-mysql bashログは Docker のコンテナログから入手できます。
$ docker logs some-mysqlカスタム MySQL 構成ファイルの使用
MySQL のデフォルト設定は
/etc/mysql/my.cnfにあります。
これには/etc/mysql/conf.dや/etc/mysql/mysql.conf.dなどの追加のディレクトリが含まれている場合があります。
詳細については mysql イメージ内の関連するファイルとディレクトリを調べてください。
/my/custom/config-file.cnfがカスタム構成ファイルのパスと名前である場合、次のように mysql コンテナーを開始できます(このコマンドでは、カスタム構成ファイルのディレクトリー・パスのみが使用されることに注意してください)。$ docker run --name some-mysql -v /my/custom:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tagこれにより MySQL インスタンスが
/etc/mysql/my.cnfと/etc/mysql/conf.d/config-file.cnfの起動設定を組み合わせて使用し、後者の設定が優先される新しいコンテナ some-mysql が起動します。cnf ファイルを使用しない設定方法
多くの構成オプションをフラグとして mysqld に渡すことができます。これにより
cnfファイルを必要とせずにコンテナーをカスタマイズできる柔軟性が得られます。
たとえば、すべてのテーブルのデフォルトのエンコーディングと照合順序を変更して UTF-8(utf8mb4)を使用する場合は、次のコマンドを実行するだけです。$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci利用可能なオプションの完全なリストを確認したい場合は以下を実行してください。
$ docker run -it --rm mysql:tag --verbose --help環境変数
mysql イメージを起動するときに
docker runコマンドラインで 1 つ以上の環境変数を渡すことにより、 MySQL インスタンスの構成を調整できます。
すでにデータベースが含まれているデータディレクトリでコンテナを起動した場合、以下の変数はいずれも効果がないことに注意してください。
既存のデータベースは、コンテナの起動時に常に変更されません。
MySQL 自体が尊重する環境変数(特にこのイメージと一緒に使用すると問題を引き起こすことが知られているMYSQL_HOSTのような変数)のドキュメントについては、https://dev.mysql.com/doc/refman/5.7/en/environment-variables.html も参照してください。MYSQL_ROOT_PASSWORD
この変数は必須であり MySQL ルートスーパーユーザーアカウントに設定されるパスワードを指定します。
上記の例ではmy-secret-pwに設定されています。MYSQL_DATABASE
この変数はオプションです。
イメージの起動時に作成されるデータベースの名前を指定できます。
ユーザー / パスワードが指定された場合(以下を参照)、そのユーザーには、このデータベースへのスーパーユーザーアクセス(GRANT ALLに対応)が付与されます。MYSQL_USER, MYSQL_PASSWORD
これらの変数はオプションです。
新しいユーザーを作成し、そのユーザーのパスワードを設定するために組み合わせて使用されます。
このユーザーには MYSQL_DATABASE 変数で指定されたデータベースに対するスーパーユーザー権限(上記を参照)が付与されます。
ユーザーを作成するには、両方の変数が必要です。
ルートスーパーユーザーを作成するためにこの仕組みを使用する必要はないことに注意してください。
ルートスーパーユーザーは、デフォルトで MYSQL_ROOT_PASSWORD 変数で指定されたパスワードで作成されます。MYSQL_ALLOW_EMPTY_PASSWORD
これはオプションの変数です。
yesのように空でない値に設定するとパスワードなしの root ユーザーでコンテナーを開始できます。注: この変数を
yesに設定することは、何をしているのかを本当に理解していない限りお勧めしません。
これにより MySQL インスタンスが完全に保護されなくなり、誰でも完全なスーパーユーザーアクセスを取得できるようになります。MYSQL_RANDOM_ROOT_PASSWORD
これはオプションの変数です。
(pwgenを使用して) root ユーザーのランダムな初期パスワードを生成するにはyesなどの空でない値に設定します。
生成された root パスワードは stdout に出力されます(GENERATED ROOT PASSWORD: ......)。MYSQL_ONETIME_PASSWORD
初期化が完了すると root ユーザー( MYSQL_USER で指定されたユーザーではない)を期限切れとして設定し、最初のログイン時にパスワードの変更を強制します。
空でない値があると、この設定がアクティブになります。注: この機能は MySQL 5.6 以降でのみサポートされています。
MySQL 5.5 でこのオプションを使用すると、初期化中にエラーが送出されます。MYSQL_INITDB_SKIP_TZINFO
デフォルトでは、エントリポイントスクリプトは
CONVERT_TZ()関数に必要なタイムゾーンデータを自動的にロードします。
不要な場合、空でない値を指定するとタイムゾーンの読み込みが無効になります。Docker Secrets
環境変数を介して機密情報を渡す代わりに、前述の環境変数に
_FILEを追加して、初期化スクリプトがコンテナ内に存在するファイルからそれらの変数の値をロードするようにすることができます。
特にこれは/run/secrets/<secret_name>ファイルに保存されている Docker シークレットからパスワードをロードするために使用できます。
例えば:$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql-root -d mysql:tag現在、これは以下の変数でのみサポートされています。
- MYSQL_ROOT_PASSWORD
- MYSQL_ROOT_HOST
- MYSQL_DATABASE
- MYSQL_USER
- MYSQL_PASSWORD
新しいインスタンスの初期化
コンテナが初めて起動されると、指定された名前の新しいデータベースが作成され、指定された構成変数で初期化されます。
さらに/docker-entrypoint-initdb.dにある拡張子.sh、.sql、および.sql.gzのファイルを実行します。
ファイルはアルファベット順に実行されます。
SQL ダンプをそのディレクトリにマウントし、提供されたデータを含むカスタムイメージを提供することで、 mysql サービスに簡単にデータを取り込むことができます。
SQL ファイルは、デフォルトで MYSQL_DATABASE 変数で指定されたデータベースにインポートされます。データの保存場所
重要な注意: Docker コンテナで実行されるアプリケーションが使用するデータを保存する方法はいくつかあります。
mysql イメージのユーザーは、次のような利用可能なオプションに慣れることをお勧めします。Docker に独自の内部ボリューム管理を使用してホストシステム上のディスクにデータベースファイルを書き込むことにより、データベースデータのストレージを管理させます。
これはデフォルトであり、ユーザーにとって簡単でかなり透過的です。
欠点はホストシステム上で直接実行されるツールやアプリケーション、つまりコンテナの外部ではファイルを見つけるのが難しい場合があることです。
ホストシステム(コンテナの外側)にデータディレクトリを作成し、これをコンテナの内側から見えるディレクトリにマウントします。
これにより、データベースファイルがホストシステムの既知の場所に配置され、ホストシステム上のツールやアプリケーションがファイルに簡単にアクセスできるようになります。
欠点はユーザーがディレクトリが存在することを確認する必要があることと、ホストシステムのディレクトリ権限およびその他のセキュリティメカニズムが正しく設定されていることを確認する必要があることです。Docker のドキュメントは、さまざまなストレージオプションとバリエーションを理解するための良い出発点であり、この分野で議論し、アドバイスを提供するブログやフォーラムの投稿が複数あります。
上記の後者のオプションの基本的な手順をここで簡単に示します。ホストシステムの適切なボリュームに
/my/own/datadirのようなデータディレクトリを作成します。
次のように mysql コンテナを起動します。$ docker run --name some-mysql -v /my/own/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tagコマンドの
-v /my/own/datadir:/var/lib/mysqlの部分は、基礎となるホストシステムから/my/own/datadirディレクトリをコンテナ内の/var/lib/mysqlとしてマウントします。
MySQL はデフォルトでデータファイルを書き込みます。
MySQL の初期化が完了するまで接続はありません。
コンテナの起動時に初期化されたデータベースがない場合は、デフォルトのデータベースが作成されます。
これは予想される動作ですが、初期化が完了するまで着信接続を受け入れないことを意味します。
これにより、複数のコンテナーを同時に起動する docker-compose などの自動化ツールを使用するときに問題が発生する可能性があります。
MySQL に接続しようとしているアプリケーションが MySQL のダウンタイムを処理しない場合、または MySQL が正常に起動するのを待っている場合は、サービスが開始する前に接続と再試行のループを設定する必要があります。
公式イメージでのそのような実装の例については WordPress または Bonita を参照してください。既存のデータベースに対する使用法
すでにデータベースが含まれているデータディレクトリ(具体的には
mysqlサブディレクトリ)で mysql コンテナインスタンスを起動する場合は、実行コマンドラインから$MYSQL_ROOT_PASSWORD変数を省略してください。
上記変数を指定した場合でも無視され、既存のデータベースは変更されません。任意のユーザーとして実行する方法
ディレクトリの権限がすでに適切に設定されていることがわかっている場合(上記のように既存のデータベースに対して実行する場合など)、または特定の UID/GID を使用して mysqld を実行する必要がある場合は、
--userを使用してこのイメージを呼び出すことができます。
目的の接続/構成を実現するには、任意の値(root/0 以外)に設定します。$ mkdir data $ ls -lnd data drwxr-xr-x 2 1000 1000 4096 Aug 27 15:54 data $ docker run -v "$PWD/data":/var/lib/mysql --user 1000:1000 --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tagデータベースダンプの作成
通常のツールのほとんどは機能しますが、 mysqld サーバーにアクセスできるようにするために、使用法が少し複雑になる場合があります。
これを確認する簡単な方法はdocker execを使用して、次のように同じコンテナーからツールを実行することです。$ docker exec some-mysql sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sqlダンプファイルからのデータリストア
データの復元用。
次のように-iフラグを指定してdocker execコマンドを使用できます。$ docker exec -i some-mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /some/path/on/your/host/all-databases.sqlライセンス
このイメージに含まれているソフトウェアのライセンス情報を表示します。
すべての Docker イメージと同様に、これらには他のライセンスの下にある可能性のある他のソフトウェア(ベースディストリビューションの Bash など、プライマリソフトウェアに含まれている直接的または間接的な依存関係)も含まれている可能性があります。
自動検出できたいくつかの追加のライセンス情報は repo-info リポジトリのmysql/ディレクトリにある可能性があります。
ビルド済みのイメージの使用に関しては、このイメージの使用がそれに含まれるすべてのソフトウェアの関連するライセンスに準拠していることを確認するのは、イメージユーザーの責任です。
- 投稿日：2020-10-24T19:49:18+09:00
Docker mysql クイックリファレンス 日本語訳
Docker Hub の mysql のクイックリファレンス の日本語訳。
MySQL とは?
MySQL は世界で最も人気のあるオープンソースデータベースです。
実証済みのパフォーマンス、信頼性、使いやすさを備えた MySQL は、個人のプロジェクトや Web サイト、 e コマースや情報サービスなど、全範囲をカバーする Web ベースのアプリケーションの主要なデータベースの選択肢になりました。
Facebook、Twitter、YouTube、Yahoo! などの Web プロパティなどでも使用されています。
MySQL Server およびその他の MySQL 製品の詳細および関連するダウンロードについては www.mysql.com にアクセスしてください。イメージの使い方
mysql サーバーインスタンスの開始
MySQL インスタンスの開始は簡単です。
$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag
some-mysqlはコンテナに割り当てる名前、my-secret-pwは MySQL root ユーザーに設定するパスワード、tagは必要な MySQL バージョンを指定するタグです。
関連するタグについては、上記のリストを参照してください。
MySQL コマンドラインクライアントから MySQL に接続します。
次のコマンドは、別の mysql コンテナインスタンスを起動し、元の mysql コンテナに対して mysql コマンドラインクライアントを実行して、データベースインスタンスに対して SQL ステートメントを実行できるようにします。$ docker run -it --network some-network --rm mysql mysql -hsome-mysql -uexample-user -p
some-mysqlは、元の mysql コンテナ(Docker ネットワークに接続されているsome-network)の名前です。
このイメージは Docker 以外のインスタンスまたはリモートインスタンスのクライアントとしても使用できます。$ docker run -it --rm mysql mysql -hsome.mysql.host -usome-mysql-user -pMySQL コマンドラインクライアントの詳細については MySQL のドキュメントを参照してください。
docker stack deploy または docker-compose を介した使用法
stack.yml記述例# Use root/example as user/password credentials version: '3.1' services: db: image: mysql command: --default-authentication-plugin=mysql_native_password restart: always environment: MYSQL_ROOT_PASSWORD: example adminer: image: adminer restart: always ports: - 8080:8080
docker stack deploy -c stack.yml mysql(またはdocker-compose -f stack.yml up)を実行し、完全に初期化されるのを待って http://swarm-ip:8080, http://localhost:8080, または http://host-ip:8080 へアクセスします。コンテナシェルへのアクセスと MySQL ログの表示
docker execコマンドを使用すると Docker コンテナ内でコマンドを実行できます。
次のコマンドラインは mysql コンテナ内に bash シェルを提供します。$ docker exec -it some-mysql bashログは Docker のコンテナログから入手できます。
$ docker logs some-mysqlカスタム MySQL 構成ファイルの使用
MySQL のデフォルト設定は
/etc/mysql/my.cnfにあります。
これには/etc/mysql/conf.dや/etc/mysql/mysql.conf.dなどの追加のディレクトリが含まれている場合があります。
詳細については mysql イメージ内の関連するファイルとディレクトリを調べてください。
/my/custom/config-file.cnfがカスタム構成ファイルのパスと名前である場合、次のように mysql コンテナーを開始できます(このコマンドでは、カスタム構成ファイルのディレクトリー・パスのみが使用されることに注意してください)。$ docker run --name some-mysql -v /my/custom:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tagこれにより MySQL インスタンスが
/etc/mysql/my.cnfと/etc/mysql/conf.d/config-file.cnfの起動設定を組み合わせて使用し、後者の設定が優先される新しいコンテナ some-mysql が起動します。cnf ファイルを使用しない設定方法
多くの構成オプションをフラグとして mysqld に渡すことができます。これにより
cnfファイルを必要とせずにコンテナーをカスタマイズできる柔軟性が得られます。
たとえば、すべてのテーブルのデフォルトのエンコーディングと照合順序を変更して UTF-8(utf8mb4)を使用する場合は、次のコマンドを実行するだけです。$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci利用可能なオプションの完全なリストを確認したい場合は以下を実行してください。
$ docker run -it --rm mysql:tag --verbose --help環境変数
mysql イメージを起動するときに
docker runコマンドラインで 1 つ以上の環境変数を渡すことにより、 MySQL インスタンスの構成を調整できます。
すでにデータベースが含まれているデータディレクトリでコンテナを起動した場合、以下の変数はいずれも効果がないことに注意してください。
既存のデータベースは、コンテナの起動時に常に変更されません。
MySQL 自体が尊重する環境変数(特にこのイメージと一緒に使用すると問題を引き起こすことが知られているMYSQL_HOSTのような変数)のドキュメントについては、https://dev.mysql.com/doc/refman/5.7/en/environment-variables.html も参照してください。MYSQL_ROOT_PASSWORD
この変数は必須であり MySQL ルートスーパーユーザーアカウントに設定されるパスワードを指定します。
上記の例ではmy-secret-pwに設定されています。MYSQL_DATABASE
この変数はオプションです。
イメージの起動時に作成されるデータベースの名前を指定できます。
ユーザー / パスワードが指定された場合(以下を参照)、そのユーザーには、このデータベースへのスーパーユーザーアクセス(GRANT ALLに対応)が付与されます。MYSQL_USER, MYSQL_PASSWORD
これらの変数はオプションです。
新しいユーザーを作成し、そのユーザーのパスワードを設定するために組み合わせて使用されます。
このユーザーには MYSQL_DATABASE 変数で指定されたデータベースに対するスーパーユーザー権限(上記を参照)が付与されます。
ユーザーを作成するには、両方の変数が必要です。
ルートスーパーユーザーを作成するためにこの仕組みを使用する必要はないことに注意してください。
ルートスーパーユーザーは、デフォルトで MYSQL_ROOT_PASSWORD 変数で指定されたパスワードで作成されます。MYSQL_ALLOW_EMPTY_PASSWORD
これはオプションの変数です。
yesのように空でない値に設定するとパスワードなしの root ユーザーでコンテナーを開始できます。注: この変数を
yesに設定することは、何をしているのかを本当に理解していない限りお勧めしません。
これにより MySQL インスタンスが完全に保護されなくなり、誰でも完全なスーパーユーザーアクセスを取得できるようになります。MYSQL_RANDOM_ROOT_PASSWORD
これはオプションの変数です。
(pwgenを使用して) root ユーザーのランダムな初期パスワードを生成するにはyesなどの空でない値に設定します。
生成された root パスワードは stdout に出力されます(GENERATED ROOT PASSWORD: ......)。MYSQL_ONETIME_PASSWORD
初期化が完了すると root ユーザー( MYSQL_USER で指定されたユーザーではない)を期限切れとして設定し、最初のログイン時にパスワードの変更を強制します。
空でない値があると、この設定がアクティブになります。注: この機能は MySQL 5.6 以降でのみサポートされています。
MySQL 5.5 でこのオプションを使用すると、初期化中にエラーが送出されます。MYSQL_INITDB_SKIP_TZINFO
デフォルトでは、エントリポイントスクリプトは
CONVERT_TZ()関数に必要なタイムゾーンデータを自動的にロードします。
不要な場合、空でない値を指定するとタイムゾーンの読み込みが無効になります。Docker Secrets
環境変数を介して機密情報を渡す代わりに、前述の環境変数に
_FILEを追加して、初期化スクリプトがコンテナ内に存在するファイルからそれらの変数の値をロードするようにすることができます。
特にこれは/run/secrets/<secret_name>ファイルに保存されている Docker シークレットからパスワードをロードするために使用できます。
例えば:$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql-root -d mysql:tag現在、これは以下の変数でのみサポートされています。
- MYSQL_ROOT_PASSWORD
- MYSQL_ROOT_HOST
- MYSQL_DATABASE
- MYSQL_USER
- MYSQL_PASSWORD
新しいインスタンスの初期化
コンテナが初めて起動されると、指定された名前の新しいデータベースが作成され、指定された構成変数で初期化されます。
さらに/docker-entrypoint-initdb.dにある拡張子.sh、.sql、および.sql.gzのファイルを実行します。
ファイルはアルファベット順に実行されます。
SQL ダンプをそのディレクトリにマウントし、提供されたデータを含むカスタムイメージを提供することで、 mysql サービスに簡単にデータを取り込むことができます。
SQL ファイルは、デフォルトで MYSQL_DATABASE 変数で指定されたデータベースにインポートされます。データの保存場所
重要な注意: Docker コンテナで実行されるアプリケーションが使用するデータを保存する方法はいくつかあります。
mysql イメージのユーザーは、次のような利用可能なオプションに慣れることをお勧めします。Docker に独自の内部ボリューム管理を使用してホストシステム上のディスクにデータベースファイルを書き込むことにより、データベースデータのストレージを管理させます。
これはデフォルトであり、ユーザーにとって簡単でかなり透過的です。
欠点はホストシステム上で直接実行されるツールやアプリケーション、つまりコンテナの外部ではファイルを見つけるのが難しい場合があることです。
ホストシステム(コンテナの外側)にデータディレクトリを作成し、これをコンテナの内側から見えるディレクトリにマウントします。
これにより、データベースファイルがホストシステムの既知の場所に配置され、ホストシステム上のツールやアプリケーションがファイルに簡単にアクセスできるようになります。
欠点はユーザーがディレクトリが存在することを確認する必要があることと、ホストシステムのディレクトリ権限およびその他のセキュリティメカニズムが正しく設定されていることを確認する必要があることです。Docker のドキュメントは、さまざまなストレージオプションとバリエーションを理解するための良い出発点であり、この分野で議論し、アドバイスを提供するブログやフォーラムの投稿が複数あります。
上記の後者のオプションの基本的な手順をここで簡単に示します。ホストシステムの適切なボリュームに
/my/own/datadirのようなデータディレクトリを作成します。
次のように mysql コンテナを起動します。$ docker run --name some-mysql -v /my/own/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tagコマンドの
-v /my/own/datadir:/var/lib/mysqlの部分は、基礎となるホストシステムから/my/own/datadirディレクトリをコンテナ内の/var/lib/mysqlとしてマウントします。
MySQL はデフォルトでデータファイルを書き込みます。
MySQL の初期化が完了するまで接続はありません。
コンテナの起動時に初期化されたデータベースがない場合は、デフォルトのデータベースが作成されます。
これは予想される動作ですが、初期化が完了するまで着信接続を受け入れないことを意味します。
これにより、複数のコンテナーを同時に起動する docker-compose などの自動化ツールを使用するときに問題が発生する可能性があります。
MySQL に接続しようとしているアプリケーションが MySQL のダウンタイムを処理しない場合、または MySQL が正常に起動するのを待っている場合は、サービスが開始する前に接続と再試行のループを設定する必要があります。
公式イメージでのそのような実装の例については WordPress または Bonita を参照してください。既存のデータベースに対する使用法
すでにデータベースが含まれているデータディレクトリ(具体的には
mysqlサブディレクトリ)で mysql コンテナインスタンスを起動する場合は、実行コマンドラインから$MYSQL_ROOT_PASSWORD変数を省略してください。
上記変数を指定した場合でも無視され、既存のデータベースは変更されません。任意のユーザーとして実行する方法
ディレクトリの権限がすでに適切に設定されていることがわかっている場合(上記のように既存のデータベースに対して実行する場合など)、または特定の UID/GID を使用して mysqld を実行する必要がある場合は、
--userを使用してこのイメージを呼び出すことができます。
目的の接続/構成を実現するには、任意の値(root/0 以外)に設定します。$ mkdir data $ ls -lnd data drwxr-xr-x 2 1000 1000 4096 Aug 27 15:54 data $ docker run -v "$PWD/data":/var/lib/mysql --user 1000:1000 --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tagデータベースダンプの作成
通常のツールのほとんどは機能しますが、 mysqld サーバーにアクセスできるようにするために、使用法が少し複雑になる場合があります。
これを確認する簡単な方法はdocker execを使用して、次のように同じコンテナーからツールを実行することです。$ docker exec some-mysql sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sqlダンプファイルからのデータリストア
データの復元用。
次のように-iフラグを指定してdocker execコマンドを使用できます。$ docker exec -i some-mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /some/path/on/your/host/all-databases.sqlライセンス
このイメージに含まれているソフトウェアのライセンス情報を表示します。
すべての Docker イメージと同様に、これらには他のライセンスの下にある可能性のある他のソフトウェア(ベースディストリビューションの Bash など、プライマリソフトウェアに含まれている直接的または間接的な依存関係)も含まれている可能性があります。
自動検出できたいくつかの追加のライセンス情報は repo-info リポジトリのmysql/ディレクトリにある可能性があります。
ビルド済みのイメージの使用に関しては、このイメージの使用がそれに含まれるすべてのソフトウェアの関連するライセンスに準拠していることを確認するのは、イメージユーザーの責任です。
- 投稿日：2020-10-24T01:13:46+09:00
MySQLクエリログの見方
前提
MySQLにおけるクエリログの見方を忘れがちなのでメモします。
MySQL8
本題
MySQLのクエリログはgeneral_logと言う設定をONにすることにより、
すべてのクエリをログに吐かせることができる。まずは、general_logの設定確認。
mysql> show variables like 'general_log%'; +------------------+---------------------------------+ | Variable_name | Value | +------------------+---------------------------------+ | general_log | OFF | | general_log_file | /var/lib/mysql/2a4a9fa16d2e.log | +------------------+---------------------------------+ 2 rows in set (0.00 sec)デフォルトでは恐らくOFFになっているため、ONに変更します。
mysql> set global general_log = on; Query OK, 0 rows affected (0.02 sec)確認。
mysql> show variables like 'general_log%'; +------------------+---------------------------------+ | Variable_name | Value | +------------------+---------------------------------+ | general_log | ON | | general_log_file | /var/lib/mysql/2a4a9fa16d2e.log | +------------------+---------------------------------+ 2 rows in set (0.01 sec)試しにクエリを投げてみます。
mysql> select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.01 sec)$ cat /var/lib/mysql/2a4a9fa16d2e.log 2020-10-24T00:00:00.278525Z 19 Query show variables like 'general_log%' 2020-10-24T00:00:00.802232Z 18 Query select 1※ログの分だけディスクを食うので注意。