SQLの詳細 - PolarDB - Alibaba Cloud ドキュメントセンター

PolarDB for MySQLのSQL詳細機能は、データベースとテーブルの更新とロック操作の詳細な監査を実行し、監査レコードを自動的に削除するために使用されます。

背景情報

データベースとテーブルの更新 (列とインデックスの作成、列とインデックスの追加、列とインデックスの削除など) とロック操作は、ビジネスに影響します。関連する操作の監査ログは、そのような操作のユーザー名、クライアントIPアドレス、開始時間、および終了時間を必要とするO&M担当者にとって重要です。

監査ログ機能はグローバルに有効にでき、すべてのSQL文が監査されます。監査のオーバーヘッドは非常に高いです。レコードを保存するために追加のコンポーネントが必要な場合もあります。

PolarDB for MySQLは、データベースとテーブルの更新とロック操作の詳細な監査を実行するためのSQL詳細機能を提供します。この機能は、SQL文の実行開始時に関連するレコードをキャプチャし、監査レコードをシステムテーブルに保存します。ビジネス要件に基づいて、監査レコードの保持期間を指定できます。この保持期間を超えたレコードは自動的に削除されます。この機能は、非常に低い監査オーバーヘッドを招く。 1つの監査レコードのストレージ容量は1 KBです。たとえば、データベーステーブルが1日に1,024回更新され、監査レコードの保持期間が30日である場合、30 MBのストレージ容量しか消費されません。

前提条件

PolarDBクラスターは、次のいずれかの要件を満たしています。

リビジョンバージョンが8.0.1.1.31以降のPolarDB for MySQL 8.0.1のクラスター。
リビジョンバージョンが8.0.2.2.12以降のPolarDB for MySQL 8.0.2のクラスター。

クラスターのバージョンを表示する方法については、「エンジンバージョンの照会」をご参照ください。

パラメーター

コンソールで次のパラメーターを設定して、SQLの詳細機能を有効にし、監査レコードの保持期間を設定できます。パラメーターの設定方法の詳細については、「クラスターとノードパラメーターの設定」をご参照ください。

パラメーター	レベル	説明
loose_awr_sqldetail_enabled	グローバル	SQL詳細機能を有効にするかどうかを指定します。デフォルト値: OFF。有効な値：オフオン
loose_awr_sqldetail_switch	グローバル	監査レコードの操作タイプ。有効な値： ddl: DDL操作を記録するかどうかを指定します。デフォルト値: ON。有効な値：オンオフ lock_db_table: LOCK DBおよびLOCK TABLE操作を記録するかどうかを指定します。デフォルト値: ON。有効な値：オンオフ
loose_awr_sqldetail_retention	グローバル	監査レコードの保持期間。この保持期間を超えたレコードは自動的に削除されます。有効な値: 0 ~ 18446744073709551615 デフォルト値: 2592000 単位は秒です。

テーブル形式

sys. hist_sqldailシステムテーブルは、監査レコードを保存するためにPolarDB for MySQLに組み込まれています。このテーブルは、システムの起動時に自動的に作成されます。手動でテーブルを作成する必要はありません。テーブルの形式は次のとおりです

。CREATE table 'hist_sqldetail' ()
  'Id' bigint (20) 符号なしNOT NULL AUTO_INCREMENT、
  'State' varchar (16) COLLATE utf8mb4_bin DEFAULT NULL、
  'Thread_id' bigint(20) 符号なしDEFAULT NULL、
  'Host'varchar (60) COLLATE utf8mb4_bin NOT NULL DEFAULT ''、
  'User'varchar (32) COLLATE utf8mb4_bin NOT NULL DEFAULT ''、
  'Client_ip 'varchar (60) COLLATE utf8mb4_bin DEFAULT NULL、
  'Db' varchar (64) COLLATE utf8mb4_bin DEFAULT NULL、
  'Sql_text' mediumtext COLLATE utf8mb4_bin NOT NULL、
  'Server_command' varchar (32) COLLATE utf8mb4_bin DEFAULT NULL、
  'Sql_command' varchar (64) COLLATE utf8mb4_bin DEFAULT NULL、
  'start_time' timestamp (6) NULL DEFAULT NULL、
  'Exec_time' bigint(20) DEFAULT NULL、
  'Wait_time' bigint(20) DEFAULT NULL、
  'error_code' int(11) DEFAULT NULL、
  'Rows_sent 'bigint(20) DEFAULT NULL、
  'Rows_examined 'bigint(20) デフォルトNULL、
  'Rrows_affected' bigint(20) デフォルトNULL、
  'Logical_read' bigint(20) DEFAULT NULL、
  'Phy_sync_read 'bigint(20) DEFAULT NULL、
  'Phy_async_read 'bigint(20) DEFAULT NULL、
  「プロセス_情報」テキストCOLLATE utf8mb4_bin、
  「エクストラ」テキストCOLLATE utf8mb4_bin、
  'create_time 'タイムスタンプ (6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) 、
  「UPDATE_time」タイムスタンプ (6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) 、
  主要なキー ('Id') 、
  KEY 'i_start_time ' ('Start_time') 、
  KEY 'i_update_time ' ('Update_time')
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

次の表は、システムテーブルのパラメータを示しています。

