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

前言：
测试环境莫名其妙有几条重要数据被删除了，由于在binlog里面只看到是公用账号删除的，无法查询是那个谁在那个时间段登录的，就考虑怎么记录每一个MYSQL账号的登录信息，在MYSQL中，每个连接都会先执行init-connect，进行连接的初始化，我们可以在这里获取用户的登录名称和thread的ID值。然后配合binlog，就可以追踪到每个操作语句的操作时间，操作人等。实现审计。MySQL数据恢复－－binlog http://www.linuxidc.com/Linux/2014-03/97907.htmMySQL中binlog日记清理 http://www.linuxidc.com/Linux/2011-02/32017.htm如何安全删除MySQL下的binlog日志 http://www.linuxidc.com/Linux/2013-06/86527.htmMySQL--binlog日志恢复数据 http://www.linuxidc.com/Linux/2013-04/82368.htmMySQL删除binlog日志及日志恢复数据的方法 http://www.linuxidc.com/Linux/2012-12/77072.htmMySQL binlog三种格式介绍及分析 http://www.linuxidc.com/Linux/2012-11/74359.htmMySQL 利用binlog增量备份+还原实例 http://www.linuxidc.com/Linux/2012-09/70815.htmMySQL删除binlog日志及日志恢复数据 http://www.linuxidc.com/Linux/2012-08/67594.htm

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"更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-07/104831p2.htm

Oracle 中极易混淆的几个 NAME 的分析和总结PostgreSQL 运行在 FreeBSD 和 Linux 的表现的测试数据相关资讯 binlog MySQL binlog

MySQL binlog中的事件类型（08月19日）
MySQL binlog 安全删除（03月30日）
通过Linux命令过滤出binlog中完整（01月30日）

MySQL数据丢失后利用binlog恢复有（04月18日）
MySQL在ROW模式下通过binlog提取（01月30日）
关于使用MySQL binlog对数据进行恢（01月24日）

本文评论查看全部评论（0）

表情：姓名：匿名字数

<button class="layui-btn layui-bg-purple" id="dofav">收藏该网址</button>
        </div>
    </div>
</div>
<div class="copyright">
    <div id="footbar">
        版权所有©石家庄振强科技有限公司2024 <a href="https://beian.miit.gov.cn" target="_blank">冀ICP备08103738号-5</a> <a href="/storage/sitemap.xml">网站地图</a>
    </div>
</div>
<script> var _mtj = _mtj || [];
(function () {
    var mtj = document.createElement("script");
    mtj.src = "https://node12.aizhantj.com:21233/tjjs/?k=p2tceukth5c";
    var s = document.getElementsByTagName("script")[0];
    s.parentNode.insertBefore(mtj, s);
})(); </script>
<script src="/static/lib/layui/layui.js"></script>
<script src="/static/lib/jquery/jquery.js"></script>
<script src="/static/lib/ajax.js"></script>
<script>
    layui.use(function () {
        var layer = layui.layer;
        var $ = layui.jquery;
        $("#dofav").click(function () {
            var artid = $(".article").attr("artid");
            var params={
                artid:artid,
                addtype:'escdns_article',
                t:Math.random()
            };
            if(artid>0){
                ajax.request({
                    method: "/index/article/addfavorite", type: "post", callback: function (res) {
                        layer.msg(res.msg);
                    }
                },params);
            }
        })
    })
</script>
</body>
</html>