This topic describes how to use the SQL Explorer function of ApsaraDB RDS MySQL. Compared with the previous SQL audit function, SQL Explorer provides more diverse value-added services such as security audit and performance diagnosis at lower costs. Upgrading SQL audit to SQL Explorer does not affect your ApsaraDB RDS MySQL instance.

Prerequisites

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

  • MySQL 8.0 High-availability Edition
  • MySQL 5.7 High-availability Edition
  • MySQL 5.6
  • MySQL 5.5

Background information

SQL Explorer records all data manipulation language (DML) and data definition language (DDL) operations through network protocol analysis and only consumes a small amount of CPU resources. The Trial Edition of SQL Explorer retains SQL log data generated within up to one day free of charge. If you want to retain your SQL log data for more than one day, you must pay for the storage resources you use.

Scenarios

  • Your database is used for the finance, security, stocks, governmental affairs, or insurance sectors that have high requirements on data security.
  • You need to analyze the running of your database to locate problems or verify the execution performance of SQL statements.
  • Your data needs to be restored by using the SQL statements recorded in SQL log data.

Differences between SQL Explorer-generated logs and binary logs

Both SQL Explorer-generated logs and binary logs offer incremental data to your ApsaraDB RDS MySQL instance. However, the two types of logs differ in the following aspects:

  • SQL Explorer-generated logs are similar to audit logs in MySQL and record all DML and DDL operations through network protocol analysis. SQL Explorer does not parse actual parameter values. If a large amount of SQL statements are executed to query data, some operation records may be lost. As a result, the incremental data obtained through SQL Explorer-generated logs may be inaccurate.
  • Binary logs record all add, delete, and modify operations and the incremental data used for data restoration. After binary logs are generated, they are temporarily stored in your ApsaraDB for RDS instance. The system periodically transfers every binary log file whose size reaches the specified threshold to OSS. In OSS buckets, these log files can be retained for up to seven days. However, a binary log file cannot be transferred if data is being written to it. Therefore, you may find that some binary log files fail to be uploaded to OSS. Binary logs are not generated in real time, but you can obtain accurate incremental data from them.

Precautions

The time range for an online query extends up to 24 hours. SQL Explorer records all database-related operations, which involve a large amount of SQL statements. If the time range selected exceeds 24 hours, the query takes a long time and may even time out.

Note If you want to query SQL log data from a time range spanning longer than 24 hours, we recommend that you export the SQL log data asynchronously to your computer.

Function description

  • SQL logging

    Records all database-related operations. The recorded information helps you locate database problems, analyze actions, and perform security audits.

  • Advanced search

    Allows you to search for data in various dimensions such as database, thread ID, user, client IP address, execution time, or number of scanned rows. You can export and download the search results.

    Note
    • If you search for data in a single dimension, you can specify more than one search criterion and the system considers the specified search criteria in OR relationships. For example, if you specify two search criteria, user1 and user2, in the Users field, the system displays all SQL statements executed by user1 and user2.
    • If you search for data in more than one dimension, the system considers the specified dimensions in AND relationships. For example, if you enter user1 in the Users field and enter SELECT in the Operation Type field, the system displays all SELECT statements executed by user1.
    • Fuzzy match is not supported.
  • SQL analysis

    Allows you to analyze SQL logs generated within the specified time range. Using the SQL log analysis results, you can identify SQL statements that cause database performance issues.

  • Cost efficiency

    The column-oriented database storage and compression technology used reduces SQL log space usage and saves 60% of overall storage costs.

Enable SQL Explorer

  1. Log on to the ApsaraDB for RDS console.
  2. In the upper-left corner of the page, select the region where the target RDS instance resides.
    Select a region
  3. Find the target RDS instance and click its ID.
  4. In the left-side navigation pane, click SQL Explorer.
  5. Click Activate Now.
  6. Specify the SQL log storage duration and click Activate.
    Note If the storage duration of an SQL log file elapses, the system deletes the file.
    • Trial Edition: SQL Explorer remains available for a long time. However, SQL log files are retained for only one day. You can query SQL log data during up to one day it is retained. You cannot use advanced functions such as data export, nor is data integrity guaranteed.
    • Paid Edition: SQL log files can be retained for 30 days, 6 months, 1 year, 3 years, or 5 years. You are charged an hourly fee of USD 0.0018/GB.

Change SQL log storage duration

  1. Log on to the ApsaraDB for RDS console.
  2. In the upper-left corner of the page, select the region where the target RDS instance resides.
    Select a region
  3. Find the target RDS instance and click its ID.
  4. In the left-side navigation pane, click SQL Explorer.
  5. Click Service Settings.
  6. On the Service Settings page that appears, change the SQL log storage duration and click OK.

Disable SQL Explorer

Note

After you disable SQL Explorer, all existing SQL log files are deleted. We recommend that you export and save SQL log files to your computer before disabling SQL Explorer.

  1. Log on to the ApsaraDB for RDS console.
  2. In the upper-left corner of the page, select the region where the target RDS instance resides.
    Select a region
  3. Find the target RDS instance and click its ID.
  4. In the left-side navigation pane, click SQL Explorer.
  5. Click Export.
  6. In the dialog box that appears, click OK.
  7. After the export is complete, click View Exported List and download SQL log files to your computer.
  8. Click Service Settings.
  9. Turn off the Activate SQL Explorer switch.

FAQ

How do I view the SQL log size after enabling SQL Explorer?

Log on to the ApsaraDB for RDS console. Find the target RDS instance and navigate to its Basic Information page. In the Usage Statistics section, view the SQL log size.

SQL log size