パラメーター	説明
Id	自動インクリメントID。
状態	操作が記録された状態。
Thread_id	SQL文を実行するスレッドのID。
ホスト	SQL文を実行するユーザー。
ユーザー	SQL文の実行に使用されるユーザー名。
Client_ip	SQL文の実行に使用されるクライアントのIPアドレス。
Db	SQL文が実行されるデータベースの名前。
Sql_text	実行されるSQLステートメント。
Server_command	SQL文の実行に使用されるserverコマンド。
Sql_command	SQL 文のタイプです。
Start_time	SQL 文の実行開始日時。
Exec_time	SQL文の実行期間。単位：マイクロ秒。
Wait_time	SQL文が実行されるまでの待ち時間。単位：マイクロ秒。
Error_code	エラーコードを示します。
Rows_sent	返された行の数。
Rows_examined	スキャンされた行の数。
Rows_affected	影響を受ける行の数。
Logical_read	論理読み取りの数。
Phy_sync_read	物理同期読み取りの数。
Phy_async_read	物理非同期読み取りの数。
Process_info	処理情報は、拡張フィールドである。
Extra	拡張フィールドである追加情報。
Create_time	レコードが作成された時刻。
Update_time	レコードが更新された時刻。

例

コンソールでloose_awr_sqldetail_enabledパラメーターをONに設定し、データベースで次のステートメントを実行します。

テーブルt(c1 int) を作成します。クエリOK、影響を受ける0行 (0.02秒)

テーブルt(c1 int) を作成します。エラー1050 (42S01): テーブル 't' は既に存在します

テーブルを変更t追加列c2 int;
クエリOK、影響を受ける0行 (0.02秒)
記録: 0重複: 0警告: 0

ロックテーブルt読み取り;
クエリOK、影響を受ける0行 (0.00秒)

テーブルのロック解除;
クエリOK、影響を受ける0行 (0.00秒)

t値 (1、2) に挿入します。クエリOK、影響を受ける1行 (0.00秒)

次のステートメントを実行して、sys.hist_sqldetailテーブルの監査レコードを表示します。

select * from sys.hist_sqldetail\G

サンプル結果:

*************************** 1。 行 ***************************
            Id: 1
         州: 終わり
     Thread_id: 18
          ホスト: localhost
          ユーザー: root
     Client_ip: 127.0.0.1
            Db: テスト
      Sql_text: テーブルtを作成する (c1 int)
Server_command: クエリ
   Sql_command: create_table
    Start_time: 2023-01-13 16:18:21.840435
     Exec_time: 17390
     Wait_time: 318
    Error_code: 0
     Rows_sent: 0
 Rows_excheded: 0
 Rows_affected: 0
  Logical_read: 420
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:18:22.391407
   Update_time: 2023-01-13 16:18:22.391407
*************************** 2. 行 ***************************
            Id: 2
         州: 終わり
     Thread_id: 18
          ホスト: localhost
          ユーザー: root
     Client_ip: 127.0.0.1
            Db: テスト
      Sql_text: テーブルtを作成する (c1 int)
Server_command: クエリ
   Sql_command: create_table
    Start_time: 2023-01-13 16:18:22.416321
     Exec_time: 822
     Wait_time: 229
    Error_code: 1050
     Rows_sent: 0
 Rows_excheded: 0
 Rows_affected: 0
  Logical_read: 55
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:18:23.393071
   Update_time: 2023-01-13 16:18:23.393071
3. 行 ***************************
            Id: 3
         州: 終わり
     Thread_id: 18
          ホスト: localhost
          ユーザー: root
     Client_ip: 127.0.0.1
            Db: テスト
      Sql_text: テーブルを変更して列c2 intを追加
Server_command: クエリ
   Sql_command: alter_table
    Start_time: 2023-01-13 16:18:34.123947
     Exec_time: 16420
     Wait_time: 245
    Error_code: 0
     Rows_sent: 0
 Rows_excheded: 0
 Rows_affected: 0
  Logical_read: 778
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:18:34.394067
   Update_time: 2023-01-13 16:18:34.394067
4. 行 ***************************
            Id: 4
         州: 終わり
     Thread_id: 18
          ホスト: localhost
          ユーザー: root
     Client_ip: 127.0.0.1
            Db: テスト
      Sql_text: ロックテーブルt読み取り
Server_command: クエリ
   Sql_command: lock_tables
    Start_time: 2023-01-13 16:19:49.891559
     Exec_time: 145
     Wait_time: 129
    Error_code: 0
     Rows_sent: 0
 Rows_excheded: 0
 Rows_affected: 0
  Logical_read: 0
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:19:50.399585
   Update_time: 2023-01-13 16:19:50.399585
*************************** 5。 行 ***************************
            Id: 5
         州: 終わり
     Thread_id: 18
          ホスト: localhost
          ユーザー: root
     Client_ip: 127.0.0.1
            Db: テスト
      Sql_text: ロック解除テーブル
Server_command: クエリ
   Sql_command: unlock_tables
    Start_time: 2023-01-13 16:19:56.924648
     Exec_time: 98
     Wait_time: 0
    Error_code: 0
     Rows_sent: 0
 Rows_excheded: 0
 Rows_affected: 0
  Logical_read: 0
 Phy_sync_read: 0
Phy_async_read: 0
  Process_info: NULL
         Extra: NULL
   Create_time: 2023-01-13 16:19:57.400294
   Update_time: 2023-01-13 16:19:57.400294

上記の結果は、SQLの詳細機能がDDL、LOCK DB、およびLOCK TABLE操作の監査レコードのみを保存し、DML操作の監査レコードは保存しないことを示しています。また、SQL detail関数は、SQL文の実行開始時にキャプチャしたすべての情報をシステムテーブルに記録し、SQL文の実行後に状態などの情報を自動的に更新します。