mita2 database life

主にMySQLに関するメモです

Gemini Code Assist に プルリク上 で DDL をレビューさせる

Gemini Code Assist に DDL をレビューさせてみました。

レビューさせるDDL

CREATE TABLE users (id int, user_name TEXT, create datetime);
CREATE TABLE user_items (id int, user_id VARCHAR(10), itemName TEXT, status VARCHAR(250), created datetime);

以下のような問題点を意図的に仕込みました。はたして、Gemini Code Assist はうまく指摘してくれるのでしょうか?

  1. Primary Key が指定されていない
  2. user_nameNOT NULL の指定漏れ
  3. 予約語 create が使用されてしまっている
  4. usersiduser_itemsuser_id の型の不一致
  5. userscreateuser_itemscreated の表記揺れ
  6. 命名規則 が CamelCase と SnakeCase の混在
  7. 不必要に大きな型 (user_name, status)

Gemini Code Assist を Github にインストール

developers.google.com

何も指示を与えない状態

とりあえず、何も指示をあたえず、デフォルトの状態でレビューさせてみます。 PRを作ると自動的に Gemini Code Assist がレビューを開始し、コメントをつけてくれます。1分程度で、結果が返ってきました。

https://github.com/samitani/DDLreview/pull/1

Gemini Code Assist

結果は以下でした。このリポジトリDDLしかありません。情報が限られた中でのレビューということを考えると、かなり良い感じだと感じました。 正直、この状態でも十分満足です。

  1. ✅ Primary Key が指定されていない
  2. user_nameNOT NULL の指定漏れ
  3. 予約語 create が使用されてしまっている
  4. usersiduser_itemsuser_id の型の不一致
  5. userscreateuser_itemscreated の表記揺れ
  6. 命名規則 が CamelCase と SnakeCase の混在
  7. ❌ 不必要に大きな型 (user_name, status)

それ以外にも、外部キー制約が必要な点が指摘されてます。

型が妥当かどうかは、ドメイン知識がないと判断が難しいのでしょう。アプリケーションのコードが一緒にコミットされてれば、検知してくれるかもしれません。

指示を与えてあらためてレビューさせる

.gemini/styleguide.md に指示を記述できます。 適当に指示を追加してレビューさせてみましょう。

% cat .gemini/styleguide.md
日本語で回答してください。

# DDL をレビューするときのポイント

* 外部キー制約は任意とします。ただし、既存のテーブルで外部キー制約をすでに利用している場合は、常に外部キー制約を利用するようにしてください。
* カラム名の表記ゆれがないか確認してください。
* TEXT型が利用されている場合、VARCHAR型を検討してください。
* フラグやステータスといった値のバリエーションが固定されているカラムにはENUM型を検討してください。

https://github.com/samitani/DDLreview/pull/2

  1. ✅ Primary Key が指定されていない
  2. user_nameNOT NULL の指定漏れ
  3. 予約語 create が使用されてしまっている
  4. usersiduser_itemsuser_id の型の不一致
  5. userscreateuser_itemscreated の表記揺れ
  6. 命名規則 が CamelCase と SnakeCase の混在
  7. ✅不必要に大きな型 (user_name, status)

7 の型については指摘が入るようになりました。 5 の表記ゆれは今回もスルーされました。もうちょっと詳しく指示しないとダメかもしれません。

初期状態では 外部キー制約を This is crucial と強く利用を勧めてきてましたが、指示を考慮して、今回は マイルドな指摘へと変化してます。

データ型を揃えることで、データ整合性を保つための外部キー制約 (FOREIGN KEY (user_id) REFERENCES users(id)) の追加も可能になります。

指示を改良してリトライ

表記ゆれを見逃さないように「似たカラム名がある場合、表記がゆれていないか確認してください」と怪しいケースを積極的にひろうように指示してみます。 また、外部キーを任意としたので、代わりにインデックスの有無を確認するよう指示を足しました。

% git diff
diff --git a/.gemini/styleguide.md b/.gemini/styleguide.md
index c110d42..9038135 100644
--- a/.gemini/styleguide.md
+++ b/.gemini/styleguide.md
@@ -3,6 +3,7 @@
 # DDL をレビューするときのポイント

 * 外部キー制約は任意とします。ただし、既存のテーブルで外部キー制約をすでに利用している場合は、常に外部キー制約を利用するようにしてください。
