MySQL DB インスタンスの一般的な DBA タスク - Amazon Relational Database Service

MySQL DB インスタンスの一般的な DBA タスク

以下では、MySQL データベースエンジンを実行する DB インスタンスに関する一般的な DBA タスクの Amazon RDS 固有の実装について説明します。マネージドサービスエクスペリエンスを提供するうえで、Amazon RDS は DB インスタンスへのシェルアクセスを提供していません。また、アドバンストの特権を必要とする特定のシステムの手順やテーブルへのアクセスも制限されいます。

Amazon RDS での MySQL ログファイルの操作に関する詳細は、「 MySQL データベースのログファイル」を参照してください。

定義済みユーザーとは

Amazon RDS は、新しい RDS for MySQL DB インスタンスを使用して、複数の事前定義済みのユーザーを自動的に作成します。事前定義済みのロールとそのロールの権限は変更できません。このような事前定義済みのロールについて、権限の削除、権限名の変更、権限の変更を行うことはできません。上記の操作を実行しようとすると、エラーが発生します。

  • rdsadminsuperuser 権限を持つ管理者がスタンドアロンの MySQL データベースで実行する管理タスクの多くを処理するために作成されるロールです。このユーザーは、多くの管理タスクのために RDS for MySQL によって内部的に使用されます。

  • rdsrepladmin – RDS for MySQL DB インスタンスおよびクラスターでのレプリケーションアクティビティをサポートするために Amazon RDS によって内部的に使用されるユーザーです。

ロールベースの特権モデル

RDS for MySQL バージョン 8.0.36 以降では、mysql データベースのテーブルを直接変更することはできません。特に、grant テーブルに対してデータ操作言語 (DML) オペレーションを実行してデータベースユーザーを作成することはできません。代わりに、MySQL アカウント管理ステートメント (CREATE USERGRANTREVOKE など) を使用してロールベースの権限をユーザーに付与します。また、mysql データベースでストアドプロシージャなど、他の種類のオブジェクトを作成することはできません。mysql テーブルにクエリを実行することはできます。バイナリログのレプリケーションを使用する場合、ソース DB インスタンスの mysql テーブルに直接行った変更は、ターゲットクラスターにレプリケートされません。

場合によっては、mysql テーブルに挿入することで、アプリケーションがショートカットを使用して、ユーザーやその他のオブジェクトを作成する場合があります。その場合は、アプリケーションコードを変更して、CREATE USER などの対応したステートメントを使用します。

外部 MySQL データベースからの移行中にデータベースユーザーのメタデータをエクスポートするには、以下のいずれかの方法を使用します。

  • MySQL Shell のインスタンスダンプユーティリティを、ユーザー、ロール、権限を除外するフィルターと共に使用します。次の例は、使用するコマンド構文を示しています。outputUrl が空であることを確認します。

    mysqlsh user@host -- util.dumpInstance(outputUrl,{excludeSchemas:['mysql'],users: true})

    詳細については、MySQL リファレンスマニュアルの「インスタンスダンプユーティリティ、スキーマダンプユーティリティ、およびテーブルダンプユーティリティ」を参照してください。

  • mysqlpump クライアントユーティリティを使用します。この例には、mysql システムデータベース内のテーブルを除くすべてのテーブルが含まれています。これには移行されたデータベース内のすべての MySQL ユーザーを再現する CREATE USERGRANT ステートメントも含まれます。

    mysqlpump --exclude-databases=mysql --users

多くのユーザーまたはアプリケーションの権限の管理を簡素化するには、CREATE ROLE ステートメントを使用して、一連の権限を持つロールを作成します。その後、GRANT および SET ROLE ステートメントと current_role 関数を使用して、ユーザーまたはアプリケーションにロールを割り当てたり、現在のロールを切り替えたり、有効なロールをチェックしたりできます。MySQL 8.0 でのロールベースのアクセス許可システムの詳細については、MySQL リファレンスマニュアルのロールの使用を参照してください。

重要

アプリケーションではマスターユーザーを直接使用しないことを強くお勧めします。代わりに、アプリケーションに必要な最小の特権で作成されたデータベースユーザーを使用するというベストプラクティスに従ってください。

