Introduction to the Percona MySQL Audit Log Plugin_MySQL

WBOY
풀어 주다: 2016-06-01 13:14:00
원래의
1153명이 탐색했습니다.

Percona has developed a MySQL Audit Log plugin that is now included in Percona Server since the recent5.5and5.6releases. This implementation is alternative to the MySQL Enterprise Audit Log Plugin: Percona re-implemented the Audit Plugin code as GPL as Oracle’s code was closed source. This post is a quick introduction to this plugin.

Installation
There are two ways to install the Percona MySQL Audit Plugin:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
로그인 후 복사

INSTALL PLUGINaudit_logSONAME'audit_log.so';

or in my.cnf

[mysqld]plugin-load="audit_log=audit_log.so"
로그인 후 복사
[mysqld]

plugin-load="audit_log=audit_log.so"

Verify installation

mysql> SHOW PLUGINS/G...*************************** 38. row ***************************  Name: audit_logStatus: ACTIVE  Type: AUDITLibrary: audit_log.soLicense: GPL38 rows in set (0.00 sec)
로그인 후 복사

mysql>SHOWPLUGINS/G

...

***************************38.row***************************

  Name:

Status:  Type:

Library:

License:

38rowsinset(0.00

Configuration
Let’s see variables provided by the Percona MySQL Audit Plugin:

mysql> show global variables like 'audit%';+--------------------------+--------------+| Variable_name            | Value        |+--------------------------+--------------+| audit_log_buffer_size    | 1048576      || audit_log_file           | audit.log    || audit_log_flush          | OFF          || audit_log_format         | OLD          || audit_log_policy         | ALL          || audit_log_rotate_on_size | 0            || audit_log_rotations      | 0            || audit_log_strategy       | ASYNCHRONOUS |+--------------------------+--------------+7 rows in set (0.00 sec)
로그인 후 복사

mysql>showglobalvariableslike'audit%';

+--------------------------+--------------+

|Variable_name           |Value       |

+--------------------------+--------------+

|

|audit_log_file          |audit.log   |

|

|

|audit_log_policy        |ALL         |

|

|

|

+--------------------------+--------------+

7rowsinset(0.00

The Percona MySQL Audit Plugin can log using the memory buffer to deliver better performance. Messages will be written into memory buffer first and then flushed to file in background. A certain amount of events can be lost in case of server crash or power outage. Another option is to log directly to file without using memory buffer. There is also an option to fsync every event.

Set audit_log_strategy to control log flushing:

  • ASYNCHRONOUS log using memory buffer, do not drop events if buffer is full
  • PERFORMANCE log using memory buffer, drop events if buffer is full
  • SEMISYNCHRONOUS log directly to file, do not fsync every event
  • SYNCHRONOUS log directly to file, fsync every event

audit_log_buffer_size specifies the size of memory buffer, it makes sense only for ASYNCHRONOUS and PERFORMANCE strategy.

Variable audit_log_file specifies the file to log into. It’s value can be path relative to datadir or absolute path.

The Percona MySQL Audit Plugin can automatically rotate log file based on size. Set audit_log_rotate_size to enable this feature. File is rotated when log grew in size to specified amount of bytes. Set audit_log_rotations to limit the number of log files to keep.

It is possible to log only logins or only queries by setting audit_log_policy value.

Log file format
Lets see how audit records look like

OLD format (audit_log_format = OLD):

<audit_record utc></audit_record>
로그인 후 복사
 "NAME"="Connect"

 "RECORD"="2_2014-04-21T12:34:32"

 "TIMESTAMP"="2014-04-21T12:34:32 UTC"

 "CONNECTION_ID"="1"

 "STATUS"="0" "USER"="root"

 "PRIV_USER"="root"

 "OS_LOGIN"=""

 "PROXY_USER"=""

 "HOST"="localhost"

 "IP"="" "DB"=""/>

NEW format (audit_log_format = NEW):

<audit_record> <name>Connect</name> <record>17481_2014-04-21T12:39:03</record> <timestamp>2014-04-21T12:39:05 UTC</timestamp> <connection_id>4</connection_id> <status>0</status> <user>root</user> <priv_user>root</priv_user> <os_login></os_login> <proxy_user></proxy_user> <host>localhost</host> <ip></ip> <db>test</db></audit_record>
로그인 후 복사

 Connect

 17481_2014-04-21T12:39:03

 2014-04-21T12:39:05 UTC

 4

 0

 root

 root

 

 

 localhost

  test

The difference is that the audit record in the OLD format was written as a single element with attributes, while in the NEW format it is written as a single element with sub-elements.

A good idea of what each sub-element means can be found in Audit Plugin API documentation here:https://dev.mysql.com/doc/refman/5.6/en/writing-audit-plugins.html.

Performance
Lets compare performance of different audit_log_strategy modes. I used readonly sysbench on my laptop for it. Workload is CPU-bound with dataset fit in buffer pool and I set number of sysbench threads to the amount for which count of transactions per seconds is maximum.

sysbench TPS for Percona Audit Log Plugin

I got TPS drop for PERFORMANCE and ASYNCHRONOUS strategies around 7%, 9% for SEMISYNCHRONOUS and 98% for SYNCHRONOUS which shows that syncing every logged statement to disk is not the best thing for performance.

Conclusion
Of course any software has bugs and this plugin has plenty of them. Please give it a try and provide us your feedback. Report any issues here:https://bugs.launchpad.net/percona-server.

원천:php.cn
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
인기 튜토리얼
더>
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿
회사 소개 부인 성명 Sitemap
PHP 중국어 웹사이트:공공복지 온라인 PHP 교육,PHP 학습자의 빠른 성장을 도와주세요!