当前位置:  首页  >  PHP教程  >  PHP 应用  >  CMS建站

在MySQL中使用init-connect与binlog来实现用户操作追踪记录

测试环境莫名其妙有几条重要数据被删除了,由于在binlog里面只看到是公用账号删除的,无法查询是那个谁在那个时间段登录的,就考

测试环境莫名其妙有几条重要数据被删除了,由于在binlog里面只看到是公用账号删除的,无法查询是那个谁在那个时间段登录的,就考

前言:
测试环境莫名其妙有几条重要数据被删除了,由于在binlog里面只看到是公用账号删除的,无法查询是那个谁在那个时间段登录的,就考虑怎么记录每一个MYSQL账号的登录信息,在MYSQL中,每个连接都会先执行init-connect,进行连接的初始化,我们可以在这里获取用户的登录名称和thread的ID值。然后配合binlog,,就可以追踪到每个操作语句的操作时间,操作人等。实现审计。

MySQL数据恢复--binlog

MySQL中binlog日记清理

如何安全删除MySQL下的binlog日志

MySQL--binlog日志恢复数据

MySQL删除binlog日志及日志恢复数据的方法

MySQL binlog三种格式介绍及分析

MySQL 利用binlog增量备份+还原实例

MySQL删除binlog日志及日志恢复数据

1,在mysql服务器db中建立单独的记录访问信息的库
set names utf8;
create databaseaccess_log;
CREATE TABLE`access_log`
(
`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, -- 登录名称
`matchname` varchar(30) DEFAULT NULL, -- 登录用户
PRIMARYKEY (`id`)
) ENGINE=InnoDBAUTO_INCREMENT=1 DEFAULT CHARSET=utf8 comment '录入用户登录信息';



2,在配置文件中配置init-connect参数。登录时插入日志表。如果这个参数是个错误的SQL语句,登录就会失败。
vim/usr/local/mysql/my.cnf
init-connect='INSERTINTO access_log.access_logVALUES(NULL,CONNECTION_ID(),NOW(),USER(),CURRENT_USER());'
然后重启数据库

3,创建普通用户,不能有super权限,而且用户必须有对access_log库的access_log表的insert权限,否则会登录失败。
给登录用户赋予insert权限,但是不赋予access_log的insert、select权限,
GRANTINSERT,DELETE,UPDATE,SELECT ON test.* TO audit_user@'%' IDENTIFIED BY'cacti_user1603';
mysql> GRANTCREATE,DROP,ALTER,INSERT,DELETE,UPDATE,SELECT ON test.* TO audit_user@'%'IDENTIFIED BY 'cacti_user1603';
Query OK, 0 rowsaffected (0.00 sec)
mysql> exit
然后去用新的audit_user登录操作
[root@db_server~]# /usr/local/mysql/bin/mysql -uaudit_user -p -S/usr/local/mysql/mysql.sock
Enter password:
Welcome to theMySQL monitor. Commands end with ; or \g.
Your MySQL connectionid is 25
Server version:5.6.12-log

Copyright (c)2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is aregistered trademark of Oracle Corporation and/or its
affiliates. Othernames may be trademarks of their respective
owners.

Type 'help;' or'\h' for help. Type '\c' to clear the current input statement.

mysql> lect *from access_log.access_log;
ERROR 2006(HY000): MySQL server has gone away
No connection.Trying to reconnect...
Connection id: 26
Current database:*** NONE ***


ERROR 1184(08S01): Aborted connection 26 to db: 'unconnected' user: 'audit_user' host:'localhost' (init_connect command failed)
mysql>
看到报错信息 (init_connect command failed),再去错误日志error log验证一下:
tail -fn 5/usr/local/mysql/mysqld.log
2014-07-28 16:03:3123743 [Warning] Aborted connection 25 to db: 'unconnected' user: 'audit_user'host: 'localhost' (init_connect command failed)
2014-07-2816:03:31 23743 [Warning] INSERT command denied to user ''@'localhost' for table'access_log'
2014-07-2816:04:04 23743 [Warning] Aborted connection 26 to db: 'unconnected' user:'audit_user' host: 'localhost' (init_connect command failed)
2014-07-2816:04:04 23743 [Warning] INSERT command denied to user ''@'localhost' for table'access_log'
看到必须要有对access_log库的access_log表的insert权限才行。


4,赋予用户access_log的insert、select权限,然后重新赋予权限:
GRANTSELECT,INSERT ON access_log.* TO audit_user@'%';
mysql>
mysql> GRANTSELECT,INSERT ON access_log.* TO audit_user@'%';
Query OK, 0 rowsaffected (0.00 sec)

mysql> exit
Bye

再登录,报错如下:
[root@db_server~]# /usr/local/mysql/bin/mysql -uaudit_user -p -S/usr/local/mysql/mysql.sock
Enter password:
ERROR 1045(28000): Access denied for user 'audit_user'@'localhost' (using password: YES)
[root@db_server~]#

去查看error日志:
2014-07-2816:15:29 23743 [Warning] INSERT command denied to user ''@'localhost' for table'access_log'
2014-07-2816:15:41 23743 [Warning] Aborted connection 37 to db: 'unconnected' user:'audit_user' host: 'localhost' (init_connect command failed)
2014-07-2816:15:41 23743 [Warning] INSERT command denied to user ''@'localhost' for table'access_log'
2014-07-2816:15:50 23743 [Warning] Aborted connection 38 to db: 'unconnected' user:'audit_user' host: 'localhost' (init_connect command failed)
2014-07-2816:15:50 23743 [Warning] INSERT command denied to user ''@'localhost' for table'access_log'

更多详情见请继续阅读下一页的精彩内容:

吐了个 "CAO" !
扫码关注 PHP1 官方微信号
PHP1.CN | 中国最专业的PHP中文社区 | PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | PHP问答
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved PHP1.CN 第一PHP社区 版权所有