RDS for MySQL バージョン 8.0.36 以降には、以下のすべての権限を持つ特別なロールが含まれています。ロールの名前は rds_superuser_role です。各 DB インスタンスのプライマリ管理ユーザーには、このロールが既に付与されています。rds_superuser_role ロールには、すべてのデータベースオブジェクトに対する次の権限が含まれます。

  • ALTER

  • APPLICATION_PASSWORD_ADMIN

  • ALTER ROUTINE

  • CREATE

  • CREATE ROLE

  • CREATE ROUTINE

  • CREATE TEMPORARY TABLES

  • CREATE USER

  • CREATE VIEW

  • DELETE

  • DROP

  • DROP ROLE

  • EVENT

  • EXECUTE

  • INDEX

  • INSERT

  • LOCK TABLES

  • PROCESS

  • REFERENCES

  • RELOAD

  • REPLICATION CLIENT

  • REPLICATION SLAVE

  • ROLE_ADMIN

  • SET_USER_ID

  • SELECT

  • SHOW DATABASES

  • SHOW VIEW

  • TRIGGER

  • UPDATE

  • XA_RECOVER_ADMIN

ロール定義には WITH GRANT OPTION が含まれるため、管理ユーザーはそのロールを他のユーザーに付与することができます。特に、管理者は MySQL クラスターをターゲットとするバイナリログレプリケーションの実行に必要な権限を付与する必要があります。

ヒント

アクセス許可の詳細全体を表示するには、次のステートメントを使用します。

SHOW GRANTS FOR rds_superuser_role@'%';

RDS for MySQL バージョン 8.0.36 以降でロールを使用してアクセスを許可する場合は、SET ROLE role_name または SET ROLE ALL ステートメントを使用してロールも有効にします。以下の例のように指定します。CUSTOM_ROLE 用に適切なロール名を置き換えます。

# Grant role to user mysql> GRANT CUSTOM_ROLE TO 'user'@'domain-or-ip-address' # Check the current roles for your user. In this case, the CUSTOM_ROLE role has not been activated. # Only the rds_superuser_role is currently in effect. mysql> SELECT CURRENT_ROLE(); +--------------------------+ | CURRENT_ROLE() | +--------------------------+ | `rds_superuser_role`@`%` | +--------------------------+ 1 row in set (0.00 sec) # Activate all roles associated with this user using SET ROLE. # You can activate specific roles or all roles. # In this case, the user only has 2 roles, so we specify ALL. mysql> SET ROLE ALL; Query OK, 0 rows affected (0.00 sec) # Verify role is now active mysql> SELECT CURRENT_ROLE(); +--------------------------------------------------+ | CURRENT_ROLE() | +--------------------------------------------------+ | `CUSTOM_ROLE`@`%`,`rds_superuser_role`@`%` | +--------------------------------------------------+

セッションやクエリの終了

DB インスタンス上のユーザーセッションまたはクエリは、rds_kill コマンドおよび rds_kill_query コマンドを使用して終了することができます。まず MySQL DB インスタンスに接続し、次に以下に示す適切なコマンドを発行します。詳細については、「MySQL データベースエンジンを実行している DB インスタンスへの接続」を参照してください。

CALL mysql.rds_kill(thread-ID) CALL mysql.rds_kill_query(thread-ID)

例えば、スレッド 99 で実行中のセッションを終了するには、次のように入力します。

CALL mysql.rds_kill(99);

スレッド 99 で実行中のクエリを終了するには、次のように入力します。

CALL mysql.rds_kill_query(99);

現在のレプリケーションエラーのスキップ

リードレプリカが反応を停止する原因となるエラーがデータの整合性に影響しない場合、リードレプリカでそのエラーをスキップすることができます。

注記

まず、そのエラーを安全にスキップできることを確認します。MySQL ユーティリティで、リードレプリカに接続して以下の MySQL コマンドを実行します。

SHOW REPLICA STATUS\G

返される値の詳細については、MySQL ドキュメントを参照してください。

MySQL の旧バージョンは SHOW SLAVE STATUS ではなく SHOW REPLICA STATUS を使用していました。8.0.23 より前の MySQL バージョンを使用している場合は、SHOW SLAVE STATUS を使用します。

リードレプリカのエラーは、次の方法でスキップできます。

mysql.rds_skip_repl_error の手順を呼び出します。

