TL;DR
- epelのsysbenchがcaching_sha2_passwordに対応してないので、root@localhostのパスワードを空のまま認証プラグインだけmysql_native_passwordに変更しようとした
- プライマリーノード以外ではパスワードがEXPIREされて再変更を促された
- プライマリーノードで
SET PASSWORD = ''を実行したらセカンダリーノードでもEXPIRE状態じゃなくなった
パスワードが空っぽの時だけ再現するので、現用環境で問題になる可能性は低い。
再現手順。
mysql> CREATE USER yoku0825 IDENTIFIED BY '';
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT user, host, plugin, password_expired FROM mysql.user WHERE user = 'yoku0825';
+----------+------+-----------------------+------------------+
| user | host | plugin | password_expired |
+----------+------+-----------------------+------------------+
| yoku0825 | % | caching_sha2_password | N |
+----------+------+-----------------------+------------------+
1 row in set (0.00 sec)
mysql> ALTER USER yoku0825 IDENTIFIED WITH mysql_native_password BY ''; -- IDENTIFIED WITH .. BY .. で認証プラグインと一緒に新パスワードを指定しているので password_expired は Nになるはず
Query OK, 0 rows affected (0.01 sec)
primary> SELECT user, host, plugin, password_expired FROM mysql.user WHERE user = 'yoku0825'; -- PRIMARYノードでは確かにそうなっているが
+----------+------+-----------------------+------------------+
| user | host | plugin | password_expired |
+----------+------+-----------------------+------------------+
| yoku0825 | % | mysql_native_password | N |
+----------+------+-----------------------+------------------+
1 row in set (0.00 sec)
secondary> SELECT user, host, plugin, password_expired FROM mysql.user WHERE user = 'yoku0825'; -- SECONDARYノードでは認証プラグインは更新されているもののpassword_exipred が Y、認証プラグインが変更された後にパスワードが更新されていないと認識されている
+----------+------+-----------------------+------------------+
| user | host | plugin | password_expired |
+----------+------+-----------------------+------------------+
| yoku0825 | % | mysql_native_password | Y |
+----------+------+-----------------------+------------------+
1 row in set (0.00 sec)
group_replication_applierのリレーログを確認すると
# at 13356
#200226 2:48:29 server id 2412749166 end_log_pos 0 GTID last_committed=61 sequence_number=62 rbr_only=no original_committed_timestamp=1582687438763140 immediate_commit_timestamp=0 transaction_length=229
# original_commit_timestamp=1582687438763140 (2020-02-26 03:23:58.763140 UTC)
# immediate_commit_timestamp=0 (1970-01-01 00:00:00.000000 UTC)
/*!80001 SET @@session.original_commit_timestamp=1582687438763140*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= '662c9473-5842-11ea-8c75-12458a6f001d:66'/*!*/;
# at 13436
#200226 3:23:58 server id 2412749166 end_log_pos 0 Query thread_id=65 exec_time=0 error_code=0 Xid = 541
SET TIMESTAMP=1582687438/*!*/;
ALTER USER 'yoku0825'@'%' IDENTIFIED WITH 'mysql_native_password'
/*!*/;
空文字の部分が握りつぶされている。
group_replication_applierはバイナリログを受け取っているわけではない(んですよ実は)ので見ても仕方ないような気がするけれど、プライマリーノードのバイナリログを覗いても同じように握りつぶされているので、リレーログとして空文字パスワードが省略されるのは仕様としては正しそう。
group_replication_applierはバイナリログを受け取っているわけではない(んですよ実は)ので見ても仕方ないような気がするけれど、プライマリーノードのバイナリログを覗いても同じように握りつぶされているので、リレーログとして空文字パスワードが省略されるのは仕様としては正しそう。
# at 37750
#200226 3:23:58 server id 2412749166 end_log_pos 37830 GTID last_committed=65 sequence_number=66 rbr_only=no original_committed_timestamp=1582687438763140 immediate_commit_timestamp=1582687438765388 transaction_length=229
# original_commit_timestamp=1582687438763140 (2020-02-26 03:23:58.763140 UTC)
# immediate_commit_timestamp=1582687438765388 (2020-02-26 03:23:58.765388 UTC)
/*!80001 SET @@session.original_commit_timestamp=1582687438763140*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= '662c9473-5842-11ea-8c75-12458a6f001d:66'/*!*/;
# at 37830
#200226 3:23:58 server id 2412749166 end_log_pos 37979 Query thread_id=65 exec_time=0 error_code=0 Xid = 541
SET TIMESTAMP=1582687438/*!*/;
ALTER USER 'yoku0825'@'%' IDENTIFIED WITH 'mysql_native_password'
/*!*/;
しかしそうなると、SQLを実行したノードではSQLをパースしている段階で
IDENTIFIED BY '' が同時指定されているからパスワードを更新したことになり、それ以外(セカンダリーノードだったり、フツーのレプリケーションのスレーブだったり)は認証プラグインの更新後にパスワードの変更が行われてないように見えるからEXPIREする、と。
「認証プラグインを変更しつつパスワードを空文字にする」なんてことがない限りは着火しないので、検証環境以外でお目にかかることはないはず。たぶん。
( ´-｀).oO（アカウントのもとのパスワードがNotからっぽだとマスターとスレーブの
authentication_string がズレるから、 password_require_current が有効だとレプリケーションが死にそうな予感…
