MySQL :: MySQL 5.6 Reference Manual :: 6.5.4.3 The Audit Log File

5.7 current
5.5

MySQL 5.6 Reference Manual / ... / The Audit Log File

6.5.4.3 The Audit Log File

Audit log file contents are not encrypted. See Section 6.5.4.2, “MySQL Enterprise Audit Security Considerations”.

The audit log file is written as XML, using UTF-8 (up to 4 bytes per character). The root element is <AUDIT>. The closing </AUDIT> tag of the root element is written when the audit log plugin terminates, so the tag is not present in the file while the plugin is active.

The root element contains <AUDIT_RECORD> elements, each of which provides information about an audited event.

In MySQL 5.6.14, a new audit log file format was implemented for better compatibility with Oracle Audit Vault. It is possible to select either the old or new format using the audit_log_format system variable, which has permitted values of OLD and NEW (default OLD).

If you change the value of audit_log_format, use this procedure to avoid writing log entries in one format to an existing log file that contains entries in a different format:

Stop the server.
Rename the current audit log file manually.
Restart the server with the new value of audit_log_format. The audit log plugin will create a new log file, which will contain log entries in the selected format.

Here is a sample log file in the default (old) format, reformatted slightly for readability:

<?xml version="1.0" encoding="utf-8"?>
<AUDIT>
  <AUDIT_RECORD
    TIMESTAMP="2012-08-02T14:52:12"
    NAME="Audit"
    SERVER_ID="1"
    VERSION="1"
    STARTUP_OPTIONS="--port=3306"
    OS_VERSION="i686-Linux"
    MYSQL_VERSION="5.6.10-log"/>
  <AUDIT_RECORD
    TIMESTAMP="2012-08-02T14:52:41"
    NAME="Connect"
    CONNECTION_ID="1"
    STATUS="0"
    USER="root"
    PRIV_USER="root"
    OS_LOGIN=""
    PROXY_USER=""
    HOST="localhost"
    IP="127.0.0.1"
    DB=""/>
  <AUDIT_RECORD
    TIMESTAMP="2012-08-02T14:53:45"
    NAME="Query"
    CONNECTION_ID="1"
    STATUS="0"
    SQLTEXT="INSERT INTO t1 () VALUES()"/>
  <AUDIT_RECORD
    TIMESTAMP="2012-08-02T14:53:51"
    NAME="Quit"
    CONNECTION_ID="1"
    STATUS="0"/>
  <AUDIT_RECORD
    TIMESTAMP="2012-08-06T14:21:03"
    NAME="NoAudit"
    SERVER_ID="1"/>
</AUDIT>

Here is a sample log file in the new format, reformatted slightly for readability:

<?xml version="1.0" encoding="utf-8"?>
<AUDIT>
 <AUDIT_RECORD>
  <TIMESTAMP>2013-09-17T15:03:24 UTC</TIMESTAMP>
  <RECORD_ID>1_2013-09-17T15:03:24</RECORD_ID>
  <NAME>Audit</NAME>
  <SERVER_ID>1</SERVER_ID>
  <VERSION>1</VERSION>
  <STARTUP_OPTIONS>/usr/local/mysql/bin/mysqld
    --socket=/usr/local/mysql/mysql.sock
    --port=3306</STARTUP_OPTIONS>
  <OS_VERSION>x86_64-osx10.6</OS_VERSION>
  <MYSQL_VERSION>5.7.2-m12-log</MYSQL_VERSION>
 </AUDIT_RECORD>
 <AUDIT_RECORD>
  <TIMESTAMP>2013-09-17T15:03:40 UTC</TIMESTAMP>
  <RECORD_ID>2_2013-09-17T15:03:24</RECORD_ID>
  <NAME>Connect</NAME>
  <CONNECTION_ID>2</CONNECTION_ID>
  <STATUS>0</STATUS>
  <STATUS_CODE>0</STATUS_CODE>
  <USER>root</USER>
  <OS_LOGIN></OS_LOGIN>
  <HOST>localhost</HOST>
  <IP>127.0.0.1</IP>
  <COMMAND_CLASS>connect</COMMAND_CLASS>
  <PRIV_USER>root</PRIV_USER>
  <PROXY_USER></PROXY_USER>
  <DB>test</DB>
 </AUDIT_RECORD>

