部署変更や転職、退職といったタイミングで権限の付与や削除が行われた際や、特に権限が削除となった場合に、今まで利用していたパスワードを変更することになると思います。しかし、特にスレーブがいるような少し複雑な構成のMySQLで、ダウンタイム無しにパスワードを変更するのはかなり大変だと思います。

今回はMySQL 8.0.14から追加されたデュアルパスワードの機能を、8.0.15を使って紹介していきます。

デュアルパスワードとは？

こちらはMySQL 8.0.14から追加された機能で、名前の通り、2つのパスワードが設定できるようになります。それぞれ、プライマリーとセカンダリーと呼びます。2つのパスワードが設定されている時には、プライマリーでもセカンダリーでもどちらでもログインが可能になります。なので、パスワードの変更を行う際には以下の順でパスワードを変更していくことになります。

• プライマリーパスワードを登録する（現在のパスワードはセカンダリーパスワードに変更される）
• 登録したプライマリーパスワードがスレーブに伝搬するのを待つ
• アプリケーション側で使用しているパスワードをプライマリーパスワードに変更する
• 問題がなければセカンダリーパスワードを破棄する
• スレーブに伝搬したら完了

プライマリーパスワードを新しく登録した時に、現在のパスワードは自動的にセカンダリーパスワードになります。

以下では、プライマリーパスワードを登録する方法と、破棄する方法を紹介していきます。

プライマリーパスワードを登録する

プライマリーパスワードを設定するには、ALTER USER文を利用していきます。構文としては以下のように設定することができます。

ALTER USER 'appuser1'@'host1.example.com'
IDENTIFIED BY 'password_b'
RETAIN CURRENT PASSWORD;

今までのALTER USER文との違いとしては、RETAIN CURRENT PASSWORD句が追加されていることがわかります。このALTER USER文が実行されると、現在のプライマリーパスワードはセカンダリーパスワードに変更されて、新しく登録したパスワードがプライマリーパスワードとして登録されます。

実際にプライマリーパスワードを登録して、セカンダリーパスワードとプライマリーパスワード両方でログインができることを確認してみましょう。

まずはユーザの作成を行います。CREATE USER権限があるユーザで実行しましょう。

$mysql -uroot -p
〈省略〉
mysql> CREATE USER 'appuser1'@'localhost' IDENTIFIED BY 'Password_1';
Query OK, 0 rows affected (0.01 sec)

作成したユーザでログインができるか確認してみましょう。

$ mysql -uappuser1 -pPassword_1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.15 MySQL Community Server - GPL
〈省略〉
mysql>

ということで、ログインすることができました。この作成したユーザに対して、RETAIN CURRENT PASSWORDを使ってPassword_2を登録してみましょう。

$mysql -uroot -p
〈省略〉
mysql> ALTER USER 'appuser1'@'localhost' IDENTIFIED BY 'Password_2' RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.01 sec)

ここで、新しく設定したパスワードと最初に設定したパスワード両方でログインできるか確認してみましょう。

$ mysql -uappuser1 -pPassword_1
〈省略〉
mysql> exit
Bye
$ mysql -uappuser1 -pPassword_2
〈省略〉
mysql>

上記から、同じユーザに2つのパスワードでログインができるようになったことがわかります。現在は最初に登録したPassword_1がセカンダリーパスワードになっています。

ちなみに、RETAIN CURRENT PASSWORD句を追加せずに通常のALTER USER文を実行した場合には、プライマリーパスワードがそのまま上書きされて、セカンダリーパスワードが登録されていたとしても変更されないという挙動になります。

$mysql -uroot -p
〈省略〉
mysql> ALTER USER 'appuser1'@'localhost' IDENTIFIED BY 'Password_3';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

$ mysql -uappuser1 -pPassword_1
〈省略〉
mysql> exit
Bye

$ mysql -uappuser1 -pPassword_2
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'appuser1'@'localhost' (using password: YES)

$ mysql -uappuser1 -pPassword_3
〈省略〉
mysql>

上記から、RETAIN CURRENT PASSWORD句を使わずにパスワードを変更すると、現在のプライマリーパスワードであったPassword_2が上書きされた様子がわかります。

セカンダリーパスワードを破棄する

デュアルパスワードを設定して、アプリケーション側のパスワードの変更も終わったら、後はセカンダリーパスワードを破棄すればパスワード変更も完了です。破棄にもALTER USER文を利用していきます。

構文は以下のとおりです。

ALTER USER 'appuser1'@'host1.example.com'
  DISCARD OLD PASSWORD;

こちら実際に試してみましょう。現在はPassword_3がプライマリーパスワードで、Password_1がセカンダリーパスワードとして登録されている状態です。

$mysql -uroot -p
〈省略〉
mysql> ALTER USER 'appuser1'@'localhost' DISCARD OLD PASSWORD;
Query OK, 0 rows affected (0.00 sec)

上記のコマンドが実行終わった状態で、どのパスワードでログインができるか確認してみましょう。

$ mysql -uappuser1 -pPassword_1
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'appuser1'@'localhost' (using password: YES)

$ mysql -uappuser1 -pPassword_3
〈省略〉
mysql>

上記から、Password_1を利用してログインできなくなっていることがわかると思います。

必要な権限に関して

この機能を利用するにあたって必要な権限としては、CREATE USER権限を持っている、もしくはAPPLICATION_PASSWORD_ADMIN権限を持っている必要があります。APPLICATION_PASSWORD_ADMINの権限を持つと、自分自身に対してのみ新規のパスワードの設定、パスワードの入れ替え、古いパスワードの削除といった処理が行えるようになります。

注意点

この機能を利用するにあたっては、mysql.userの構造が2つのパスワードが持てるように変更されるため、このバージョン以前のMySQLからアップデートを行った場合にmysql_upgradeの実行とサーバの再起動が必要となるので注意をしましょう。

まとめ

今回はMySQLのデュアルパスワード機能について紹介しました。この機能を使うと、ダウンタイムを必要とせずにパスワードを変更していけるので非常に便利です。Master1台構成であったとしても、アプリケーション側で変更後のパスワードを使用して、事前に接続の確認ができるので便利になると思います。

難点としましては、バージョンを8.0.14まであげないと使えないところと、バージョンをあげる際にメンテナンスが必要になりそうなところだと思います。しかし、2020年の10月にコミュニティ版の5.7系がサポートが終了します。それに向けて移行していく中で8.0のどのバージョンまであげるかという検討をする際には管理コストや今後のことを考えて、ぜひ8.0.15までアップデートしてみてはいかがでしょうか。