SQL Detail - PolarDB

PolarDB MySQL版提供的SQL Detail功能，用於對庫表變更和加鎖操作做詳細審計，並對審計記錄做自動淘汰管理。

背景資訊

在使用資料庫的過程中，庫表的變更（如建立列和索引、增加列和索引以及刪除列和索引）或對庫表加鎖等操作會影響業務的正常使用，對應操作的審計日誌對資料庫的營運人員至關重要，營運人員需要知道對應操作的使用者帳號、用戶端IP地址、操作時間以及完成時間等詳細資料。

在此之前，審計日誌基本都是全域開關，即對所有的SQL語句做審計，雖然記錄的審計日誌很全面，但成本相對較高。甚至有時會需要額外的組件來儲存資訊。

PolarDB MySQL版提供了SQL Detail功能，能夠對庫表變更和加鎖操作做詳細審計。該功能在相關語句開始執行時就捕獲到相關記錄，並將審計記錄儲存在資料庫的系統資料表中。您可以根據業務需要配置審計記錄的儲存時間，超過該時間的審計記錄將會被自動淘汰。且該功能的審計成本極低，以單條審計記錄的儲存容量為1 KB，單日庫表變更1024次，審計記錄儲存時間為30天計算，僅需要30 MB的儲存空間。

前提條件

PolarDB叢集版本需滿足如下條件之一：

PolarDB MySQL版8.0.1版本且修訂版本為8.0.1.1.31及以上。
PolarDB MySQL版8.0.2版本且修訂版本為8.0.2.2.12及以上。

您可以通過查詢版本號碼來確認叢集版本。

參數說明

您可以在控制台上設定以下參數，來開啟SQL Detail功能以及設定審計記錄的儲存時間。設定參數操作步驟請參見設定叢集參數和節點參數。

參數	層級	說明
loose_awr_sqldetail_enabled	Global	開啟或關閉SQL Detail功能。取值如下： OFF（預設）：關閉SQL Detail功能。 ON：開啟SQL Detail功能。
loose_awr_sqldetail_switch	Global	SQL Detail記錄的操作類型。子開關如下： ddl：記錄DDL操作。取值如下： ON（預設）：記錄DDL操作的詳細資料。 OFF：不記錄DDL操作的詳細資料。 lock_db_table：記錄LOCK DB、LOCK TABLE操作。取值如下： ON（預設）：記錄LOCK DB、LOCK TABLE操作的詳細資料。 OFF：不記錄LOCK DB、LOCK TABLE操作的詳細資料。
loose_awr_sqldetail_retention	Global	審計記錄的儲存時間。超過該時間的記錄會被自動淘汰。取值範圍：0~18446744073709551615。預設值為2592000。單位：秒。

表結構說明

PolarDB MySQL版內建了一張系統資料表sys.hist_sqldetail，用於儲存審計記錄。系統啟動時會自動建立該表，無需您手動建立。該系統資料表的表結構如下：

CREATE TABLE `hist_sqldetail` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `State` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
  `Thread_id` bigint(20) unsigned 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) DEFAULT NULL,
  `Rows_affected` bigint(20) DEFAULT NULL,
  `Logical_read` bigint(20) DEFAULT NULL,
  `Phy_sync_read` bigint(20) DEFAULT NULL,
  `Phy_async_read` bigint(20) DEFAULT NULL,
  `Process_info` text COLLATE utf8mb4_bin,
  `Extra` text COLLATE utf8mb4_bin,
  `Create_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `Update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`Id`),
  KEY `i_start_time` (`Start_time`),
  KEY `i_update_time` (`Update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

系統資料表中的參數說明如下：

參數	說明
Id	自增ID。
State	該操作被記錄時所處的狀態。
Thread_id	執行該SQL語句的線程ID。
Host	執行該SQL語句的使用者。
User	執行該SQL語句的使用者名稱。
Client_ip	執行該SQL語句的用戶端IP地址。
Db	執行該SQL語句的資料庫名稱。
Sql_text	執行的SQL語句。
Server_command	執行該SQL語句的Server命令。
Sql_command	SQL語句的命令類型。
Start_time	開始執行SQL語句的時間。
Exec_time	執行耗時。單位：微秒。
Wait_time	等待時間。單位：微秒。
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，並在資料庫中執行以下命令。

create table t(c1 int);
Query OK, 0 rows affected (0.02 sec)

create table t(c1 int);
ERROR 1050 (42S01): Table 't' already exists

alter table t add column c2 int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

lock tables t read;
Query OK, 0 rows affected (0.00 sec)

unlock tables;
Query OK, 0 rows affected (0.00 sec)

insert into t values(1, 2);
Query OK, 1 row affected (0.00 sec)

執行以下命令，查看sys.hist_sqldetail表中記錄的審計資訊。

select * from sys.hist_sqldetail\G

執行結果如下：

*************************** 1. row ***************************
            Id: 1
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: create table t(c1 int)
Server_command: Query
   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_examined: 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. row ***************************
            Id: 2
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: create table t(c1 int)
Server_command: Query
   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_examined: 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. row ***************************
            Id: 3
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: alter table t add column c2 int
Server_command: Query
   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_examined: 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. row ***************************
            Id: 4
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: lock tables t read
Server_command: Query
   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_examined: 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. row ***************************
            Id: 5
         State: FINISH
     Thread_id: 18
          Host: localhost
          User: root
     Client_ip: 127.0.0.1
            Db: test
      Sql_text: unlock tables
Server_command: Query
   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_examined: 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 Detail功能只記錄DDL、LOCK DB和LOCKTABLE的審計資訊，而不記錄DML操作的審計資訊。且SQL Detail功能在SQL語句開始執行時，就將捕獲到的資訊記錄到系統資料表中，當該SQL語句執行完成後，會自動更新系統資料表中的狀態等資訊。