Use the SQL Explorer and Audit feature - ApsaraDB RDS - Alibaba Cloud Documentation Center

In ApsaraDB RDS for MySQL, the SQL Explorer feature is upgraded to the SQL Explorer and Audit feature. Database Autonomy Service (DAS) provides the SQL Explorer and Audit feature for ApsaraDB RDS for MySQL. This feature is developed based on the full request analysis and security audit capabilities. In addition, this feature integrates with SQL statement search, SQL Explorer, security audit, and traffic playback and stress testing. 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 verify instance specifications.

Prerequisites

Your RDS instance runs RDS High-availability Edition, RDS Enterprise Edition, or RDS Cluster Edition.
If you use the credentials of a RAM user to use the SQL statement search feature, make sure that the AliyunRDSReadOnlyWithSQLLogArchiveAccess policy is attached to the RAM user. For more information about how to grant permissions to a RAM user, see Use RAM to manage ApsaraDB RDS permissions.
You can also create a custom policy to grant the RAM user the permissions to use the SQL statement 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.

Important

The SQL Explorer and Audit feature records information about all Data Query Language (DQL), DML, and DDL statements that are executed. The system obtains the information from database kernels, which consumes a small number of CPU resources.

Feature description

ApsaraDB RDS for MySQL instances support the new version of the SQL Explorer and Audit feature. Compared with the original version of the SQL Explorer and Audit feature, the new version uses mixed storage of hot data and cold data. The new version helps reduce costs and increase efficiency. For more information, see Billing overview.

For more information about the differences between the original version and new version of the SQL Explorer and Audit feature, see What are the differences between the original SQL Explorer and Audit feature and the new Explorer and Audit feature?
For more information about how to upgrade the SQL Explorer and Audit feature from the original version to the new version, see Upgrade the SQL Explorer and Audit feature from the original version to the new version.

Important

You can upgrade the SQL Explorer and Audit feature from the original version to the new version if your RDS instance resides in the China (Hangzhou), China (Shanghai), China (Beijing), or China (Shenzhen) region.

Region	Supported feature	Billing
China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Shenzhen), China (Guangzhou), China (Heyuan), China (Zhangjiakou), China (Ulanqab), China (Hong Kong), and Singapore	New version of SQL Explorer and Audit SQL statement search SQL Explorer Security audit Traffic playback and stress testing	If the SQL Explorer and Audit feature is enabled for your RDS instance 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 RDS instance before the SQL Explorer feature is upgraded to the SQL Explorer and Audit feature, the fee for the SQL Explorer feature is billed to ApsaraDB RDS. Note You can perform the following operations to upgrade the SQL Explorer feature to the SQL Explorer and Audit feature: Log on to the ApsaraDB RDS 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.
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, Malaysia (Kuala Lumpur), and Indonesia (Jakarta)	Original version of SQL Explorer and Audit SQL statement search SQL Explorer Security audit Traffic playback and stress testing
All regions other than the preceding regions	SQL Explorer (formerly SQL Audit) SQL statement search Analytics	The fee is billed to ApsaraDB RDS.

Note

If the fee for the SQL Explorer and Audit feature is billed to ApsaraDB RDS, the fee is deducted every hour. The price varies based on the region of your RDS instance.

USD 0.0015 per GB per hour: China (Hong Kong), US (Silicon Valley), and US (Virginia)
USD 0.0018 per GB per hour: Singapore, Japan (Tokyo), Germany (Frankfurt), UAE (Dubai), Australia (Sydney), Malaysia (Kuala Lumpur), India (Mumbai), Indonesia (Jakarta), and UK (London)
USD 0.0012 per GB per hour: China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Shenzhen), China (Heyuan), China (Guangzhou), China (Chengdu), Thailand (Bangkok), South Korea (Seoul), and Philippines (Manila)

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 RDS instance 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.

Differences between SQL Explorer logs and binary logs

SQL Explorer logs and binary logs contain information about the incremental changes of your RDS instance. The two types of logs differ in the following aspects:

