tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TencentDB for MySQL
    Documentation
    Download PDF

    Viewing Audit Log

    Last updated: 2024-12-06 17:49:31
    Download PDF
      This document describes how to view database audit logs and their list field.
      Note:
      SQL statements executed via functions and stored procedures are not recorded in the audit logs.
      A new version of the audit log page was released on July 12, 2023. The new version added a new audit log search field "Scanned Rows". For existing audit logs before this release date, the data in this field will be displayed as "-", and the corresponding downloaded files and APIs will be displayed as "-1".
      The unit of the execution time which is the audit log field has been uniformly adjusted to microsecond in both the console and the downloaded audit log files.
      The unit of the CPU time which is the audit log field has been uniformly adjusted to microsecond in both the console and the downloaded audit log files.
      The unit of the timestamp field in the audit log files has been enhanced to display time with the unit being millisecond.
      When searching audit logs, the character used to separate multiple search items is changed from comma to line break.
      After database audit is enabled, instances in the regions of Tianjin, Taipei (China), and Shenzhen will have different audit log storage regions in CFS. Please refer to the table below for the corresponding storage regions.
      Instance Region
      Audit log storage region
      Tianjin
      Beijing
      Taipei (China)
      Hong Kong (China)
      Shenzhen
      Guangzhou

      Prerequisite

      You have enabled the audit service. For more information, see Enabling Audit Service.

      Viewing Audit Log

      Note:
      The audit log display time is down to milliseconds, facilitating more precise sorting and problem analysis of SQL commands.
      1. Log in to the TencentDB for MySQL console.
      2. On the left sidebar, click Database Audit.
      3. Select Region at the top, click Audit Instance tab, click Audit Status, and click Enabled to filter the audit-enabled instances.
      
      4. Find the target instance in the Audit Instance list, or search for it by resource attribute in the search box, and click View Audit Log in the Operation column to enter the Audit Log tab and view logs.
      

      Tool list

      In the audit instance filter box, you can choose to switch to other audit instances that have enabled the audit service.
      In the Time Frame, the default selection is Nearly 1 Hour. Other time periods (Last 3 Hours, Last 24 Hours, Last 7 Days) can be quickly selected. It also supports for custom time range to view audit logs within the selected time period.
      Note:
      You can select any time period with data for search. Up to the first 60,000 eligible records can be displayed.
      In the search box, select search items (SQL details, client IP, user account, database name, Table Name, SQL type, error code, execution time (μs), lock wait time (μs), IO wait time (ns), transaction duration (μs), CPU time (μs), risk level, thread ID, transaction ID, scanned rows, affected rows, returned rows, audit rules, etc.) to search, and you can view relevant audit results. Multiple search items are separated by line break.
      Search Item
      Match Item
      Description
      SQL Command Details
      Include - Or - Tokenize
      Rule Description
      Enter SQL Command Details, separating multiple keywords with line breaks.
      The SQL Command Details search box matches on three levels: The first level sets the match mode (inclusive or exclusive); The second level sets the logical relationship between keywords (OR, AND); The third level sets the match mode for each keyword (tokenization, wildcard).
      Note:
      SQL Command Details search is case-insensitive.
      Supports two match modes: "Inclusive" and "Exclusive".
      Keywords support two logical matches, "OR" and "AND". "OR" represents a "union" relationship between different keywords, while "AND" represents an "intersection" relationship.
      Each keyword supports "tokenization" and "wildcard" match modes. "Tokenization" means each keyword in the SQL Command Details needs to be exactly matched, while "wildcard" means each keyword in the SQL Command Details can be fuzzily matched.
      Example Description
      Assume the SQL Command Details are as follows: SELECT * FROM test_db1 JOIN test_db2 LIMIT 1;
      Under the "Inclusive (Tokenization)" search mode, you can search using tokenized keywords such as "SELECT", "select * from", "*", "SELECT * FROM test_db1 join test_db2 LIMIT 1;", "from Test_DB1", etc. However, wildcard keywords such as "SEL", "sel", "test", etc., cannot be used for search.
      Under the "Inclusive (Wildcard)" search mode, you can perform searches using wildcard keywords like "SEL", "sel", "test", and "DB".
      Under the "Inclusive (AND)" search mode, there is an "AND" relationship between multiple keywords. That is to say, entering keywords such as "SELECT", "test_db" will retrieve all SQL commands that include both "SELECT" and "test_db".
      Under the "Inclusive (OR)" search mode, there exists an "OR" relationship between multiple keywords. In other words, inputting "test_db1" or "test_db2" will yield all SQL commands that either include "test_db1" or "test_db2".
      Include - AND - Segmentation
      Exclude - AND - Segmentation
      Include - OR - Wildcard
      Include - AND - Wildcard
      Exclude - AND - Wildcard
      Client IP
      Include Exclude Equal to Not equal to
      Enter the client IP, separate multiple keywords with a new line; IP addresses can be filtered using * as a condition. For example, searching client IP: 9.223.23.2* will match IP addresses beginning with 9.223.23.2.
      User Account
      Include Exclude Equal to Not equal to
      Enter the user account, separating multiple keywords with a new line.
      Database Name
      Include Exclude Equal to Not equal to
      Enter the database name, separating multiple keywords with a new line.
      Note:
      The database name search is case-insensitive.
      Table Name
      Equal to Not equal to
      Input the table name, and the table name search are described as follows:
      Case-insensitive.
      The search format is DbName.TableName.
      For example: If the database test_db contains the table test_table, to search for table test_table, you need to input: the table name equals to test_db.test_table.
      Note:
      A maximum of 64 table names can be recorded.
      For the field "Table Name", MySQL 5.7 versions dated 20240331 and later already support it. If MySQL 8.0 versions dated 20230630 and later need to support it, submit a ticket for a solution. Other versions do not support it. If support is required, upgrade to a version that supports this field.
      SQL Type
      Equal to Not equal to
      Select from the drop-down list the SQL types (ALTER, CHANGEUSER, CREATE, DELETE, DROP, EXECUTE, INSERT, LOGOUT, OTHER, REPLACE, SELECT, SET, UPDATE). Multiple selections are allowed.
      Error Code
      Equal to Not equal to
      Enter the error code. Separate multiple keywords with a line break.
      Execution time (μs)
      Interval Format
      Enter the execution time in the format of M-N, such as 10-100 or 20-200.
      Lock wait time (μs)
      Interval Format
      Enter the lock wait time in the format of M-N, such as 10-100 or 20-200.
      IO wait time (ns)
      Interval Format
      Enter the IO wait time in the format of M-N, like 10-100 or 20-200.
      Transaction duration (μs)
      Interval Format
      Enter the transaction duration in the format of M-N, like 10-100 or 20-200.
      CPU time (μs)
      Interval Format
      Input the CPU time in the format M-N, for example, 10-100 or 20-200.
      Risk Level
      Include Exclude
      Select low risk, medium risk, or high risk to filter the audit logs set by the risk level of the matched rule template.
      Support is also available for blank inputs, which means filtering audit logs without a risk level TAG from historical data.
      Thread ID
      Equal to
      Not equal to
      Enter the Thread ID, separate multiple keywords using a line break.
      Transaction ID
      Equal to Not equal to
      Enter the transaction ID, and use a line break to separate multiple keywords.
      Note：
      For the field "Transaction ID", it is supported in MySQL 5.7 versions dated 20240331 and later, as well as MySQL 8.0 versions dated 20230630 and later. Other versions do not support it. If support is required, upgrade to a version that supports this field.
      Currently, a transaction ID is generated only when an explicit transaction is added, deleted, or modified. Implicit transactions do not have a transaction ID.
      Number of scanned rows
      Interval Format
      Enter the number of lines to be scanned in an M-N format, for example, 10-100 or 20-200.
      Number of affected rows
      Interval Format
      Enter the number of affected rows in an M-N format, such as 10-100 or 20-200.
      Number of returned rows
      Interval Format
      Enter the number of rows returned in the format M-N, such as 10-100 or 20-200.
      Audit Rule
      Include Exclude
      Displays the Template ID and Template Name of all rule templates in a certain region. You can filter out the audit logs that match this rule template.
      It accepts blank input, indicative of filtering out audit logs without any audit rule TAG from historical data, and the full audit logs that did not hit any rules.
      Enables search operations based on Rule Template ID and Rule Template Name for audit rules.
      Allows selection of multiple rule templates at the same time.

      Log list

      The Returned Rows field represents the specific number of rows returned by executing the SQL command, which is mainly used to determine the impact of SELECT commands.
      

      Audit Fields

      The following fields are supported in TencentDB for MySQL audit logs. On the Audit Log tab, click the download icon in the upper right corner. After download, click the file list icon. On the page redirected to, copy the download address and access it to get the complete SQL audit logs.
      
      Note:
      Currently, you can download audit log files of a database instance only at the Tencent Cloud private network address by using a CVM instance in the same region. For example, to download the audit logs of database instances in Beijing region, download them with a CVM instance in Beijing.
      Log files are valid for 24 hours. Download them promptly.
      Up to 30 log files can be retained for one database instance. Delete files promptly after download.
      If the status is Failed, there may be too many logs. You can download them in batches by narrowing down the time range.
      No.
      Field
      Remarks
      
      1
      Time
      Records the exact timestamp of the operation.
      
      2
      Risk Level
      Indicates the risk level of the operation. The risks are divided into low, medium, and high risks. In full audit logs, the risk level will be displayed as "-" for logs that do not hit audit rules.
      
      3
      Client IP
      The IP address of the client initiating the database operation.
      
      4
      Database Name
      The name of the database involved in the operation.
      
      5
      Table Name
      The name of the specific data table (if any) involved in the operation. Up to 64 table names can be recorded.
      
      6
      User Account
      User account executing the operation.
      
      7
      SQL Type
      Type of the SQL statement, such as SELECT, INSERT, UPDATE, and DELETE.
      
      8
      SQL Details
      Specific SQL command text executed.
      
      9
      Error Code
      An error code is generated when an error occurs in the execution of an SQL statement. The error code is an integer used to identify a specific error type. 0 indicates success.
      
      10
      Thread ID
      Each client connected to the database has a unique thread ID. This ID is used to identify the client executing a specific operation.
      
      11
      Transaction ID
      In storage engines (such as InnoDB) that support transactions, each transaction has a unique transaction ID. This ID is used to identify a specific transaction.
      
      12
      Scanned Rows
      The number of rows scanned by the database when a query is executed. This number can help you understand the efficiency of the query.
      
      13
      Returned Rows
      The number of rows returned in the query results. This number can help you understand the result set size of the query.
      
      14
      Affected Rows
      The number of rows actually affected when modification operations (such as INSERT, UPDATE, and DELETE) are performed on a data table. This number can help you understand the impact range of the operation.
      
      15
      Execution Time (μs)
      The time from starting execution of an SQL statement to finishing it, in microseconds. This number can help you understand the performance of the query.
      
      16
      CPU Time (μs)
      The time spent executing SQL statements on the CPU, in microseconds. This number can help you understand the CPU usage for the query.
      
      17
      Lock Wait Time (μs)
      The time spent waiting to obtain a database lock, in microseconds. This number can help you understand the lock contention for the query.
      
      18
      IO Wait Time (ns)
      The time spent waiting for IO operations to complete, in nanoseconds. This number can help you understand the IO performance for the query.
      
      19
      Transaction Duration (μs)
      The total time consumed for a transaction from start to submission or rollback, in microseconds. This number can help you understand the performance of the transaction.
      
      20
      Policy Name
      -
      
      
      21
      Audit Rule
      This displays the rule template that the audit log has hit. By clicking on the corresponding rule template, you can see the specific details of the rule template, including basic information, parameter settings, and modification history.
      For historical audit logs, the value of the audit rule is displayed as "-".
      For full audit logs that haven't hit any rules, the value of the audit rule will be displayed as "-".
      

      Relationship Between SQL Statement Type and SQL Statement Mapping Object

      No.
      SQL Statement Type
      SQL Statement Mapping Object
      0
      OTHER
      All other SQL statement types except the following.
      1
      SELECT
      SQLCOM_SELECT
      2
      INSERT
      SQLCOM_INSERT, SQLCOM_INSERT_SELECT
      3
      UPDATE
      SQLCOM_UPDATE, SQLCOM_UPDATE_MULTI
      4
      DELETE
      SQLCOM_DELETE, SQLCOM_DELETE_MULTI, SQLCOM_TRUNCATE
      5
      CREATE
      SQLCOM_CREATE_TABLE, SQLCOM_CREATE_INDEX, SQLCOM_CREATE_DB, SQLCOM_CREATE_FUNCTION, SQLCOM_CREATE_USER, SQLCOM_CREATE_PROCEDURE, SQLCOM_CREATE_SPFUNCTION, SQLCOM_CREATE_VIEW, SQLCOM_CREATE_TRIGGER, SQLCOM_CREATE_SERVER, SQLCOM_CREATE_EVENT, SQLCOM_CREATE_ROLE, SQLCOM_CREATE_RESOURCE_GROUP, SQLCOM_CREATE_SRS
      6
      DROP
      SQLCOM_DROP_TABLE, SQLCOM_DROP_INDEX, SQLCOM_DROP_DB, SQLCOM_DROP_FUNCTION, SQLCOM_DROP_USER, SQLCOM_DROP_PROCEDURE, SQLCOM_DROP_VIEW, SQLCOM_DROP_TRIGGER, SQLCOM_DROP_SERVER, SQLCOM_DROP_EVENT, SQLCOM_DROP_ROLE, SQLCOM_DROP_RESOURCE_GROUP, SQLCOM_DROP_SRS
      7
      ALTER
      SQLCOM_ALTER_TABLE, SQLCOM_ALTER_DB, SQLCOM_ALTER_PROCEDURE, SQLCOM_ALTER_FUNCTION, SQLCOM_ALTER_TABLESPACE, SQLCOM_ALTER_SERVER, SQLCOM_ALTER_EVENT, SQLCOM_ALTER_USER, SQLCOM_ALTER_INSTANCE, SQLCOM_ALTER_USER_DEFAULT_ROLE, SQLCOM_ALTER_RESOURCE_GROUP
      8
      REPLACE
      SQLCOM_REPLACE, SQLCOM_REPLACE_SELECT
      9
      SET
      SQLCOM_SET_OPTION, SQLCOM_RESET, SQLCOM_SET_PASSWORD, SQLCOM_SET_ROLE, SQLCOM_SET_RESOURCE_GROUP
      10
      EXECUTE
      SQLCOM_EXECUTE
      11
      LOGIN
      Behavior of logging into the database, which is not constrained by audit rules. The login behavior is recorded by default.
      12
      LOGOUT
      Behavior of logging out of the database, which is not constrained by audit rules. The logout behavior is recorded by default.
      13
      CHANGEUSER
      Behavior of changing the user, which is not constrained by audit rules. The user change behavior is recorded by default.
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy