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 diversified security audit and performance diagnosis capabilities at lower costs. The upgrade from SQL audit to SQL Explorer does not interrupt the workloads on your RDS instance.

Prerequisites

Your RDS instance runs one of the following MySQL versions and RDS editions:

  • MySQL 8.0 on RDS Enterprise Edition
  • MySQL 8.0 on RDS High-availability Edition
  • MySQL 5.7 on RDS Enterprise Edition
  • MySQL 5.7 on RDS High-availability Edition
  • MySQL 5.6
  • MySQL 5.5

Background information

The SQL Explorer feature logs all the executed data manipulation language (DML) and data definition language (DDL) statements. This is implemented by using network protocol analysis and 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.

Billing

  • Trial Edition: Since August 20th, 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 unavailable. 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: The Paid Edition allows you to store SQL log files for 30 days, 6 months, 1 year, 3 years, or 5 years. You are charged an hourly fee of USD 0.0018 per GB of storage.

Scenarios

  • Your RDS instance is used for sectors that require high data security. These sectors include finance, security, stocks, governmental affairs, and insurance.
  • You need to analyze the running of your RDS instance to locate issues or to check the performance of SQL statements. Issues may occur in extreme circumstances.
  • You need to restore the data of your RDS instance by using the logged information of the executed SQL statements. This restoration is required in extreme circumstances.

Differences between SQL logs and binary logs

Both SQL logs and binary logs include 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 the executed 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 include information about all the performed add, delete, and modify operations. You can restore the accurate incremental data of your RDS instance by using binary logs. However, you cannot obtain binary logs in real time. 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 transferred to an OSS bucket. As a result, after a periodic transfer is complete, you may find binary log files that cannot be transferred to the OSS bucket.

Precautions

  • The time range that is allowed 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 the 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.
  • Each keyword must contain at least four characters.
  • Fuzzy match is not supported for online queries.
  • 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 the operations that are performed on your RDS instance. You can use SQL logs to identify 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 time, 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 the 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 the 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 usage for SQL logs. This reduces the overall storage costs by about 60%.

Enable the SQL Explorer feature

  1. Go to the SQL Explorer page.
    1. Log on to the ApsaraDB for RDS console. In the left-side navigation pane, click Instances. In the top navigation bar, select the region where your RDS instance resides.
      选择地域
    2. Find your RDS instance and click its ID. In the left-side navigation pane, click SQL Explorer.
  2. Click Activate Now.
  3. Select an SQL log storage duration and click Activate.
    Note ApsaraDB RDS deletes all the SQL log files that are stored longer than the specified SQL log storage duration.

Change the SQL log storage duration

  1. Go to the SQL Explorer page.
    1. Log on to the ApsaraDB for RDS console. In the left-side navigation pane, click Instances. In the top navigation bar, select the region where your RDS instance resides.
      选择地域
    2. Find your RDS instance and click its ID. In the left-side navigation pane, click SQL Explorer.
  2. Click Service Settings.
  3. Select an SQL log storage duration and click OK.

Disable the SQL Explorer feature

Note After you disable the SQL Explorer feature, all the stored SQL logs are deleted. Before you disable the SQL Explorer feature, we recommend that you export the stored SQL logs as a file and download the file to your computer.
  1. Go to the SQL Explorer page.
    1. Log on to the ApsaraDB for RDS console. In the left-side navigation pane, click Instances. In the top navigation bar, select the region where your RDS instance resides.
      选择地域
    2. Find your RDS instance and click its ID. In the left-side navigation pane, click SQL Explorer.
  2. Click Export.
  3. In the message that appears, click OK.
  4. After the export is complete, click View Exported List and download the exported SQL log file to your computer.
  5. Click Service Settings.
  6. Turn off the switch next to 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