いつも Docker での動作検証しかしていなかったので，
Docker以外の環境とかMySQLの初期化処理とか知らなかった．
事の始まり
AWS EC2 / Amazon Linux2 に MySQL をインストールして利用したい．
このとき，開発・検証環境だから弱いパスワードを許可したかった(もちろん本当はよくないけれど)．
ところが，弱いパスワードを設定しようとするとエラーになる．
軽く検索すると設定ファイルにパスワードポリシーの変更を追記するといいよという記事がたくさんヒットする．
ただし，設定項目が記事によって異なる，タイポ？バージョンで違う？
しかも設定を反映するとMySQLがエラーで起動しなくなる．
とりあえず非公式情報は参考にならなそう．
ということで，なぜパスワードが変更できないのか，なぜ設定変更するとMySQLが起動しなくなるのか，
その設定を変更する正しい手順はどうすればいいのか
について調査した．
したいこと
- Amazon Linux2 に MySQL 5.7.30 をインストールする
- validate_passwordプラグインの制限を緩和する
- この設定のセットアップ手順を確立する
なぜ上手くいかなかったのか
validate_passwordプラグインによるパスワードポリシー設定
弱いパスワードが設定できなかったのはデフォルトで validate_passwordプラグインが有効になっているため．
このプラグインにより，弱いパスワードを設定することができなくなっている．
dev.mysql.com
このプラグインがデフォルトで有効になるのはyumリポジトリからMySQLをインストールした場合などで，
今回のAmazon Linux2ではYumリポジトリからインストールしていたのでこのプラグインが有効化されていた．
dev.mysql.com
Note
If you installed MySQL 5.7 using the MySQL Yum repository, MySQL SLES Repository, or RPM packages provided by Oracle, validate_password is enabled by default after you start your MySQL Server for the first time.
実際に Dockerの公式イメージ(mysql:5.7.30)で確認するとプラグインは有効になっておらず，
validate_passwod プラグインのみ差異があることがわかる．
公式DockerイメージはDebianベースなので確かに公式ドキュメントの通りvalidate_passwordプラグインは有効にならない．
Yumリポジトリからインストールした場合
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.00 sec)
Dockerで起動した場合
mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
+----------------------------+----------+--------------------+---------+---------+
44 rows in set (0.01 sec)
対策としては，セキュアなパスワードを利用するか，ポリシーを変更するか，プラグインをアンインストールするか．
パスワードの制限を緩和するためには，ポリシーとして validate_password_policy = OFF を設定すればよい．
これにより利用できるパスワードの制限を変更できる．
もう少しパスワードポリシーをカスタマイズしたい場合は，
以下のプラグインオプションが参考になる．
dev.mysql.com
一方で初回起動前にこの設定を my.cnf に記載すると，validate_password プラグインがインストールされる前に反映しようとして，
無効な設定としてエラーになってしまう．
2020-05-04T10:31:49.970600Z 0 [ERROR] unknown variable 'validate_password_policy=LOW'
これは公式ドキュメントにも記載されている.
This option is available only if the validate_password plugin has been previously registered with INSTALL PLUGIN or is loaded with --plugin-load-add. See Section 6.4.3.1, “Password Validation Plugin Installation”.
ではこのプラグイン自体を最初から無効化すればいいかというとそれは難しい．
プラグインの設定反映はMySQLをインストールしたときの systemdの設定ファイルによるもので，
/usr/lib/systemd/system/mysqld.service には
# Needed to create system tables
ExecStartPre=/usr/bin/mysqld_pre_systemd
の記載があり， この /usr/bin/mysqld_pre_systemd で validate_passwordプラグインのインストールを含む
証明書の作成やシステムテーブルの作成などの処理(mysqld --initialize)を実行している．
validate_passwordプラグインを有効にしないオプションなどは存在せず，
このpreスクリプトを実行しないと自分で初期化処理が必要になるので
このスクリプトを実行しない選択肢は基本的にない．
このスクリプトを自前でカスタマイズするのもリスクが高いので，
Yumリポジトリからインストールした場合は基本的にvalidate_passwordプラグインは有効になっている想定がよさそう．
初回起動直後にプラグインをアンインストールしようとしても，
初期パスワードの変更前にパスワード変更以外の操作は受け付けないので，
初回起動→複雑なパスワード設定→プラグインアンインストール→簡易なパスワードの設定
といった無意味な手順が生じてしまう．
以上から，一度MySQLを起動してプラグインを反映した上でパスワードポリシーを変更して再起動，
という手順がよさそう．
validate_passwordプラグインを回避するセットアップ手順
Amazon Linux2ではMariaDBが有効になっている．
MySQL5.7を利用したい場合はMySQLのリポジトリを追加し，
MySQL8を無効化して 明示的に5.7を有効化・インストールする必要がある．
dev.classmethod.jp
このタイミングでパスワードポリシーも変更したいが，それは実行しない．
自動起動の設定は反映されていないので sudo systemctl enable mysqld で自動起動を有効にだけしておく．
MySQLの初回起動後に /etc/my.cnf にパスワードポリシーの変更設定を記載する．
設定ファイルの末尾に validate_password_policy = OFF を追記だけしてあげればよい．
その後MySQLを再起動すればパスワードポリシーが変更される．
あとは初期パスワードを弱いパスワードでも自由に設定できる．
初期パスワードはログ(/var/log/mysql.log)に出力されているので，
これを利用してパスワードを変更する．
パスワード変更時にpassword関数を利用する方法はDeprecatedなのでpassword関数は利用しない．
またSET PASSWORD FOR root@localhost = 'password' よりはALTER USERの方が推奨のようで，
ALTER USER root@localhost IDENTIFIED BY 'password' でよい．
dev.mysql.com
Note
SET PASSWORD ... = PASSWORD('auth_string') syntax is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.
SET PASSWORD ... = 'auth_string' syntax is not deprecated, but ALTER USER is the preferred statement for account alterations, including assigning passwords. For example:
参考
dev.mysql.com
hub.docker.com
hirose31.hatenablog.jp
qiita.com
