MySQL8.0で新たに追加されているレプリケーション接続オプション

はじめに

MySQL 8.0 でレプリケーション環境を構築しようと、従来の(5.7までのよくある)手順でレプリケーションユーザーを作成して、CHANGE MASTER コマンドを実行したところ、SHOW SLAVE STATUS に以下のエラーが出たこと、ありませんか？

例えば、GTIDベースレプリケーションとして、リファレンスマニュアルに載っている CHANGE MASTER コマンドを実行してみると…

MySQL :: MySQL 8.0 Reference Manual :: 17.1.3.4 Setting Up Replication Using GTIDs

mysql> CHANGE MASTER TO
    ->     MASTER_HOST = '192.168.56.34',
    ->     MASTER_PORT = 3306,
    ->     MASTER_USER = 'repl_user',
    ->     MASTER_PASSWORD = 'XXXXXX',
    ->     MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.47 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.59 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.56.34
                  Master_User: repl_user
                  Master_Port: 3306
 
                        :

             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes

                        :
    
                Last_IO_Errno: 2061
                Last_IO_Error: error connecting to master 'repl_user@192.168.56.34:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

                        :

mysql> CHANGE MASTER TO

-> MASTER_HOST = '192.168.56.34',

-> MASTER_PORT = 3306,

-> MASTER_USER = 'repl_user',

-> MASTER_PASSWORD = 'XXXXXX',

-> MASTER_AUTO_POSITION = 1;

Query OK, 0 rows affected, 2 warnings (0.47 sec)

mysql> START SLAVE;

Query OK, 0 rows affected (0.59 sec)

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Connecting to master

Master_Host: 192.168.56.34

Master_User: repl_user

Master_Port: 3306

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Last_IO_Errno: 2061

Last_IO_Error: error connecting to master 'repl_user@192.168.56.34:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

Last_IO_Error 列のメッセージからも明白ですが、IOスレッドが接続エラーとなっています。

これは弊社ブログでも過去に取り上げている MySQL 8.0 で caching_sha2_password 認証プラグインがデフォルトとなったことに影響しています。

caching_sha2_password を使用しているアカウントでの接続には、「安全な接続」または「RSAベースのパスワード交換」を使用すること、と公式リファレンスマニュアル上で随所に記載があります。

MySQL :: MySQL 8.0 Reference Manual :: 2.11.4 Changes in MySQL 8.0
MySQL :: MySQL 8.0 Reference Manual :: 13.4.2.1 CHANGE MASTER TO Statement
MySQL :: MySQL 8.0 Reference Manual :: 17.1.2.3 Creating a User for Replication
MySQL :: MySQL 8.0 Reference Manual :: 17.1.2.7 Setting the Master Configuration on the Slave

今回の例では、新規でレプリケーション用ユーザーを作成するときに認証プラグインを特に指定していないので、caching_sha2_password を使うことになり、この「安全な接続」または「RSAベースのパスワード交換」を設定しないと、前述のエラーが出て接続できない、という顛末です。

回避策としては、(大きくは)以下のいずれかが考えられます。

レプリケーション用ユーザーの認証プラグインを mysql_native_password にする。
レプリケーション用ユーザーの認証プラグインを caching_sha2_password のままとし、「安全な接続」または「RSAベースのパスワード交換」で接続する。

前者の場合、旧バージョンからアップグレードすると、認証プラグインはmysql_native_password のままユーザー情報が移行されるので顕在化しない、というケースもありますが、
8.0 を新規構築するなら、将来的な互換性も考慮して、後者のcaching_sha2_passwordを採用したいというニーズももちろん考えられますので、その場合は意図的な対応が必要です。

リファレンスマニュアルを見てみると CHANGE MASTER コマンドで以下のいずれかのオプションを使用しましょう、ということですが、割とざっくりとした説明のみです。

To connect to a caching_sha2_password account for master/slave replication:

Use any of the following CHANGE MASTER TO options:
MASTER_SSL = 1
GET_MASTER_PUBLIC_KEY = 1
MASTER_PUBLIC_KEY_PATH=’path to RSA public key file’

Alternatively, you can use the RSA public key-related options if the required keys are supplied at server startup.