...

 <AUDIT_RECORD>
  <TIMESTAMP>2013-09-17T15:03:41 UTC</TIMESTAMP>
  <RECORD_ID>4_2013-09-17T15:03:24</RECORD_ID>
  <NAME>Query</NAME>
  <CONNECTION_ID>2</CONNECTION_ID>
  <STATUS>0</STATUS>
  <STATUS_CODE>0</STATUS_CODE>
  <USER>root[root] @ localhost [127.0.0.1]</USER>
  <OS_LOGIN></OS_LOGIN>
  <HOST>localhost</HOST>
  <IP>127.0.0.1</IP>
  <COMMAND_CLASS>drop_table</COMMAND_CLASS>
  <SQLTEXT>DROP TABLE IF EXISTS t</SQLTEXT>
 </AUDIT_RECORD>
 <AUDIT_RECORD>
  <TIMESTAMP>2013-09-17T15:03:41 UTC</TIMESTAMP>
  <RECORD_ID>5_2013-09-17T15:03:24</RECORD_ID>
  <NAME>Query</NAME>
  <CONNECTION_ID>2</CONNECTION_ID>
  <STATUS>0</STATUS>
  <STATUS_CODE>0</STATUS_CODE>
  <USER>root[root] @ localhost [127.0.0.1]</USER>
  <OS_LOGIN></OS_LOGIN>
  <HOST>localhost</HOST>
  <IP>127.0.0.1</IP>
  <COMMAND_CLASS>create_table</COMMAND_CLASS>
  <SQLTEXT>CREATE TABLE t (i INT)</SQLTEXT>
 </AUDIT_RECORD>

...

 <AUDIT_RECORD>
  <TIMESTAMP>2013-09-17T15:03:41 UTC</TIMESTAMP>
  <RECORD_ID>7_2013-09-17T15:03:24</RECORD_ID>
  <NAME>Quit</NAME>
  <CONNECTION_ID>2</CONNECTION_ID>
  <STATUS>0</STATUS>
  <STATUS_CODE>0</STATUS_CODE>
  <USER></USER>
  <OS_LOGIN></OS_LOGIN>
  <HOST></HOST>
  <IP></IP>
  <COMMAND_CLASS>connect</COMMAND_CLASS>
 </AUDIT_RECORD>

...

 <AUDIT_RECORD>
  <TIMESTAMP>2013-09-17T15:03:47 UTC</TIMESTAMP>
  <RECORD_ID>9_2013-09-17T15:03:24</RECORD_ID>
  <NAME>Shutdown</NAME>
  <CONNECTION_ID>3</CONNECTION_ID>
  <STATUS>0</STATUS>
  <STATUS_CODE>0</STATUS_CODE>
  <USER>root[root] @ localhost [127.0.0.1]</USER>
  <OS_LOGIN></OS_LOGIN>
  <HOST>localhost</HOST>
  <IP>127.0.0.1</IP>
  <COMMAND_CLASS></COMMAND_CLASS>
 </AUDIT_RECORD>
 <AUDIT_RECORD>
  <TIMESTAMP>2013-09-17T15:03:47 UTC</TIMESTAMP>
  <RECORD_ID>10_2013-09-17T15:03:24</RECORD_ID>
  <NAME>Quit</NAME>
  <CONNECTION_ID>3</CONNECTION_ID>
  <STATUS>0</STATUS>
  <STATUS_CODE>0</STATUS_CODE>
  <USER></USER>
  <OS_LOGIN></OS_LOGIN>
  <HOST></HOST>
  <IP></IP>
  <COMMAND_CLASS>connect</COMMAND_CLASS>
 </AUDIT_RECORD>
 <AUDIT_RECORD>
  <TIMESTAMP>2013-09-17T15:03:49 UTC</TIMESTAMP>
  <RECORD_ID>11_2013-09-17T15:03:24</RECORD_ID>
  <NAME>NoAudit</NAME>
  <SERVER_ID>1</SERVER_ID>
 </AUDIT_RECORD>
