This topic describes how to use the SQL Explorer feature on an ApsaraDB RDS for MySQL instance. The SQL Audit feature is upgraded to the SQL Explorer feature to provide more value-added capabilities such as security audit and performance diagnosis at lower costs. The upgrade does not interrupt the workloads on your RDS instance.

Prerequisites

  • Your RDS instance does not run RDS Basic Edition.
  • The RAM user whose credentials you use is granted the read and write permissions on ApsaraDB RDS. For example, the AliyunRDSFullAccess policy is attached to the RAM user. This prerequisite must be met if you log on to your RDS instance by using the credentials of a RAM user. For more information about how to grant permissions to a RAM user, see Use RAM to manage ApsaraDB RDS permissions.

Background information

After you enable the SQL Explorer feature for your RDS instance, the SQL Explorer feature records the information about all data query language (DQL), DML, and DDL operations that are performed on your RDS instance. ApsaraDB RDS obtains the information by using network protocol analysis techniques. This way, only a small number of vCPU resources are consumed. SQL Explorer Trial Edition allows you to store SQL audit log files for up to one day free of charge. If you want to store SQL audit log files for more than one day, you are charged additional fees.

Notification

ApsaraDB RDS for MySQL supports the SQL Explorer and Audit feature. This feature provides capabilities such as Source Analysis, SQL Review, and Related SQL Identification. You can open the Upgrade from SQL Explorer to SQL Explorer and Audit dialog box to view the differences in functionality and billing between the SQL Explorer feature and the SQL Explorer and Audit feature. For more information about the SQL Explorer and Audit feature, see Use the SQL Explorer and Audit feature on an ApsaraDB RDS for MySQL instance.