さて、これを見ただけではどれを選択すればよいか困惑してしまいそうです…
非互換要素でもあるのですが、リファレンスマニュアルの説明が散在していて分かりづらい印象だったので、マニュアルを元に改めて情報整理してみます。

「安全な接続」とは

SSL/TLSによる暗号化通信での接続のことで、マスター・スレーブで SSL/TLS証明書をセットアップし、CHANGE MASTER コマンドにて MASTER_SSLを指定した接続です。

MySQL :: MySQL 8.0 Reference Manual :: 17.3.1 Setting Up Replication to Use Encrypted Connections
MySQLでのSSL/TLS利用の基本 | スマートスタイル TECH BLOG

これは、5.7までの既存のバージョンでも利用可能な接続方式です。

レプリケーション接続をセキュアな通信にする必要性(要件)がある場合は、SSL/TLSを用いて暗号化接続を行うことが強く推奨されます。

また、レプリケーション用ユーザー作成時に、REQUIRE SSLを付与してSSL/TLS認証を必須とする運用もできます。

mysql> CREATE USER repl_user IDENTIFIED BY 'XXXX' REQUIRE SSL;

1	mysql> CREATE USER repl_user IDENTIFIED BY 'XXXX' REQUIRE SSL;

「RSAベースのパスワード交換」とは

SSL/TLS接続を利用しない場合に、スレーブからマスターへ、接続認証のパスワードをRSAで暗号化して送信する方式です。

CHANGE MASTER コマンドにて、以下のMySQL 8.0 で新たに追加されたいずれかのオプションを指定して接続します。

CHANGE MASTER TO
    ...
    MASTER_PUBLIC_KEY_PATH = 'RSA公開鍵のファイル名'
      または
    GET_MASTER_PUBLIC_KEY = 1
;

CHANGE MASTER TO

...

MASTER_PUBLIC_KEY_PATH = 'RSA公開鍵のファイル名'

または

GET_MASTER_PUBLIC_KEY = 1

;

リファレンスマニュアルにも明記されていますが、MASTER_PUBLIC_KEY_PATHが指定され、有効な公開キーファイルが指定されている場合、GET_MASTER_PUBLIC_KEYよりも優先される(GET_MASTER_PUBLIC_KEY = 1を併せて設定しても無視される)という挙動です。

MASTER_PUBLIC_KEY_PATH

本パラメータを用いた場合の仕組みは以下のようになります。

スレーブ(クライアント)はここで指定した公開鍵を使用してパスワードを暗号化し、マスター(サーバー)に送信します。
マスター側でRSA秘密鍵を使用してパスワードを復号化し、パスワードが正しいかどうかに基づいて接続を許可または拒否します。

MASTER_PUBLIC_KEY_PATH に指定する公開鍵ファイルですが、正しく接続するためには以下の条件を満たしている必要があります。

