Percona Audit Log Plugin provides monitoring and logging of connection and query activity that were performed on specific server. Information about the activity will be stored in the XML log file where each event will have its NAME field, its own unique RECORD_ID field and a TIMESTAMP field. This implementation is alternative to the MySQL Enterprise Audit Log Plugin

Percona 審計日志插件 提供特定服務器上的 連接、查詢活動的監(jiān)控和記錄。信息會被記錄到XML格式的文檔中，

文檔中包含名字、唯一ID和時間字段等。這個功能是根據(jù)mysql 企業(yè)版的審計日志插件改良得到。使用方式和mysql企業(yè)版相似

Audit Log plugin produces the log of following events:

審計日志插件 記錄如下的事件

Audit - Audit event indicates that audit logging started or finished. NAME field will be Audit when logging started and NoAudit when logging finished. Audit record also includes server version and command-line arguments.

審計-審計事件記錄審計日志開啟和關閉。開啟為Audit，關閉為NoAudit。其中包含系統(tǒng)版本和命令行啟動命令

Example of the Audit event:

例子如下

<AUDIT_RECORD
 "NAME"="Audit"
 "RECORD"="1_2014-04-29T09:29:40"
 "TIMESTAMP"="2014-04-29T09:29:40 UTC"
 "MYSQL_VERSION"="5.6.17-65.0-655.trusty"
 "STARTUP_OPTIONS"="--basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306"
 "OS_VERSION"="x86_64-debian-linux-gnu",
 />

Connect/Disconnect - Connect record event will have NAME field Connect when user logged in or login failed, or Quit when connection is closed. Additional fields for this event are CONNECTION_ID, STATUS, USER, PRIV_USER, OS_LOGIN, PROXY_USER, HOST, and IP. STATUS will be 0 for successful logins and non-zero for failed logins.

連接/斷開連接 - 連接記錄 會記錄登錄，登錄失敗，退出等信息。附加字段記錄連接ID，狀態(tài)，用戶，權限，系統(tǒng)用戶，代理用戶，主機名，IP地址。

狀態(tài)值為0時表示成功登錄，非0表示失敗的連接
Example of the Disconnect event:

例子如下

<AUDIT_RECORD
 "NAME"="Quit"
 "RECORD"="24_2014-04-29T09:29:40"
 "TIMESTAMP"="2014-04-29T10:20:13 UTC"
 "CONNECTION_ID"="49"
 "STATUS"="0"
 "USER"=""
 "PRIV_USER"=""
 "OS_LOGIN"=""
 "PROXY_USER"=""
 "HOST"=""
 "IP"=""
 "DB"=""
 />