Amazon RDS では、リードレプリカのエラーをスキップするために呼び出すことができるストアドプロシージャを提供しています。まず、リードレプリカに接続してから以下のように適切なコマンドを発行します。詳細については、「MySQL データベースエンジンを実行している DB インスタンスへの接続」を参照してください。

エラーをスキップするには、次のコマンドを実行します。

CALL mysql.rds_skip_repl_error;

このコマンドは、出典 DB インスタンス、またはレプリケーションエラーが発生していないリードレプリカでは実行しても効果はありません。

mysql.rds_skip_repl_error をサポートする MySQL のバージョンなどの詳細については、「mysql.rds_skip_repl_error」を参照してください。

重要

mysql.rds_skip_repl_errorを呼び出そうとして次のエラーが発生した場合、ERROR 1305 (42000): PROCEDURE mysql.rds_skip_repl_error does not exist、MySQL DB インスタンスを最新のマイナーバージョンまたはmysql.rds_skip_repl_errorにリストされている最小のマイナーバージョンの1つにアップグレードします。

slave_skip_errors パラメータの設定

1 つ以上のエラーをスキップするには、slave_skip_errorsリードレプリカに静的パラメータを設定します。このパラメータでは、1 つ以上の特定のレプリケーションエラーコードをスキップするように設定できます。現在、このパラメータは RDS for MySQL 5.7 DB インスタンスに対してのみ設定できます。パラメータの設定変更後に新しい設定を有効にするには、DB インスタンスを必ず再起動してください。これらのパラメータ設定の詳細については、MySQL のドキュメントを参照してください。

このパラメータは別の DB パラメータグループに設定することをお勧めします。この DB パラメータグループは、エラーをスキップする必要があるリードレプリカにのみ関連付けることができます。このベストプラクティスに従うことで、他の DB インスタンスやリードレプリカに与える潜在的な影響が軽減されます。

重要

このパラメータにデフォルト以外の値を設定すると、レプリケーションの不整合につながることがあります。問題を解決するための他のオプションを使い果たし、リードレプリカのデータに潜在的な影響が確実である場合にのみ、このパラメータをデフォルト以外の値に設定してください。

InnoDB テーブルスペースの操作によるクラッシュリカバリ時間の短縮

MySQL のすべてのテーブルは、テーブル定義、データ、およびインデックスから構成されます。MySQL のストレージエンジン InnoDB は、tablespace にテーブルのデータとインデックスを格納します。InnoDB は、データディクショナリおよびその他の関連メタデータを含むグローバル共有データスペースを作成し、テーブルのデータとインデックスを含めることができます。また、InnoDB は、テーブルおよびパーティションごとに個別のテーブルスペースを作成することもできます。これらの個別のテーブルスペースは、.ibd の拡張子を持つファイルに格納され、各テーブルスペースのヘッダーには、それを一意に識別する数値が含まれます。

Amazon RDS は innodb_file_per_table と呼ばれる MySQL パラメータグループにパラメータを提供します。このパラメータは、InnoDB が、新しいテーブルデータとインデックスを共有テーブルスペースに追加するか (パラメータ値を 0 に設定)、または個別のテーブルスペースに追加するか (パラメータ値を 1 に設定) を制御します。Amazon RDS は、innodb_file_per_table パラメータのデフォルト値を 1 に設定します。この設定により、個別に InnoDB テーブルを削除し、それらのテーブルで使用していたストレージを、DB インスタンス用として再要求することができます。ほとんどの場合、innodb_file_per_table パラメータは 1 に設定することをお勧めします。

多数のテーブルがある場合 (スタンダード (磁気) または汎用 SSD ストレージを使用するときは 1,000 以上のテーブル、プロビジョンド IOPS ストレージを使用するときは 10,000 以上のテーブル) は、innodb_file_per_table パラメータを 0 に設定する必要があります。このパラメータを 0 に設定すると、個別のテーブルスペースは作成されないため、データベースのクラッシュ回復時間を短縮できます。

