はじめに
MySQL 8.0.18 から、レプリケーションの際に PRIVILEGE_CHECKS_USER アカウントとしてレプリケーション権限チェック用のユーザーを追加することが出来るようになっています。
今回はこの PRIVILEGE_CHECKS_USER アカウントについて、利用手順や注意事項などについて確認していきたいと思います。
使用目的について
PRIVILEGE_CHECKS_USER アカウントが設定されている場合、リレーログに記載された内容をレプリカ SQL スレッドで実行する前に、各トランザクションが実行できるかをチェックするようになります。
これによって、以下のようなケースで有効に活用できる可能性があります。
-
レプリケーションで想定外のテーブルへの更新や DDL が実行されないように制御する
-
ソースサーバーがクラウドサービス上のインスタンスなどに存在しており、ユーザーの権限やシステム変数の設定などが難しい場合に、レプリカサーバー側で処理を制御する
そのため、レプリケーションを実行する際の追加のセキュリティレイヤーとして機能させることが出来るようになります。
PRIVILEGE_CHECKS_USER アカウントの利用方法について
ユーザーおよび必要な権限の付与
PRIVILEGE_CHECKS_USER アカウントを使用する場合、レプリケーション用のユーザーとは別に、 レプリカサーバーで 権限チェック用のユーザーを作成する必要があります。
したがって、ソースサーバーが MySQL 8.0.17 以前であっても本機能を利用することが可能です。
バイナリログの BINLOG ステートメントを実行するために、まずは REPLICATION_APPLIER 権限を付与する必要があります。
このとき、ユーザーは匿名ユーザー(ユーザー名が空白)あるいは CURRENT_USER を指定することが出来ないので注意してください。
また、新しいユーザーを作成する場合は CREATE USER で新規に作成しますが、既存のユーザーに権限を追加して運用することも可能です。
|
1 2 |
mysql> CREATE USER 'priv_repl'@'%' IDENTIFIED BY 'password'; mysql> GRANT REPLICATION_APPLIER ON *.* TO 'priv_repl'@'%'; |
なお、ホスト名とパスワードは指定しても認証されておらずユーザー名と付与された権限のみを参照しているようであるため、localhost からしか接続できないユーザーを指定しても問題なく利用することができます。
権限を付与しないでレプリケーションを開始した場合、以下のエラーが出力されてレプリケーションが開始できません。
|
1 |
[ERROR] [MY-013496] [Repl] Slave SQL for channel '': PRIVILEGE_CHECKS_USER for replication channel '' was set to <code>priv_repl</code>@<code>%</code>, but this user does not have REPLICATION_APPLIER privilege. Correct this before starting the replication threads. Error_code: MY-013496 |
その他、バイナリログに記載されているイベントを実行するために必要な権限が PRIVILEGE_CHECKS_USER アカウントに付与されている必要があります。
具体的には、以下のような権限を追加しておく必要があります。
- 特定のシステム変数を変更するために必要な権限 : SUPER or SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN
- 実施される関連テーブルへの更新処理に必要な権限 : INSERT, UPDATE, DELETE など
- 実施される関連テーブルへの DDL の実行に必要な権限 : CREATE, DROP など
特に *_VARIABLES_ADMIN 権限については、どんなイベントでも内部的に何らかのシステム変数の変更が実施されていることが多いため、実質的に必須な権限であるかと思います。
また、ユーザーに権限を追加した場合は、一度レプリケーションを停止/再開しなければ反映されないので注意するようにしてください。
レプリケーションの設定
その後、CHANGE REPLICATION SOURCE TO コマンドで通常のレプリケーションの設定をおこなう際に、PRIVILEGE_CHECKS_USER オプションを指定します。
既存のレプリケーションに設定を追加する場合は、一度レプリケーションを停止してから PRIVILEGE_CHECKS_USER オプションを追加してレプリケーションを再開してください。
|
1 2 3 4 |
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='[host_name]',SOURCE_USER='[user]',SOURCE_PASSWORD='[password', SOURCE_LOG_FILE='[file_name]',SOURCE_LOG_POS=[position],SOURCE_SSL=1,GET_SOURCE_PUBLIC_KEY=1, PRIVILEGE_CHECKS_USER = 'priv_repl'@'%'; mysql> START REPLICA; |
レプリケーション権限チェック用のユーザーが追加されているかどうかは、performance_schema.replication_applier_configuration テーブルの PRIVILEGE_CHECKS_USER カラムで確認することが出来ます。
|
1 2 3 4 5 6 7 8 9 |
mysql> SELECT * FROM performance_schema.replication_applier_configuration\G *************************** 1. row *************************** CHANNEL_NAME: DESIRED_DELAY: 0 PRIVILEGE_CHECKS_USER: 'priv_repl'@'%' REQUIRE_ROW_FORMAT: NO REQUIRE_TABLE_PRIMARY_KEY_CHECK: OFF ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS_TYPE: OFF ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS_VALUE: NULL |
実際の挙動とエラー対処
その後は、通常のレプリケーションと同様に使用することが出来ます。
なお、権限チェック時にエラーが発生した場合も、通常と同様以下のようなエラーが出力されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> SHOW REPLICA STATUS\G (...) Last_Errno: 1142 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log binlog.000003, end_log_pos 1276. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. (...) Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1142 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log binlog.000003, end_log_pos 1276. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. mysql> SELECT * FROM performance_schema.replication_applier_status_by_worker\G *************************** 1. row *************************** CHANNEL_NAME: WORKER_ID: 1 THREAD_ID: NULL SERVICE_STATE: OFF LAST_ERROR_NUMBER: 1142 LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at master log binlog.000003, end_log_pos 1276; Error 'DROP command denied to user 'priv_repl'@'%' for table 't5'' on query. Default database: ''. Query: 'DROP TABLE <code>repl</code>.<code>t5</code> /* generated by server */' LAST_ERROR_TIMESTAMP: 2023-01-05 15:20:44.755791 (...) |
上記のケースでは、PRIVILEGE_CHECKS_USER アカウントである ‘priv_repl’@’%’ ユーザーに t5 テーブルへの DROP 権限が不足しているエラーが発生していることがわかります。
このレプリケーションにおいて、もし t5 テーブルへの DROP コマンドの実行が許容できる場合は、PRIVILEGE_CHECKS_USER アカウントのユーザーに DROP 権限を付与してからレプリケーションを再開します。
あるいは、本コマンドが実行されてはいけない場合、ソースサーバー側の処理を確認するか、該当のトランザクションを以下のようにスキップして処理を飛ばします。
ただしトランザクションをスキップする場合、ソースサーバーとのデータ不整合が発生することを十分理解した上で実施する必要があるので注意してください。
|
1 2 3 |
mysql> STOP REPLICA; mysql> SET GLOBAL sql_replica_skip_counter = 1; mysql> START REPLICA; |
GTID ベースレプリケーションを利用している場合は、代わりに以下の手順でトランザクションをスキップ可能です。
|
1 2 3 4 5 6 |
mysql> STOP REPLICA; mysql> SET GTID_NEXT='aaa-bbb-ccc-ddd:N'; mysql> BEGIN; mysql> COMMIT; mysql> SET GTID_NEXT='AUTOMATIC'; mysql> START REPLICA; |
まとめ
ここまで、PRIVILEGE_CHECKS_USER アカウントの利用方法について確認してきました。
権限チェック時にエラーが発生した際の挙動は、従来のレプリカ SQL スレッドのエラーとほぼ同様であり、違いがわかりにくくあまり使い勝手も良くない印象を受けました。
ただし、従来のレプリケーションフィルターでは制限しきれなかった DDL の実行などを一括で制限できるなど、たとえばデータ移行で想定外の挙動を実行しないように制限する場合などのケースでは、有効に扱うことができるのではないでしょうか。
