MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
The MySQL server calls the audit log plugin to write an audit
record to its log file whenever an auditable event occurs.
Typically the first audit record written after plugin startup
contains the server description and startup options. Elements
following that one represent events such as client connect and
disconnect events, executed SQL statements, and so forth. Only
top-level statements are logged, not statements within stored
programs such as triggers or stored procedures. Contents of
files referenced by statements such as LOAD
DATA
are not logged.
To select the log format that the audit log plugin uses to write
its log file, set the
audit_log_format
system
variable at server startup. These formats are available:
New-style XML format
(audit_log_format=NEW
): An
XML format that has better compatibility with Oracle Audit
Vault than old-style XML format. MySQL 5.7 introduced this
format, which was backported to MySQL 5.6 as of
MySQL 5.6.14.
Old-style XML format
(audit_log_format=OLD
): The
original audit log format used by default in older MySQL
series. MySQL 5.6 uses old-style XML format by
default.
Changing the value of
audit_log_format
can result
in writing log entries in one format to an existing log file
that contains entries in a different format. To avoid this
issue, use the procedure described at
Audit Log File Format.
Audit log file contents are not encrypted. See Section 6.4.4.2, “MySQL Enterprise Audit Security Considerations”.
The following sections describe the available audit logging formats:
Here is a sample log file in new-style XML format
(audit_log_format=NEW
),
reformatted slightly for readability:
<?xml version="1.0" encoding="utf-8"?> <AUDIT> <AUDIT_RECORD> <TIMESTAMP>2017-10-16T14:06:33 UTC</TIMESTAMP> <RECORD_ID>1_2017-10-16T14:06:33</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>i686-Linux</OS_VERSION> <MYSQL_VERSION>5.6.39-log</MYSQL_VERSION> </AUDIT_RECORD> <AUDIT_RECORD> <TIMESTAMP>2017-10-16T14:09:38 UTC</TIMESTAMP> <RECORD_ID>2_2017-10-16T14:06:33</RECORD_ID> <NAME>Connect</NAME> <CONNECTION_ID>5</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER>root</USER> <OS_LOGIN/> <HOST>localhost</HOST> <IP>127.0.0.1</IP> <COMMAND_CLASS>connect</COMMAND_CLASS> <PRIV_USER>root</PRIV_USER> <PROXY_USER/> <DB>test</DB> </AUDIT_RECORD> ... <AUDIT_RECORD> <TIMESTAMP>2017-10-16T14:09:38 UTC</TIMESTAMP> <RECORD_ID>6_2017-10-16T14:06:33</RECORD_ID> <NAME>Query</NAME> <CONNECTION_ID>5</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER>root[root] @ localhost [127.0.0.1]</USER> <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>2017-10-16T14:09:39 UTC</TIMESTAMP> <RECORD_ID>8_2017-10-16T14:06:33</RECORD_ID> <NAME>Quit</NAME> <CONNECTION_ID>5</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER>root</USER> <OS_LOGIN/> <HOST>localhost</HOST> <IP>127.0.0.1</IP> <COMMAND_CLASS>connect</COMMAND_CLASS> </AUDIT_RECORD> ... <AUDIT_RECORD> <TIMESTAMP>2017-10-16T14:09:43 UTC</TIMESTAMP> <RECORD_ID>11_2017-10-16T14:06:33</RECORD_ID> <NAME>Quit</NAME> <CONNECTION_ID>6</CONNECTION_ID> <STATUS>0</STATUS> <STATUS_CODE>0</STATUS_CODE> <USER>root</USER> <OS_LOGIN/> <HOST>localhost</HOST> <IP>127.0.0.1</IP> <COMMAND_CLASS>connect</COMMAND_CLASS> </AUDIT_RECORD> <AUDIT_RECORD> <TIMESTAMP>2017-10-16T14:09:45 UTC</TIMESTAMP> <RECORD_ID>12_2017-10-16T14:06:33</RECORD_ID> <NAME>NoAudit</NAME> <SERVER_ID>1</SERVER_ID> </AUDIT_RECORD> </AUDIT>
The audit log file is written as XML, using UTF-8 (up to 4
bytes per character). The root element is
<AUDIT>
. The root element contains
<AUDIT_RECORD>
elements, each of
which provides information about an audited event. When the
audit log plugin begins writing a new log file, it writes the
XML declaration and opening <AUDIT>
root element tag. When the plugin closes a log file, it writes
the closing </AUDIT>
root element
tag. The closing tag is not present while the file is open.
Elements within <AUDIT_RECORD>
elements have these characteristics:
Some elements appear in every
<AUDIT_RECORD>
element. Others
are optional and may appear depending on the audit record
type.
Order of elements within an
<AUDIT_RECORD>
element is not
guaranteed.
Element values are not fixed length. Long values may be truncated as indicated in the element 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]
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_
command values listed in the
xxx
mysql_com.h
header file. For example,
Create DB
and Change
user
correspond to
COM_CREATE_DB
and
COM_CHANGE_USER
, respectively.
<RECORD_ID>
A unique identifier for the audit record. The value is
composed from a sequence number and timestamp, in the
format
.
When the audit log plugin opens the audit log file, it
initializes the sequence number to the size of the audit
log file, then increments the sequence by 1 for each
record logged. The timestamp is a UTC value in
SEQ_TIMESTAMP
format indicating the date and time when the audit log
plugin opened the file.
YYYY-MM-DD
Thh:mm:ss
Example:
<RECORD_ID>12_2017-10-16T14:06:33</RECORD_ID>
<TIMESTAMP>
A string representing a UTC value in
format indicating the date and time when the
audit event was generated. For example, the event
corresponding to execution of an SQL statement received
from a client has a YYYY-MM-DD
Thh:mm:ss
UTC<TIMESTAMP>
value occurring after the statement finishes, not when it
was received.
Example:
<TIMESTAMP>2017-10-16T14:09:45 UTC</TIMESTAMP>
The following elements are optional in
<AUDIT_RECORD>
elements. Many of them
occur only with specific <NAME>
element values.
<COMMAND_CLASS>
A string that indicates the type of action performed.
Example:
<COMMAND_CLASS>drop_table</COMMAND_CLASS>
The values correspond to the
Com_
status variables that indicate command counts; for example
xxx
Com_drop_table
and
Com_select
count
DROP TABLE
and
SELECT
statements,
respectively. The following statement displays the
possible names:
SELECT LOWER(REPLACE(VARIABLE_NAME, 'COM_', '')) AS name FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE 'COM%' ORDER BY name;
<CONNECTION_ID>
An unsigned integer representing the client connection
identifier. This is the same as the value returned by the
CONNECTION_ID()
function
within the session.
Example:
<CONNECTION_ID>127</CONNECTION_ID>
<DB>
A string representing the default database name.
Example:
<DB>test</DB>
<HOST>
A string representing the client host name.
Example:
<HOST>localhost</HOST>
<IP>
A string representing the client IP address.
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.
Example:
<MYSQL_VERSION>5.6.39-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 element is empty. The value is the same as that of
the external_user
system
variable (see Section 6.2.12, “Proxy Users”).
Example:
<OS_LOGIN>jeffrey</OS_LOGIN>
<OS_VERSION>
A string representing the operating system on which the server was built or is running.
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.
Example:
<PRIV_USER>jeffrey</PRIV_USER>
<PROXY_USER>
A string representing the proxy user (see Section 6.2.12, “Proxy Users”). The value is empty if user proxying is not in effect.
Example:
<PROXY_USER>developer</PROXY_USER>
<SERVER_ID>
An unsigned integer representing the server ID. This is
the same as the value of the
server_id
system
variable.
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. 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. The first option is the path to the server executable.
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. See the description for
<STATUS_CODE>
for information
about how it differs from
<STATUS>
.
The audit log does not contain the SQLSTATE value or error message. To see the associations between error codes, SQLSTATE values, and messages, see Server Error Message Reference.
Warnings are not logged.
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.
Example:
<USER>root[root] @ localhost [127.0.0.1]</USER>
<VERSION>
An unsigned integer representing the version of the audit log file format.
Example:
<VERSION>1</VERSION>
Here is a sample log file in old-style XML format
(audit_log_format=OLD
),
reformatted slightly for readability:
<?xml version="1.0" encoding="utf-8"?> <AUDIT> <AUDIT_RECORD TIMESTAMP="2017-10-16T14:25:00 UTC" RECORD_ID="1_2017-10-16T14:25:00" NAME="Audit" SERVER_ID="1" VERSION="1" STARTUP_OPTIONS="--port=3306" OS_VERSION="i686-Linux" MYSQL_VERSION="5.6.39-log"/> <AUDIT_RECORD TIMESTAMP="2017-10-16T14:25:24 UTC" RECORD_ID="2_2017-10-16T14:25:00" NAME="Connect" CONNECTION_ID="4" STATUS="0" STATUS_CODE="0" USER="root" OS_LOGIN="" HOST="localhost" IP="127.0.0.1" COMMAND_CLASS="connect" PRIV_USER="root" PROXY_USER="" DB="test"/> ... <AUDIT_RECORD TIMESTAMP="2017-10-16T14:25:24 UTC" RECORD_ID="6_2017-10-16T14:25:00" NAME="Query" CONNECTION_ID="4" STATUS="0" STATUS_CODE="0" USER="root[root] @ localhost [127.0.0.1]" OS_LOGIN="" HOST="localhost" IP="127.0.0.1" COMMAND_CLASS="drop_table" SQLTEXT="DROP TABLE IF EXISTS t"/> ... <AUDIT_RECORD TIMESTAMP="2017-10-16T14:25:24 UTC" RECORD_ID="8_2017-10-16T14:25:00" NAME="Quit" CONNECTION_ID="4" STATUS="0" STATUS_CODE="0" USER="root" OS_LOGIN="" HOST="localhost" IP="127.0.0.1" COMMAND_CLASS="connect" <AUDIT_RECORD TIMESTAMP="2017-10-16T14:25:32 UTC" RECORD_ID="12_2017-10-16T14:25:00" NAME="NoAudit" SERVER_ID="1"/> </AUDIT>
The audit log file is written as XML, using UTF-8 (up to 4
bytes per character). The root element is
<AUDIT>
. The root element contains
<AUDIT_RECORD>
elements, each of
which provides information about an audited event. When the
audit log plugin begins writing a new log file, it writes the
XML declaration and opening <AUDIT>
root element tag. When the plugin closes a log file, it writes
the closing </AUDIT>
root element
tag. The closing tag is not present while the file is open.
Attributes of <AUDIT_RECORD>
elements
have these characteristics:
Some attributes appear in every
<AUDIT_RECORD>
element. Others
are optional and may appear depending on the audit record
type.
Order of attributes within an
<AUDIT_RECORD>
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]
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_
command values listed in the
xxx
mysql_com.h
header file. For example,
"Create DB"
and "Change
user"
correspond to
COM_CREATE_DB
and
COM_CHANGE_USER
, respectively.
RECORD_ID
A unique identifier for the audit record. The value is
composed from a sequence number and timestamp, in the
format
.
When the audit log plugin opens the audit log file, it
initializes the sequence number to the size of the audit
log file, then increments the sequence by 1 for each
record logged. The timestamp is a UTC value in
SEQ_TIMESTAMP
format indicating the date and time when the audit log
plugin opened the file.
YYYY-MM-DD
Thh:mm:ss
Example:
RECORD_ID="12_2017-10-16T14:25:00"
TIMESTAMP
A string representing a UTC value in
format indicating the date and time when the
audit event was generated. For example, the event
corresponding to execution of an SQL statement received
from a client has a YYYY-MM-DD
Thh:mm:ss
UTCTIMESTAMP
value
occurring after the statement finishes, not when it was
received.
Example: TIMESTAMP="2017-10-16T14:25:32
UTC"
The following attributes are optional in
<AUDIT_RECORD>
elements. Many of them
occur only for elements with specific values of the
NAME
attribute.
COMMAND_CLASS
A string that indicates the type of action performed.
Example: COMMAND_CLASS="drop_table"
The values correspond to the
Com_
status variables that indicate command counts; for example
xxx
Com_drop_table
and
Com_select
count
DROP TABLE
and
SELECT
statements,
respectively. The following statement displays the
possible names:
SELECT LOWER(REPLACE(VARIABLE_NAME, 'COM_', '')) AS name FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE 'COM%' ORDER BY name;
CONNECTION_ID
An unsigned integer representing the client connection
identifier. This is the same as the value returned by the
CONNECTION_ID()
function
within the session.
Example: CONNECTION_ID="127"
DB
A string representing the default database name.
Example: DB="test"
HOST
A string representing the client host name.
Example: HOST="localhost"
IP
A string representing the client IP address.
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.
Example: MYSQL_VERSION="5.6.39-log"
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 attribute is empty. The value is the same as that of
the external_user
system
variable (see Section 6.2.12, “Proxy Users”).
Example: OS_LOGIN="jeffrey"
OS_VERSION
A string representing the operating system on which the server was built or is running.
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 it may differ
from the USER
value.
Example: PRIV_USER="jeffrey"
PROXY_USER
A string representing the proxy user (see Section 6.2.12, “Proxy Users”). The value is empty if user proxying is not in effect.
Example: PROXY_USER="developer"
SERVER_ID
An unsigned integer representing the server ID. This is
the same as the value of the
server_id
system
variable.
Example: SERVER_ID="1"
SQLTEXT
A string representing the text of an SQL statement. The value can be empty. Long values may be truncated. 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.
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. See the description for
STATUS_CODE
for information about how
it differs from STATUS
.
The audit log does not contain the SQLSTATE value or error message. To see the associations between error codes, SQLSTATE values, and messages, see Server Error Message Reference.
Warnings are not logged.
Example: STATUS="1051"
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"
USER
A string representing the user name sent by the client.
This may differ from the PRIV_USER
value.
VERSION
An unsigned integer representing the version of the audit log file format.
Example: VERSION="1"