-* カラム名の表記ゆれがないか確認してください。
+* キーとなるカラムにインデックスが追加されているか確認してください。
+* 似たカラム名がある場合、表記がゆれていないか確認してください。
 * TEXT型が利用されている場合、VARCHAR型を検討してください。
 * フラグやステータスといった値のバリエーションが固定されているカラムにはENUM型を検討してください。

https://github.com/samitani/DDLreview/pull/3

今回は表記ゆれもバッチリ検知してくれました 👏

中 (Medium): created カラム名も users テーブルと合わせて created_at に統一すると、スキーマ全体の一貫性が高まります。

高 (High): 外部キー候補である user_id カラムにインデックスがありません。検索パフォーマンス向上のため、インデックスの追加を強く推奨します。1

Gemini Code Assist すばらしい。

go-sysbench のカスタムシナリオが書きやすくなりました

go-sysbench

go-sysbench は Go 言語で書かれた、sysbench のクローンです。複雑なカスタムシナリオを sysbench より簡単に作れることを目的として作りました。

github.com

sysbench はシンプルで、非常に使いやすいベンチマークツールで、長年愛用してきました。Lua 言語でカスタムシナリオを書くこともできます。 ただ、Lua 言語で複雑なカスタムシナリオを書くのは難しいと感じてました(Lua 言語に慣れていないということもあると思います)。 そこで、使い勝手や出力は sysbench のまま、より柔軟性の高い Go言語で sysbench のクローンを作りました。

$ go-sysbench --help
Usage:
  go-sysbench [options]... [oltp_read_only|oltp_read_write] [prepare|run]

Application Options:
      --threads=                        number of threads to use (default: 1)
      --events=                         limit for total number of events (default: 0)
      --time=                           limit for total execution time in seconds (default: 10)
      --report-interval=                periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports (default: 0)
      --histogram=[on|off]              print latency histogram in report (default: off)
      --percentile=                     percentile to calculate in latency statistics (1-100) (default: 95)
      --tables=                         number of tables (default: 1)
      --table_size=                     number of rows per table (default: 10000)
      --table-size=                     alias of --table_size
      --db-driver=[mysql|pgsql|spanner] specifies database driver to use (default: mysql)
      --db-ps-mode=[auto|disable]       prepared statements usage mode (default: auto)
      --version                         show version

MySQL:
      --mysql-host=                     MySQL server host (default: localhost)
      --mysql-port=                     MySQL server port (default: 3306)
      --mysql-user=                     MySQL user (default: sbtest)
      --mysql-password=                 MySQL password [$MYSQL_PWD]
      --mysql-db=                       MySQL database name (default: sbtest)
      --mysql-ssl=[on|off]              use SSL connections (default: off)
      --mysql-ignore-errors=            list of errors to ignore, or "all" (default: 1213,1020,1205)

PostgreSQL:
      --pgsql-host=                     PostgreSQL server host (default: localhost)
      --pgsql-port=                     PostgreSQL server port (default: 5432)
      --pgsql-user=                     PostgreSQL user (default: sbtest)
      --pgsql-password=                 PostgreSQL password [$PGPASSWORD]
      --pgsql-db=                       PostgreSQL database name (default: sbtest)
      --pgsql-ssl=[on|off]              use SSL connections (default: off)
      --pgsql-ignore-errors=            list of errors to ignore, or "all" (default: 40P01,23505,40001)

Spanner:
      --spanner-project=                Spanner Google Cloud project name
      --spanner-instance=               Spanner instance id
      --spanner-db=                     Spanner database name (default: sbtest)

Help Options:
  -h, --help                            Show this help message

今回修正した内容

go-sysbench の初期実装は、シナリオとベンチマーカー本体のロジックが十分整理できておらず、少しカスタムしにくい実装でした。 今回、インターフェイスを整理して、完全に独立したライブラリとして使えるようにしました。

使い方

github.com/samitani/go-sysbench を import して、計測したい処理だけ記述すればOKです。

  • Init()でDBの接続など、準備を行い、Event() 関数に計測したい処理を記述します。
  • Event() 関数は Read/Write/Others/IgnoreError 数を返す必要があります、これらの数は集計され、ベンチマーク結果に出力されます。
  • Event() 関数が1回実行されると、1回トランザクションが成功したとみなされます(結果のtransactions がインクリメントされます)。
package main

import (
        "context"
        "fmt"
        "os"

        "database/sql"

        _ "github.com/go-sql-driver/mysql"

        "github.com/samitani/go-sysbench"
)

