设为首页 收藏本站
查看: 750|回复: 0

[经验分享] [转]mysql 用init-connect+binlog实现用户操作追踪 做access 的ip的log 记录

[复制链接]

尚未签到

发表于 2016-10-20 08:43:31 | 显示全部楼层 |阅读模式
  from:http://blog.iyunv.com/uid-24086995-id-168445.htmlhttp://www.mysqlsystems.com/2009/11/mysql-audit-access-log.html


在MYSQL中,每个连接都会先执行init-connect,进行连接的初始化。我们可以在这里获取用户的登录名称和thread的ID值。然后配合binlog,就可以追踪到每个操作语句的操作时间,操作人等。实现审计。
实验过程:
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=DEFAULT CHARSET=utf8;


2:在配置文件中配置init-connect参数。登录时插入日志表。如果这个参数是个错误的SQL语句,登录就会失败。
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登录失败。

  • 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的操作。


  • 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来追踪到是谁,什么时间,做了什么操作。

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-288653-1-1.html 上篇帖子: 远程连接mysql速度慢的解决方法:skip-name-resolve取消DNS的反向解析 下篇帖子: MySQL锁机制/管理(并发锁,行锁,表锁,预加锁,全局锁等等)
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表