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
应用描述:
某天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博客 ” ,链接:http://blog.itpub.net/26855487/viewspace-760645/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26855487/viewspace-760645/