type CustomBenchmark struct {
        db *sql.DB
}

// when Runner.Prepare(), Runner.Run() is called, Init() is called once in advance.
func (b *CustomBenchmark) Init(ctx context.Context) error {
        db, err := sql.Open("mysql", "root:password@/my_database")
        if err != nil {
                return err
        }

        err = db.Ping()
        if err != nil {
                return err
        }

        b.db = db
        return nil
}

// when Runner.Prepare(), Runner.Run() is called, Done() is called once at the end.
func (b *CustomBenchmark) Done() error {
        b.db.Close()
        return nil
}

// when Runner.Prepare() is called, Prepare() is called once.
func (b *CustomBenchmark) Prepare(ctx context.Context) error {
        // nothing to do
        return nil
}

// when Runner.Run() is called, PreEvent() is called once before event loop.
func (b *CustomBenchmark) PreEvent(ctx context.Context) error {
        // nothing to do
        return nil
}

// when Runner.Run() is called, Event() is called in a loop
func (b *CustomBenchmark) Event(ctx context.Context) (numReads, numWrites, numOthers, numIgnoredErros uint64, err error) {
        var readCount uint64 = 0
        var writeCount uint64 = 0

        // something you want to measure
        for i := 0; i < 5; i++ {
                rows, err := b.db.QueryContext(ctx, "SELECT NOW()")
                if err != nil {
                        return readCount, 0, 0, 0, err
                }
                defer rows.Close()

                // fetch rows from server
                for rows.Next() {
                }

                readCount = readCount + 1
        }

        return readCount, writeCount, 0, 0, nil
}

func main() {
        bench := &CustomBenchmark{}

        r := sysbench.NewRunner(&sysbench.RunnerOpts{
                Threads:        10,
                Events:         0,
                Time:           60,
                ReportInterval: 1,
                Histogram:      "on",
                Percentile:     95,
        }, bench)

        if err := r.Run(); err != nil {
                fmt.Println(err)
                os.Exit(1)
        }
}

そして、結果は見慣れた sysbench の形式で出力されます。

$ ./main
Running the test with following options:
Number of threads: 10
Report intermediate results every 1 second(s)

[ 1s ] thds: 10 tps: 5072.00 qps: 25360.00 (r/w/o: 25360.00/0.00/0.00) lat (ms,95%): 2.91 err/s 0.00 reconn/s: N/A
[ 2s ] thds: 10 tps: 5162.00 qps: 25810.00 (r/w/o: 25810.00/0.00/0.00) lat (ms,95%): 2.91 err/s 0.00 reconn/s: N/A
[ 3s ] thds: 10 tps: 5180.00 qps: 25900.00 (r/w/o: 25900.00/0.00/0.00) lat (ms,95%): 2.86 err/s 0.00 reconn/s: N/A
[ 4s ] thds: 10 tps: 5387.00 qps: 26935.00 (r/w/o: 26935.00/0.00/0.00) lat (ms,95%): 2.76 err/s 0.00 reconn/s: N/A
[ 5s ] thds: 10 tps: 5340.00 qps: 26700.00 (r/w/o: 26700.00/0.00/0.00) lat (ms,95%): 2.86 err/s 0.00 reconn/s: N/A
[ 6s ] thds: 10 tps: 5199.00 qps: 25995.00 (r/w/o: 25995.00/0.00/0.00) lat (ms,95%): 2.86 err/s 0.00 reconn/s: N/A
[ 7s ] thds: 10 tps: 5422.00 qps: 27110.00 (r/w/o: 27110.00/0.00/0.00) lat (ms,95%): 2.76 err/s 0.00 reconn/s: N/A
[ 8s ] thds: 10 tps: 5193.00 qps: 25965.00 (r/w/o: 25965.00/0.00/0.00) lat (ms,95%): 2.86 err/s 0.00 reconn/s: N/A
[ 9s ] thds: 10 tps: 5283.00 qps: 26415.00 (r/w/o: 26415.00/0.00/0.00) lat (ms,95%): 2.81 err/s 0.00 reconn/s: N/A
<snip>
Latency histogram (values are in milliseconds)
       value  ------------- distribution ------------- count
       0.127 |                                         1
       0.312 |                                         1
       0.318 |                                         1
       0.448 |                                         1
       0.546 |                                         1
       0.576 |                                         1