</AUDIT>

Attributes of <AUDIT_RECORD> elements have these characteristics:

Some attributes appear in every element, but most are optional and do not necessarily appear in every element.
Order of attributes within an element is not guaranteed.
Attribute values are not fixed length. Long values may be truncated as indicated in the attribute descriptions given later.
The <, >, ", and & characters are encoded as <, >, ", and &, respectively. NUL bytes (U+00) are encoded as the ? character.
Characters not valid as XML characters are encoded using numeric character references. Valid XML characters are:
```
#x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF]
```

New Audit Log File Format

Every <AUDIT_RECORD> element contains a set of mandatory elements. Other optional elements may appear, depending on the audit record type.

The following elements are mandatory in every <AUDIT_RECORD> element:

<NAME>
A string representing the type of instruction that generated the audit event, such as a command that the server received from a client.
Example:
```
<NAME>Query</NAME>
```
Some common <NAME> values:
```
Audit    When auditing starts, which may be server startup time
Connect  When a client connects, also known as logging in
Query    An SQL statement (executed directly)
Prepare  Preparation of an SQL statement; usually followed by Execute
Execute  Execution of an SQL statement; usually follows Prepare
Shutdown Server shutdown
Quit     When a client disconnects
NoAudit  Auditing has been turned off
```
The possible values are Audit, Binlog Dump, Change user, Close stmt, Connect Out, Connect, Create DB, Daemon, Debug, Delayed insert, Drop DB, Execute, Fetch, Field List, Init DB, Kill, Long Data, NoAudit, Ping, Prepare, Processlist, Query, Quit, Refresh, Register Slave, Reset stmt, Set option, Shutdown, Sleep, Statistics, Table Dump, Time.
With the exception of Audit and NoAudit, these values correspond to the COM_xxx command values listed in the mysql_com.h header file. For example, Create DB and Shutdown correspond to COM_CREATE_DB and COM_SHUTDOWN, respectively.
<RECORD_ID>
A unique identifier for the audit record. The value is composed from a sequence number and timestamp, in the format SEQ_TIMESTAMP. The sequence number is initialized to the size of the audit log file at the time the audit log plugin opens it and increments by 1 for each record logged. The timestamp is a UTC value in yyyy-mm-ddThh:mm:ss format indicating the time when the audit log plugin opened the file.
Example:
```
<RECORD_ID>28743_2013-09-18T21:03:24</RECORD_ID>
```
<TIMESTAMP>
The date and time that the audit event was generated. For example, the event corresponding to execution of an SQL statement received from a client has a <TIMESTAMP> value occurring after the statement finishes, not when it is received. The value has the format yyyy-mm-ddThh:mm:ss UTC (with T, no decimals). The format includes a time zone specifier at the end. The time zone is always UTC.
Example:
```
<TIMESTAMP>2013-09-17T15:03:49 UTC</TIMESTAMP>
```

The following elements are optional in <AUDIT_RECORD> elements. Many of them occur only with specific <NAME> values.