PEM形式の鍵ファイルであること。
内容がマスター側のシステム変数 caching_sha2_password_public_key_path にセットされた公開鍵ファイルの内容と同一であること。
デフォルトのままの場合は、以下のような状態になっていて、データディレクトリを基準として配置された鍵ファイルが認識されます。

  mysql> SHOW GLOBAL VARIABLES LIKE 'caching%';
  +----------------------------------------------+-----------------+
  | Variable_name                                | Value           |
  +----------------------------------------------+-----------------+
  | caching_sha2_password_auto_generate_rsa_keys | ON              |
  | caching_sha2_password_private_key_path       | private_key.pem |
  | caching_sha2_password_public_key_path        | public_key.pem  |
  +----------------------------------------------+-----------------+
  3 rows in set (0.20 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'caching%';

+----------------------------------------------+-----------------+

| Variable_name | Value |

+----------------------------------------------+-----------------+

| caching_sha2_password_auto_generate_rsa_keys | ON |

| caching_sha2_password_private_key_path | private_key.pem |

| caching_sha2_password_public_key_path | public_key.pem |

+----------------------------------------------+-----------------+

3 rows in set (0.20 sec)

公開鍵ファイルの内容は cat コマンドなどや、mysqlクライアントからは以下のコマンドでも確認できます。

  mysql> SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'\G
  *************************** 1. row ***************************
  Variable_name: Caching_sha2_password_rsa_public_key
          Value: -----BEGIN PUBLIC KEY-----
  (鍵ファイルのbase64エンコード内容が表示されます)
  -----END PUBLIC KEY-----

  1 row in set (0.07 sec)

mysql> SHOW STATUS LIKE 'Caching_sha2_password_rsa_public_key'\G

*************************** 1. row ***************************

Variable_name: Caching_sha2_password_rsa_public_key

Value: -----BEGIN PUBLIC KEY-----

(鍵ファイルのbase64エンコード内容が表示されます)

-----END PUBLIC KEY-----

1 row in set (0.07 sec)

mysqld 実行ユーザー(mysql)の書き込み権限があること

ここまでを踏まえると、具体的な設定手順は以下の流れとなります。

マスター側の公開鍵ファイル(caching_sha2_password_public_key_path から確認)をスレーブ側にコピーします。
公開鍵ファイルは、mysqlユーザーがアクセスでき、書き込み可能な権限を付与します。
CHANGE MASTERコマンドで、MASTER_PUBLIC_KEY_PATH オプションで公開鍵ファイルを指定して接続します。

GET_MASTER_PUBLIC_KEY

本パラメータを用いた場合の仕組みは以下のようになります。

パラメータに 1 を設定すると、スレーブはマスターに公開鍵を要求します。
マスターはRSA公開鍵をクライアントに送信し、スレーブはそれを使用してパスワードを暗号化し、マスターに送信します。
マスター側は、RSA秘密鍵を使用してパスワードを復号化し、パスワードが正しいかどうかに基づいて接続を許可または拒否します。

MASTER_PUBLIC_KEY_PATH との違いは、

スレーブ側にRSA公開鍵を配置する必要がない
その代わり、初回の接続認証時、マスターへの公開鍵の要求が発生する

ということが挙げられます。

MASTER_PUBLIC_KEY_PATH と GET_MASTER_PUBLIC_KEY、どちらを使用すればいい？

GET_MASTER_PUBLIC_KEY は、スレーブ側で公開鍵を配置したり管理をする必要がなくお手軽です。
GET_MASTER_PUBLIC_KEY のパスワード交換方式では、万が一接続先がなりすまされた場合、スレーブはパスワードを盗み取られることが回避できないので、SSL/TLSを使うほどではないが対策はしておきたいということであれば、MASTER_PUBLIC_KEY_PATH を使用するのがよいものと考察します。

対象システムでのセキュリティ要件や公開鍵の管理を是とするかによってご判断いただければと存じます。

(補足：1) MySQLクライアントでの同パラメータ

ここまで本記事で取り上げてきた MASTER_PUBLIC_KEY_PATH と GET_MASTER_PUBLIC_KEY は CHANGE MASTER コマンドのオプションですが、
MySQL クライアントでも同義のパラメータが(そもそも)存在していて、仕組みや考え方は同じです。

(補足：2) Group Replication での同パラメータ

当然ながら Group Replication でも用意されています。

MySQL :: MySQL 8.0 Reference Manual :: 2.11.4 Changes in MySQL 8.0

To connect to a caching_sha2_password account for Group Replication:
For MySQL built using OpenSSL, set any of the following system variables:

SET GLOBAL group_replication_recovery_use_ssl = ON;
SET GLOBAL group_replication_recovery_get_public_key = 1;
SET GLOBAL group_replication_recovery_public_key_path = ‘path to RSA public key file’;

Alternatively, you can use the RSA public key-related options if the required keys are supplied at server startup.

(補足：3) caching_sha2_password なので、一度接続に成功したあとの注意点

読んで字のごとくですが、caching_sha2_password プラグインでは、一度クライアントからの接続に成功すると、認証情報をサーバーサイドのメモリーキャッシュ上に格納します。(これによりクライアント認証を高速化します)

以下のリファレンスマニュアルページでも明記されている通り、サーバーサイドでこのキャッシュエントリーをクリアする操作が実行されると、再認証(SSL/TLSなりRSA鍵ベースパスワード交換なり)が行われるようです。

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

ユーザーアカウント作成(CREATE USER)後
ユーザーアカウントのパスワード変更後
ユーザーアカウントのユーザー名変更(RENAME USER)後
FLUSH PRIVILEGES 実行後
サーバーまたは mysqld のシャットダウン、再起動

FLUSH PRIVILEGES は caching_sha2_password プラグインを使用するすべてのアカウントに影響する点、注意です。

本記事で言及しているレプリケーション接続においては、「安全な接続」または「RSAベースのパスワード交換」のオプションで接続成功できているならば、キャッシュクリアされても再接続は問題なく成功すると思います。
(MySQL クライアントはうっかりオプションを付け忘れて認証エラーになってしまいそうなので注意でしょうか)

どちらかというと、一度、MASTER_PUBLIC_KEY_PATH でも GET_MASTER_PUBLIC_KEY でも、正常に公開鍵を使って接続に成功した場合、マスター側がキャッシュクリアされない限りは、別の公開鍵を指定しても(キャッシュを使って)接続できてしまう、という点に注意です。

例えば、初回に GET_MASTER_PUBLIC_KEY で接続成功した後、正しくない公開鍵が指定された MASTER_PUBLIC_KEY_PATH で CHANGE MASTER するとその時はエラーになりませんが、マスター側のmysqldを再起動してしまうと、再接続時にスレーブで以下のエラーが発生します。
一見すると今まで接続できたのにパスワードが違っている？という誤解をしてしまいそうです。

    Last_IO_Error: error connecting to master 'repl_user@192.168.56.34:3306' - retry-time: 60 retries: 1 message: Access denied for user 'repl_user'@'192.168.56.35' (using password: YES)

1	Last_IO_Error: error connecting to master 'repl_user@192.168.56.34:3306' - retry-time: 60 retries: 1 message: Access denied for user 'repl_user'@'192.168.56.35' (using password: YES)

ご注意ください。

※CHANGE MASTERで初めてスレーブからマスターに接続する前に、まずはマスター側で作成したばかりのレプリケーション用ユーザーをmysqlクライアントからローカル接続したあと、mysqld を再起動しても同じ現象となりますね…

(補足：4) InnoDB ReplicaSet で自動設定されたレプリケーション接続は…？

つい最近の弊社ブログでも取り上げている InnoDB ReplicaSet ではどのように設定されるか気になってみたので、環境構築直後に SHOW SLAVE STATUSで確認してみましたところ、
GET_MASTER_PUBLIC_KEY を使用して設定されていました。
シンプルなセットアップが実現されているので、この選択なのでしょうか？

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: db01
                  Master_User: mysql_innodb_rs_20
                  Master_Port: 3306
                     ...
           Master_SSL_Allowed: No
                     ...
       Master_public_key_path: 
        Get_master_public_key: 1
            Network_Namespace: 
1 row in set (0.00 sec)

mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: db01

Master_User: mysql_innodb_rs_20

Master_Port: 3306

...

Master_SSL_Allowed: No

...

Master_public_key_path:

Get_master_public_key: 1

Network_Namespace:

1 row in set (0.00 sec)

ちなみに、MySQL Shell の ReplicaSet.status() の extended オプション(2)では Master_public_key_path と Get_master_public_key に相当する情報は表示されませんでした…

MySQL Shell API: ReplicaSet Class Reference

 MySQL  db01:33060+ ssl  JS > rs.status({extended:'2'});
{
    "metadataVersion": "2.0.0", 
    "replicaSet": {
        "name": "replicaset_01", 
        "primary": "db01:3306", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "db01:3306": {
                "address": "db01:3306", 
                "fenceSysVars": [], 
                "fenced": false, 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", 
                "serverUuid": "1a8e6322-5139-11ea-ae41-5254008afee6", 
                "status": "ONLINE"
            }, 
            "db02:3306": {
                "address": "db02:3306", 
                "fenceSysVars": [
                    "read_only", 
                    "super_read_only"
                ], 
                "fenced": true, 
                "instanceRole": "SECONDARY", 
                "mode": "R/O", 
                "replication": {
                    "applierQueuedTransactionSet": "", 
                    "applierQueuedTransactionSetSize": 0, 
                    "applierState": "ON", 
                    "applierStatus": "APPLIED_ALL", 
                    "applierThreadState": "Slave has read all relay log; waiting for more updates", 
                    "applierWorkerThreads": 1, 
                    "options": {
                        "connectRetry": 60, 
                        "delay": 0, 
                        "heartbeatPeriod": 30, 
                        "retryCount": 86400
                    }, 
                    "receiverStatus": "ON", 
                    "receiverThreadState": "Waiting for master to send event", 
                    "receiverTimeSinceLastMessage": "00:00:14.409509", 
                    "replicationLag": null, 
                    "source": "db01:3306"
                }, 
                "serverUuid": "1dbee6ef-5139-11ea-baf3-5254008afee6", 
                "status": "ONLINE", 
                "transactionSetConsistencyStatus": "OK"
            }
        }, 
        "type": "ASYNC"
    }
}

