All Products
Search
Document Center

PolarDB:️SQL Explorer and Audit

Last Updated:Aug 10, 2023

The SQL Explorer feature in PolarDB for MySQL is upgraded to SQL Explorer and Audit. The SQL Explorer and Audit feature is provided by Database Autonomy Service (DAS). This feature is developed based on the full request feature and the SQL Audit feature. In addition, this feature is integrated with the following features: Search, SQL Explorer, Security Audit, and Traffic Playback and Stress Test. This feature helps you obtain information about the SQL statements that are executed. You can use the information to troubleshoot various performance issues, identify the sources of high risks, and check whether you need to upgrade your PolarDB cluster.

Background information

The SQL Explorer and Audit feature records information about all Data Query Language (DQL), DML, and DDL statements that are executed. DAS obtains the information from database kernels, which consumes only a small amount of CPU resources. This topic describes how to use the SQL Explorer and Audit feature on a PolarDB cluster.

Prerequisites

If you use the Search feature as a RAM user, make sure that the AliyunPolardbReadOnlyWithSQLLogArchiveAccess permissions are granted to the RAM user. For more information, see Create and authorize a RAM user.

Note

You can also create a custom policy to grant the RAM user the permissions to use the search feature, including the log export feature. For more information, see the "Use custom policies to grant a RAM user the permissions to use the search and export features in the SQL Explorer and Audit module" section of the What do I do if I do not have permissions to access DAS as a RAM user? topic.

Features

Feature

Region

Billing

New SQL Explorer and Audit

  • SQL statement search

  • ️SQL Explorer

  • Security audit

China (Hangzhou), China (Shanghai), China (Beijing), China (Shenzhen), China (Zhangjiakou), China (Hong Kong), and Singapore (Singapore)

  • If the SQL Explorer and Audit feature is enabled for your PolarDB cluster after the SQL Explorer feature is upgraded to the SQL Explorer and Audit feature, the fee for the SQL Explorer and Audit feature is billed to DAS. For more information, see Billing overview.

  • If the SQL Explorer feature is enabled for your PolarDB cluster before the SQL Explorer feature is upgraded to the SQL Explorer and Audit feature, the fee for the SQL Explorer feature is billed to PolarDB for MySQL. For more information, see Billing rules of SQL Explorer (optional).

    Note
    • You can perform the following operations to upgrade the SQL Explorer feature of your PolarDB cluster to the SQL Explorer and Audit feature: Log on to the PolarDB console, go to the SQL Explorer tab, and then click Upgrade in the dialog box that appears. After the upgrade is complete, the fee for the SQL Explorer and Audit feature is billed to DAS.

    • For more information about the differences between new SQL Explorer and Audit and original SQL Explorer and Audit, see Billing overview.

    • If new SQL Explorer and Audit is supported in the region of your cluster, you can upgrade original SQL Explorer and Audit to new SQL Explorer and Audit. For more information, see Upgrade to new SQL Explorer and Audit.

Original SQL Explorer and Audit

  • SQL statement search

  • SQL Explorer

  • Security audit

  • Traffic playback and stress testing

China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Shenzhen), China (Zhangjiakou), China (Hohhot), China (Chengdu), China (Ulanqab), China (Hong Kong), Singapore (Singapore), Malaysia (Kuala Lumpur), and Indonesia (Jakarta)

️SQL Explorer

  • SQL statement search

  • Analytics

Regions other than the preceding regions, Philippines (Manila), Thailand (Bangkok), and South Korea (Seoul)

The fee is billed to PolarDB for MySQL. For more information, see Billing rules of SQL Explorer (optional).

  • SQL statement search: This feature is used to query and export information about the SQL statements that are executed. The information includes the execution history of the SQL statements and the database, status, and execution duration of each SQL statement.

  • SQL Explorer: This feature is used to perform health checks on SQL statements that are executed within a specific period of time, troubleshoot performance issues, and analyze business traffic.

  • Security audit: This feature is used to identify risks, such as high-risk SQL statements, SQL injection attacks, and new access sources.

  • Traffic playback and stress testing: This feature is used to check whether your cluster needs to be scaled to handle traffic spikes.

  • Analytics: This feature is used to analyze SQL statements that are executed within a specific period of time to identify abnormal SQL statements and troubleshoot performance issues.

Enable the SQL Explorer and Audit feature

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
  3. Find the cluster and click the cluster ID.
  4. In the left-side navigation pane, choose Log and Audit > SQL Explorer.

  5. Enable the SQL Explorer and Audit feature.

    • For original SQL Explorer and Audit or new SQL Explorer and Audit, click Enable to enable the SQL Explorer and Audit feature.

      Note

      If DAS Professional Edition is not activated for your Alibaba Cloud account, follow the on-screen instructions to activate DAS Professional Edition. For more information, see Purchase DAS Professional Edition.

    • For SQL Explorer, click Official Edition, configure the storage duration, and then click OK to enable the SQL Explorer and Audit feature.

      Note
      • The default storage duration is 30 Days for the SQL Explorer and Audit feature. You can click Service Settings to modify the storage duration .

      • PolarDB deletes all SQL audit logs that are stored for longer than the specified retention period.

  6. On the page that appears, click a tab based on your business requirements. Then, you can view the information about the feature.

    • For more information about the SQL statement search feature, see Search.

    • For more information about the SQL Explorer feature, see SQL Explorer.

    • For more information about the security audit feature, see Security audit.

    • For more information about the traffic playback and stress testing feature, see Traffic playback and stress testing.

