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 for MySQL 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 for MySQL cluster.

Features

Region Feature Billing
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 for MySQL 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 for MySQL 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 Pricing of SQL Explorer (optional).
    Note You can perform the following operations to upgrade the SQL Explorer feature of your PolarDB for MySQL 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.
All regions other than the preceding regions
  • Search
  • Analysis
 Fees for the Search and Analysis features are billed to PolarDB for MySQL. For more information, see Pricing 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 for MySQL 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 resides in one of the following regions: PolarDB for MySQL, 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 on-screen instructions to enable DAS Professional Edition. For more information, see Purchase DAS Professional Edition.
    • If your PolarDB for MySQL cluster resides in a region rather than the preceding regions, click PolarDB for MySQL, configure the Storage Duration parameter, and then click OK to enable the SQL Explorer feature.
      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 for MySQL 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 SQL Explorer and Audit

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.
    Note To query logs generated more than 18 months ago, submit a ticket.
    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 interactive_timeout or wait_timeout.

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() |
+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+