大家好，又见面了，我是全栈君。
第一种方法：安装插件的方式
这里使用的是macfee的mysql audit插件，虽然日志信息比较大，对性能影响大，但是如果想要开启审计，那也应该忍受了。介绍几个参考地址：
[root@iZ2zeh44pi6rlahxj7s9azZ data]# ls
audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip my3307 zzz
[root@iZ2zeh44pi6rlahxj7s9azZ data]# unzip audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip
在配置文件my.cnf的[mysqld]中加入
plugin-load=audit=libaudit_plugin.so
[root@iZ2zeh44pi6rlahxj7s9azZ /]# /data/audit-plugin-mysql-5.7-1.1.4-725/lib/libaudit_plugin.so
[root@iZ2zeh44pi6rlahxj7s9azZ lib]# mv libaudit_plugin.so /opt/mysql/lib/plugin/
[root@iZ2zeh44pi6rlahxj7s9azZ plugin]# chown -R mysql:mysql libaudit_plugin.so
[root@iZ2zeh44pi6rlahxj7s9azZ plugin]# chmod +x libaudit_plugin.so
然后进行插件的加载：
root(none) 04:17:18> INSTALL PLUGIN audit SONAME ‘libaudit_plugin.so’;
卸载插件的方法：
root(none) 04:17:55> uninstall plugin audit;
插件的加载出现问题（可在error-log中查看）：
(1)root@test 05:15:37>INSTALL PLUGIN audit_log SONAME ‘libaudit_plugin.so’;
ERROR 1127 (HY000): Can’t find symbol ‘audit_log’ in library
原因：是前面说的配置文件中加入的plugin-load=audit=libaudit_plugin.so，中间的audit是对其的命名，如果加载用audit_log则会报错
解决办法：用一致的名字
(2)ERROR 1123 (HY000): Can’t initialize function ‘audit’; Plugin initialization function failed.
问题：从报错很明显是因为加载时初始化出现了问题，可能是数据不一致导致的
解决办法：用
offest-extract.sh解决，方法如下：
offest-extract.sh解决，方法如下：
Download the offset-extract.sh script from:
https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh
https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh
Note:
The offest-extract.sh script changed as the plugin added additional offsets. If you are using a build earlier than 1.0.8-515, you will need to use the script from the 1.0.7 tag:
https://raw.githubusercontent.com/mcafee/mysql-audit/v1.0.7/offset-extract/offset-extract.sh
. Further more, if you are using a build earlier than 1.0.4-451, you will need to use the script from the 1.0.3 tag:
https://raw.github.com/mcafee/mysql-audit/v1.0.3/offset-extract/offset-extract.sh
The offest-extract.sh script changed as the plugin added additional offsets. If you are using a build earlier than 1.0.8-515, you will need to use the script from the 1.0.7 tag:
https://raw.githubusercontent.com/mcafee/mysql-audit/v1.0.7/offset-extract/offset-extract.sh
. Further more, if you are using a build earlier than 1.0.4-451, you will need to use the script from the 1.0.3 tag:
https://raw.github.com/mcafee/mysql-audit/v1.0.3/offset-extract/offset-extract.sh
[root@iZ2zeh44pi6rlahxj7s9azZ data]# chmod +x ./offset-extract.sh
[root@iZ2zeh44pi6rlahxj7s9azZ data]# ./offset-extract.sh /opt/mysql-5.7.19-linux-glibc2.12-x86_64/bin/mysqld
ERROR: gdb not found. Make sure gdb is installed and on the path.
[root@iZ2zeh44pi6rlahxj7s9azZ data]# yum install gdb
[root@iZ2zeh44pi6rlahxj7s9azZ data]# ./offset-extract.sh /opt/mysql-5.7.19-linux-glibc2.12-x86_64/bin/mysqld
//offsets for: /opt/mysql-5.7.19-linux-glibc2.12-x86_64/bin/mysqld (5.7.19)
{“5.7.19″,”b4633eb887552a3bbb5db3a1eea76e48”, 7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964, 4352, 3648, 3656, 3660, 6048, 2072, 8, 7032, 7072, 7056},
在配置文件my.cnf的[mysqld]中加入
audit_offsets=7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964, 4352, 3648, 3656, 3660, 6048, 2072, 8, 7032, 7072, 7056
然后再次加载插件就可以了。通过show plugins;验证是否成功（最后一行）
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL |
+—————————-+———-+——————–+——————–+———+
开启审计日志：
root@test 04:48:57> SET GLOBAL audit_json_file=ON;
进入datadir目录，看到mysql-audit.json的文件即为审计日志文件
ps:具体有关插件的参数含义见
https://github.com/mcafee/mysql-audit/wiki/Configuration
https://github.com/mcafee/mysql-audit/wiki/Configuration
第二种：通过init-connect
1、创建审计用的库表。
yuelei@(none) 04:36:31>create database db_monitor ;
Query OK, 1 row affected (0.00 sec)
yuelei@(none) 04:36:35>use db_monitor ;
Database changed
yuelei@db_monitor 04:37:17>CREATE TABLE accesslog
-> ( thread_id int(11) DEFAULT NULL, #进程id
-> log_time datetime default null, #登录时间
-> localname varchar(50) DEFAULT NULL, #登录名称，带详细ip
-> matchname varchar(50) DEFAULT NULL, #登录用户
-> key idx_log_time(log_time)
-> )
Query OK, 0 rows affected (0.02 sec)
2、配置init-connect参数
yuelei@db_monitor 04:37:21>set global init_connect=’insert into db_monitor.accesslog(thread_id,log_time,localname,matchname) values(connection_id(),now(),user(),current_user())’;
Query OK, 0 rows affected (0.00 sec)
yuelei@db_monitor 04:39:34>flush privileges;
Query OK, 0 rows affected (0.00 sec)
3、授予普通用户对accesslog表的insert权限（在root用户下）
root@(none) 04:38:34>create user demon@’%’;
Query OK, 0 rows affected (0.00 sec)
root@(none) 04:38:34>grant insert on db_monitor.accesslog to demon@’%’;
Query OK, 0 rows affected (0.00 sec)
进入具有insert（普通权限）的用户demon中对数据库进行一系列操作
demon@db_monitor 04:39:42>use test;
Database changed
demon@test 04:40:00>delete from test4 where id =10;
Query OK, 1 row affected (0.01 sec)
demon@test 04:40:16>flush privileges;
Query OK, 0 rows affected (0.00 sec)
进入具有高级权限的用户下，查看表中的记录，配合binlog日志是否能追踪到时哪个用户,，结果是一目了然。
root@test 04:40:23>select * from db_monitor.accesslog;
+———–+———————+—————–+—————–+
| thread_id | log_time | localname | matchname |
+———–+———————+—————–+—————–+
| 9 | 2017-07-24 16:44:43 | demon@127.0.0.1 | demon@127.0.0.1 |
+———–+———————+—————–+—————–+
1 row in set (0.00 sec)
[root@iZ2zeh44pi6rlahxj7s9azZ log]# /opt/mysql/bin/mysqlbinlog mysql-bin.000044;
# at 3563
#170724 16:46:23 server id 1 end_log_pos 3624 GTID last_committed=17 sequence_number=18 rbr_only=no
SET @@SESSION.GTID_NEXT= ’90ad28b0-6d2b-11e7-8eb5-00163e06ff5b:347’/*!*/;
# at 3624
#170724 16:46:23 server id 1 end_log_pos 3699 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1500885983/*!*/;
BEGIN
/*!*/;
# at 3699
#170724 16:46:23 server id 1 end_log_pos 3798 Query thread_id=9 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1500885983/*!*/;
delete from test3 where id =9
/*!*/;
# at 3798
#170724 16:46:23 server id 1 end_log_pos 3825 Xid = 65
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
总结：
第一种方法缺点：日志信息比较大，对性能影响大。优点：对每一时刻每一用户的操作都有记录
第二种方法缺点：只对有低级权限的用户的操作有记录，权限高的则没有 。优点：日志信息比较小，对性能影响小
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/108297.html原文链接：https://javaforall.net