SQL statistics:
    queries performed:
        read:                            1572640
        write:                           0
        other:                           0
        total:                           1572640
    transactions:                        314535 (5242.20 per sec.)
    queries:                             1572640 (26210.44 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          N/A    (N/A per sec.)

General statistics:
    total time:                          60.0005s
    total number of events:              314535

Latency (ms):
         min:                                    0.13
         avg:                                    1.91
         max:                                    8.12
         95th percentile:                        2.86
         sum:                               599624.51

Threads fairness (Event distribution by threads):
    events (avg/stddev):           31453.5000/69.05
    execution time (avg/stddev):   59.9625/0.00

より、実用的な例は oltp_read_only, oltp_read_write の実装をみてください。

MySQL SYSTEM_USER 権限を持ったユーザの操作には SYSTEM_USER 権限 が必要

動的権限と静的権限

「動的権限は、従来の静的権限が細分化されたもの。静的権限を持っていれば、動的権限はREVOKEしても影響はない」と考えていました。 例えば、SUPER 権限 (静的) を持っていれば、BINLOG_ADMIN 権限 (動的) がなくとも、バイナリログをパージできます。

mysql> SHOW GRANTS;
+-------------------------------------------+
| Grants for super@localhost                |
+-------------------------------------------+
| GRANT SUPER ON *.* TO `super`@`localhost` |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>  PURGE BINARY LOGS TO 'binlog.000008';
Query OK, 0 rows affected (0.01 sec)

しかし、SYSTEM_USER 権限は、静的権限では満たせない仕様がありました。

dev.mysql.com

SYSTEM_USER 権限によるアカウント保護

簡単に言うと、SYSTEM_USER を持つユーザ(システムユーザ)の権限を変更したり、ユーザを削除するには、SYSTEM_USER 権限を持っている必要があります。 SUPER 等の静的権限では、システムユーザを変更できません。

スマートスタイルさんの記事がわかりやすいです。

https://blog.s-style.co.jp/2020/06/6097/

システムユーザを作成する。

mysql> CREATE USER 'system_user'@'localhost';
Query OK, 0 rows affected (0.09 sec)

mysql> GRANT SYSTEM_USER ON *.* TO  'system_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.04 sec)
-- 自分 (root) から SYSTEM_USER を 剥奪
mysql> REVOKE SYSTEM_USER ON *.* FROM 'root'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

システムユーザに権限を付与することも、削除することもできなくなりました。

mysql> GRANT SELECT ON *.* TO 'system_user'@'localhost';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

mysql> DROP USER 'system_user'@'localhost';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

SYSTEM_USER 権限を復活(付与)する

SYSTEM_USER 権限の付与は SUPER 権限を持っていれば可能です。

mysql> GRANT SYSTEM_USER  ON *.* TO 'root'@'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)

まとめ

  • SYSTEM_USER 権限を REVOKE してしまうと、例え SUPER 権限をもってても、システムユーザの変更はできない
  • SUPER権限を持つユーザを持っていれば、SYSTEM_USER権限 を付与することは可能

「マネージドサービスを実装するために、追加された仕組みなんだろうなぁ」と思いました

BCP/DR:バックアップ・レプリカ・マルチリージョンクラスターの比較

  • 目標復旧時点(Recovery Point Objective = RPO)
  • 目標復旧時間(Recovery Time Objective = RTO)

バックアップの遠隔地保管

  • RPO

    • RPO はバックアップ頻度に依存
    • 日次バックアップであれば、RPOは1日、最大で1日分のデータがロスト
  • RTO

    • 比較的、長い
    • データベースのサイズに比例
    • 特にサイズの大きいデータベースのリカバリは時間がかかる
  • コスト
    • ストレージ保管コスト+バックアップを転送するコストがかかる
    • 基本的には低コストだが、クラウドではネットワーク転送コストが高くつくため、バックアップのサイズが大きいと、レプリカのほうが安くつく場合がある

非同期レプリカを遠隔地に設置

  • RPO
    • ミリ秒〜数秒レベル
    • トランザクションログ(MySQLでいうとバイナリログ)を転送するのにかかる時間だけ考えれば良い
    • MySQL の Applier Thread の遅延はRPOに影響しない。SHOW SLAVE STATUSSeconds_Behind_Master の 値 は RPOには関係ない。データの転送は Receiver IO Thread がバイナリログを読み取った時点で完了している。
  • RTO
    • 比較的、短め
    • データのリカバリが不要
    • 復旧時に、レプリカの台数やスペックを変更する場合はその時間も考慮
  • コスト
    • レプリカの台数や構成に依存
    • すぐに切り替えられるよう、本番と同じ構成にすると高コスト
    • 最小構成であれば比較的低コスト
      • テーブルを限定することも可能

マルチリージョンクラスタ

分散データベースを想定。DBサーバを複数のリージョンに配置し、複数のリージョンにまたがった単一のクラスターを構成する。 データは複数のリージョンに同期的に書き込まれる(複製される)。

  • RPO
    • RPO: 0。データロストなし。
  • RTO
    • 最短。タイムアウトの設定にもよるが、秒レベルで障害サイトが切り離される。
    • 復旧作業自体が不要。
  • コスト
    • 基本的に高い
  • その他
    • クエリの性能(レイテンシ)が悪化する
    • 切り戻しが容易
    • 分散データベースを用いるため、3リージョン以上

ざっくりとしたまとめ

手法 RPO RTO コスト
日次バックアップの遠隔地保管 1日 長め 低め
レプリカ 数秒 構成に依存 中ぐらい
マルチリージョンクラスタ 秒レベル 高い

MySQL バージョンアップ時の動的権限の追加処理について理解する

動的権限 / Dynamic Privilege

MySQL 8.0 で、動的権限と呼ばれる、従来より細かい範囲で操作を許可できる権限が追加されました。

gihyo.jp

バージョンアップ時に自動的に動的権限が付与される

一部の静的権限(従来の権限)を保持しているユーザには、MySQL バージョンアップ時に動的権限が自動的に付与されます。 例えば、MySQL 5.7 で作成した SUPER 権限保持ユーザの権限は、MySQL 8.0 へのアップグレードで以下のように変化します。

mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 5.7.44    |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR super57@'localhost';
+---------------------------------------------+
| Grants for super57@localhost                |
+---------------------------------------------+
| GRANT SUPER ON *.* TO 'super57'@'localhost' |
+---------------------------------------------+
1 row in set (0.00 sec)
  • 8.0.28 バージョンアップ後
mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR super57@'localhost' \G
*************************** 1. row ***************************
Grants for super57@localhost: GRANT SUPER ON *.* TO `super57`@`localhost`
*************************** 2. row ***************************
Grants for super57@localhost: GRANT AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `super57`@`localhost`
2 rows in set (0.00 sec)

SUPER 権限は将来的に、削除が予定されています。互換性の維持のため、このように自動的に動的権限へと変換してくれるのでしょう。

バージョンアップ後に作成したユーザには動的権限の自動追加はされない

バージョンアップの過程で、動的権限の追加は行われます。同じ静的権限を付与したとしても、バージョンアップ後に改めて作成したユーザには、動的権限は追加されません。 同じ GRANT 文を実行して作成したユーザでも、バージョンアップを経たかどうかで表示される権限が異なってしまいます。

  • MySQL 8.0.28 で新たにユーザ作成した場合
mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE USER super8028@'localhost' IDENTIFIED BY 'XXXXXXXXXXXXX';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SUPER ON *.* TO super8028@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

-- 新規に作成したSUPERを持つユーザ、動的権限は明示的に指定されない限り付与されない
mysql> SHOW GRANTS FOR super8028@'localhost' \G
*************************** 1. row ***************************
Grants for super8028@localhost: GRANT SUPER ON *.* TO `super8028`@`localhost`
1 row in set (0.00 sec)

ただ、実際に、許可されるオペレーションに差はないはずです。 動的権限は基本的に、静的権限が細分化されたものであり、静的権限(例えばSUPER)を保持していれば、動的権限がなくても同様の操作が許可されているはずです。

マイナーアップデートでも発生する

MySQL 8.0 ではマイナーバージョンで追加された、動的権限があります。とみたさんの MySQL Parameters で見ると非常にわかりやすいです。

マイナーバージョンアップ時も動的権限の追加は発生します。動的権限の追加は、権限ごとに「その動的権限を保持しているユーザがいないこと」が発動(権限追加)の条件になります。

  • 例) BINLOG_ENCRYPTION_ADMIN 権限を追加する処理
