DocsICP FilingConsole
官方文档
首页

SQL Explorer and Audit

更新时间：
复制 MD 格式
产品详情
我的收藏

For security compliance auditing, performance analysis, and troubleshooting, we recommend enabling SQL Explorer and Audit. After this feature is enabled, the system automatically records SQL statements executed by the database kernel, along with related information such as the executing account, IP address, and execution details. This feature has a negligible impact on instance performance, providing a reliable data source for querying historical SQL execution records and for subsequent analysis and auditing.

Prerequisites

Important

SQL Explorer and Audit can record all DQL, DML, and DDL operations. This information is collected from the database kernel and has minimal impact on CPU utilization.

Feature overview

SQL Explorer and Audit provides features such as audit, SQL Explorer, security audit, traffic playback and stress testing, and SQL analysis.

  • Audit: Queries and exports the execution history of SQL statements and related information, such as the database, status, and execution time.

  • SQL Explorer: Diagnoses the health of SQL statements within a specific time range, troubleshoots performance issues, and analyzes service traffic.

  • Security audit (Legacy): Automatically identifies risks such as high-risk SQL statements, SQL injection, and new access sources.

  • Traffic playback and stress testing: Provides traffic playback and stress testing to help you determine if you need to scale up your instance specifications to handle traffic spikes.

  • SQL analysis: Analyzes SQL statements within a specified period to identify abnormal SQL statements and locate performance issues.

SQL Explorer and Audit is often used in the following scenarios:

  • Industries with strict data security requirements, such as finance, security, securities, government, and insurance.

  • Scenarios that require detailed investigation of database operations, such as issue troubleshooting, SQL performance analysis, and abnormal session identification.

  • In extreme cases of data loss or corruption, you can use the SQL statements recorded by SQL Explorer to analyze issues and recover 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

  • If you purchased an instance and enabled the SQL Explorer feature before its upgrade to SQL Explorer and Audit: ApsaraDB RDS charges the feature fee on an hourly basis. The price varies based on the instance region. You can use storage plans and savings plans to offset the fees.

    • CNY 0.0122 per GB/hour: China (Hong Kong) and Singapore.

    • CNY 0.008 per GB/hour: All other regions except China (Hong Kong) and Singapore.

    Note

    In the upgrade dialog box that appears on the SQL Explorer tab, you can click One-click Upgrade to upgrade to SQL Explorer and Audit. After the upgrade, the fees are charged by DAS.

  • If you enable the SQL Explorer and Audit feature after the upgrade from SQL Explorer: The fees are charged by DAS. You must enable Database Autonomy Service (DAS) Enterprise Edition to use the SQL Explorer and Audit feature. The supported regions and billing methods vary based on the edition. For more information, see Supported databases and regions for each edition and Billing of each edition.

    Note

    In the console, you can enable only the latest version of DAS Enterprise Edition that is supported in the current region.