SQL Explorer logs include information about all DQL, DML, and DDL operations that are executed. The system obtains the information that consumes a small amount of CPU resources from database kernels. If your RDS instance is heavily loaded, a small number of records may be lost. As a result, the incremental data that is obtained from SQL Explorer logs may be inaccurate.
Binary logs record all add, delete, and modify operations that are performed and the incremental data used to restore data. Binary log files are temporarily stored on your RDS instance. The system periodically transfers the binary log files to which data is written to an Object Storage Service (OSS) bucket. Binary log files can be stored for seven days in the OSS bucket. Binary log files to which data is being written cannot be transferred to an OSS bucket. Therefore, after you use the Upload Binlogs feature to upload binary logs files to an OSS bucket, some binary log files fail to be uploaded to the OSS bucket. Binary logs are not generated in real time. However, you can use binary log files to obtain accurate incremental data.

Scenarios

Your RDS instance is used for sectors that require high data security, such as finance, security, stocks, public service, and insurance sectors.
You want to analyze the status of your RDS instance to troubleshoot issues in extreme scenarios and check the performance of SQL statements.
You want to restore your RDS instance by using the information about SQL statements that is generated by the SQL Explorer feature.

Usage notes

The time range for an online query spans up to 24 hours. The SQL Explorer feature records a large number of SQL statements. You can use the feature to trace all operations that are performed on your RDS instance. If the time range for an online query exceeds 24 hours, the query may time out or the query results are returned within a long period of time.
Note
If you want to query the execution records of SQL statements over a time range that exceeds 24 hours, you can use Log Service to access logs that are generated by the SQL Explorer feature. For more information, see Collect RDS SQL audit logs.
You can specify a combination of conditions for an online query. For example, you can enter test1 test2 in the Keywords field to query the SQL logs that contain the test1 or test2 keywords.
Fuzzy match is not supported for online queries.
Each keyword for an online query must contain at least four characters.
An SQL statement that is recorded by using the SQL Explorer and Audit feature can be up to 8,192 bytes in length. The maximum length is specified by the loose_rds_audit_max_sql_size or loose_rds_audit_log_event_buffer_size parameter. Valid values of the parameters: 0 to 32768. Unit: bytes.
- If you specify a value that is less than or equal to 8192 for one of the preceding parameters and the length of the SQL statement exceeds the specified parameter value, the excess part is truncated. A prefix is added to the SQL statement during data collection and processing. As a result, the maximum length of the SQL statement is slightly less than the specified parameter value.
- If you specify a value that is greater than 8192 for one of the preceding parameters, the maximum length of the SQL statement is 8,192 bytes. If the maximum length exceeds 8,192 bytes, the excess part is truncated. A prefix is added to the SQL statement during data collection and processing. As a result, the maximum length of the SQL statement is slightly less than 8,192 bytes.
Note
- If your RDS instance runs MySQL 5.6 or MySQL 5.7, you can use the loose_rds_audit_max_sql_size parameter to specify the maximum length of an SQL statement.
- If your RDS instance runs MySQL 8.0, you can use the loose_rds_audit_log_event_buffer_size parameter to specify the maximum length of an SQL statement.
If you use SQL Explorer Trial Edition, you cannot call the DescribeSQLLogRecords or DescribeSQLLogFiles operation to query audit logs.
The lock wait time is recorded in the SQL Explorer log but not recorded in the slow query log.
If you use the database proxy endpoint to connect to your RDS instance and the transaction-level connection pool feature is enabled for the database proxy, the connection may be reused. As a result, the IP address and port of the client may be different from the IP address and port that are returned by the SHOW PROCESSLIST statement or are displayed on the SQL Explorer tab. For more information, see What are database proxies?
If you use the Prepare method, the SQL Explorer feature records two SQL statements. One statement contains a question mark (?) and another statement contains a specific value.
If an SQL statement is executed on your RDS instance that is attached to a PolarDB-X 1.0 instance, multiple logs are generated by the SQL Explorer and Audit feature for the RDS instance due to the horizontal sharding of databases and tables.

