Help Center/ Relational Database Service_RDS for MySQL/ User Guide/ Log Management/ Enabling SQL Audit
Updated on 2026-09-18 GMT+08:00
View PDF
Share

Enabling SQL Audit

Scenarios

After you enable the SQL audit function, all SQL operations will be recorded in log files. You can download audit logs to view log details.

By default, SQL audit is disabled for RDS for MySQL instances. Enabling this feature increases database load (consuming the instance's CPUs, memory, storage, and disk IOPS), but the performance impact is typically no more than 5%. This section describes how to enable, modify, and disable SQL audit.

Supported Database Versions

Only the versions listed below support SQL audit. If your DB engine version is too old, upgrade it to the latest version by referring to Upgrading a Minor Version.
  • RDS for MySQL 5.6 instances using cloud disks: 5.6.43 and later versions
  • RDS for MySQL 5.7 instances using cloud disks: 5.7.23 and later versions
  • RDS for MySQL 8.0

Constraints

Table 1 Audit log constraints

Audit Log Type

Constraints

SQL audit logs
  • While a standby read replica is acting as a primary read replica due to an exception in the primary, the audit logs generated are not retrievable.
  • If your instance is deployed in LA-Mexico City2 and you want to enable SQL audit for it, submit a service ticket.
  • Audit log ingestion to LTS is unavailable in certain regions. To use it, submit a service ticket to obtain required permissions.
  • Both primary DB instances and read replicas support SQL audit logging.
  • Times in audit logs use the Coordinated Universal Time (UTC) format, regardless of the time zone configured for the DB instance.
  • After SQL audit is enabled, RDS records SQL operations in audit logs. The generated audit log files are temporarily stored in the instance and then uploaded to OBS and stored in the backup space. If there is not enough free backup space available for generated audit logs, the additional space required is billed.
  • Audit logs are cleaned up every hour. After you change the retention period of audit logs, expired audit logs will be deleted 1 hour later.
  • After SQL audit is enabled, a large number of audit logs may be generated during peak hours. As a result, there are many audit log files temporarily stored in the instance, and the storage may be full. You are advised to enable storage autoscaling.

Audit logs reported to LTS
  • Access logs provide all log details required by instances and are stored in LTS.
  • Check whether Continue to Collect Logs When the Free Quota Is Exceeded is enabled. If it is not enabled, log uploads will fail once 500 MB of logs have been sent to LTS. For more information, see Setting LTS Log Collection Quota and Usage Alarms.
  • Once the function is configured, there is a delay of approximately 10 minutes before the settings are applied.
  • You will be billed for enabling this function. For details, see LTS pricing details.
  • If you have configured audit log injection to LTS, all audit policies are uploaded by default.
  • Before enabling both SQL audit logging and audit log ingestion to LTS, keep the following points in mind:
    • Enabling either SQL audit logging or audit log ingestion to LTS will generate audit logs. Sensitive information contained in these audit logs is not anonymized.
    • If SQL audit logging is already enabled and you then enable audit log reporting to LTS, LTS will continue to reuse the existing audit policy configured for your instance. In addition to the original audit log billing, you will be billed for ingesting audit logs to LTS. The original audit log billing will stop only after you disable the original audit policy.
    • If SQL audit logging is already enabled and you then enable audit log ingestion to LTS, for the sake of instance stability, you are advised to keep the original audit policy enabled for a period of time. After confirming that audit log ingestion to LTS is functioning properly, you may then disable the original audit policy.
  • Audit logs may be lost while being uploaded to LTS in certain situations. If audit logging is enabled, you can download all audit logs from OBS.
    • Some logs may be lost when traffic is heavy, audit logs are generated too fast, or the LTS service fails.
    • Each audit log record uploaded to LTS cannot exceed 512 KB. Larger records will be truncated.

Procedure

  1. Log in to the RDS console.
  2. Click in the upper left corner and select a region.
  3. On the Instances page, click the target instance name.
  4. In the navigation pane on the left, choose Logs. On the displayed page, click the SQL Audit Logs tab.
  5. On the displayed tab page, click Set SQL Audit. In the displayed dialog box, specify parameters and click OK.

    Enabling or setting SQL audit
    • To enable SQL audit, toggle on the switch (from to ).
    • Audit logs can be retained from 1 to 732 days and are retained for 7 days by default.
    • Once Rapid Audit Log Rotation is enabled, the service polls audit logs every 5 minutes and uploads newly generated audit logs to OBS or LTS.
    Figure 1 Setting SQL audit

    The Operations option is available only in the CN North-Beijing4, CN South-Guangzhou, and CN-Hong Kong regions. To use this option in other regions, submit a service ticket.

    The SQL statements executed by PreparedStatement and scheduled tasks through a MySQL client will be treated as PREPARED_STATEMENT and CREATE, respectively. However, the SQL statements executed by PreparedStatement through JDBC will not be recorded.

    After SQL audit is enabled, Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and other operation types are supported. The details are as follows:

    Table 2 DDL types and operations

    Type

    Operation

    Remarks

    CREATE

    create_db, create_event, create_function, create_index, create_procedure, create_table, create_trigger, create_udf, create_view

    -

    ALTER

    alter_db, alter_db_upgrade, alter_event, alter_function, alter_instance, alter_procedure, alter_table, alter_tablespace

    The alter_user operation is no longer supported from January 26, 2024.

    DROP

    drop_db, drop_event, drop_function, drop_index, drop_procedure, drop_table, drop_trigger, drop_view

    -

    RENAME

    rename_table

    -

    TRUNCATE

    truncate

    -

    REPAIR

    repair

    Added on January 26, 2024

    OPTIMIZE

    optimize

    Added on January 26, 2024
    Table 3 DML types and operations

    Type

    Operation

    Remarks

    INSERT

    insert, insert_select

    -

    DELETE

    delete, delete_multi

    -

    UPDATE

    update, update_multi

    The update_multi operation was added on January 26, 2024.

    REPLACE

    replace, replace_select

    -

    SELECT

    select

    -
    Table 4 DCL types and operations

    Type

    Operation

    Remarks

    CREATE_USER

    create_user

    -

    DROP_USER

    drop_user

    -

    RENAME_USER

    rename_user

    -

    GRANT

    grant_roles, grant

    The grant_roles operation was added on January 26, 2024.

    REVOKE

    revoke, revoke_all, revoke_roles

    The revoke_roles operation was added on January 26, 2024.

    ALTER_USER

    alter_user

    Added on January 26, 2024

    ALTER_USER_DEFAULT_ROLE

    alter_user_default_role

    Added on January 26, 2024
    Table 5 Other types and operations

    Type

    Operation

    Remarks

    BEGIN/COMMIT/ROLLBACK

    begin, commit, release_savepoint, rollback, rollback_to_savepoint, savepoint

    -

    PREPARED_STATEMENT

    execute_sql, prepare_sql, dealloc_sql

    The dealloc_sql operation was added on January 26, 2024.

    CALL_PROCEDURE

    call_procedure

    Added on January 26, 2024

    KILL

    kill

    Added on January 26, 2024

    SET_OPTION

    set_option

    Added on January 26, 2024

    CHANGE_DB

    change_db

    Added on January 26, 2024

    UNINSTALL_PLUGIN

    uninstall_plugin

    Added on January 26, 2024

    INSTALL_PLUGIN

    install_plugin

    Added on January 26, 2024

    SHUTDOWN

    shutdown

    Added on January 26, 2024

    SLAVE_START

    slave_start

    Added on January 26, 2024

    SLAVE_STOP

    slave_stop

    Added on January 26, 2024

    LOCK_TABLES

    lock_tables

    Added on January 26, 2024

    UNLOCK_TABLES

    unlock_tables

    Added on January 26, 2024

    FLUSH

    flush

    Added on January 26, 2024

    XA

    xa_commit,xa_end,xa_prepare,xa_recover,xa_rollback,xa_start

    Added on January 26, 2024

    Disabling SQL audit

    To disable SQL audit, toggle (enabled) to (disabled).

    If you select the check box "I acknowledge that after audit log is disabled, all audit logs are deleted." and click OK, all audit logs will be deleted.

    Deleted audit logs cannot be recovered. Exercise caution when performing this operation.

  1. Log in to the RDS console.
  2. Click in the upper left corner and select a region.
  3. On the Instances page, click the target instance name.
  4. In the navigation pane, choose Logs.
  5. On the SQL Audit Logs tab page, click Ingest Logs to LTS in the upper right corner.
  6. In the displayed dialog box, select Ingest Logs to LTS, select Audit log under Log Types, select a log group and log stream, and click OK.

    Figure 2 Enabling audit log ingestion to LTS

  7. If audit log ingestion to LTS is enabled, you can switch to the analysis view in the upper right corner of the page to see more details.

    Figure 3 List view
    Figure 4 Analysis view
    • On the Log Search tab page, you can view structured fields and index configuration fields under Quick Analysis as well as view and download log contents. For more information, see Searching and Analyzing Logs.

      For explanations of the fields on the Raw Logs page in the analysis view, see Table 6.

      Table 6 LTS audit log field description

      Parameter

      Description

      logType

      Log type. Fixed value: audit_log.

      instanceId

      Instance ID.

      nodeId

      Node ID within an instance.

      _resource_id

      Resource ID. Fixed value: DBS_AUDIT_LOG. Ignore this parameter.

      _resource_name

      Resource name. Fixed value: DBS_AUDIT_LOG. Ignore this parameter.

      _service_type

      Service type. Fixed value: MySQL.

      record_id

      ID of a record, which is the unique global ID of each SQL statement recorded in the audit log.

      connection_id

      ID of the session executed for the record, which is the same as the ID in the show processlist command output.

      connection_status

      Session status, which is usually the returned error code of a statement. If a statement is successfully executed, the value 0 is returned.

      name

      Name of the record type. Generally, DML and DDL operations are QUERY, connection and disconnection operations are CONNECT and QUIT, respectively.

      timestamp

      UTC time when the SQL statement is executed.

      command_class

      SQL command type. The value is the parsed SQL type, for example, select or update. (This field does not exist if the connection is disconnected.)

      sqltext

      Executed SQL statement content. (This field does not exist if the connection is disconnected.)

      user

      Login account.

      host

      Login host. The value is localhost for local login and is empty for remote login.

      external_user

      External username.

      ip

      IP address of the remotely-connected client. For local connection, the field is empty.

      default_db

      Default database on which SQL statements are executed.

      trx_id

      ID of the transaction in which the SQL statement is executed.

      execute_time

      Time required for executing the SQL statement, in milliseconds.
    • On the Charts tab page, you can view scenario-specific data in multiple chart types, including tables, bar charts, and line charts. For more information, see Statistical Charts.
    • On the Log Analysis tab page, you can search for and analyze collected log data. For more information, see Log Search and Analysis.
    • On the Real-Time Logs tab page, you can view logs reported in real time to quickly search for and analyze log data. For more information, see Viewing Real-Time Logs.

FAQ

If SQL Audit Logging Is Disabled but Audit Log Ingestion to LTS Is Enabled, Will SQL Audit Logs Still Be Generated?

Yes.

If Audit Log Ingestion to LTS Is Enabled but the Corresponding Log Stream Is Deleted, Will SQL Audit Logs Still Be Generated?

If the log stream is deleted, RDS for MySQL will continue to generate audit logs and will try to report the logs. If the number of logs that fail to be reported exceeds the upper limit 50 (at most 50 logs can be kept locally), the excess logs will be deleted.

How Do I Download SQL Audit Logs in CSV Format?

After SQL audit logs are integrated with LTS, you can download them in .csv format on the LTS page.

Related APIs

Parent Topic: Log Management