追踪mysql操作记录实践2
来源:互联网 发布:手机淘宝店铺发货流程 编辑:程序博客网 时间:2024/05/01 13:12
from:http://blog.chinaunix.net/uid-24086995-id-168445.htmlhttp://www.mysqlsystems.com/2009/11/mysql-audit-access-log.html
在日志表里记录的和日志文件里面记录的相同。可以通过这个thread_id来追踪到是谁,什么时间,做了什么操作。
在MYSQL中,每个连接都会先执行init-connect,进行连接的初始化。我们可以在这里获取用户的登录名称和thread的ID值。然后配合binlog,就可以追踪到每个操作语句的操作时间,操作人等。实现审计。
实验过程:
1:创建登录日志库,登录日志表
1:创建登录日志库,登录日志表
- CREATE
DATABASE `accesslog`; - USE
`accesslog`; - CREATE
TABLE `accesslog` - (
-
`id` int(11) NOT NULL AUTO_INCREMENT, -
`thread_id` int(11) DEFAULT NULL, #线程ID,这个值很重要 -
`log_time` timestamp NOT NULL DEF AULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, #登录时间 -
`localname` varchar(30) DEFAULT NULL, #登录名称带IP -
`matchname` varchar(30) DEFAULT NULL, #登录用户,user的全称 -
PRIMARY KEY (`id`) - )
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2:在配置文件中配置init-connect参数。登录时插入日志表。如果这个参数是个错误的SQL语句,登录就会失败。
Linux 下的配置文件为 my.cnf,windows下位my.ini
Linux 下的配置文件为 my.cnf,windows下位my.ini
- init-connect='insert into accesslog.accesslog values(null,connection_id(),now(),user(),current_user());'
- log-bin
重启service mysqld 以使其配置文件生效
3:创建普通用户,不能有super权限。init-connect对具有super权限的用户不起作用。同时此用户必须要有INSERT权限,如果没有,登录后的任何操作都会导致MYSQL登录失败。
3:创建普通用户,不能有super权限。init-connect对具有super权限的用户不起作用。同时此用户必须要有INSERT权限,如果没有,登录后的任何操作都会导致MYSQL登录失败。
REATE USER 'username'@'host' IDENTIFIED BY 'password';
例子: CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';
- grant
insert,select,update on *.* to 'user1'@'localhost'; #带INSERT权限 - grant
select,update on *.* to 'user2'@'localhost'; #不带INSERT权限
4:SESSION1登录,并查看日志
- D:\mysql6\bin>mysql -uuser1 -p
- Enter
password: - Welcome
to the MySQL monitor. Commands end with ; or \g. - Your MySQL connection id
is 65 - Server
version: 5.1.45-community-log MySQL Community Server (GPL) - Type
'help;' or '\h' for help. Type '\c' to clear the current input statement. - mysql>
select * FROM accesslog.accesslog; - +----+-----------+---------------------+-----------------+-----------------+
- |
id | thread_id | log_time | localname | matchname | - +----+-----------+---------------------+-----------------+-----------------+
- |
1 | 65 | 2011-03-11 19:18:25 | user1@localhost | user1@localhost | - +----+-----------+---------------------+-----------------+-----------------+
- 1 row
in set (0.00 sec) - mysql>
show processlist;# 当前运行的threadId - +----+-------+----------------+------+---------+------+-------+------------------+
- |
Id | User | Host | db | Command | Time | State | Info | - +----+-------+----------------+------+---------+------+-------+------------------+
- |
65 | user1 | localhost:1339 | NULL | Query | 0 | NULL | show processlist | - +----+-------+----------------+------+---------+------+-------+------------------+
- 1 row
in set (0.00 sec) - mysql>
5:再用user2登录
- D:\mysql6\bin>mysql -uuser2 -p
- Enter
password: - Welcome
to the MySQL monitor. Commands end with ; or \g. - Your MySQL connection id
is 76 - Server
version: 5.1.45-community-log - Type
'help;' or '\h' for help. Type '\c' to clear the current input statement. - mysql>
select * FROM accesslog.accesslog; - ERROR 2006
(HY000): MySQL server has gone away - No connection.
Trying to reconnect... - Connection id: 77
- Current
database: *** NONE *** - ERROR 2013
(HY000): Lost connection to MySQL server during query - mysql>
select * FROM accesslog.accesslog; - ERROR 2006
(HY000): MySQL server has gone away - No connection.
Trying to reconnect... - Connection id: 78
- Current
database: *** NONE ***
看下错误日志
如果没有对log-bin指定log文件,默认在 /var/lib/mysql目录下以mysqld-bin.00000X等作为名称。而 mysqld-bin.index则记录了所有的log的文件名称
使用时则使用mysqlbinlog /var/lib/mysql|grep "*****"等来追踪database的操作。
如果没有对log-bin指定log文件,默认在 /var/lib/mysql目录下以mysqld-bin.00000X等作为名称。而 mysqld-bin.index则记录了所有的log的文件名称
使用时则使用mysqlbinlog /var/lib/mysql|grep "*****"等来追踪database的操作。
- 110311 19:23:47
[Warning] Aborted connection 77 to db: 'unconnected' user: 'user2' host: 'localhost' (init_connect command failed) - 110311 19:23:47
[Warning] INSERT command denied to user 'user2'@'localhost' for table 'accesslog' - 110311 19:23:53
[Warning] Aborted connection 78 to db: 'unconnected' user: 'user2' host: 'localhost' (init_connect command failed) - 110311 19:23:53
[Warning] INSERT command denied to user 'user2'@'localhost' for table 'accesslog'
6:下面以USER1登录,并做一个INSERT操作,查看日志文件。
- mysql>
insert into t3 values(10,10,'2011-10-10 00:00:00'); - Query OK,
1 row affected (0.00 sec) - mysql>
show processlist; - +----+-------+----------------+-----------+---------+------+-------+------------------+
- |
Id | User | Host | db | Command | Time | State | Info | - +----+-------+----------------+-----------+---------+------+-------+------------------+
- |
69 | user1 | localhost:1439 | accesslog | Query | 0 | NULL | show processlist | - +----+-------+----------------+-----------+---------+------+-------+------------------+
- 1 row
in set (0.00 sec) - mysql>
select * from accesslog.accesslog; - +----+-----------+---------------------+-----------------+-----------------+
- |
id | thread_id | log_time | localname | matchname | - +----+-----------+---------------------+-----------------+-----------------+
- |
1 | 65 | 2011-03-11 19:18:25 | user1@localhost | user1@localhost | - |
2 | 91 | 2011-03-11 19:28:33 | user1@localhost | user1@localhost | - |
3 | 2 | 2011-03-11 19:31:49 | user1@localhost | user1@localhost | - |
4 | 2 | 2000-10-10 10:10:10 | user1@localhost | user1@localhost | - |
5 | 21 | 2000-10-10 11:11:11 | root@localhost | root@% | - |
6 | 69 | 2011-03-12 21:35:43 | user1@localhost | user1@localhost | - +----+-----------+---------------------+-----------------+-----------------+
- 6 rows
in set (0.01 sec)
查看日志文件的内容
- # at 340
- #110312 21:36:01 server id 1 end_log_pos 453 Query thread_id=69 exec_time=0 error_code=0
- use
text; - SET
TIMESTAMP=1299936961; - insert
into t3 values(10,10,'2011-10-10 00:00:00') - ;
- # at 453
thread_id=69
在日志表里记录的和日志文件里面记录的相同。可以通过这个thread_id来追踪到是谁,什么时间,做了什么操作。
0 0
- 追踪mysql操作记录实践2
- 追踪mysql操作记录时间1.
- mysql日志记录操作人员的信息以便于追踪
- MySql怎样追踪用户操作(增删改)记录
- 在MySQL中使用init-connect与binlog来实现用户操作追踪记录
- mysql 用init-connect+binlog实现用户操作追踪 做access 的ip的log 记录
- 在MySQL中使用init-connect与binlog来实现用户操作追踪记录
- MySQL操作实践
- 部署Zipkin分布式性能追踪日志系统的操作记录
- Centos系统对历史操作记录添加ip追踪设置
- QT 操作记录,待实践
- MySQL实践异常记录self
- Mysql双服互备配置实践记录
- 2千万记录的Mysql表操作
- Mysql Cluster操作记录
- mysql 操作记录
- mysql操作记录
- MySQL常用操作记录
- 找出数组中的最大数和最小数
- 两个开源恶意代码的查杀引擎 clamav yara
- 巧用“沃通国际认证”防钓鱼
- 根据uuid来挂载磁盘
- Android高效加载大图、多图解决方案,有效避免程序OOM
- 追踪mysql操作记录实践2
- IIS 设置了Session的超时时间
- [ExtJS5学习笔记]第三十五节 sencha extjs 5 组件查询方法总结
- UIEventKit EKCalender EKEventStore EKSource
- ORACLE数值类型的性能优化技巧number,Int,float
- mysql中You can't specify target table for update in FROM clause错误
- 39999961853你的线上店铺被我们承包了丨阿里巴巴全行业&u8226;优质班开课嘞!
- Android技术——列表呈现,AdapterView及其子类(下)
- 淘金昌龙:阿里巴巴缘何在美遭空头狙击