<COMMAND_CLASS>
A string that indicates the type of action performed.
Example:
```
<COMMAND_CLASS>drop_table</COMMAND_CLASS>
```
The values come from the com_status_vars array in the sql/mysqld.cc file in a MySQL source distribution. They correspond to the status variables displayed by this statement:
```
SHOW STATUS LIKE 'Com%';
```
<CONNECTION_ID>
An unsigned integer representing the client connection identifier. This is the same as the CONNECTION_ID() function value within the session.
Example:
```
<CONNECTION_ID>127</CONNECTION_ID>
```
<DB>
A string representing the default database name. This element appears only if the <NAME> value is Connect or Change user.
<HOST>
A string representing the client host name. This element appears only if the <NAME> value is Connect, Change user, or Query.
Example:
```
<HOST>localhost</HOST>
```
<IP>
A string representing the client IP address. This element appears only if the <NAME> value is Connect, Change user, or Query.
Example:
```
<IP>127.0.0.1</IP>
```
<MYSQL_VERSION>
A string representing the MySQL server version. This is the same as the value of the VERSION() function or version system variable. This element appears only if the <NAME> value is Audit.
Example:
```
<MYSQL_VERSION>5.7.1-m11-log</MYSQL_VERSION>
```
<OS_LOGIN>
A string representing the external user name used during the authentication process, as set by the plugin used to authenticate the client. With native (built-in) MySQL authentication, or if the plugin does not set the value, this variable is NULL. The value is the same as that of the external_user system variable. See Section 6.3.8, “Proxy Users”.
This element appears only if the <NAME> value is Connect, Change user, or Query.
<OS_VERSION>
A string representing the operating system on which the server was built or is running. This element appears only if the <NAME> value is Audit.
Example:
```
<OS_VERSION>x86_64-Linux</OS_VERSION>
```
<PRIV_USER>
A string representing the user that the server authenticated the client as. This is the user name that the server uses for privilege checking, and may differ from the <USER> value. This element appears only if the <NAME> value is Connect or Change user.
<PROXY_USER>
A string representing the proxy user. The value is empty if user proxying is not in effect. This element appears only if the <NAME> value is Connect or Change user.
<SERVER_ID>
An unsigned integer representing the server ID. This is the same as the value of the server_id system variable. This element appears only if the <NAME> value is Audit or NoAudit.
Example:
```
<SERVER_ID>1</SERVER_ID>
```
<SQLTEXT>
A string representing the text of an SQL statement. The value can be empty. Long values may be truncated. This element appears only if the <NAME> value is Query or Execute.
The string, like the audit log file itself, is written using UTF-8 (up to 4 bytes per character), so the value may be the result of conversion. For example, the original statement might have been received from the client as an SJIS string.
Example:
```
<SQLTEXT>DELETE FROM t1</SQLTEXT>
```
<STARTUP_OPTIONS>
A string representing the options that were given on the command line or in option files when the MySQL server was started. This element appears only if the <NAME> value is Audit.
Example:
```
<STARTUP_OPTIONS>/usr/local/mysql/bin/mysqld
  --port=3306 --log-output=FILE</STARTUP_OPTIONS>
```
<STATUS>
An unsigned integer representing the command status: 0 for success, nonzero if an error occurred. This is the same as the value of the mysql_errno() C API function.
The audit log does not contain the SQLSTATE value or error message. To see the associations between error codes, SQLSTATE values, and messages, see Section B.3, “Server Error Codes and Messages”.
Warnings are not logged.
See the description for <STATUS_CODE> for information about how it differs from <STATUS>.
Example:
```
<STATUS>1051</STATUS>
```
<STATUS_CODE>
An unsigned integer representing the command status: 0 for success, 1 if an error occurred.
The STATUS_CODE value differs from the STATUS value: STATUS_CODE is 0 for success and 1 for error, which is compatible with the EZ_collector consumer for Audit Vault. STATUS is the value of the mysql_errno() C API function. This is 0 for success and nonzero for error, and thus is not necessarily 1 for error.
Example:
```
<STATUS_CODE>0</STATUS_CODE>
```
<USER>
A string representing the user name sent by the client. This may differ from the <PRIV_USER> value. This element appears only if the <NAME> value is Connect, Change user, or Query.
Example:
```
<USER>root[root] @ localhost [127.0.0.1]</USER>
```
<VERSION>
An unsigned integer representing the version of the audit log file format. This element appears only if the <NAME> value is Audit.
Example:
```
<VERSION>1</VERSION>
```

Old Audit Log File Format

Every <AUDIT_RECORD> element contains a set of mandatory attributes. Other optional attributes may appear depending on the audit record type.

The following attributes are mandatory in every <AUDIT_RECORD> element:

