Apa itu SQL detail dan contohnya - PolarDB - Alibaba Cloud Documentation Center

PolarDB for MySQL menyediakan fitur SQL detail yang memberikan audit terperinci terhadap perubahan skema dan operasi lock pada database serta tabel, serta secara otomatis mengelola periode retensi catatan audit.

Latar Belakang

Saat menggunakan database, perubahan skema—seperti pembuatan, penambahan, atau penghapusan kolom dan indeks—serta operasi lock pada database dan tabel dapat memengaruhi operasi bisnis normal. Log audit untuk operasi tersebut sangat penting bagi personel O&M database karena mencakup detail seperti akun pengguna, alamat IP klien, waktu mulai, dan waktu penyelesaian setiap operasi.

Secara tradisional, log audit menggunakan sakelar global yang merekam semua pernyataan SQL. Meskipun pendekatan ini menghasilkan catatan audit yang komprehensif, biayanya relatif tinggi dan mungkin memerlukan komponen tambahan untuk menyimpan informasi tersebut.

PolarDB for MySQL menyediakan SQL detail untuk melakukan audit terperinci terhadap perubahan skema dan operasi lock pada database serta tabel. Fitur ini menangkap catatan audit segera setelah pernyataan terkait mulai dieksekusi dan menyimpannya dalam tabel sistem database. Anda dapat mengonfigurasi periode retensi catatan audit sesuai kebutuhan bisnis. Catatan audit yang lebih lama dari periode retensi akan dihapus secara otomatis. Biaya audit fitur ini sangat rendah. Sebagai contoh, jika setiap catatan audit memerlukan ruang penyimpanan 1 KB, terjadi 1.024 perubahan skema per hari, dan periode retensi adalah 30 hari, maka hanya diperlukan ruang penyimpanan sebesar 30 MB.

Prasyarat

Kluster PolarDB Anda harus memenuhi salah satu persyaratan versi berikut:

PolarDB for MySQL 8.0.1 dengan versi revisi 8.0.1.1.31 atau yang lebih baru.
PolarDB for MySQL 8.0.2 dengan versi revisi 8.0.2.2.12 atau yang lebih baru.

Anda dapat menanyakan versi engine untuk memastikan versi kluster.

Parameter

Anda dapat mengonfigurasi parameter berikut di Konsol untuk mengaktifkan SQL detail dan mengatur periode retensi catatan audit. Untuk prosedur pengaturan parameter, lihat Set cluster and node parameters.

Parameter	Level	Description
loose_awr_sqldetail_enabled	Global	Mengaktifkan atau menonaktifkan SQL detail. Nilai yang valid: OFF (default): menonaktifkan SQL detail. ON: mengaktifkan SQL detail.
loose_awr_sqldetail_switch	Global	Jenis operasi yang direkam oleh SQL detail. Sub-sakelar: ddl: merekam operasi DDL. Nilai yang valid: ON (default): merekam detail operasi DDL. OFF: tidak merekam detail operasi DDL. lock_db_table: merekam operasi LOCK DB dan LOCK TABLE. Nilai yang valid: ON (default): merekam detail operasi LOCK DB dan LOCK TABLE. OFF: tidak merekam detail operasi LOCK DB dan LOCK TABLE.
loose_awr_sqldetail_retention	Global	Periode retensi catatan audit. Catatan yang lebih tua dari periode ini akan secara otomatis dihapus. Nilai yang valid: 0 hingga 18446744073709551615. Nilai default: 2592000. Satuan: detik.

Skema Tabel

PolarDB for MySQL menyediakan tabel sistem bawaan sys.hist_sqldetail untuk menyimpan catatan audit. Tabel ini dibuat secara otomatis saat sistem dimulai sehingga tidak perlu dibuat secara manual. Skema tabelnya adalah sebagai berikut:

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;

Tabel berikut menjelaskan kolom-kolom dalam tabel sistem tersebut.

Kolom	Deskripsi
Id	ID auto-increment.
State	Status operasi saat catatan ditulis.
Thread_id	ID thread yang mengeksekusi pernyataan SQL.
Host	Host tempat pernyataan SQL dieksekusi.
User	Username yang mengeksekusi pernyataan SQL.
Client_ip	Alamat IP client tempat pernyataan SQL dieksekusi.
Db	Nama database tempat pernyataan SQL dieksekusi.
Sql_text	Pernyataan SQL yang dieksekusi.
Server_command	Perintah server yang digunakan untuk mengeksekusi pernyataan SQL.
Sql_command	Jenis perintah pernyataan SQL.
Start_time	Waktu saat pernyataan SQL mulai dieksekusi.
Exec_time	Durasi eksekusi. Satuan: mikrodetik.
Wait_time	Waktu tunggu. Satuan: mikrodetik.
Error_code	Kode kesalahan.
Rows_sent	Jumlah baris yang dikembalikan.
Rows_examined	Jumlah baris yang dipindai.
Rows_affected	Jumlah baris yang terpengaruh.
Logical_read	Jumlah logical read.
Phy_sync_read	Jumlah pembacaan fisik sinkron.
Phy_async_read	Jumlah physical asynchronous read.
Process_info	Bidang tambahan. Informasi pemrosesan.
Extra	Bidang tambahan. Informasi lainnya.
Create_time	Waktu saat catatan ditulis.
Update_time	Waktu saat catatan terakhir diperbarui.

Contoh

Di Konsol, atur parameter loose_awr_sqldetail_enabled menjadi ON, lalu jalankan pernyataan berikut pada database.

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)

Jalankan pernyataan berikut untuk melihat catatan audit dalam tabel sys.hist_sqldetail.

select * from sys.hist_sqldetail\G

Output:

*************************** 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

Output tersebut menunjukkan bahwa SQL detail hanya merekam informasi audit untuk pernyataan DDL, LOCK DB, dan LOCK TABLE, serta tidak merekam informasi audit untuk pernyataan DML. Selain itu, SQL detail menulis catatan ke tabel sistem segera setelah pernyataan SQL mulai dieksekusi dan secara otomatis memperbarui bidang seperti state dalam catatan tersebut setelah pernyataan selesai.