41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

MySQL8.0を再起動するとアプリからつながらなくなる理由

この記事は MySQL Advent Calendar 2021 の18日目の記事です。

それ、多分 caching_sha2_password が原因です

この話、実は既にyoku0825さんが語っている話なので、別に目新しさは無いですw

でも、アプリケーションからというように、少しDBへの接続の仕方が変わるとこの話に気づきにくくなってる気がするのであえて書いてみました。

caching_sha2_passwordって何?(おさらい)

caching_sha2_password は MySQL8.0のデフォルトの認証プラグインです。

MySQL5.7まではmysql_native_password がデフォルトの認証プラグインでした。

認証プラグインって?

MySQLサーバは認証方式をプラグイン化しています。

そして、アカウント毎にその認証方式を選べるようにしています。

例えば、

  • ローカルからしか接続しないので、パスワードのみでの認証方式にしたり、
  • 外部からDBへ接続するので暗号化されたパスワードハッシュを使って認証したり、
  • OSユーザーと連携したいので、Windows認証やLDAP認証といった方式も使えます。

caching_sha2_passwordはそれら複数ある認証方式の一つで、SHA-2プラガブル認証をより使いやすくしたものです。

どうやって変更するの?

デフォルトの認証方式はdefault_authentication_pluginを変更すれば

mysql> CREATE USER xxx@localhost;

というように特にIDENTIFIED WITH句を指定しない場合に、そのデフォルトの認証方式が使われます。

逆にIDENTIFIED WITH句を使えば、ユーザーごとに異なる認証方式を設定できます。

ところで、caching_sha2_passwordの何がいいの?

雑ーなマトリクスを作りました。

mysql_native_password sha256_password caching_sha2_password
セキュリティ ×
パフォーマンス ×

caching_sha2_passwordはその名の通り、パスワードのハッシュ化にSHA256を使用していますが、それをサーバーのメモリ上にキャッシュしています。

詳しい話はドキュメント「SHA-2 プラガブル認証のキャッシュ」に書かれています。

さて、本題の原因についての説明です。

くどいようですが、caching_sha2_passwordはMySQL8.0からのデフォルトの認証方式になりました。

そのため、認証方式というものをそもそも意識していないと、「ユーザーが急に入れなくなった!」と感じたりします。

そんなときは以下のようなことを確認してみましょう。

原因と思われる作業

  • MySQLサーバを再起動してませんか?
  • FLUSH PRIVILEGESを実行しませんでしたか?
  • ALTER USERしたユーザーで接続しましたか?
  • CREATE USERしたばかりのユーザーで接続しましたか?
  • MySQLサーバーへ接続する際、非SSLTCP接続じゃないですか?

対応方法

主な方法は以下の通りです。

  1. 接続ユーザーをmysql_native_password認証に変更する
  2. mysqlクライアント等のSSL接続ができるものから一度接続し、認証情報のキャッシュをサーバー上に作った後、非SSL方式で接続する
  3. サーバーの公開鍵を使い、認証を行う
  4. クライアント側、アプリケーションの接続設定部分でSSL接続を有効にする

1の接続ユーザーをmysql_native_password認証に変更するのはセキュリティ的にはあんまりお勧めできません。

2のようにキャッシュが乗った状態なら非SSL方式でもパスワード認証でOKなので、再起動後等に一回つなげばいいんですが、めんどくさいですよね。

というわけで、3か4で対応することをお勧めします。

どうでもいい豆知識ですが、

default_authentication_pluginパラメータでcaching_sha2_passwordを設定できるのはMySQL8.0からです。

設定ファイルに

default_authentication_plugin=caching_sha2_password

と書くと、MySQL5.7以前だとエラーとなり、起動しません。

[ERROR] Can't start server: Invalid value for --default-authentication-plugin

参考情報

MySQL :: MySQL 5.7 Reference Manual :: 6.4.1.4 Caching SHA-2 Pluggable Authentication

MySQL :: MySQL 8.0 Reference Manual :: 6.4.1.2 Caching SHA-2 Pluggable Authentication

MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.4.1.2 SHA-2 プラガブル認証のキャッシュ

明日は

明日は@shallow1729 さんです。お楽しみに!