We recommend that you enable the SQL Explorer and Audit feature in scenarios such as security compliance audit, performance analysis, and troubleshooting. After you enable the feature, the system automatically records the SQL changes in database kernels and information about the SQL changes, including the accounts that are used to execute the SQL statements, IP addresses, and execution details. This provides reliable data support for you to query SQL change records and perform analysis and audit operations. Enabling and using the feature has the minimal impact on instance performance.
Prerequisites
Database Autonomy Service (DAS) Enterprise Edition is purchased by using your Alibaba Cloud account.
You can use the SQL Explorer and Audit feature only after you activate DAS Enterprise Edition. In the ApsaraDB RDS console, you can enable only the SQL Explorer and Audit feature provided by the most recent version of DAS Enterprise Edition that is supported in the current region. Different versions of DAS Enterprise Edition are supported in different regions.
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 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 .
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
The SQL Explorer and Audit feature provides the following capabilities:
Search (audit): This feature is used to query and export information about the SQL statements that are executed. The information includes the database, status, and execution time.
SQL Explorer: diagnoses the health status of SQL statements, troubleshoots performance issues, and analyzes business traffic.
Security audit: identifies risks such as high-risk SQL statements, SQL injection attacks, and new access sources.
Traffic playback and stress testing: checks whether your RDS instance needs to be scaled out to handle traffic spikes.
SQL analysis: analyzes SQL statements that are executed within a specified period of time to identify abnormal SQL statements and locates performance issues.
The SQL Explorer and Audit feature is suitable for the following 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 scenarios, such as issue troubleshooting, check on the performance of SQL statements, and abnormal session identification.
In extreme cases, if data loss or corruption occurs, you can use the SQL statements recorded by the SQL Explorer and Audit feature to analyze and troubleshoot issues and restore data.
Supported regions
China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Shenzhen), China (Heyuan), China (Guangzhou), China (Chengdu), China (Hong Kong), Singapore, Japan (Tokyo), Malaysia (Kuala Lumpur), Indonesia (Jakarta), US (Silicon Valley), UK (London), US (Virginia), and Germany (Frankfurt)
Billing rules
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 is included in the bills of ApsaraDB RDS. The pricing of the feature varies based on the instance region. Bills are generated on an hourly basis.
USD 0.0015 per GB-hour: China (Hong Kong), US (Silicon Valley), and US (Virginia)
USD 0.0018 per GB-hour: Singapore, Japan (Tokyo), Germany (Frankfurt), UAE (Dubai), Malaysia (Kuala Lumpur), Indonesia (Jakarta), and UK (London)
USD 0.0012 per GB-hour: China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Zhangjiakou), China (Hohhot), China (Ulanqab), China (Shenzhen), China (Heyuan), China (Guangzhou), and China (Chengdu).
NoteYou 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 One click upgrade in the dialog box that appears. After the upgrade is complete, the fee is included in the bills of Database Autonomy Service (DAS).
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 is included in the bills of DAS. You can use the SQL Explorer and Audit feature only after you activate DAS Enterprise Edition. The supported regions and billing rules vary based on the version of DAS Enterprise Edition. For more information, see DAS editions and supported features and Billing overview.
NoteIn the ApsaraDB RDS console, you can enable only the SQL Explorer and Audit feature provided by the most recent version of DAS Enterprise Edition that is supported in the current region.
Usage notes
Online query
Time range: The time range for an online query spans up to 24 hours. You can query data of any 24-hour period within the data storage duration of SQL Explorer. If the time range for an online query exceeds 24 hours, the query may time out. If you want to query the execution records of SQL statements over a time range that exceeds 24 hours, you can use Simple Log Service to access logs that are generated by the SQL Explorer feature. For more information, see Collect RDS SQL audit logs.
Query method: You can specify a combination of conditions for an online query. Fuzzy match is not supported for online queries. Each keyword for an online query must contain at least four characters.
SQL Explorer and Audit
Maximum length of an SQL statement: 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 theloose_rds_audit_max_sql_size
for MySQL 5.6 and MySQL 5.7 orloose_rds_audit_log_event_buffer_size
parameter for MySQL 8.0. The minimum value among the three parameter values is used. 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 or the value that you specified.Audit log query: The feature allows you to query SQL statements that are executed on your RDS instance by thread ID and transaction ID. If you want to query SQL statements that are executed on your RDS instance by transaction ID, you must set the
loose_rds_audit_log_version
parameter toMYSQL_V3
and make sure that the minor engine version meets the requirements. If your RDS instance runs MySQL 8.0, the RDS instance must run a minor engine version of 20210930 or later. If your RDS instance runs MySQL 5.7, the RDS instance must run a minor engine version of 20210630 or later. For more information, see Parameters supported by ApsaraDB RDS instances that run MySQL 8.0 and Update the minor engine version.SQL Explorer Trial Edition: If you use SQL Explorer Trial Edition, you cannot call the DescribeSQLLogRecords and DescribeSQLLogFiles operations to query audit logs. For more information, see DescribeSQLLogRecords and DescribeSQLLogFiles.
Lock wait time: The lock wait time is recorded in the SQL Explorer log but not recorded in the slow query log.
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 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 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
If you enable the audit log collection feature for your RDS instance in the CloudLens for RDS application of Simple Log Service, the SQL Explorer and Audit feature is automatically enabled for the RDS instance. For more information, see CloudLens for RDS.
If the SQL Explorer and Audit feature is disabled for your RDS instance and you want to view SQL execution records, you can view the binary logs of the RDS instance. However, in binary logs, you can query only the SQL statements that are executed to add, delete, and modify data within the backup retention period. The information about source IP addresses and accounts cannot be queried. For more information, see Manage binary log files.
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 .
Click Enable DAS Enterprise Edition V3.
Select the sub-features that you want to enable and click Submit.
Modify the storage duration of data generated by the SQL Explorer and Audit feature
After you reduce the storage duration of the data that is generated by the SQL Explorer and Audit feature, 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 the SQL Explorer and Audit feature.
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 .
Click Service Settings.
In the Service Settings panel, modify the storage duration of logs generated by the SQL Explorer and Audit feature and click OK.
Disable the SQL Explorer and Audit feature
After you disable the SQL Explorer and Audit feature, all SQL audit logs are deleted. We recommend that you export and save the logs. If you enable the SQL Explorer and Audit feature again, logs that are generated by the SQL Explorer and Audit 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
.In the Logs section of the Search tab, click Export.
NoteYou can export up to 10 million data records within seven days at a time.
You can configure the Export Time Range parameter to export logs generated in a large time range.
In the dialog box that appears, configure the Exported Fields and Export Time Range parameters, and click OK.
After the logs are exported, download the log file and save it to your computer.
Click Service Settings. In the dialog box that appears, disable the SQL Explorer and Audit feature.
If you have activated DAS Enterprise V3, clear all features of the SQL Explorer and Audit module. Click Submit.
NoteThe storage space that is occupied by the data generated by the SQL Explorer and Audit module is released one hour after the SQL Explorer and Audit module is disabled.
Migrate data between different versions of DAS Enterprise Edition
Data migration cannot be terminated or rolled back. Read the migration instructions carefully before you migrate data.
If your RDS instance supports DAS Enterprise Edition V3, you can migrate data from DAS Enterprise Edition V1 or DAS Enterprise Edition V2 to DAS Enterprise Edition V3 to reduce costs. For more information, see How do I migrate data between different versions of DAS Enterprise Edition?.
Compared with DAS Enterprise Edition V1, the underlying storage architecture of DAS Enterprise Edition V2 is changed. The hybrid storage of hot and cold data is used to increase performance at lower costs. DAS Enterprise Edition V3 uses the hybrid storage of hot and cold data and subdivides the billable items by feature to achieve more flexible billing.