Enable the SQL Explorer and Audit feature

Note

If you enable the audit log collection feature for your RDS instance in the CloudLens for RDS application of Log Service, the SQL Explorer and Audit feature is automatically enabled for the RDS instance. For more information, see CloudLens for RDS.

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane of the page that appears, choose Autonomy Services > SQL Explorer and Audit.
Enable the SQL Explorer feature.
- If your RDS instance resides 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, Malaysia (Kuala Lumpur), and Indonesia (Jakarta), click Enable to enable the SQL Explorer and Audit feature.
  Note
  If DAS Professional Edition is not purchased within your Alibaba Cloud account, follow the instructions to purchase DAS Professional Edition. For more information, see Purchase DAS Professional Edition.
- If your RDS instance resides in a region other than the preceding regions, 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 .
- The system deletes all SQL logs that are stored for longer than the specified retention period.
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 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 Test feature, see Traffic Playback and Stress Test.

Modify the storage duration of data generated by SQL Explorer and Audit

Warning

After you reduce the storage duration of the data that is generated by SQL Explorer and Audit, DAS immediately deletes the SQL audit logs that are retained for a longer period of time than the storage duration. We recommend that you export and save SQL audit logs to your computer and then reduce the storage duration of the data that is generated by SQL Explorer and Audit.

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane of the page that appears, choose Autonomy Services > SQL Explorer and Audit.
Click Service Settings.
In the Service Settings dialog box, modify the storage duration and click OK.
Note
- You can view the size of logs that are generated by the SQL Explorer and Audit feature in the Collected and Analyzed SQL Statements: section.
- You can also view the size of logs that are generated by the SQL Explorer and Audit feature in the Usage Statistics section of the Basic Information page.
- You can use the SQL statement search feature to query and export the information about logs that are generated by the SQL Explorer and Audit feature.

Disable the SQL Explorer and Audit feature

Warning

After you disable the SQL Explorer and Audit feature, all logs that are generated by the feature are deleted. We recommend that you export and save the logs that are generated by the SQL Explorer and Audit feature to your computer and then disable the SQL Explorer and Audit feature. If you enable the SQL Explorer and Audit feature again, logs that are generated by the feature are recorded from the point in time at which the SQL Explorer and Audit feature is enabled again.

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane of the page that appears, choose Autonomy Services > SQL Explorer and Audit.
In the Logs section of the Search tab, click Export.
In the dialog box that appears, configure the Exported Fields and Export Time Range parameters and click OK.
After the logs are exported, click View Exported Logs to download the log file that you exported to your computer.
In the Service Settings panel, turn off SQL insight and audit, select After you disable SQL Explorer, all relevant data is deleted., then click Disable the service.
Note
If you enable the audit log collection feature for your RDS instance in the CloudLens for RDS application of Log Service, the SQL Explorer and Audit feature is automatically enabled for the RDS instance. Therefore, you must also disable the audit log collection feature for the RDS instance. For more information, see CloudLens for RDS.

Upgrade the SQL Explorer and Audit feature from the original version to the new version

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane of the page that appears, choose Autonomy Services > SQL Explorer and Audit.
Click Migrate to a new version.
In the Migrate to a new version dialog box, configure the Data Storage Duration parameter and click OK.

Warning

The upgrade operation cannot be terminated or rolled back. Carefully read the upgrade instructions before you upgrade the feature.

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.

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 accounts and databases.
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.

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;

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.

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

After I execute some SQL statements on my RDS instance, data is returned but the Logs section on the Search tab on the SQL Explorer and Audit page displays zero scanned rows. Why?

The fast query cache is enabled for the RDS instance. In the Logs section, the number of scanned rows for an SQL statement is the number of rows that are scanned on the InnoDB storage engine. After the fast query cache is enabled, MySQL caches the query results. If the same query request is sent and hits the query cache, the system directly returns the cached query result instead of sending the query request to the InnoDB storage engine. Therefore, after you execute some SQL statements, data is returned but the Logs section displays zero scanned rows For more information, see Fast query cache.