Billing

  • SQL Explorer Trial Edition: Since August 20, 2020, new billing rules are applied to SQL Explorer Trial Edition in all Alibaba Cloud regions.

    SQL Explorer Trial Edition provides a 15-day free trial of SQL Explorer. After the 15-day free trial expires, the SQL Explorer feature becomes unavailable. If you want to continue using the SQL Explorer feature, we recommend that you purchase SQL Explorer Paid Edition before the free trial expires.

    Note You can enable SQL Explorer Trial Edition only once for each RDS instance.
  • SQL Explorer Paid Edition: You are charged on an hourly basis. The pricing varies in Alibaba Cloud regions.
    • USD 0.0015 per GB per hour: China (Hong Kong), US (Silicon Valley), and US (Virginia).
    • USD 0.0018 per GB per hour: Singapore (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: all regions except the preceding regions.
  • SQL Explorer and Audit: After you enable the SQL Explorer and Audit feature, the fees that you must pay for the original SQL Explorer feature are billed to DAS Professional Edition. The fees are no longer billed to your RDS instance. For more information, see Use the SQL Explorer and Audit feature on an ApsaraDB RDS for MySQL instance and Pricing of DAS Professional Edition.

Scenarios

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

Differences between SQL audit logs and binary logs

Both SQL audit logs and binary logs contain the incremental data of your RDS instance. The two types of logs differ in the following aspects:

  • SQL audit logs are similar to audit logs in MySQL and include information about all executed DQL, DML, and DDL operations. ApsaraDB RDS obtains the information by using network protocol analysis techniques. The SQL Explorer feature does not parse actual parameter values. If a large number of SQL statements are executed to query data, a small number of records may be lost. As a result, the incremental data that is obtained from SQL audit logs may be inaccurate.
  • Binary logs record all add, delete, and modify operations that are performed and the incremental data that can be used to restore data. After a binary log file is generated, it is temporarily stored on your RDS instance. ApsaraDB RDS periodically transfers the binary log files whose sizes reach the specified threshold to an Object Storage Service (OSS) bucket. Binary log files can be stored for seven days in the OSS bucket. A binary log file to which data is being written cannot be transferred to an OSS bucket. After a periodic transfer is complete, you may find binary log files that cannot be transferred to the OSS bucket. Binary logs are not generated in real time. However, you can still use binary log files to obtain accurate incremental data.

Precautions

  • The time range for an online query extends up to 24 hours. This is because the SQL Explorer feature logs a large number of SQL statements. You can use the logs to trace all operations that are performed on your RDS instance. If the time range for an online query exceeds 24 hours, the query requires a long period of time and may time out.
    Note If you want to query SQL audit logs over a time range that exceeds 24 hours, we recommend that you export SQL audit logs as a file in asynchronous mode and download the file to your computer.
  • 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 audit logs that contain the keyword test1 or test2.
  • Fuzzy match is not supported for online queries.
  • Each keyword for an online query must contain at least four characters.
  • The size per SQL statement is limited to 2,000 bytes. If the size of an SQL statement exceeds 2,000 bytes, the excessive bytes cannot be logged.
  • If you enable the SQL Explorer Trial Edition for your RDS instance, you cannot call the DescribeSQLLogRecords operation to query the logs that are generated by the SQL Explorer feature for your RDS instance.

Functionality

  • SQL audit logging

    The SQL Explorer feature logs all operations that are performed on your RDS instance. You can use SQL audit logs to analyze faults and behavior and audit security.

  • Advanced search

    The SQL Explorer feature allows you to query data in various dimensions, such as database, user, client IP address, thread ID, execution duration, and number of scanned rows. You can export and download query results.

    Note
    • If you query data in a single dimension, you can specify more than one search condition. ApsaraDB RDS applies the OR operator to the specified search conditions. For example, if you specify two search conditions, user1 and user2, in the Users field, ApsaraDB RDS returns all SQL statements that are executed by user1 and those that are executed by user2.
    • If you query data in more than one dimension, ApsaraDB RDS applies the AND operator to the specified dimensions. For example, if you enter user1 in the Users field and select the SELECT statement for the Operation Type parameter, ApsaraDB RDS returns all SELECT statements that are executed by user1.
    • Fuzzy match is not supported.
  • SQL analysis

    The SQL Explorer feature allows you to view and analyze the SQL audit logs that are generated over a specified time range. You can identify abnormal SQL statements and troubleshoot performance issues by using the analysis results.

  • Cost reduction

    The SQL Explorer feature uses columnar storage and compression technologies to reduce the storage usage for SQL audit logs. This reduces the overall storage costs by approximately 60%.

Enable the SQL Explorer feature

  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. In the left-side navigation pane, click SQL Explorer.
  3. On the SQL Explorer Upgraded to SQL Explorer and Audit page, click Close.
    Note
  4. Click Activate Now.
  5. Select a retention period and click Activate.
    Note ApsaraDB RDS deletes all SQL audit log files that are stored for longer than the specified retention period.

Modify the retention period of SQL audit logs

  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. In the left-side navigation pane, click SQL Explorer.
  3. Click Service Settings.
  4. Select a retention period and click OK.

Disable the SQL Explorer feature

Note After you disable the SQL Explorer feature, all SQL audit logs are deleted. Before you disable the SQL Explorer feature, we recommend that you export the SQL audit logs as a file and download the file to your computer.
  1. Visit the RDS instance list, select a region above, and click the target instance ID.
  2. In the left-side navigation pane, click SQL Explorer.
  3. Click Export.
  4. In the message that appears, click OK.
  5. After the export is complete, click View Exported List and download the SQL audit log file that you exported to your computer.
  6. Click Service Settings.
  7. Turn off the switch next to Activate SQL Explorer and click OK.

FAQ

  • How do I obtain the size of the logs that are generated by the SQL Explorer feature?
    Log on to the ApsaraDB RDS console, find your RDS instance, and then go to the Basic Information page. In the Usage Statistics section of the page, you can view the size of the logs that are generated by the SQL Explorer feature. Log Size
  • Can I delete a specified part of the generated SQL audit logs?

    No, you cannot delete a specified part of the generated SQL audit logs. To reduce costs, you can disable the SQL Explorer feature.