-- Add the privilege BINLOG_ENCRYPTION_ADMIN for every user who has the privilege SUPER
-- provided that there isn't a user who already has the privilige BINLOG_ENCRYPTION_ADMIN.
SET @hadBinLogEncryptionAdminPriv = (SELECT COUNT(*) FROM global_grants WHERE priv = 'BINLOG_ENCRYPTION_ADMIN');
INSERT INTO global_grants SELECT user, host, 'BINLOG_ENCRYPTION_ADMIN', IF(grant_priv = 'Y', 'Y', 'N')
FROM mysql.user WHERE super_priv = 'Y' AND @hadBinLogEncryptionAdminPriv = 0;
COMMIT;

この結果、アップグレードパスとユーザ作成のタイミングによって、権限の異なるユーザが出来てしまい、ややこしいです。 例えば、MySQL 5.7 → 8.0.28 → 8.0.40 とバージョンアップし、それぞれのバージョンでユーザを追加したとします。最終的にそれぞれのユーザの権限は以下になります。

-- MySQL 5.7 時点で作成したユーザ
mysql> SHOW GRANTS FOR super57@'localhost' \G
*************************** 1. row ***************************
Grants for super57@localhost: GRANT SUPER ON *.* TO `super57`@`localhost`
*************************** 2. row ***************************
Grants for super57@localhost: GRANT AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN,TELEMETRY_LOG_ADMIN ON *.* TO `super57`@`localhost`
2 rows in set (0.00 sec)

