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

Prerequisites

  • The RDS instance does not run the RDS Basic Edition.
  • If you log on to the RDS instance by using the credentials of a RAM user, the RAM user must be granted the read and write permissions such as AliyunRDSFullAccess on the RDS instance. For more information about how to grant permissions to RAM users, see Use RAM to manage ApsaraDB RDS permissions.

Background information

After you enable the SQL Explorer feature on an RDS instance, the SQL Explorer feature records the information about all Data Query Language (DQL), DML, and DDL statements that are executed on the RDS instance. The system captures the information by analyzing data transmitted over network protocols. This consumes only a small number of CPU resources. The Trial Edition of SQL Explorer allows you to store SQL log files for up to one day free of charge. If you want to store SQL log files for more than one day, you must pay extra fees.

Notification

ApsaraDB RDS for MySQL supports the Use the SQL Explorer and Audit feature on an ApsaraDB RDS for MySQL instance feature, which provides new features such as Source Statistics, SQL Review, and Related SQL Identification. You can open the Upgrade from SQL Explorer to SQL Explorer and Audit dialog box to check the differences in functionality and billing between the SQL Explorer feature and the SQL Explorer and Audit feature.

Billing

  • Trial Edition of SQL Explorer: Since August 20, 2020, the Trial Edition is subject to the new billing policies in all Alibaba Cloud regions.

    The Trial Edition is allowed a 15-day validity period. After the 15-day validity period elapses, the SQL Explorer feature is automatically disabled. If you want to continue using the SQL Explorer feature, we recommend that you purchase the Paid Edition.

    Note The Trial Edition of SQL Explorer can be enabled only once on each RDS instance.
  • Paid Edition of SQL Explorer: You are charged on an hourly basis. The pricing varies in different Alibaba Cloud regions.
    • USD 0.0015/(GB × hour): China (Hong Kong), US (Silicon Valley), and US (Virginia).
    • USD 0.0018/(GB × hours): Singapore (Singapore), Japan (Tokyo), Germany (Frankfurt), UAE (Dubai), Australia (Sydney), Malaysia (Kuala Lumpur), India (Mumbai), Indonesia (Jakarta), and UK (London).
    • USD 0.0012/(GB × hours): all regions except the preceding regions.
  • SQL Explorer and Audit: After the SQL Explorer and Audit feature is enabled, 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. For more information, see Pricing of DAS Professional Edition.

Scenarios

  • Your RDS instance can be used in sectors that require high data security. These sectors include finance, security, stocks, public service, and insurance.
  • You need to analyze the running of your RDS instance to locate issues in extreme circumstances or check the performance of SQL statements.
  • You need to restore the data of your RDS instance in extreme circumstances by using the logged information of the executed SQL statements.

Differences between SQL logs and binary logs

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

  • SQL logs are similar to audit logs in MySQL and include information about all executed DQL, DML, and DDL statements. The information is obtained by using network protocol analysis. The SQL Explorer feature does not parse actual parameter values. If a large number of SQL statements are executed, a small number of records may be lost. As a result, the incremental data that is obtained from SQL logs may be inaccurate.
  • Binary logs record all add, delete, and modify operations and are used to restore the incremental data. After binary log files are generated, they are temporarily stored on your RDS instance. ApsaraDB RDS periodically transfers every binary log file whose size reaches the specified threshold to an Object Storage Service (OSS) bucket. These binary log files are stored for seven days in the OSS bucket. However, a binary log file to which data is being written cannot be saved on your RDS instance or transferred to an OSS bucket. As a result, you may find some binary log files that are not transferred to the OSS bucket. Binary logs are not generated in real time but they can help you 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. These logs can be used 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 even time out.
    Note If you want to query SQL logs over a time range that exceeds 24 hours, we recommend that you asynchronously export SQL logs as a file 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 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 length per SQL statement is limited to 2,000 bytes. If the length of an SQL statement exceeds 2,000 bytes, the excessive bytes cannot be logged.

Functionality

  • SQL logging

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

  • Advanced search

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

    Note
    • If you query data in a single dimension, you can specify more than one search condition. ApsaraDB RDS considers the specified search conditions to be in OR relationships. 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 considers the specified dimensions to be in AND relationships. 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 logs that are generated over a specified time range. Based on the SQL log analysis results, you can identify abnormal SQL statements and locate performance issues.

  • Cost efficiency

    The SQL Explorer feature uses columnar storage and compression technologies to reduce the storage that is occupied by SQL logs. This reduces the overall storage costs by about 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. In the SQL Explorer Upgraded to SQL Explorer and Audit page that appears, click Close.
    Note
  4. Click Activate Now.
  5. Select an SQL log storage duration and click Activate.
    Note ApsaraDB RDS deletes all SQL log files that are stored longer than the specified SQL log storage duration.

Change the SQL log storage duration

  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 an SQL log storage duration and click OK.

Disable the SQL Explorer feature

Note After you disable the SQL Explorer feature, all SQL logs are deleted. Before you disable the SQL Explorer feature, we recommend that you export the SQL 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 exported SQL log file to your computer.
  6. Click Service Settings.
  7. Turn off Activate SQL Explorer and click OK.

FAQ

After I enable the SQL Explorer feature on my RDS instance, how do I view the size of the generated SQL logs?

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 SQL logs that are generated by the SQL Explorer feature.

Log Size for SQL Explorer