MySQL は、クラッシュ回復サイクル中に個々のメタデータファイルを処理して、そこにテーブルスペースを格納します。MySQL が共有テーブルスペース内のメタデータ情報を処理するために必要な時間は、複数のテーブルスペースが存在するときに数千のテーブルスペースファイルを処理するために必要な時間と比べるとごく僅かです。テーブルスペース番号は、各ファイルのヘッダーに保存されているため、すべてのテーブルスペースファイルを読み込むための時間を集計すると、数時間かかる可能性があります。例えば、クラッシュ回復サイクル中に、スタンダードストレージの 100 万の InnoDB テーブルスペースを処理するには、5 ~ 8 時間かかる可能性があります。場合によっては、InnoDB がクラッシュ回復サイクル後に、追加クリーンアップが必要であると判断し、再度クラッシュ回復サイクルをスタートすることがあり、それによって回復時間が長くなります。クラッシュ回復サイクルには、テーブルスペース情報の処理以外に、トランザクションのロールバック、損傷したページの修復、およびその他の処理も必要であることに注意してください。

innodb_file_per_table パラメータはパラメータグループ内にあるため、DB インスタンスを再起動しなくても、DB インスタンスが使用するパラメータグループを編集することで、このパラメータ値を変更できます。例えば、設定が 1 (個別のテーブルを作成する) から 0 (共有テーブルスペースを使用する) に変更されると、その後、新しい InnoDB テーブルが共有テーブルスペースに追加されますが、既存のテーブルには個別のテーブルスペースがそのまま残ります。InnoDB テーブルを共有テーブルスペースに移動するには、ALTER TABLE コマンドを使用する必要があります。

複数のテーブルスペースを共有テーブルスペースに移行する

InnoDB テーブルのメタデータを固有のテーブルスペースから共有テーブルスペースに移動することができます。このコマンドは、innodb_file_per_table パラメータ設定に従って、テーブルメタデータを再構築します。まず MySQL DB インスタンスに接続し、次に以下に示す適切なコマンドを発行します。詳細については、「MySQL データベースエンジンを実行している DB インスタンスへの接続」を参照してください。

ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY;

例えば、次のクエリは共有テーブルスペースにない InnoDB テーブルごとに ALTER TABLE ステートメントを返します。

MySQL 5.7 DB インスタンスの場合:

