既知の通り、MySQL5.7以前と8.0以上でユーザ認証の方法が変更になっています。
自宅端末でMySQLをCommunity serverからHomebrewに変更した際に色々ハマったので備忘。
□ 作業環境
- macOS 12.7.1
- Homebrew 4.1.19
- MySQL 8.1.0(Homebrew)
1. MySQLの認証プラグイン
MySQL 8.0以上
「caching_sha2_password」というプラグインが用いられています。
より厳格で強力なパスワード設定が必要。
近年流行り（？）のポリシーで大文字小文字、特殊記号を最低1文字ずつ使う必要あり。
面倒だし、パスワード忘れやすい...
MySQL 5.7以前
「mysql_native_passwowrd」
これまで以前のポリシー。
ローカルのテスト環境であればこれで良いのでは？と。
mysql_native_passwordは非推奨になっているので、本来であればcaching_sha2_passwordを使う方がいいのでしょうが、caching_sha2_passwordが下位互換なさそうなので様々な環境が入り混じっている場合はmysql_native_passwordで設定した方が無難な気がします。
推奨環境対応はまたいずれ。
2. rootユーザのパスワード設定
HomebrewでMySQLをインストールした場合
We've installed your MySQL database without a root password. To secure it run:
mysql_secure_installation
MySQL is configured to only allow connections from localhost by default
To connect run:
mysql -uroot
To have launchd start mysql now and restart at login:
brew services start mysql
Or, if you don't want/need a background service you can just run:
mysql.server start
というような結果が表示されます。
・インストールしたときはrootにパスワードがないからmysql_secure_installationで設定してね
・mysql -urootで接続できるよ
・ログイン時にmysqlを起動するにはbrew services start mysql
・バックグラウンドで使う必要がないときはmysql.server start
と書かれています。
記載の通り、mysql_secure_installationに接続すると対話式で入力が始まり、順に進めていけばrootのパスワードが設定できます。
設定したらログインしてます。
% mysql -u root -p
ログインパスワードが聞かれるので先ほど設定したパスワードでログインできます。
今になって思えばここでパスワード設定せずにmysqlにrootでログインすれば4.の冒頭で起きる問題は発生せずにプラグイン変更できたのかも、と思います。
3. 認証プラグインの確認
ログインしたら認証プラグインを確認してみましょう。
mysql> select User, Plugin from mysql.user;
+------------------+-----------------------+
| User | Plugin |
+------------------+-----------------------+
| mysql.infoschema | caching_sha2_password |
| mysql.session | caching_sha2_password |
| mysql.sys | caching_sha2_password |
| root | caching_sha2_password |
+------------------+-----------------------+
8.0以上のバージョンであれば初期状態はこのようになっているはずです。
ちなみに、
mysql> use mysql;
mysql> show variables like 'default_authentication_plugin';
で現在の設定も確認できます。
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
4. 認証プラグインの変更
4.1 変更しようとしたらエラー
認証プラグインを変更していきます。
調べている中で
mysql> ALTER USER <User>@<Host> IDENTIFIED WITH mysql_native_password BY '<password>';
で変更しましょう、というものを多々見ました。
パスワード変更と同時に認証プラグインも変更しましょうということなのだと思います。
結果的にはこのコマンドを使うのですが、現状のmysqlにログインした状態では変更できませんでした。
WITH mysql_native_passwordの箇所がうまく機能していないのか、
Your password does not satisfy the current policy requirements
というエラーが出てしまいます。
おそらく既に「caching_sha2_password」でパスワードが設定されてしまっているから起きるエラーなのではないかと。
なので一旦設定したパスワードを削除しますが、もちろんこのままの状態だと「rootのパスワードなし」はポリシー違反なので削除はできないのでセーフモードで設定していきます。
4.2.セーフモードでrootのパスワード削除
一度exitでmysqlからログアウトします。
ログアウト後、mysql.server stopでmysqlも停止させます。
その後、セーフモードでmysqlを起動させます。
% mysqld_safe --skip-grant-tables &
[2] 9912
2023-11-10T05:37:00.6NZ mysqld_safe Logging to '/usr/local/var/mysql/iMac5K.local.err'.
2023-11-10T05:37:00.6NZ mysqld_safe A mysqld process already exists
[2] - exit 1 mysqld_safe --skip-grant-tables
セーフモードでmysqlを起動させると、rootにパスワードなしでログインできるようになります。
rootのパスワード忘れなどにも有効な方法です。
ログインしたら早速パスワードを削除します。
削除というよりはnullで上書きするイメージです。
mysql> UPDATE mysql.user SET authentication_string=null WHERE User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
問題なければ上記のように表示されて完了です。
セーフモードを抜けて、改めてmysqlを起動、rootのパスワードなしでログインしてみましょう。
ログインできればパスワード削除成功です。
4.3 改めて認証プラグイン変更
rootのパスワードを削除したら4.1でエラーがでたコマンドをもう一度実行してみます。
mysql> ALTER USER 'root'@'localhost' identified WITH mysql_native_password BY '****';
Query OK, 0 rows affected (0.00 sec)
今度はエラーが出ずにQuery OKになりました。
一度ログアウトして、設定したパスワードでログインしてみます。
% mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.1.0 Homebrew
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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>
ログインできました。
続いて認証プラグインの確認です。
mysql> select User, Plugin from mysql.user;
+------------------+-----------------------+
| User | Plugin |
+------------------+-----------------------+
| mysql.infoschema | caching_sha2_password |
| mysql.session | caching_sha2_password |
| mysql.sys | caching_sha2_password |
| root | mysql_native_password |
+------------------+-----------------------+
こちらも変更されています。
5. デフォルト設定の変更
rootの認証プラグインの変更が完了しました。
ただ、3.で見た通り、デフォルトの認証プラグインはcaching_sha2_passwordになっており、
新しいユーザを作った際、プラグインがcaching_sha2_passwordになってしまう可能性があります。
なのでデフォルト設定を変更します。
設定を変更するにはmy.cnfに設定を記述する必要があります。
my.cnfの場所は下記で調べることができます。
% mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
これはすべての場所にmy.cnfがあるということではなく、上記の場所にmy.cnfがあれば左から順番に読み込んでいるということを表しています。
ちなみに私の環境では/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf にはファイルは存在しませんでした。なので/usr/local/etc/my.cnfが読み込まれています。
my.cnfを開き、
default_authentication_plugin=mysql_native_password
を追記します。
追記したら保存して、mysqlを再起動しましょう。
% mysql.server restart
Shutting down MySQL
. SUCCESS!
Starting MySQL
. SUCCESS!
Starting後にSUCCESS！と表示されればmy.cnfの記述に問題もなくmysqlが起動できています。
ログインして設定を確認しましょう。
mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)
変更されているのが確認できました。