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.
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.
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
|
|
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
| |
All regions other than the preceding regions | SQL Explorer (formerly SQL Audit)
| The fee is billed to ApsaraDB RDS. |
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.
NoteIf 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.
NoteIf 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
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 .
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.
NoteIf 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.
NoteThe 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
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 .
Click Service Settings.
In the Service Settings dialog box, modify the storage duration and click OK.
NoteYou 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
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 .
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.
NoteIf 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.
The upgrade operation cannot be terminated or rolled back. Carefully read the upgrade instructions before you upgrade the feature.