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 runs RDS High-availability Edition or RDS Enterprise Edition.
  • The RAM user 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 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

The SQL Explorer feature is upgraded to the SQL Explorer and Audit feature in some regions. The regions include China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Shenzhen), China (Zhangjiakou), China (Hohhot), China (Chengdu), China (Guangzhou), China (Heyuan), China (Ulanqab), China (Hong Kong), Singapore (Singapore), Malaysia (Kuala Lumpur), and Indonesia (Jakarta). The SQL Explorer and Audit feature also supports SQL Explorer - Display by Comparison, Source Statistics, SQL Review, Related SQL Identification, SQL Diagnosis and Optimization, Traffic Playback and Stress Test, and Real-time Audit. For more information, see Use the SQL Explorer and Audit feature on an ApsaraDB RDS for MySQL instance.

Billing

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

      SQL Explorer Trial Edition provides a 15-day free trial. 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 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 (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 SQL Explorer and Audit feature are billed to Database Autonomy Service (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.

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 about executed SQL statements in extreme circumstances.

Differences between SQL audit logs and binary logs

Both SQL audit 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 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.

Usage notes

  • 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,048 bytes. If the size of an SQL statement exceeds 2,048 bytes, the excessive bytes cannot be logged. If the size of an SQL statement exceeds 8,192 bytes, the SQL statement cannot be logged.
  • If you enable SQL Explorer Trial Edition, you cannot call the DescribeSQLLogRecords or DescribeSQLLogFiles operation to query audit logs.

Functionality

  • SQL Explorer:
    • SQL audit logs

      The SQL Explorer feature records 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 from 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 from a single dimension, you can specify more than one search condition. The specified search conditions are evaluated by using a logical OR. For example, if you specify two search conditions, user1 and user2, in the User field, ApsaraDB RDS returns all SQL statements that are executed by user1 and user2.
      • If you query data from more than one dimension, the specified dimensions are evaluated by using a logical AND. For example, if you enter user1 in the User field and set the Operation Type parameter to SELECT, ApsaraDB RDS returns all SELECT statements that are executed by user1.
      • Fuzzy match is not supported.
    • SQL analysis

      The SQL Explorer feature provides a visualized and interactive solution to analyze the audit logs of SQL statements that are executed with a specific period of time. This allows you to find the SQL statements that are not executed as normal and locate performance issues.

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

  • SQL Explorer and Audit. For more information, see Use the SQL Explorer and Audit feature on an ApsaraDB RDS for MySQL instance.

Enable the SQL Explorer feature

Note If you enable the audit log collection feature for your RDS instance in the CloudLens for RDS application of Log Service, the SQL Explorer feature is automatically enabled for the RDS instance. For more information, see CloudLens for RDS.
  1. Access RDS Instances, select a region at the top, and then click the ID of the target RDS instance.
  2. In the left-side navigation pane, click SQL Explorer to go to the SQL Explorer and Audit page.
  3. Enable the SQL Explorer feature.
    • If your RDS instance resides in one of the following regions: China (Hangzhou), China (Shanghai), China (Qingdao), China (Beijing), China (Shenzhen), China (Zhangjiakou), China (Hohhot), China (Chengdu), China (Guangzhou), China (Heyuan), China (Ulanqab), China (Hong Kong), Singapore (Singapore), Malaysia (Kuala Lumpur), and Indonesia (Jakarta), click Enable to enable the SQL Explorer and Audit feature.
      Note If you do not activate DAS Professional Edition for your Alibaba Cloud account, follow the on-screen instructions to activate DAS Professional Edition. For more information, see Purchase DAS Professional Edition.
    • If your RDS instance resides in a region rather than the preceding regions, click Official Edition, configure the Storage Duration parameter, and then click OK to enable the SQL Explorer feature.
    Note
    • The default value of the Storage Duration parameter for the SQL Explorer and Audit feature is 30 Days. You can change the value of this parameter in the Service Settings dialog box.
    • ApsaraDB RDS deletes all SQL audit logs that are stored for longer than the specified retention period.

Modify the retention period of SQL audit logs

  1. Access RDS Instances, select a region at the top, and then click the ID of the target RDS instance.
  2. In the left-side navigation pane, click SQL Explorer to go to the SQL Explorer and Audit page.
  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. If you enable the SQL Explorer feature again, SQL audit logs are recorded from the time when the SQL Explorer feature is enabled.
  1. Access RDS Instances, select a region at the top, and then click the ID of the target RDS instance.
  2. In the left-side navigation pane, click SQL Explorer to go to the SQL Explorer and Audit page.
  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 audit logs are exported, click View Exported Logs and download the SQL audit log file that you exported to your computer.
  6. Click Service Settings.
  7. Turn off the switch next to Enable and click OK.
    Note If you enable the audit log collection feature for your RDS instance in the CloudLens for RDS application of Log Service, the SQL Explorer feature is automatically enabled for the RDS instance. Therefore, you must also disable the audit log collection feature for the RDS instance. For more information, see CloudLens for RDS.

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.

  • What does the logout! SQL statement that is displayed in the Log Entries section of the SQL Explorer page mean?
    The logout! SQL statement indicates a disconnection. The time that is displayed in the Time Consumption column of the logout! SQL statement refers to the time difference between the point in time at which the server interacts with the client for the last time and the point in time at which the logout! SQL statement is executed. During the time difference, the connection remains idle. 1158 in the Status column indicates that the network is disconnected. The following list describes the possible reasons:
    • The client connection times out.
    • The server is unexpectedly disconnected.
    • The connection to the server is reset if the duration of the connection exceeds the value of the interactive_timeout or wait_timeout parameter.
  • In the Log Entries section of the SQL Explorer page, why a percent sign (%) is displayed in the Client IP Address column?
    When you use a stored procedure, a percent sign (%) may be displayed in the Client IP Address column in the Log Entries section of the SQL Explorer page. You can perform the following operations to reproduce this situation:
    Note The test account test_user and the test database testdb are used.
    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 databases and accounts for an ApsaraDB RDS for MySQL instance.
    2. Use the test_user account to connect to an RDS 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 create a stored procedure by executing the following statements.
      -- 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 RDS 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() |
      +--------+-----------+--------+--------+---------+------+-----------+-------------------------------------------------------------------------+