SELECT CONCAT('ALTER TABLE `', REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

MySQL 8.0 DB インスタンスの場合:

SELECT CONCAT('ALTER TABLE `', REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

テーブルのメタデータを共有テーブルスペースに移動するために MySQL テーブルを再構築するには、一時的にテーブルの再構築のための追加ストレージ領域が必要になるため、DB インスタンスには使用可能なストレージ領域が必要です。再構築中は、テーブルがロックされ、クエリのアクセスが制限されます。小さなテーブルや、頻繁にアクセスされないテーブルの場合、これは問題にはならないことがあります。大きなテーブルや過酷な同時実行環境で頻繁にアクセスされるテーブルの場合は、リードレプリカでテーブルを再構築できます。

リードレプリカを作成し、リードレプリカの共有テーブルスペースにテーブルのメタデータを移行できます。ALTER TABLE ステートメントはリードレプリカのアクセスをブロックしますが、出典 DB インスタンスは影響を受けません。テーブルの再構築中はリードレプリカが停滞しますが、出典 DB インスタンスはバイナリログを生成し続けます。再構築には追加ストレージ領域が必要で、再生ログファイルは大きくなる可能性があるため、出典 DB インスタンスより大きなストレージが割り当てられたリードレプリカを作成する必要があります。

リードレプリカを作成し、共有テーブルスペースを使用する InnoDB テーブルを再構築するには、次のステップに従ってください。

  1. バイナリログ作成が有効になるように、出典 DB インスタンスでバックアップ保持が有効になっていることを確認します。

  2. AWS Management Console または AWS CLI を使用して、出典 DB インスタンスのリードレプリカを作成します。リードレプリカの作成にはクラッシュ回復と同じプロセスが多く含まれているため、InnoDB テーブルスペースの数が多い場合は、作成プロセスに時間がかかることがあります。出典 DB インスタンスで現在使用しているよりも大きいストレージ領域をリードレプリカに割り当てます。

  3. リードレプリカが作成されたら、パラメータを read_only = 0 および innodb_file_per_table = 0 に設定したパラメータグループを作成します。次に、パラメータグループをリードレプリカに関連付けます。

  4. レプリカで移行するすべてのテーブルに対して、次の SQL ステートメントを発行します。

    ALTER TABLE name ENGINE = InnoDB
  5. リードレプリカですべての ALTER TABLE ステートメントが完了したら、リードレプリカが出典 DB インスタンスに接続され、2 つのインスタンスが同期していることを確認します。

  6. コンソールまたは CLI を使用して、リードレプリカをインスタンスに昇格します。新しいスタンドアロン DB インスタンスに使用されるパラメータグループの innodb_file_per_table パラメータが 0 に設定されていることを確認します。新しいスタンドアロン DB インスタンスの名前を変更し、アプリケーションを新しいスタンドアロン DB インスタンスにします。

Global Status History の管理

ヒント

データベースのパフォーマンスを分析するには、Amazon RDS のPerformance Insights を使用することもできます。詳細については、「Amazon RDS での Performance Insights を使用したDB 負荷のモニタリング」を参照してください。

MySQL はオペレーションに関する情報を提供する多くのステータス可変を維持しています。その値は、DB インスタンスのロックまたはメモリ問題の検出に役立ちます。これらのステータス可変の値は、最後に DB インスタンスがスタートされてからの累積です。ほとんどのステータス可変は、FLUSH STATUS コマンドを使用して 0 にリセットできます。

これらの値を経時的にモニタリングできるように、Amazon RDS は、これらのステータス可変値のスナップショットを作成し、前回のスナップショット以降に行われた変更と共にテーブルに書き込む一連の手順を提供します。このインフラストラクチャは Global Status History (GoSH) と呼ばれ、5.5.23 で始まるバージョンのすべての MySQL DB インスタンスにインストールされています。GoSH は、デフォルトでは無効化されています。

GoSH を有効にするには、初期にパラメータ event_schedulerON に設定し、DB パラメータグループからイベントスケジューラーを有効にします。MySQL 5.7 を実行している MySQL DB インスタンスの場合、パラメータ show_compatibility_561 にサーバーしてください。DB パラメータグループの作成と変更の詳細については、「「パラメータグループを使用する」 」を参照してください。このパラメータを有効にした場合の副作用の詳細については、「MySQL 5.7 リファレンスマニュアル」の「show_patibility_56」を参照してください。

次に、以下の表の手順を使用して、GoSH を有効化し、設定します。まず MySQL DB インスタンスに接続し、次に以下に示す適切なコマンドを発行します。詳細については、「MySQL データベースエンジンを実行している DB インスタンスへの接続」を参照してください。各手順で次のように入力します。

CALL procedure-name;

ここで、procedure-name は、表の手順の 1 つです。

手順

説明

mysql.rds_enable_gsh_collector

GoSH のデフォルトのスナップショット作成を有効化します。間隔は rds_set_gsh_collector で指定します。

mysql.rds_set_gsh_collector

スナップショット作成の間隔を分単位で指定します。デフォルト値は 5 です。

mysql.rds_disable_gsh_collector

スナップショットを無効にします。

mysql.rds_collect_global_status_history

オンデマンドでスナップショットを作成します。

mysql.rds_enable_gsh_rotation

mysql.rds_global_status_history テーブルから mysql.rds_global_status_history_old へのコンテンツのローテーションを有効化します。間隔は rds_set_gsh_rotation で指定します。

mysql.rds_set_gsh_rotation

テーブルのローテーション間隔を日単位で指定します。デフォルト値は 7 です。

mysql.rds_disable_gsh_rotation

テーブルのローテーションを無効にします。

mysql.rds_rotate_global_status_history

mysql.rds_global_status_history テーブルのコンテンツを mysql.rds_global_status_history_old にオンデマンドでローテーションします。

GoSH の実行中は、書き込んでいるテーブルに対してクエリを実行できます。例えば、Innodb バッファープールのヒット率を照会するには、次のクエリを実行します。

select a.collection_end, a.collection_start, (( a.variable_Delta-b.variable_delta)/a.variable_delta)*100 as "HitRatio" from mysql.rds_global_status_history as a join mysql.rds_global_status_history as b on a.collection_end = b.collection_end where a. variable_name = 'Innodb_buffer_pool_read_requests' and b.variable_name = 'Innodb_buffer_pool_reads'