1. to ensure security and operational traceability, more and more companies have added audit functions. Mysql55 has released the relevant audit function. by 5620, the function is barely available. Although the fine-grained aspect is not very good, the subsequent version is still 1. preface in consideration of security and Operation traceability, more and more companies have added audit functions. Mysql5.5 has released related audit functions. the 5.6.20 function is barely available. Although the fine-grained aspect is not very good, you can still look forward to it in subsequent versions. This section describes the related functions and features.
2. enable audit 2.1 the audit file in mysql5.6 is loaded using the audit_log plug-in. we can load this plug-in the configuration file to enable it.
[mysqld] plugin-load=audit_log.so
If you want the database to enable the audit function forcibly, if it is not enabled, the server will not start, or if the audit function cannot be enabled, the server will be suspended and added
[mysqld] plugin-load=audit_log.so audit-log=FORCE_PLUS_PERMANENT
2.2 to enable the agent list audit function, install the audit agent in the command line. Make sure that the database plug-in directory contains audit_log.so. [The default directory of the i686 database plug-in is/usr/lib/mysql/plugin. you can also specify the plugin_dir parameter]
mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
3. parameter introduction audit parameters are as follows:
3.1 audit_log_buffer_sizeaudit_log_buffer_size: Audit cache. it is recommended to set this parameter to a multiple of 4096. this parameter takes effect only when audit_log_strategy is ASYNCHRONOUS.
3.2 audit_log_connection_policyaudit_log_connection_policy: records the connection audit information. Contains three parameters
| Value |
Description |
ALL |
Log all connection events |
ERRORS |
Log only failed connection events |
NONE |
Do not log connection events |
If audit_log_policy is set, it may be overwritten. 3.3 audit_log_current_sessionaudit_log_current_session: indicates whether the current session is audited. it is a read-only parameter and can only be controlled through audit_log_exclude_accounts and audit_log_include_accounts.
3.4 audit_log_exclude_accounts/audit_log_include_accountsaudit_log_exclude_accounts: controls where users do not enter audit. string types can be separated by commas by default.
Audit_log_include_accounts: controls where users can enter audit. string types can be separated by commas by default.
Exclude and include have only one parameter. 3.5 audit_log_fileaudit_log_file: used to control the name and path of audit logs.
3.6 audit_log_flushaudit_log_flush: controls audit log archiving only when audit_log_rotate_on_size = 0. after manually renaming audit log archiving, you can specify audit_log_flush = 1 to generate new audit logs.
3.7 audit_log_formataudit_log_format: Audit log format, which can be OLD or NEW (NEW format is 5.6.14 ). To change the format, perform the following three steps:
1: disable Database 2: rename the current audit. log file
3: Change the audit_log_format parameter and restart mysql. a new audit. log file is automatically generated after the restart.
Prevent the NEW format and OLD format from being in the same audit log, resulting in an audit function error. Audit_log_policy: record the audit log control policy:
| Value |
Description |
ALL |
Log all events |
LOGINS |
Log only login events |
QUERIES |
Log only query events |
NONE |
Log nothing (disable the audit stream |
3.8 audit_log_statement_policyaudit_log_statement_policy: indicates the audit policy of the statement, which may be overwritten by audit_log_policy:
| Value |
Description |
ALL |
Log all statement events |
ERRORS |
Log only failed statement events |
NONE |
Do not log statement events |
3.9 audit_log_rotate_on_sizeaudit_log_rotate_on_size: size of the audit log file. When the parameter is greater than 0, when the audit log exceeds the limit, it is automatically renamed as a log file with a timestamp suffix. Create a new audit log at the same time.
3.10 audit_log_strategyaudit_log_strategy: Audit log refresh policies are divided:
| Value |
Meaning |
ASYNCHRONOUS |
Log asynchronously, wait for space in output buffer |
PERFORMANCE |
Log asynchronously, drop request if insufficient space in output buffer |
SEMISYNCHRONOUS |
Log synchronously, permit caching by operating system |
SYNCHRONOUS |
Log synchronously, callsync() after each request |
4. log format audit log format is XML, and NEW is more detailed than OLD labels. The specific tag information is as follows: instance:
2013-09-17T15:03:24 UTC
1_2013-09-17T15:03:24
Audit
1
1
/usr/local/mysql/bin/mysqld --socket=/usr/local/mysql/mysql.sock --port=3306
x86_64-osx10.6
5.7.2-m12-log
2013-09-17T15:03:40 UTC
2_2013-09-17T15:03:24
Connect
2
0
0
root
localhost
127.0.0.1
connect
root
test
...
2013-09-17T15:03:41 UTC
4_2013-09-17T15:03:24
Query
2
0
0
root[root] @ localhost [127.0.0.1]
localhost
127.0.0.1
drop_table
DROP TABLE IF EXISTS t
2013-09-17T15:03:41 UTC
5_2013-09-17T15:03:24
Query
2
0
0
root[root] @ localhost [127.0.0.1]
localhost
127.0.0.1
create_table
CREATE TABLE t (i INT)
...
2013-09-17T15:03:41 UTC
7_2013-09-17T15:03:24
Quit
2
0
0
connect
...
2013-09-17T15:03:47 UTC
9_2013-09-17T15:03:24
Shutdown
3
0
0
root[root] @ localhost [127.0.0.1]
localhost
127.0.0.1
2013-09-17T15:03:47 UTC
10_2013-09-17T15:03:24
Quit
3
0
0
connect
2013-09-17T15:03:49 UTC
11_2013-09-17T15:03:24
NoAudit
1
: The Root label of the file isEnd label
: Contains a series of required and optional tags. whether or not the optional tags appear depends on the audit record type.
: Required. for example Query Possible values include 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.
: Required. for example 28743_2013-09-18T21: 03: 24 , Contains some column numbers and timestamps. The number indicates the number of records. each time a record is added, the number is incremented by 1.
: Required. for example 2013-09-17T15: 03: 49 UTC It contains the timestamp and time zone, and records the time at which the SQL execution is received from the client.
The following Tag audit record type determines whether or not it appears
: Command type. For example Drop_table .
For example 127 Represents the unsigned integer number of the client connection identifier.
: The default database name for mysql connection. This label is only available in It appears when the value is Connect or Change user.
: The host name of the client. When the value is Connect, Change user or Query, for example Localhost .
: The IP address of the client. When the value is Connect, Change user or Query, for example 127.0.0.1 .
: Mysql version, only in When the value is Audit, for example 5.7.1 m11-log
: External user. the tag is only The value is "Connect", "Change user", or "Query.
: Indicates the operating system of the server that runs the database. When the value is Audit, for example X86_64-Linux .
: The name of the server-authenticated client. This label is only available in When the value is Connect or Change user. For example Root .
: The user who connects to mysql through proxy. This label is only available in When the value is Connect or Change user.
: ID of the mysql database server. When the value is Audit or No Audit. For example 1 .
: The SQL statement actually executed. This label is only available in When the value is Query or Execute. For example Delete from t1 .
: Mysql database startup option. This label is only available in When the value is Audit, for example /Usr/local/mysql/bin/mysqld -- port = 3306 -- log-output = FILE
: Indicates the execution status of the SQL command. 0 indicates that the command is successful, and other statements indicate that there is an error. For example 1051 .
: Indicates the execution status of the SQL command. 0 indicates successful, and 1 indicates an error. For example 0 .
: The username used by the client to connect to the mysql server. For example Root [root] @ localhost [127.0.0.1] .
: Indicates the version number of the log file format. This label is only available in When the value is Audit. For example 1 .
5. Audit restrictions audit logs are stored in the data path by default. because the XML file is not encrypted, we recommend that you specify a special path through parameters on the official website and set the corresponding personnel permissions for security control. In addition, the audit function cannot be recorded in the following situations: 1) only top-level statements can be audited. Stored programs such as stored procedures, triggers, and functions are not audited; 2) statements involving external files cannot be audited, such as load data infile.
