mysql audit-访问日志记录

应用描述:
某天DB被drop,查询被什么账号所致;

//accesslog 存放连接信息;
create database accesslog;

CREATE TABLE `accesslog` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`localname` varchar(30) DEFAULT NULL,
`matchname` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=archive DEFAULT CHARSET=utf8; //archive 引擎有利数据压缩存放;

//stop mysqld
[mysqld]下添加:
init-connect='insert into accesslog.accesslog values(connection_id(),now(),user(),current_user());'

start mysqld 生效init-connect参数;

//指定读accesslog库的用户名:
grant select on accesslog.* to 'root'@'ip' identified by 'passwd';
--connect root
create database tx;

//测试用户;
grant select,drop on *.* to 'tuser'@'ip' identified by 'passwd';
--connect tuser;
drop database tx;

//目前应用于binlog.xxxx
mysqlbinlog [--start-time='' --stop-time=''] mysqlbin.xxx | grep 'tx' -B 5
获取如下信息:
# at 1086
#130509 9:36:28 server id 2 end_log_pos 1163 Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1368063388/*!*/;
drop database tx

//connect root
select * from accesslog.accesslog where id = 7(thread_id);
+----+---------------------+------------------+-----------+
| id | time                | localname        | matchname |
+----+---------------------+------------------+-----------+
| 7 | 2013-05-09 09:36:22 | tuser@ip          | tuser@%   |
+----+---------------------+------------------+-----------+

注意:
1.不记录有Super权限的用户登录信息;
2.用户每次连接时往数据库中插入一条记录,不会对DB产生很大影响,除非连接频率高;
3.accesslog表其他用途,对数据库连接的情况进行数据分析,如,每日连接数分布图等

//zz:OurMySQL

ITPub博客

mysql audit-访问日志记录应用

最新文章