Chinaunix首页 | 论坛 | 博客
  • 博客访问: 589830
  • 博文数量: 38
  • 博客积分: 587
  • 博客等级: 中士
  • 技术积分: 579
  • 用 户 组: 普通用户
  • 注册时间: 2011-10-17 14:32
文章存档

2013年(15)

2012年(23)

分类: Mysql/postgreSQL

2013-06-05 10:51:25

一、测试环境
MySQL: Percona-Server-5.5.21
audit-plugin: audit-plugin-mysql-5.5-1.0.3-371-linux-x86_64

二、安装
1、下载软件包:https://github.com/mcafee/mysql-audit/downloads
这里测试使用的是 Percona-Server-5.5.21,所以选择下载列中的:audit-plugin-mysql-5.5-1.0.3-371-linux-x86_64.zip


2、添加libaudit_plugin.so插件到现有MySQL的plugin_dir。

1)查看当前的MysQL的plugin目录
mysql> show variables like 'plugin_dir';
+---------------+-----------------------------------------------------------------------------+
| Variable_name | Value                                                                             |
+---------------+-----------------------------------------------------------------------------+
| plugin_dir    | /usr/local/Percona-Server-5.5.21-rel25.1-234.Linux.x86_64/lib/mysql/plugin/ |
+---------------+-----------------------------------------------------------------------------+
1 row in set (0.01 sec)

2)把压缩包中的libaudit_plugin.so文件,拷贝到1)中的plugin_dir目录下

3、Offset Extraction指定,目前macfee audit plugin还没有提供统一的API的,所以要手动提取offset

1)  下载官方脚本 :https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh
2) chmod u+x offset-extract.sh
3) ./offset-extract.sh /usr/local/mysql/bin/mysqld

[root@10_5_12_15 audit-plugin-mysql-5.5]# ./offset-extract.sh  /usr/local/mysql/bin/mysqld
//offsets for: /usr/local/mysql/bin/mysqld (5.5.21-rel25.1-log)
{"5.5.21-rel25.1","a877e71dd4ddc965390714ae8be2e540", 6456, 6504, 4064, 4504, 104, 2576},

上面的6456, 6504, 4064, 4504, 104, 2576数字要添加到/etc/my.cnf的 [mysqld]下

4、配置和验证
1)在/etc/my.cnf中的[mysqld]下添加下面两行
#Audit Plugin#
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets=6456, 6504, 4064, 4504, 104, 2576

2)安装plugin,INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';

3)验证
mysql> show global status like 'AUDIT_version';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| AUDIT_version | 1.0.3-371 |
+---------------+-----------+
1 row in set (0.00 sec)

5、查看审计的结果

1)开启审计功能:审计功能默认是关闭的,由audit_json_file参数控制,可动态调整;
    set global audit_json_file=ON;

2)查看audit_json_log_file: 默认为在datadir下创建一个mysql-audit.json的json格式的文本文件,保存审计的信息。
示例:
执行的SQL:
use test;
create table t_audit(id int primary key , action varchar(200)) engine=innodb default charset=utf8;
insert into t_audit values(1,'insert'),(2,'delete');
delete from t_audit whre id=1;
DROP TABLE t_audit;

mysql-audit.json文件中的内容:
{"msg-type":"activity","date":"1369380547292","thread-id":"3","query-id":"29","user":"root","priv_user":"root","ip":"127.0.0.1","cmd":"select","query":"SELECT DATABASE()"}
{"msg-type":"activity","date":"1369380547292","thread-id":"3","query-id":"30","user":"root","priv_user":"root","ip":"127.0.0.1","cmd":"Init DB","query":"Init DB"}
{"msg-type":"activity","date":"1369380616387","thread-id":"3","query-id":"32","user":"root","priv_user":"root","ip":"127.0.0.1","cmd":"create_table","objects":[{"db":"test","name":"t_audit","obj_type":"TABLE"}],"query":"create table t_audit(id int primary key , action varchar(200)) engine=innodb default charset=utf8"}
{"msg-type":"activity","date":"1369380634779","thread-id":"3","query-id":"33","user":"root","priv_user":"root","ip":"127.0.0.1","cmd":"insert","objects":[{"db":"test","name":"t_audit","obj_type":"TABLE"}],"query":"insert into t_audit values(1,'insert'),(2,'delete')"}
{"msg-type":"activity","date":"1369380660369","thread-id":"3","query-id":"36","user":"root","priv_user":"root","ip":"127.0.0.1","cmd":"delete","objects":[{"db":"test","name":"t_audit","obj_type":"TABLE"}],"query":"delete from t_audit where id=1"}
{"msg-type":"activity","date":"1369380742378","thread-id":"3","query-id":"37","user":"root","priv_user":"root","ip":"127.0.0.1","cmd":"drop_table","objects":[{"db":"test","name":"t_audit","obj_type":"TABLE"}],"query":"DROP TABLE t_audit"}


安装出错解决:下面为重新启动MySQL时error log出现的信息:
130524 14:46:16 Percona XtraDB (http://www.percona.com) 1.1.8-rel25.1 started; log sequence number 1602858
130524 14:46:16 [Note] Audit Plugin: starting up. Version: 1.0.3 , Revision: 371 (64bit). AUDIT plugin interface version: 50521. MySQL Server version: 5.5.21-rel25.1-log.
130524 14:46:16 [Note] Audit Plugin: setup_offsets audit_offsets: (null) validate_checksum: 1 offsets_by_version: 1
130524 14:46:17 [Note] Audit Plugin: mysqld: /usr/local/Percona-Server-5.5.21-rel25.1-234.Linux.x86_64/bin/mysqld (a877e71dd4ddc965390714ae8be2e540)
130524 14:46:17 [ERROR] Audit Plugin: Offsets: 5.5.21 (4a03ad064ed393dabdde175f3ea05ff2) match thread validation check fails with value: 0. Skipping offest.
130524 14:46:17 [ERROR] Audit Plugin: Offsets: 5.5.21 (4a03ad064ed393dabdde175f3ea05ff2) match thread validation check fails with value: 0. Skipping offest.
----------------------------------------------省略---------------------------------------------------------------------
130524 14:46:17 [ERROR] Plugin 'AUDIT' init function returned error.
130524 14:46:17 [Note] Audit Plugin: deinit


解决:出现以上问题,一般是没有指定offset, 根据上面第3步做。

【参考】
阅读(3072) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册