Query - Additional fields for this event are: COMMAND_CLASS (values come from the com_status_vars array in the sql/mysqld.cc` file in a MySQL source distribution. Examples are select, alter_table, create_table, etc.), CONNECTION_ID, STATUS (indicates error when non-zero), SQLTEXT (text of SQL-statement), USER, HOST, OS_USER, IP. Possible values for the NAME name field for this event are Query, Prepare, Execute, Change user, etc.

查詢 - 附加字段為 命令種類（值取自mysql源碼sql/mysqld.cc中的com_status_vars數(shù)組，例如select, alter_table, create_table,等）,連接ID,狀態(tài)（非0代表錯誤）,SQL記錄，用戶，主機名，系統(tǒng)用戶，IP。name字段可能為Query, Prepare, Execute, Change user，等

Example of the Query event:

例子如下

<AUDIT_RECORD
 "NAME"="Query"
 "RECORD"="23_2014-04-29T09:29:40"
 "TIMESTAMP"="2014-04-29T10:20:10 UTC"
 "COMMAND_CLASS"="select"
 "CONNECTION_ID"="49"
 "STATUS"="0"
 "SQLTEXT"="SELECT * from mysql.user"
 "USER"="root[root] @ localhost []"
 "HOST"="localhost"
 "OS_USER"=""
 "IP"=""
 />

Installation
Audit Log plugin is shipped with Percona Server, but it is not installed by default. To enable the plugin you must run the following

審計日志插件隨percona Server分發(fā)，但不默認安裝

command:

命令，使用install 命令會將插件記錄到mysql.plugin表中，隨數(shù)據(jù)庫啟動加載

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

You can check if the plugin is loaded correctly by running:

運行下面命令查看是否安裝成功

SHOW PLUGINS;

Audit log should be listed in the output:


+--------------------------------+----------+--------------------+--------------+---------+
| Name                           | Status   | Type               | Library      | License |
+--------------------------------+----------+--------------------+--------------+---------+
...
| audit_log                      | ACTIVE   | AUDIT              | audit_log.so | GPL     |
+--------------------------------+----------+--------------------+--------------+---------+

Log Format

日志格式

The audit log plugin supports four log formats: OLD, NEW, JSON, and CSV. OLD and NEW formats are based on XML, where the former outputs log record properties as XML attributes and the latter as XML tags. Information logged is the same in all four formats. The log format choice is controlled by audit_log_format variable.

審計日志格式支持四種日志格式：OLD, NEW, JSON, and CSV。OLD和NEW格式基于XML。四種格式記錄的信息都是相同的。使用變量 audit_log_format

選擇使用哪種格式

Example of the OLD format:

OLD格式示例

<AUDIT_RECORD
 "NAME"="Query"
 "RECORD"="2_2014-04-28T09:29:40"
 "TIMESTAMP"="2014-04-28T09:29:40 UTC"
 "COMMAND_CLASS"="install_plugin"
 "CONNECTION_ID"="47"
 "STATUS"="0"
 "SQLTEXT"="INSTALL PLUGIN audit_log SONAME 'audit_log.so'"
 "USER"="root[root] @ localhost []"
 "HOST"="localhost"
 "OS_USER"=""
 "IP"=""
/>

Example of the NEW format:

NEW格式示例

<AUDIT_RECORD>
 <NAME>Quit</NAME>
 <RECORD>10902_2014-04-28T11:02:54</RECORD>
 <TIMESTAMP>2014-04-28T11:02:59 UTC</TIMESTAMP>
 <CONNECTION_ID>36</CONNECTION_ID>
 <STATUS>0</STATUS>
 <USER></USER>
 <PRIV_USER></PRIV_USER>
 <OS_LOGIN></OS_LOGIN>
 <PROXY_USER></PROXY_USER>
 <HOST></HOST>
 <IP></IP>
 <DB></DB>
</AUDIT_RECORD>

Example of the JSON format:

JSON格式示例

{"audit_record":{"name":"Query","record":"4707_2014-08-27T10:43:52","timestamp":"2014-08-27T10:44:19 UTC","command_class":"show_databases","connection_id":"37","status":0,"sqltext":"show databases","user":"root[root] @ localhost []","host":"localhost","os_user":"","ip":""}}

Example of the CSV format:

CSV格式示例

"Query","49284_2014-08-27T10:47:11","2014-08-27T10:47:23 UTC","show_databases","37",0,"show databases","root[root] @ localhost []","localhost","",""

Streaming the audit log to syslog

將審計日志記錄到SYSlog

To stream the audit log to syslog you’ll need to set audit_log_handler variable to SYSLOG. To control the syslog file handler, the following variables can be used: audit_log_syslog_ident, audit_log_syslog_facility, and audit_log_syslog_priority These variables have the same meaning as appropriate parameters described in the syslog(3) manual.

為了將審計日志記錄到系統(tǒng)日志，需要設置audit_log_handler 變量為SYSLOG。以下變量可以使用：audit_log_syslog_ident, audit_log_syslog_facility, and audit_log_syslog_priority。這些變量在手冊中使用適當?shù)膮?shù)具有相同的意義

Note

注意

Variables: audit_log_strategy, audit_log_buffer_size, audit_log_rotate_on_size, audit_log_rotations have effect only with FILE handler.

Filtering by user

通過用戶過濾

In 5.7.14-7 Percona Server has implemented filtering by user. This was implemented by adding two new global variables: audit_log_include_accounts and audit_log_exclude_accounts to specify which user accounts should be included or excluded from audit logging.

在5.7.14-7版本，添加按用戶過濾功能。添加兩個新的系統(tǒng)變量：audit_log_include_accounts 和 audit_log_exclude_accounts，用來確定哪些賬戶被記錄或被從審計中排除

Warning

注意

Only one of these variables can contain a list of users to be either included or excluded, while the other needs to be NULL. If one of the variables is set to be not NULL (contains a list of users), the attempt to set another one will fail. Empty string means an empty list.

同一時間只有一個參數(shù)會生效，如要使用一個參數(shù)，另一個參數(shù)需要設置為null，否則會添加失敗?？兆址馕吨樟斜怼?/h1>

Note

注意

Changes of audit_log_include_accounts and audit_log_exclude_accounts do not apply to existing server connections.
改變這兩個變量不會應用于已存在的連接

Example
Following example shows adding users who will be monitored:

下面的例子添加需要監(jiān)控的用戶

mysql> SET GLOBAL audit_log_include_accounts = 'user1@localhost,root@localhost';
Query OK, 0 rows affected (0.00 sec)

If you you try to add users to both include and exclude lists server will show you the following error:

如果你再設置另一個參數(shù)，會報下面的錯誤

mysql> SET GLOBAL audit_log_exclude_accounts = 'user1@localhost,root@localhost';
ERROR 1231 (42000): Variable 'audit_log_exclude_accounts' can't be set to the value of 'user1@localhost,root@localhost'

To switch from filtering by included user list to the excluded one or back, first set the currently active filtering variable to NULL:

為了轉換兩個參數(shù)，需要先把之前的參數(shù)設置為NULL；

mysql> SET GLOBAL audit_log_include_accounts = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_accounts = 'user1@localhost,root@localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_accounts = "'user'@'host'";
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_accounts = '''user''@''host''';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_accounts = '\'user\'@\'host\'';
Query OK, 0 rows affected (0.00 sec)

To see what users are currently in the on the list you can run:

mysql> SELECT @@audit_log_exclude_accounts;
+------------------------------+
| @@audit_log_exclude_accounts |
+------------------------------+
| 'user'@'host'                |
+------------------------------+
1 row in set (0.00 sec)

Account names from mysql.user table are the one that are logged in the audit log. For example when you create a user:

mysql> CREATE USER 'user1'@'%' IDENTIFIED BY '111';
Query OK, 0 rows affected (0.00 sec)
This is what you’ll see when user1 connected from localhost:

<AUDIT_RECORD
  NAME="Connect"
  RECORD="4971917_2016-08-22T09:09:10"
  TIMESTAMP="2016-08-22T09:12:21 UTC"
  CONNECTION_ID="6"
  STATUS="0"
  USER="user1" ;; this is a 'user' part of account in 5.7
  PRIV_USER="user1"
  OS_LOGIN=""
  PROXY_USER=""
  HOST="localhost" ;; this is a 'host' part of account in 5.7
  IP=""
  DB=""
/>

To exclude user1 from logging in Percona Server 5.7 you must set:

SET GLOBAL audit_log_exclude_accounts = 'user1@%';

The value can be NULL or comma separated list of accounts in form user@host or 'user'@'host' (if user or host contains comma).

用戶的格式可以使用分好分隔，或不使用分號

Filtering by SQL command type

按sql 命令類型過濾

In 5.7.14-7 Percona Server has implemented filtering by SQL command type. This was implemented by adding two new global variables: audit_log_include_commands and audit_log_exclude_commands to specify which command types should be included or excluded from audit logging.

在5.7.14版本的 Percona Server中，增加了按SQL命令類型過濾的功能。通過增加兩個新的全局變量來實現(xiàn)：audit_log_include_commands和audit_log_exclude_commands,用來分辨哪些命令類型被記錄到日志中

Warning

Only one of these variables can contain a list of command types to be either included or excluded, while the other needs to be NULL. If one of the variables is set to be not NULL (contains a list of command types), the attempt to set another one will fail. Empty string means an empty list.

只有一個參數(shù)可以被設置，另一個需要為NULL，

Note

If both audit_log_exclude_commands and audit_log_include_commands are NULL all commands will be logged.

當兩個參數(shù)都為NULL時，所有的命令都會被記錄

Example

例子

The available command types can be listed by running:

可用的命令類型可以用運行下面的SQL的到（percona版本）：

mysql> SELECT name FROM performance_schema.setup_instruments WHERE name LIKE "statement/sql/%" ORDER BY name;
+------------------------------------------+
| name                                     |
+------------------------------------------+
| statement/sql/alter_db                   |
| statement/sql/alter_db_upgrade           |
| statement/sql/alter_event                |
| statement/sql/alter_function             |
| statement/sql/alter_procedure            |
| statement/sql/alter_server               |
| statement/sql/alter_table                |
| statement/sql/alter_tablespace           |
| statement/sql/alter_user                 |
| statement/sql/analyze                    |
| statement/sql/assign_to_keycache         |
| statement/sql/begin                      |
| statement/sql/binlog                     |
| statement/sql/call_procedure             |
| statement/sql/change_db                  |
| statement/sql/change_master              |
...
| statement/sql/xa_rollback                |
| statement/sql/xa_start                   |
+------------------------------------------+
145 rows in set (0.00 sec)

You can add commands to the include filter by running:

添加過濾機制：

mysql> SET GLOBAL audit_log_include_commands= 'set_option,create_db';

If you now create a database:

mysql> CREATE DATABASE world;

You’ll see it the audit log:

<AUDIT_RECORD
  NAME="Query"
  RECORD="10724_2016-08-18T12:34:22"
  TIMESTAMP="2016-08-18T15:10:47 UTC"
  COMMAND_CLASS="create_db"
  CONNECTION_ID="61"
  STATUS="0"
  SQLTEXT="create database world"
  USER="root[root] @ localhost []"
  HOST="localhost"
  OS_USER=""
  IP=""
  DB=""
/>

To switch command type filtering type from included type list to excluded one or back, first reset the currently-active list to NULL:

為了切換想過濾的類型，需要先設置為NULL

mysql> SET GLOBAL audit_log_include_commands = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_commands= 'set_option,create_db';
Query OK, 0 rows affected (0.00 sec)

Note

Invocation of stored procedures have command type call_procedure, and all the statements executed within the procedure have the same type call_procedure as well.

調用存儲過程的命令類型為call_procedure，存儲過程中的SQL均為一個類型

Filtering by database

通過數(shù)據(jù)庫過濾

In 5.7.14-7 Percona Server has implemented filtering by SQL database. This was implemented by adding two new global variables: audit_log_include_databases and audit_log_exclude_databases to specify which databases should be included or excluded from audit logging.

在5.7.14版本的 Percona Server中，添加了按數(shù)據(jù)庫過濾的功能。添加了兩個新的參數(shù)：audit_log_include_databases 和 audit_log_exclude_databases

Warning

注意

Only one of these variables can contain a list of databases to be either included or excluded, while the other needs to be NULL. If one of the variables is set to be not NULL (contains a list of databases), the attempt to set another one will fail. Empty string means an empty list.

只有一個參數(shù)可以被設置，另一個需要為NULL

If query is accessing any of databases listed in audit_log_include_databases, the query will be logged. If query is accessing only databases listed in audit_log_exclude_databases, the query will not be logged. CREATE TABLE statements are logged unconditionally.

CREATE TABLE 語句不受該條件限制，無條件記錄

Note

Changes of audit_log_include_databases and audit_log_exclude_databases do not apply to existing server connections.

已經存在的連接不生效

Example
To add databases to be monitored you should run:

添加過濾規(guī)則

mysql> SET GLOBAL audit_log_include_databases = 'test,mysql,db1';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_include_databases= 'db1,```db3"`';
Query OK, 0 rows affected (0.00 sec)

If you you try to add databases to both include and exclude lists server will show you the following error:

mysql> SET GLOBAL audit_log_exclude_databases = 'test,mysql,db1';
ERROR 1231 (42000): Variable 'audit_log_exclude_databases can't be set to the value of 'test,mysql,db1'

To switch from filtering by included database list to the excluded one or back, first set the currently active filtering variable to NULL:

mysql> SET GLOBAL audit_log_include_databases = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL audit_log_exclude_databases = 'test,mysql,db1';
Query OK, 0 rows affected (0.00 sec)

系統(tǒng)參數(shù)

System Variables
variable audit_log_strategy
Command Line: Yes
Scope: Global
Dynamic: No
Variable Type: String
Default Value: ASYNCHRONOUS
Allowed values: ASYNCHRONOUS, PERFORMANCE, SEMISYNCHRONOUS, SYNCHRONOUS

This variable is used to specify the audit log strategy, possible values are:

確定日志刷新策略

ASYNCHRONOUS - (default) log using memory buffer, do not drop messages if buffer is full
PERFORMANCE - log using memory buffer, drop messages if buffer is full
SEMISYNCHRONOUS - log directly to file, do not flush and sync every event
SYNCHRONOUS - log directly to file, flush and sync every event
This variable has effect only when audit_log_handler is set to FILE.

variable audit_log_file
Command Line:    Yes
Scope:    Global
Dynamic:    No
Variable Type:    String
Default Value:    audit.log

This variable is used to specify the filename that’s going to store the audit log. It can contain the path relative to the datadir or absolute path.

日志名字路徑

variable audit_log_flush
Command Line:    Yes
Scope:    Global
Dynamic:    Yes
Variable Type:    String
Default Value:    OFF

When this variable is set to ON log file will be closed and reopened. This can be used for manual log rotation.

日志刷新，可用來手動切換日志

variable audit_log_buffer_size
Command Line:    Yes
Scope:    Global
Dynamic:    No
Variable Type:    Numeric
Default Value:    4096

This variable can be used to specify the size of memory buffer used for logging, used when audit_log_strategy variable is set to ASYNCHRONOUS or PERFORMANCE values. This variable has effect only when audit_log_handler is set to FILE.

日志緩存大小

variable audit_log_exclude_accounts

Version Info:
5.7.14-7 – Implemented
Command Line:
Yes
Scope:
Global
Dynamic:
Yes
Variable Type:
String

This variable is used to specify the list of users for which Filtering by user is applied. The value can be NULL or comma separated list of accounts in form user@host or 'user'@'host' (if user or host contains comma). If this variable is set, then audit_log_include_accounts must be unset, and vice versa.

variable audit_log_exclude_commands
Version Info:
5.7.14-7 – Implemented
Command Line:
Yes
Scope:
Global
Dynamic:
Yes
Variable Type:
String

This variable is used to specify the list of commands for which Filtering by SQL command type is applied. The value can be NULL or comma separated list of commands. If this variable is set, then audit_log_include_commands must be unset, and vice versa.

variable audit_log_exclude_databases
Version Info:
5.7.14-7 – Implemented
Command Line:
Yes
Scope:
Global
Dynamic:
Yes
Variable Type:
String

This variable is used to specify the list of commands for which Filtering by database is applied. The value can be NULL or comma separated list of commands. If this variable is set, then audit_log_include_databases must be unset, and vice versa.

variable audit_log_format
Command Line:    Yes
Scope:    Global
Dynamic:    No
Variable Type:    String
Default Value:    OLD
Allowed values:    OLD, NEW, CSV, JSON

This variable is used to specify the audit log format. The audit log plugin supports four log formats: OLD, NEW, JSON, and CSV. OLD and NEW formats are based on XML, where the former outputs log record properties as XML attributes and the latter as XML tags. Information logged is the same in all four formats.

日志格式

variable audit_log_include_accounts
Version Info:
5.7.14-7 – Implemented
Command Line:
Yes
Scope:
Global
Dynamic:
Yes
Variable Type:
String

This variable is used to specify the list of users for which Filtering by user is applied. The value can be NULL or comma separated list of accounts in form user@host or 'user'@'host' (if user or host contains comma). If this variable is set, then audit_log_exclude_accounts must be unset, and vice versa.

variable audit_log_include_commands
Version Info:
5.7.14-7 – Implemented
Command Line:
Yes
Scope:
Global
Dynamic:
Yes
Variable Type:
String

This variable is used to specify the list of commands for which Filtering by SQL command type is applied. The value can be NULL or comma separated list of commands. If this variable is set, then audit_log_exclude_commands must be unset, and vice versa.

variable audit_log_include_databases
Version Info:
5.7.14-7 – Implemented
Command Line:
Yes
Scope:
Global
Dynamic:
Yes
Variable Type:
String

This variable is used to specify the list of commands for which Filtering by database is applied. The value can be NULL or comma separated list of commands. If this variable is set, then audit_log_exclude_databases must be unset, and vice versa.

variable audit_log_policy
Command Line:    Yes
Scope:    Global
Dynamic:    Yes
Variable Type:    String
Default Value:    ALL
Allowed values:    ALL, LOGINS, QUERIES, NONE

This variable is used to specify which events should be logged. Possible values are:

日志記錄策略

ALL - all events will be logged
LOGINS - only logins will be logged
QUERIES - only queries will be logged
NONE - no events will be logged

variable audit_log_rotate_on_size
Command Line:    Yes
Scope:    Global
Dynamic:    No
Variable Type:    Numeric
Default Value:    0 (don’t rotate the log file)

This variable is used to specify the maximum audit log file size. Upon reaching this size the log will be rotated. The rotated log files will be present in the same same directory as the current log file. A sequence number will be appended to the log file name upon rotation. This variable has effect only when audit_log_handler is set to FILE.

確定審計日志文件的大小，當達到該大小時，日志自動進行切換

variable audit_log_rotations
Command Line:    Yes
Scope:    Global
Dynamic:    No
Variable Type:    Numeric
Default Value:    0

This variable is used to specify how many log files should be kept when audit_log_rotate_on_size variable is set to non-zero value. This variable has effect only when audit_log_handler is set to FILE.

設置多少個文件將會被保留

variable audit_log_handler
Command Line:    Yes
Scope:    Global
Dynamic:    No
Variable Type:    String
Default Value:    FILE
Allowed values:    FILE, SYSLOG

This variable is used to configure where the audit log will be written. If it is set to FILE, the log will be written into a file specified by audit_log_file variable. If it is set to SYSLOG, the audit log will be written to syslog.

確定是記錄到文件中，還是記錄到syslog中

variable audit_log_syslog_ident
Command Line:    Yes
Scope:    Global
Dynamic:    No
Variable Type:    String
Default Value:    percona-audit

This variable is used to specify the ident value for syslog. This variable has the same meaning as the appropriate parameter described in the syslog(3) manual.

variable audit_log_syslog_facility
Command Line:    Yes
Scope:    Global
Dynamic:    No
Variable Type:    String
Default Value:    LOG_USER

This variable is used to specify the facility value for syslog. This variable has the same meaning as the appropriate parameter described in the syslog(3) manual.

variable audit_log_syslog_priority
Command Line:    Yes
Scope:    Global
Dynamic:    No
Variable Type:    String
Default Value:    LOG_INFO

This variable is used to specify the priority value for syslog. This variable has the same meaning as the appropriate parameter described in the syslog(3) manual.

Version Specific Information
5.7.10-1 Feature ported from Percona Server 5.6
5.7.14-7 Percona Server Audit Log Plugin now supports filtering by user, sql_command, and databases.

bug-fixed Valgrind: Invalid read of size 8 at get_record_buffer by audit_log_notify
Here's the reduced test case:
DROP DATABASE test;
SET STATEMENT max_join_size=0 FOR SELECT 0 t0;

里程碑版 mysql-5.7.15
該版本oracle 社區(qū)版不能使用