-- MySQL 8.0.28 時点で作成したユーザ
-- AUDIT_ABORT_EXEMPT などは、super57 がすでに、保持しているため追加されない
mysql> SHOW GRANTS FOR super8028@'localhost' \G
*************************** 1. row ***************************
Grants for super8028@localhost: GRANT SUPER ON *.* TO `super8028`@`localhost`
*************************** 2. row ***************************
Grants for super8028@localhost: GRANT TELEMETRY_LOG_ADMIN ON *.* TO `super8028`@`localhost`
2 rows in set (0.00 sec)

-- MySQL 8.0.40 時点で作成したユーザ
mysql> SHOW GRANTS FOR super8040@'localhost' \G
*************************** 1. row ***************************
Grants for super8040@localhost: GRANT SUPER ON *.* TO `super8040`@`localhost`
1 row in set (0.00 sec)

追加された動的権限を剥奪してもバージョンアップで復活する

上記で記載したように、各動的権限が追加される条件は、バージョンではなく、「その動的権限を保持しているユーザがいないこと」です。 そのため、静的権限だけで運用しようと自動的に追加された動的権限を剥奪したとしても、またバージョンアップをすると、復活してしまいます *1

追加される動的権限の対応表

mysql_system_tables_fix.sql に動的権限の付与処理が書かれています。動的権限が追加される権限を整理してみました (8.0のみ、8.4 は確認してません)。

SUPER だけでなく、CREATE USER 権限 や SELECT 権限 に対して付与される動的権限もあります。また、SYSTEM_VARIABLES_ADMINAUTHENTICATION_POLICY_ADMIN のような、動的権限 → 動的権限 の対応も存在しているようです。

権限を管理するツールを自前で開発していると、この自動追加処理の内容を加味して実装する必要があり面倒でした。 個人的には、この処理は、MySQLのバージョンアップ時に強制せず、任意のタイミングで管理者が別途、実行できるほうが良かったように思います。

※赤字はDynamic Privilege

対象となる権限 追加される Dynamic Privilege
SUPER AUDIT_ADMIN
BACKUP_ADMIN
BINLOG_ADMIN
BINLOG_ENCRYPTION_ADMIN
CLONE_ADMIN
CONNECTION_ADMIN
ENCRYPTION_KEY_ADMIN
GROUP_REPLICATION_ADMIN
INNODB_REDO_LOG_ENABLE
PERSIST_RO_VARIABLES_ADMIN
REPLICATION_APPLIER
REPLICATION_SLAVE_ADMIN
RESOURCE_GROUP_ADMIN
RESOURCE_GROUP_USER
ROLE_ADMIN
SERVICE_CONNECTION_ADMIN
SESSION_VARIABLES_ADMIN
SET_USER_ID
SYSTEM_USER
SYSTEM_VARIABLES_ADMIN
TABLE_ENCRYPTION_ADMIN
TELEMETRY_LOG_ADMIN
XA_RECOVER_ADMIN
RELOAD INNODB_REDO_LOG_ARCHIVE
FLUSH_OPTIMIZER_COSTS
FLUSH_STATUS
FLUSH_USER_RESOURCES
FLUSH_TABLES
CREATE USER APPLICATION_PASSWORD_ADMIN
PASSWORDLESS_USER_ADMIN
SET_USER_ID SYSTEM_USER
SELECT SHOW_ROUTINE
SYSTEM_VARIABLES_ADMIN AUTHENTICATION_POLICY_ADMIN
SYSTEM_USER AUDIT_ABORT_EXEMPT
FIREWALL_EXEMPT
SYSTEM_VARIABLES_ADMIN SENSITIVE_VARIABLES_OBSERVER

