阿里云和腾讯云的MYSQL都带有云审计功能，如果只是想自己实现简单的审计功能，可以通过init-connect来实现审计功能。
建审计库及表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
create database auditlog;
create table auditlog.t_audit(
id int not null auto_increment,
thread_id int not null,
login_time timestamp,
localname varchar(50) default null,
matchname varchar(50) default null,
primary key (id)
)ENGINE=InnoDB default charset=utf8 comment '审计用户登录信息';
|
给所有用户授权
1
|
select concat("grant select,insert on auditlog.t_audit to '",user,"'@'",host,"';") from mysql.user;
|
然后将这些查出来的sql语句执行，切勿忘记，不执行的话，mysql用户会连接不上！！！！
注意，以后每添加一个用户都必须授权此表的插入权限，要不会连接不上！
1
2
|
grant select,insert on auditlog.t_audit to 'usera'@'%';
grant select,insert on auditlog.t_audit to 'userb'@'%';
|
设置init_connect
1
|
set global init_connect='insert into auditlog.t_audit(id,thread_id,login_time,localname,matchname) values(null,connection_id(),now(),user(),current_user());';
|
set global log_timestamps = SYSTEM;
set global general_log = on;
查询
show global variables like ‘%init_connect%’;
show global variables like ’log_timestamps';
show global variables like ‘%general%’;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> show global variables like '%init_connect%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------+
| init_connect | insert into auditlog.t_audit(id,thread_id,login_time,localname,matchname) values(null,connection_id(),now(),user(),current_user()); |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
mysql> show global variables like 'log_timestamps';
Empty set
mysql> show global variables like '%general%';
+------------------+--------------------------------------------+
| Variable_name | Value |
+------------------+--------------------------------------------+
| general_log | OFF |
| general_log_file | /data1/mysql_root/data/20176/TENCENT64.log |
+------------------+--------------------------------------------+
2 rows in set (0.09 sec)
mysql>
|
参考文章：
https://www.cnblogs.com/yangliheng/p/6270279.html
https://blog.csdn.net/Smile_coderrr/article/details/89920877?utm_source=distribute.pc_relevant.none-task