Usage notes

  • Online query:

    • Time range: A single online query can span a maximum of 24 hours. You can query data for any 24-hour period within the data retention period. If the specified time range is too long, the query may time out. If you need to query SQL records over a longer time range, use Simple Log Service (SLS) to access the explorer logs. For more information, see Collect ApsaraDB RDS for MySQL audit logs.

    • Query method: Online queries support combined conditions but not fuzzy searches. Each query keyword must contain at least four characters.

  • SQL Explorer and Audit:

    • Maximum length of an SQL statement: The maximum length of an SQL statement recorded by SQL Explorer and Audit is 8,192 bytes. This limit is also affected by the loose_rds_audit_max_sql_size parameter (for MySQL 5.6/5.7) or the loose_rds_audit_log_event_buffer_size parameter (for MySQL 8.0/8.4). The effective limit is the smallest of these values. Because a prefix is added during information collection, the actual recorded length of an SQL statement is slightly less than 8,192 bytes or the value specified by the parameter.

    • Audit record query: You can query SQL statement records by thread ID and transaction ID. To query by transaction ID, you must set the loose_rds_audit_log_version parameter to MYSQL_V3. This feature requires a minimum minor engine version: all versions of MySQL 8.4 are supported, MySQL 8.0 must be 20210930 or later, and MySQL 5.7 must be 20210630 or later. For more information, see Parameters available for ApsaraDB RDS for MySQL 8.0 instances and Upgrade the minor engine version.

    • Trial version of SQL Explorer: If you are using the trial version of SQL Explorer, you cannot call the DescribeSQLLogRecords and DescribeSQLLogFiles API operations to query audit logs.

    • Lock wait time: The explorer log contains the lock wait time, whereas the slow query log does not.

    • When an application uses the Prepare method, two statements appear in SQL Explorer: one with a question mark and one with a specific value.

  • If you connect to an ApsaraDB RDS instance by using a database proxy endpoint and the transaction-level connection pool is enabled for the proxy, connections may be reused. As a result, the IP address and port displayed by the show processlist command or in SQL Explorer may not match the client's actual IP address and port.

  • When an SQL statement is executed on an ApsaraDB RDS for MySQL instance that is attached to a PolarDB-X 1.0 instance, multiple SQL Explorer and Audit logs are generated on the ApsaraDB RDS for MySQL instance due to horizontal sharding.

  • Log integrity: In the following extreme scenarios, SQL Explorer and Audit logs may be incomplete or partially lost:

    • The instance is under high load and experiences performance bottlenecks.

    • The instance generates a large number of SQL requests per unit of time.

    • The control service component responsible for audit log collection is not working correctly.

Enable SQL Explorer and Audit

Note

  • If you have enabled the audit log collection feature for your ApsaraDB RDS for MySQL instance in CloudLens for RDS of Simple Log Service (SLS), the SQL Explorer and Audit feature is automatically enabled for that instance.

  • If you have not enabled SQL Explorer and Audit, you can view SQL execution records by using binary logs. Binary logs contain only INSERT, DELETE, and UPDATE statements within the backup retention period and do not provide source IP address or account information.

  1. 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.

  2. In the left-side navigation pane, choose Autonomy Services > SQL Explorer and Audit.

  3. Click Enable DAS Enterprise Edition V3.

  4. Select the features that you want to enable and click Activate.

Modify data retention period

Warning

If you reduce the data retention period for SQL Explorer and Audit, DAS immediately deletes all SQL audit logs that exceed the new retention period. We recommend exporting and saving the SQL audit logs to your local machine before you reduce the data retention period.

  1. 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.

  2. In the left-side navigation pane, choose Autonomy Services > SQL Explorer and Audit.

  3. Click Service Settings.

  4. On the Service Settings page, modify the log retention period for the Audit Scenarios and click Submit.

Disable SQL Explorer and Audit

Warning

When you disable SQL Explorer and Audit, all logs are cleared. We recommend exporting and saving the logs first. If you re-enable the feature, logs are recorded starting from the time of re-enabling.

  1. 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.

  2. In the left-side navigation pane, choose Autonomy Services > SQL Explorer and Audit.

  3. On the Audit tab, in the Logs section, click Export.

  4. In the dialog box that appears, select the fields to export, specify an export time range, and then click OK.

    Note

    • You can export up to 10 million data records within a seven-day period in a single operation.

    • You can set the Export Time Range to export SQL records over a longer time range.

  5. After the export is complete, download the exported file and save it to a secure location.

  6. Click Service Settings to disable SQL Explorer and Audit.

    If you have enabled DAS Enterprise Edition V3, clear the checkboxes for all SQL Explorer and Audit features and click Submit.

    Note

    About one hour after you disable the SQL Explorer and Audit feature, the system releases the storage space occupied by the SQL Explorer and Audit data.

Migrate data between Enterprise versions

Warning

The migration operation cannot be terminated or rolled back. Read the migration instructions carefully.

