O que é SQL detail e exemplos - PolarDB: Central de Documentação do Alibaba Cloud

Contexto

Ao utilizar um banco de dados, alterações de esquema em bancos de dados e tabelas — como criação, adição ou remoção de colunas e índices — ou operações de bloqueio podem impactar as operações normais do negócio. Os logs de auditoria dessas operações são essenciais para a equipe de O&M do banco de dados, que precisa conhecer detalhes como conta de usuário, endereço IP do cliente, horário de início e conclusão de cada operação.

Tradicionalmente, os logs de auditoria utilizam uma chave global para auditar todas as instruções SQL. Embora os registros sejam abrangentes, essa abordagem tem custo relativamente alto e pode exigir componentes adicionais para armazenar as informações.

PolarDB for MySQL disponibiliza o SQL detail para auditar detalhadamente alterações de esquema e operações de bloqueio em bancos de dados e tabelas. Esse recurso captura registros de auditoria assim que as instruções relevantes começam a executar e os armazena em uma tabela de sistema do banco de dados. Configure o período de retenção dos registros de auditoria conforme as necessidades do seu negócio. Registros anteriores ao período de retenção são removidos automaticamente. O custo de auditoria desse recurso é extremamente baixo. Por exemplo, se cada registro consumir 1 KB de armazenamento, ocorrerem 1.024 alterações de esquema por dia e o período de retenção for de 30 dias, apenas 30 MB de armazenamento serão necessários.

Pré-requisitos

Seu cluster PolarDB deve atender a um dos seguintes requisitos de versão:

PolarDB for MySQL 8.0.1 com versão de revisão 8.0.1.1.31 ou posterior.
PolarDB for MySQL 8.0.2 com versão de revisão 8.0.2.2.12 ou posterior.

Consulte Query the engine version para confirmar a versão do cluster.

Parâmetros

Configure os parâmetros a seguir no console para ativar o SQL detail e definir o período de retenção dos registros de auditoria. Para obter o procedimento de configuração de parâmetros, consulte Set cluster and node parameters.

Parâmetro	Nível	Descrição
loose_awr_sqldetail_enabled	Global	Ativa ou desativa o SQL detail. Valores válidos: OFF (padrão): desativa o SQL detail. ON: ativa o SQL detail.
loose_awr_sqldetail_switch	Global	Tipos de operação registrados pelo SQL detail. Subchaves: ddl: registra operações DDL. Valores válidos: ON (padrão): registra os detalhes das operações DDL. OFF: não registra os detalhes das operações DDL. lock_db_table: registra operações LOCK DB e LOCK TABLE. Valores válidos: ON (padrão): registra os detalhes das operações LOCK DB e LOCK TABLE. OFF: não registra os detalhes das operações LOCK DB e LOCK TABLE.
loose_awr_sqldetail_retention	Global	Período de retenção dos registros de auditoria. Registros anteriores a esse período são removidos automaticamente. Valores válidos: 0 a 18446744073709551615. Valor padrão: 2592000. Unidade: segundos.

Esquema da tabela

PolarDB for MySQL fornece uma tabela de sistema integrada sys.hist_sqldetail para armazenar registros de auditoria. O sistema cria essa tabela automaticamente durante a inicialização, sem necessidade de criação manual. O esquema da tabela é o seguinte:

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;

A tabela a seguir descreve as colunas da tabela de sistema.

Coluna	Descrição
Id	ID de incremento automático.
State	Estado da operação no momento da gravação do registro.
Thread_id	ID da thread que executou a instrução SQL.
Host	Host de origem da execução da instrução SQL.
User	Nome de usuário que executou a instrução SQL.
Client_ip	Endereço IP do cliente que executou a instrução SQL.
Db	Nome do banco de dados onde a instrução SQL foi executada.
Sql_text	Instrução SQL executada.
Server_command	Comando do servidor usado para executar a instrução SQL.
Sql_command	Tipo de comando da instrução SQL.
Start_time	Horário de início da execução da instrução SQL.
Exec_time	Duração da execução. Unidade: microssegundos.
Wait_time	Tempo de espera. Unidade: microssegundos.
Error_code	Código de erro.
Rows_sent	Quantidade de linhas retornadas.
Rows_examined	Quantidade de linhas verificadas.
Rows_affected	Quantidade de linhas afetadas.
Logical_read	Número de leituras lógicas.
Phy_sync_read	Número de leituras físicas síncronas.
Phy_async_read	Número de leituras físicas assíncronas.
Process_info	Campo estendido. Informações de processamento.
Extra	Campo estendido. Outras informações.
Create_time	Horário de gravação do registro.
Update_time	Horário da última atualização do registro.

Exemplo

No console, defina o parâmetro loose_awr_sqldetail_enabled como ON e execute as instruções a seguir no banco de dados.

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)

Execute a instrução a seguir para visualizar os registros de auditoria na tabela sys.hist_sqldetail.

select * from sys.hist_sqldetail\G

Saída:

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

A saída demonstra que o SQL detail registra informações de auditoria apenas para instruções DDL, LOCK DB e LOCK TABLE, sem registrar informações de auditoria para instruções DML. Além disso, o SQL detail grava um registro na tabela de sistema assim que a instrução SQL começa a executar e atualiza automaticamente campos como o estado no registro após a conclusão da instrução.