MySQL 5.6 Community Edition on audit function, no wrangling

Website

https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/

I. Environmental description

MySQL 5.6.25 Community Edition

MARIADB 10.0.25 Community Edition

MySQL Enterprise Edition has audit function to charge, Community edition was castrated not to die, and the ancient eunuch

There is no difference, the more important features are not, but also can make the use of. Might have said a little too,

It is not rough, it is suggested that the enterprise version can be considered Percona and mariadb, personal advice, for reference only.

Second, installation

1. Download the MARIADB 10.0.25_x64 installation package, unzip the upload server_audit.so file into the/tmp directory

2. View the plugin path

([email protected]) [(None)]> show variables like ' plugin_dir '; +---------------+--------------------------+| variable_name | Value |+---------------+--------------------------+| Plugin_dir | /usr/lib64/mysql/plugin/|+---------------+--------------------------+1 row in Set (0.00 sec)

3.CP plug-in, Grant execute permission

# cp/tmp/server_audit.so/usr/lib64/mysql/plugin/# chmod +x/usr/lib64/mysql/plugin/server_audit.so #

4. Installing plugins

([email protected]) [(none)]> Install plugin server_audit SONAME ' server_audit.so '; Query OK, 0 rows Affected (0.00 sec)

5. View Parameters

([email protected])  [(none)]> show variables like  '%audit% '; +---------------- ---------------+-----------------------+| variable_name                  | Value                  |+-------------------------------+---------------- -------+| server_audit_events           |                         | |  server_audit_excl_users       |                        | |  server_audit_file_path        | server_audit.log       | |  server_audit_file_rotate_now  | OFF                    | |  server_audit_file_rotate_size | 1000000                | |  server_audit_file_rotations   | 9                      | |  server_audit_incl_users       |                        | |  server_audit_loc_info         |                         | |  server_audit_logging          | off                    | |  server_audit_mode             | 1                       | |  server_audit_output_type      | file                   | |  server_audit_query_log_limit  | 1024                   | |  server_audit_syslog_facility  | LOG_USER               | |  server_audit_syslog_ident     | mysql-server_auditing | |  server_audit_syslog_info      |                        | |  server_audit_syslog_priority  | LOG_INFO               |+-------------------------------+-----------------------+16 rows  in set  (0.00 SEC)

6. Modifying parameters

([email protected]) [(None)]> set global server_audit_logging=on; Query OK, 0 rows Affected (0.00 sec) ([email protected]) [(none)]> set global server_audit_file_rotate_now=on; Query OK, 0 rows Affected (0.00 sec)

7. View audit log path

# Find/-name Server_audit.log/data/mysqldata/server_audit.log--Default in Data path

# tail-f/data/mysqldata/server_audit.log20161226 22:46:38,mysql91,root,localhost,1,3,query,, ' Set global Server_ Audit_logging=on ', 020161226 22:46:58,mysql91,root,localhost,1,4,query,, ' Show variables like '%audit%\ ', 020161226 22:48:36,mysql91,root,localhost,1,5,query,, ' Show databases ', 020161226 22:49:27,mysql91,root,localhost,1,6,query, , ' GRANT all privileges on * * to \ ' roidba\ ' @\ '%\ ' Identified by PASSWORD * * * * *, 0

Three, the formal environment needs to solve two problems

1. Log path specification

([email protected]) [(None)]> set global server_audit_file_path= '/data/mysqllogs/'; Query OK, 0 rows Affected (0.00 sec)

2. Usually the query does not need to audit (MySQL test filter can not be lost, mariadb)

([email protected]) [(None)]> set global server_audit_events= ' QUERY_DDL,QUERY_DML '; Query OK, 0 rows Affected (0.00 sec)

3. Curing Parameters

VI/ETC/MY.CNF ####### #mariadb 10.0.25 audit plugin###### #server_audit_logging =onserver_audit_events= ' Query_dml, Query_ddl ' Server_audit_file_path =/data/mysqllogs/server_audit_file_rotate_size=200000000server_audit_file_ rotations=200

4. Recommended restart

Service MySQL Restart

This article is from the "ROIDBA" blog, make sure to keep this source http://roidba.blog.51cto.com/12318731/1886301

MySQL 5.6 Community Edition on audit function, no wrangling