MySQL db01:33060+ ssl JS > rs.status({extended:'2'});

{

"metadataVersion": "2.0.0",

"replicaSet": {

"name": "replicaset_01",

"primary": "db01:3306",

"status": "AVAILABLE",

"statusText": "All instances available.",

"topology": {

"db01:3306": {

"address": "db01:3306",

"fenceSysVars": [],

"fenced": false,

"instanceRole": "PRIMARY",

"mode": "R/W",

"serverUuid": "1a8e6322-5139-11ea-ae41-5254008afee6",

"status": "ONLINE"

"db02:3306": {

"address": "db02:3306",

"fenceSysVars": [

"read_only",

"super_read_only"

"fenced": true,

"instanceRole": "SECONDARY",

"mode": "R/O",

"replication": {

"applierQueuedTransactionSet": "",

"applierQueuedTransactionSetSize": 0,

"applierState": "ON",

"applierStatus": "APPLIED_ALL",

"applierThreadState": "Slave has read all relay log; waiting for more updates",

"applierWorkerThreads": 1,

"options": {

"connectRetry": 60,

"delay": 0,

"heartbeatPeriod": 30,

"retryCount": 86400

"receiverStatus": "ON",

"receiverThreadState": "Waiting for master to send event",

"receiverTimeSinceLastMessage": "00:00:14.409509",

"replicationLag": null,

"source": "db01:3306"

"serverUuid": "1dbee6ef-5139-11ea-baf3-5254008afee6",

"status": "ONLINE",

"transactionSetConsistencyStatus": "OK"

}

"type": "ASYNC"

}

まとめ

MySQL 8.0 で新規にレプリケーションを組む場合や、バージョンアップで caching_sha2_password プラグインに対応するのであれば、従来の CHANGE MASTER コマンドを見直して、以下のいずれかのオプションを適用することをご検討ください。

SSL/TLS接続(MASTER_SSL = 1)
RSA公開鍵によるパスワード交換
- ローカルの公開鍵を指定する方式(MASTER_PUBLIC_KEY_PATH = 'マスターのRSA公開鍵ファイル(のコピー)')
- マスターから自動送信を要求する方式(GET_MASTER_PUBLIC_KEY = 1)

データベース＆クラウド技術情報

MySQLでミッションクリティカルシステムのダウンタイム”ゼロ...

【HeatWavejp】祝１周年はじめてでもわかる！MySQL HeatWav...

HeatWavejp Meetup #06 イベントレポート

【HeatWavejp】MySQL HeatWave へのデータ同期はどうするの...

HeatWavejp Meetup #05 イベントレポート

【共催セミナー】「Oracle Cloud」の実力とは？～低価格・高品質・移行...

HeatWavejp Meetup #03 イベントレポート

HeatWavejp Meetup #02 イベントレポート

HeatWavejp Meetup #01 イベントレポート

MySQL8.0で新たに追加されているレプリケーション接続オプション

はじめに

「安全な接続」とは

「RSAベースのパスワード交換」とは

MASTER_PUBLIC_KEY_PATH

GET_MASTER_PUBLIC_KEY

MASTER_PUBLIC_KEY_PATH と GET_MASTER_PUBLIC_KEY、どちらを使用すればいい？

(補足：1) MySQLクライアントでの同パラメータ

(補足：2) Group Replication での同パラメータ

(補足：3) caching_sha2_password なので、一度接続に成功したあとの注意点

(補足：4) InnoDB ReplicaSet で自動設定されたレプリケーション接続は…？

まとめ