NAME
A string representing the type of instruction that generated the audit event, such as a command that the server received from a client.
Example: NAME="Query"
Some common NAME values:
```
"Audit"    When auditing starts, which may be server startup time
"Connect"  When a client connects, also known as logging in
"Query"    An SQL statement (executed directly)
"Prepare"  Preparation of an SQL statement; usually followed by Execute
"Execute"  Execution of an SQL statement; usually follows Prepare
"Shutdown" Server shutdown
"Quit"     When a client disconnects
"NoAudit"  Auditing has been turned off
```
The possible values are "Audit", "Binlog Dump", "Change user", "Close stmt", "Connect Out", "Connect", "Create DB", "Daemon", "Debug", "Delayed insert", "Drop DB", "Execute", "Fetch", "Field List", "Init DB", "Kill", "Long Data", "NoAudit", "Ping", "Prepare", "Processlist", "Query", "Quit", "Refresh", "Register Slave", "Reset stmt", "Set option", "Shutdown", "Sleep", "Statistics", "Table Dump", "Time".
With the exception of "Audit" and "NoAudit", these values correspond to the COM_xxx command values listed in the mysql_com.h header file. For example, "Create DB" and "Shutdown" correspond to COM_CREATE_DB and COM_SHUTDOWN, respectively.
TIMESTAMP
The date and time that the audit event was generated. For example, the event corresponding to execution of an SQL statement received from a client has a TIMESTAMP value occurring after the statement finishes, not when it is received. The value is UTC, in the format yyyy-mm-ddThh:mm:ss (with T, no decimals).
Example: TIMESTAMP="2012-08-09T12:55:16"

The following attributes are optional in <AUDIT_RECORD> elements. Many of them occur only for elements with specific values of the NAME attribute.

CONNECTION_ID
An unsigned integer representing the client connection identifier. This is the same as the CONNECTION_ID() function value within the session.
Example: CONNECTION_ID="127"
DB
A string representing the default database name. This attribute appears only if the NAME value is "Connect" or "Change user".
HOST
A string representing the client host name. This attribute appears only if the NAME value is "Connect" or "Change user".
Example: HOST="localhost"
IP
A string representing the client IP address. This attribute appears only if the NAME value is "Connect" or "Change user".
Example: IP="127.0.0.1"
MYSQL_VERSION
A string representing the MySQL server version. This is the same as the value of the VERSION() function or version system variable. This attribute appears only if the NAME value is "Audit".
Example: MYSQL_VERSION="5.6.11-log"
OS_LOGIN
A string representing the external user (empty if none). The value may differ from USER, for example, if the server authenticates the client using an external authentication method. This attribute appears only if the NAME value is "Connect" or "Change user".
OS_VERSION
A string representing the operating system on which the server was built or is running. This attribute appears only if the NAME value is "Audit".
Example: OS_VERSION="x86_64-Linux"
PRIV_USER
A string representing the user that the server authenticated the client as. This is the user name that the server uses for privilege checking, and may be different from the USER value. This attribute appears only if the NAME value is "Connect" or "Change user".
PROXY_USER
A string representing the proxy user. The value is empty if user proxying is not in effect. This attribute appears only if the NAME value is "Connect" or "Change user".
SERVER_ID
An unsigned integer representing the server ID. This is the same as the value of the server_id system variable. This attribute appears only if the NAME value is "Audit" or "NoAudit".
Example: SERVER_ID="1"
SQLTEXT
A string representing the text of an SQL statement. The value can be empty. Long values may be truncated. This attribute appears only if the NAME value is "Query" or "Execute".
The string, like the audit log file itself, is written using UTF-8 (up to 4 bytes per character), so the value may be the result of conversion. For example, the original statement might have been received from the client as an SJIS string.
Example: SQLTEXT="DELETE FROM t1"
STARTUP_OPTIONS
A string representing the options that were given on the command line or in option files when the MySQL server was started. This attribute appears only if the NAME value is "Audit".
Example: STARTUP_OPTIONS="--port=3306 --log-output=FILE"
STATUS
An unsigned integer representing the command status: 0 for success, nonzero if an error occurred. This is the same as the value of the mysql_errno() C API function.
The audit log does not contain the SQLSTATE value or error message. To see the associations between error codes, SQLSTATE values, and messages, see Section B.3, “Server Error Codes and Messages”.
Warnings are not logged.
Example: STATUS="1051"
USER
A string representing the user name sent by the client. This may be different from the PRIV_USER value. This attribute appears only if the NAME value is "Connect" or "Change user".
VERSION
An unsigned integer representing the version of the audit log file format. This attribute appears only if the NAME value is "Audit".
Example: VERSION="1"

PREV HOME UP NEXT

User Comments