If your database instance supports Enterprise Edition V3, you can migrate data from DAS Enterprise Edition V1 or V2 to Enterprise Edition V3 to benefit from more favorable pricing. For more information, see How do I migrate data between DAS Enterprise Edition versions?

Compared with V1, DAS Enterprise Edition V2 changes the underlying storage architecture to a hybrid hot/cold storage model to reduce costs. DAS Enterprise Edition V3 builds on this hybrid storage by further breaking down billing items by function, offering more flexible billing.

FAQ

Q: ️SQL Explorerlogout! statement mean in the Full Request Statistics section of ️SQL Explorer?

A: logout! indicates a disconnection. The duration of a logout! event is the time difference between the last interaction and the moment the logout! occurred. This duration can be interpreted as the connection idle time. A status of 1158 in the Status column indicates a network disconnection, which can be caused by the following:

  • The client connection timed out.

  • The server disconnected unexpectedly.

  • The server reset the connection because the interactive_timeout or wait_timeout duration was exceeded.

Q: ️SQL ExplorerSource Statistics section of ️SQL Explorer, why does a percent sign (%) appear as an Access Source?

A: This can occur when you use a stored procedure. You can reproduce this behavior by following the example below:

Note

This example uses an ApsaraDB RDS for MySQL instance, a test account named test_user, and a test database named testdb.

  1. In the ApsaraDB RDS console, create a standard account and authorize it to access a database. For more information, see (Deprecated, redirected to "Step 1") Create a database and an account.

  2. Use the test account to connect to the database instance from the command line. For more information, see (Deprecated, redirected to "Step 2") Connect to an ApsaraDB RDS for MySQL instance by using a client or the CLI.

  3. Switch to the test database and create the following stored procedure.

    -- Switch to the test 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 database instance. For more information, see (Deprecated, redirected to "Step 2") Connect to an ApsaraDB RDS for MySQL instance by using a client or the CLI.

  5. Call the stored procedure.

    -- Switch to the test 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() |
+--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+

Q: Why is the scanned row count 0 even when data is returned?

A: This is because the query_cache_type feature is enabled on the database instance. The scanned row count in the log list reflects the number of rows scanned at the InnoDB engine layer. When query_cache_type is enabled, MySQL attempts to cache query results. If a subsequent identical query request hits the cache, the result is returned directly from the cache without accessing the InnoDB layer. Therefore, data is returned, but the scanned row count is 0. For more information, see Fast Query Cache.

Q: What is the difference between SQL Explorer and binary logs?

You can view incremental data for ApsaraDB RDS for MySQL instances by using SQL Explorer or the binary log. The differences are as follows:

  • SQL Explorer: Suitable for capturing all incremental data from the database. However, under very high instance loads, a small number of records may be lost, which can lead to inaccuracies in incremental data statistics. It can record all DQL, DML, and DDL operations. This information is collected from the database kernel and has minimal impact on CPU utilization.

  • Binary log: Suitable for obtaining accurate incremental data over a short period, but does not provide real-time log information. It accurately records all INSERT, DELETE, and UPDATE operations and can be used to restore incremental user data. Binary logs are temporarily stored on the instance. The system periodically transfers completed binary log files to Object Storage Service (OSS), where they are retained for 7 days. Binary log files that are actively being written to cannot be saved. As a result, some binary logs may not be uploaded after you click to upload them. For more information, see Remotely obtain and parse the binary log of an ApsaraDB RDS for MySQL instance.

Q: Why did the SQL Explorer entry disappear from the console?

A: The entry for the latest version has been renamed to SQL Explorer and Audit.

Q: Can I enable the old version of SQL Explorer?

A: You can only enable the latest version of SQL Explorer and Audit that is supported by your instance.

Q: Are original records available after export?

A: Yes. The export operation does not delete the original audit records.

Q: How to view historical connection IPs?

A: For more information, see How do I view the IP addresses that access an ApsaraDB RDS instance?.

Previous：Query governanceNext：Optimization and throttling