All Products
Search

This Product

    ApsaraDB RDS:Use the SQL Explorer and Audit feature

    Document Center

    ApsaraDB RDS:Use the SQL Explorer and Audit feature

    Last Updated:Nov 15, 2024

    If you want to monitor and manage SQL statements that are executed on an ApsaraDB RDS for MySQL instance in scenarios such as security compliance audit, performance analysis, and troubleshooting, you can use the SQL Explorer and Audit feature. The feature enables you to record and perform aggregate analysis on the execution statuses of SQL statements. After this feature is enabled, SQL statements from database kernels, the accounts that are used to execute the SQL statements, IP addresses, and execution details are automatically recorded. Instance performance is not affected.

    Prerequisites

    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 and billing rules

    Region

    Supported feature

    Billing

    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)

    SQL Explorer and Audit

    • Search

    • SQL Explorer

    • Security audit

    • Traffic playback and stress testing

    • If you enable the SQL Explorer and Audit feature after the SQL Explorer feature is upgraded to the SQL Explorer and Audit feature, the fee for the SQL Explorer and Audit feature is included in the bills of Database Autonomy Service (DAS). After you enable DAS Enterprise Edition, you can use the SQL Explorer and Audit feature.

      • DAS provides different versions of DAS Enterprise Edition. The version varies based on regions. For more information, see Supported databases and regions.

      • The pricing of each version of DAS Enterprise Edition is different. For more information, see Billing overview.

      Important

      In the ApsaraDB RDS console, you can only enable the most recent version of DAS Enterprise Edition that is supported in the current region. The upgrade to DAS Enterprise Edition introduces more flexible billing methods.

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

      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 One click upgrade in the dialog box that appears. After the upgrade is complete, the fee is included in the bills of DAS.

    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), 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).

    • Search: queries and exports 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.

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

    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 the data of your RDS instance by using the logged information about executed SQL statements in extreme circumstances.

    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

      • You can query data of any 24-hour period within the data storage duration of SQL Explorer.

      • 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 SQL audit logs of an ApsaraDB RDS instance.

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

    • The audit log feature allows you to query SQL statements that are executed on your RDS instance by thread ID and transaction ID.

      Note

      • If you want to query SQL statements that are executed on your RDS instance by thread ID, you must set the loose_rds_audit_log_version parameter to MYSQL_V3. For more information, see Parameters supported by ApsaraDB RDS instances that run MySQL 8.0.

      • If you want to query SQL statements executed on your RDS instance that runs MySQL 8.0 by thread ID, you must make sure that the RDS instance runs a minor engine version of 20210930 or later. If you want to query SQL statements executed on your RDS instance that runs MySQL 5.7 by thread ID, you must make sure that the RDS instance runs a minor engine version of 20210630 or later.

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

    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 of the page that appears, choose Autonomy Services > SQL Explorer and Audit.

    3. Click Enable Enterprise Edition V3.

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

    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.

    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 of the page that appears, choose Autonomy Services > SQL Explorer and Audit.

    3. Click Service Settings.

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

    Warning

    After you disable the SQL Explorer and Audit feature, all logs that are generated by the SQL Explorer and Audit 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 SQL Explorer and Audit feature are recorded from the point in time at which the SQL Explorer and Audit feature is enabled.

    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. In the Logs section of the Search tab, click Export.

    4. In the dialog box that appears, configure the Exported Fields and Export Time Range parameters, and click OK.

    5. After the logs are exported, download the log file and save it to your computer.

    6. In the Service Settings panel, clear all sub-features of the SQL Explorer and Audit feature and click Submit.

    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.

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

    Warning

    Data migration cannot be terminated or rolled back. Read the migration instructions carefully before you migrate data.

    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.

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

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

    3. Switch to the testdb database and execute the following statements to create a stored procedure: 

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

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

    What are the differences between SQL Explore logs and binary logs?

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

    • SQL Explorer logs are suitable for scenarios in which you want to obtain all incremental data in a database. If your RDS instance is heavily loaded, a small number of records are lost. As a result, the incremental data that is obtained from SQL Explorer logs may be inaccurate. 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.

    • Binary logs are suitable for scenarios in which you want to obtain accurate incremental data within a short period of time. However, binary logs are not generated in real time. Binary logs record all add, delete, and modify operations that are performed and the incremental data that can be 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. For more information, see How do I remotely obtain and parse the binary log file of an ApsaraDB RDS for MySQL instance?

    Why does the entry point to SQL Explore disappear in the ApsaraDB RDS console?

    The SQL Explorer and Audit feature is updated. The entry point changes to SQL Explorer and Audit.

    Can I enable the SQL Explorer feature?

    No, you can enable only the latest version of the SQL Explorer and Audit feature for your RDS instance. For more information, see DAS editions and supported features.