まとめ

  • バージョンアップ時に動的権限が自動的に追加されるケースがある
  • バージョンアップを経たかどうかで、権限が変わる。環境ごとに権限が意図せず異っている場合、この処理が原因かも。

*1:一部の権限は mysql.session などのシステムユーザにも付与されるため、復活しないものもあると思います

MySQL 各バージョンの caching_sha2_password 対応状況まとめ

自分用のメモです。

MySQL 5.7

  • デフォルトは mysql_native_password
  • caching_sha2_password は利用できない
  • ただし、クライアントは caching_sha2_password をサポート

MySQL 8.0

  • デフォルト は caching_sha2_password
    • default_authentication_plugin パラメータを変更し、デフォルトを mysql_native_password にすることも可能
  • mysql_native_password も 利用可能

MySQL 8.4

  • デフォルト は caching_sha2_password
  • default_authentication_plugin パラメータは削除された
  • mysql_native_password を利用するには、mysql_native_password=on 設定が必要

MySQL 9.0 (Innovation Release)

  • mysql_native_password は削除
  • クライアントは引き続き、mysql_native_password をサポート
$ rpm -qli mysql-community-client-plugins-9.0.1-1.el9.x86_64 | grep native
/usr/lib64/mysql/plugin/mysql_native_password.so

クラウドの設定状況

2024.09.21 時点の状況です。

8.0 世代 の default_authentication_plugin の値

クラウドでは default_authentication_pluginmysql_native_password に調整されている。

default_authentication_plugin の値
MySQL Community caching_sha2_password
AWS RDS mysql_native_password
AWS Aurora mysql_native_password
Google Cloud SQL mysql_native_password

8.4 世代

AWS RDS MySQL はデフォルトで、mysql_native_password=on してくれている。

Cloud SQLmysql_native_password は有効化されているものの、新規のパスワードには利用できない

mysql> CREATE USER user84 IDENTIFIED WITH mysql_native_password BY 'MySQL8.4';
ERROR 4052 (HY000): Invalid plugin "mysql_native_password" specified as 1 factor during "CREATE USER".

8.4 にアップグレードする前に作られたユーザのみ継続利用できる。

mysql_native_password
MySQL Community -
AWS RDS *1 ON
AWS Aurora -
Google Cloud SQL

*1:RDS Preview 版で確認

MySQL ruby ドライバの caching_sha2_password 平文接続の挙動がバラバラだった件

caching_sha2_password は 少なくとも1回はセキュアな経路を必要とする

caching_sha2_password 形式のパスワードを使った、平文の接続 をする場合、その過程で、少なくとも一度は事前に、暗号化されたセキュアな経路を通して認証成功させる必要があります。 サーバがキャッシュ(パスワードのハッシュ)を生成するのに、平文のパスワードを要求するためです。

この「少なくとも一度は〜」の背景は、MySQL 運用・管理 実践入門で詳しく解説されていました。気になる人は読むと良いでしょう

「少なくとも一度は〜」と書きましたが、厳密に言うと「1度」だけ済まない可能性があります。 キャッシュはメモリ上にしか保持されないため、サーバの再起動で消えます。また、FLUSH PRIVILEGE コマンドでもクリアされます。

キャッシュが未生成だとエラーになるドライバがある

キャッシュ未生成時、サーバがセキュアな経路を要求してきたときの挙動はドライバ依存です。 go-sql-driver/mysqlphp_mysqlnd では、セキュアの経路を自動的にフォールバックし、何事もなく接続できます。コードの変更も不要です。

rubyのドライバの対応状況を確認してみると、挙動の違いがあって、面白かったです。

ドライバ名 caching_sha2 平文接続のサポート caching_sha2 キャッシュ未作成時
ruby-mysql ⚪︎ ⚪︎ エラーなし (get_server_public_key=trueが必要)
mysql2 ⚪︎ × エラー
trilogy × サポートされていない -

なお、この仕様を意識する必要があるのは平文接続の場合のみです。常にセキュアな通信をする SSL/TLS 接続やUnix Domain Socket では意識する必要ありません。

