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 AliyunRDSReadOnlyWithSQLLogArchiveAccess permissions are granted to the RAM user. For more information about how to grant permissions to a RAM user, 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 Use custom policies to grant a RAM user the permissions to use the search and export features in the SQL Explorer and Audit module .

Features

RegionFeatureBilling
China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Shenzhen), China (Zhangjiakou), China (Hohhot), China (Chengdu), China (Guangzhou), China (Heyuan), China (Ulanqab), China (Hong Kong), Singapore (Singapore), Malaysia (Kuala Lumpur), and Indonesia (Jakarta).
  • Search
  • SQL Explorer
  • Security audit
  • Traffic playback and stress testing
  • 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.
  • 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.
Regions other than the preceding regions, Philippines (Manila), Thailand (Bangkok), and South Korea (Seoul)
  • Search
  • Analysis
Fees for the Search and Analysis features are billed to PolarDB for MySQL. For more information, see Billing rules of SQL Explorer (optional).
  • Search: This feature is used to query and export the SQL statements that are executed and the information about the SQL statements. The information includes the database, status, and execution duration of each SQL statement.
  • SQL Explorer: This feature is used to perform health diagnoses on SQL statements that are executed within a specified 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 PolarDB cluster needs to be scaled up or scaled out to handle traffic spikes during peak hours.
  • Analysis: This feature is used to analyze SQL statements that are executed within a specified period of time to identify abnormal SQL statements and locate 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.
    • If your PolarDB for MySQL cluster is deployed in one of the following regions: China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Shenzhen), China (Zhangjiakou), China (Hohhot), China (Chengdu), China (Guangzhou), China (Heyuan), China (Ulanqab), China (Hong Kong), Singapore (Singapore), Malaysia (Kuala Lumpur), and Indonesia (Jakarta), click Enable to enable the SQL Explorer and Audit feature.
      Note If you have not enabled DAS Professional Edition for your Alibaba Cloud account, follow the on-screen instructions to enable DAS Professional Edition. For more information, see Purchase DAS Professional Edition.
    • If your PolarDB for MySQL cluster is deployed in a region other than the preceding regions, Philippines (Manila), Thailand (Bangkok), and South Korea (Seoul), click Official Edition, select a retention period, and then click OK to enable SQL Explorer and Audit.
      Note
      • The default value of the Storage Duration parameter for the SQL Explorer and Audit feature is 30 Days. You can change the value of this parameter in the Service Settings dialog box.
      • 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 relevant information about the feature.

Modify the retention period of SQL audit 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.
    Disable
    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.
    Billing details
  5. View the billing details in the data entries in which the value in the Billing Item column is sql_explorer.

FAQ

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

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. It indicates the connection timeout period. 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 connection to the client times out.
  • The server is unexpectedly 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.

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

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 instance by means of 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 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() |
+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+