Change the retention period of SQL logs

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
  3. Find the cluster and click the cluster ID.
  4. In the left-side navigation pane, choose Log and Audit > SQL Explorer.

  5. In the upper-right corner of the SQL Explorer page, click Service Settings.

  6. Modify the storage duration and click OK.

Disable the SQL Explorer and Audit feature

Note

After you disable the SQL Explorer and Audit feature, all SQL audit logs are deleted. Before you disable the SQL Explorer and Audit feature, we recommend that you export the SQL audit logs as a file and download the file to your computer. If you enable the SQL Explorer and Audit feature again, SQL audit logs are recorded from the point in time at which the SQL Explorer and Audit feature is enabled.

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
  3. Find the cluster and click the cluster ID.
  4. In the left-side navigation pane, choose Log and Audit > SQL Explorer.

  5. Click Service Settings.

  6. In the dialog box that appears, turn off the Enable switch and click Ok.

    关闭
    Note
    • If you enable the audit log collection feature for your PolarDB for MySQL cluster in the CloudLens for PolarDB application of Log Service, the SQL Explorer and Audit feature is automatically enabled for the PolarDB for MySQL cluster. Therefore, you must also disable the audit log collection feature for the PolarDB for MySQL cluster. For more information, see Enable data collection.

    • After you disable the SQL Explorer and Audit feature, all SQL log entries are deleted. We recommend that you export SQL log entries before you disable this feature. For more information about how to export SQL records, see Export SQL log entry.

  7. In the message that appears, click OK.

View the size and consumption details of audit logs

  1. Log on to the Alibaba Cloud Management Console.

  2. In the upper-right corner of the page, choose Expenses > User Center.

  3. In the left-side navigation pane, choose Spending Summary > Spending Summary.

  4. On the Bills page, click the Details tab. In the search bar, select Instance ID from the drop-down list and enter the ID of the cluster for which you want to query the details.

  5. View the billing details in the data entries in which the value in the Billing Item column is sql_explorer.

Upgrade to new SQL Explorer and Audit

Note

You can upgrade original SQL Explorer and Audit to new SQL Explorer and Audit if your PolarDB for MySQL cluster is deployed in the China (Hangzhou), China (Shanghai), China (Beijing), or China (Shenzhen) region.

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the console, select the region in which the cluster that you want to manage is deployed.
  3. Find the cluster and click the cluster ID.
  4. In the left-side navigation pane of the page that appears, choose Autonomy Service > SQL Explorer and Audit.

  5. Click Migrate to a new version in the upper-right corner.

  6. In the Migrate to a new version dialog box, configure the Data Storage Duration parameter and then click OK.

FAQ

Q: What does the logout! statement in the Full Request Statistics section on the SQL Explorer tab indicate?

A: The logout! statement indicates a disconnection. The execution duration of the logout! statement is the difference between the last interaction time and the time when the disconnection occurs. During the time difference, the connection remains idle. The 1158 code displayed in the Status column indicates network disconnection that may be caused by the following reasons:

  • The client connection times out.

  • The server is disconnected.

  • The connection to the server is reset if the duration of the connection exceeds the value specified by the interactive_timeout or wait_timeout parameter.

Q: Why does a percent sign (%) appear in the Access Source column on the Source Statistics tab of the SQL Explorer tab?

A: When you use a stored procedure, a percent sign (%) may be displayed in the Access Source column on the Source Statistics tab of the SQL Explorer tab. You can perform the following operations to reproduce this situation.

Note

In this example, the database instance is an ApsaraDB RDS for MySQL instance, the test account is test_user, and the test database is testdb.

  1. In the ApsaraDB RDS console, create a database and a standard account and grant permissions on the database to the standard account. For more information, see Create databases and accounts for an ApsaraDB RDS for MySQL instance.

  2. Use the test_user account to connect to the database instance by using the CLI. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

  3. Switch to the testdb database and create a stored procedure by executing the following statements:

    -- Switch to the testdb database.
    USE testdb;
    
    -- Create a stored procedure.
    DELIMITER $$
    DROP PROCEDURE IF EXISTS `das` $$
    CREATE DEFINER=`test_user`@`%` PROCEDURE `das`()
    BEGIN
    SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID();
    END $$
    DELIMITER;
  4. Use a privileged account to connect to the database instance. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

  5. Call the stored procedure that you created.

    -- Switch to the testdb database.
    USE testdb;
    
    -- Call the stored procedure.
    CALL das();
    
    +--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+
    | ID     | USER      | HOST   | DB     | COMMAND | TIME | STATE     | INFO                                                                    |
    +--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+
    | 487818 | test_user | %:2065 | testdb | Query   |    0 | executing | SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID() |
    +--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+