ruby-mysql

オプションに get_server_public_key=true を付けておけば、キャッシュ未作成時もエラーなく接続可能でした。mysql コマンドにあるオプションと同じですね。 サーバの公開鍵を利用して、自動的に「初回のセキュアな経路」を作成します。

my = Mysql.connect(URI::Parser.new.escape("mysql://#{username}:#{password}@#{hostname}:#{port}/?ssl_mode=disabled&get_server_public_key=true"))

mysql2

サーバサイドにキャッシュが未作成だとエラーになります。回避するためのオプション等は提供されていなそうでした。 ワークアラウンドとして、このエラーをトラップして、何らか別手段 (rubyで書く必要もない) で一度認証を成功させてキャッシュを作ることになりそうです。

Mysql2::Error: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

一旦、キャッシュが作成されしまえば、以降は問題なく接続可能です。

trilogy

trilogycaching_sha2_password 形式の認証は SSLUnix Domain Socket のみサポートです。平文は利用できません。 サーバにキャッシュが作成済みであろうと、なかろうと、caching_sha2_password と 平文接続を組み合わせた時点で、エラーになります。

Trilogy::BaseConnectionError: trilogy_auth_recv: caching_sha2_password requires either TCP with TLS or a unix socket: TRILOGY_UNSUPPORTED

PR にも明確に書かれていました。

chosen on purpose to only implement the path where TLS or a unix socket is used. We will not be implementing the non-TLS/non-unix socket path. https://github.com/trilogy-libraries/trilogy/pull/165

trilogyで平文接続を利用している場合は、caching_sha2_password 導入とあわせて、SSL/TLS接続への変更が必要になります。

ruby-mysqlmysql2 では、キャッシュが生成済みであれば、エラーは出ません。 これらのドライバでは「初回のみセキュアな経路を必要とする」仕様を見逃しやすく、サーバ再起動などでキャッシュがなくなったときに、ある日突然、エラーに直面するリスクがあるように感じます。

trilogy はサーバサイドのキャッシュの有無に依存せず、常にエラーになるため、事前に問題に気づきやすく、一番、安全側に倒した選択とも言えそうです。

試したバージョン

$ gem list | egrep 'mysql|trilogy'
mysql2 (0.5.6)
ruby-mysql (4.1.0)
trilogy (2.8.1)

$ dpkg -l | grep mysql
ii  libmysqlclient-dev     8.0.39-0ubuntu0.24.04.1  amd64 MySQL database development files
ii  libmysqlclient21:amd64 8.0.39-0ubuntu0.24.04.1  amd64 MySQL database client library
ii  mysql-client-core-8.0  8.0.39-0ubuntu0.24.04.1  amd64 MySQL database core client binaries
ii  mysql-common           5.8+1.1.0build1          all   MySQL database common files, e.g. /etc/mysql/my.cnf

検証用のコード

require 'uri'

require 'mysql'
require 'mysql2'
require 'trilogy'

port     = 3307
hostname = '192.168.103.123'
username = 'app'
password = 'Password'
q        = 'show status like "Ssl_cipher"'

# mysql2
begin
    my2 = Mysql2::Client.new(:host => hostname, :port => port, :username => username, :password => password, :ssl_mode => :disabled)

    my2.query(q).each do |row|
      p row
    end
    # clear sha256 cache if successfuly loggin
    my2.query("FLUSH PRIVILEGES")
rescue => e
  p e
end

# ruby-mysql
begin
    my = Mysql.connect(URI::Parser.new.escape("mysql://#{username}:#{password}@#{hostname}:#{port}/?ssl_mode=disabled&get_server_public_key=true"))
    my.query(q).each do |row|
      p row
    end
    # clear sha256 cache if successfuly loggin
    my.query("FLUSH PRIVILEGES")
rescue => e
  p e
end


# trilogy
begin
    tri = Trilogy.new(host: hostname, port: port, username: username, password: password)
    tri.query(q).each_hash do |row|
     p row
    end
    # clear sha256 cache if successfuly loggin
    tri.query("FLUSH PRIVILEGES")
rescue => e
  p e
end
$ ruby test.rb
#<Mysql2::Error: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.>
["Ssl_cipher", ""]
#<Trilogy::BaseConnectionError: trilogy_auth_recv: caching_sha2_password requires either TCP with TLS or a unix socket: TRILOGY_UNSUPPORTED>