Mysql添加操作审计日志功能的实现

背景

鉴于目前MySQL审计需求不断深入扩张，以提高数据资产的安全，使得数据库愈来愈规范化管理，数据库实现审计功能成为必要安全环节。

众所周知，MySQL社区版是不带审计插件的（Audit Plugin），那么该如何实现审计功能呢？我们自然会想到使用binlog日志做审计，因为binlog日志本就记录着数据库上的所有改变。但是无奈，binlog日志并不会记录用户的登录信息，所以无法做到真正意义上的审计。

环境信息

本文将介绍如下两种方式，实现使用审计功能。

Mysql版本：MySQL - 5.7.25（社区版）

工具 获取源包名
MariaDB审计插件 mariadb-10.2.43-linux-systemd-x86_64
McAfee审计插件 audit-plugin-mysql-5.7-1.1.11-985-linux-x86_64.zip

MariaDB审计插件

MariaDB开发的MariaDB审计插件，虽然具有一些仅适用于MariaDB的独特功能，但它也可以与MySQL一起使用。MariaDB审计插件所生成的审计日志记录内容包括：用户名和主机、执行了哪些查询、访问了哪些表以及更改了服务器变量等详情信息。

获取MariaDB的审计插件 server_audit.so

这里需要注意的是MariaDB的审计插件貌似Mysql-5.7.34以上版本就不支持了，这里推荐用McAfee审计插件，请注意版本号限制。

1
2
3
4
5
# 下载MariaDB Server安装包
# 下载地址：https://mariadb.org/download/?t=mariadb&p=mariadb&r=10.2.43&os=Linux&cpu=x86_64&i=systemd&m=bkns
# 本地解压后，在解压目录下找到审计插件`server_audit.so

$ ./mariadb/mariadb-10.2.43-linux-systemd-x86_64/lib/plugin/server_audit.so

将审计插件 server_audit.so复制到MySQL的plugin目录下，并授权。

1
2
3
4
5
6
7
8
9
# 你不知道你mysql的插件目录在哪，可以用下面mysql命令先查询一下。
sql> show variables like "%plugin_dir%";

# 复制插件到插件目录下
cp ./mariadb/mariadb-10.2.43-linux-systemd-x86_64/lib/plugin/server_audit.so /data/mysql/3306/base/lib/plugin/

# 设置权限
chown -R mysql:mysql server_audit.so
chmod 755 server_audit.so

进入MySQL终端，安装插件。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#install审计插件
mysql> install plugin server_audit soname 'server_audit.so';
Query OK, 0 rows affected (0.07 sec)

#查看当前的插件信息
mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
 ……
| ngram                      | ACTIVE   | FTPARSER           | NULL               | GPL     |
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
| SERVER_AUDIT               | ACTIVE   | AUDIT              | server_audit.so    | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
47 rows in set (0.00 sec)

当安装完审计插件之后，MySQL中将有一些新的全局变量出现。这些变量就是用于配置审计日志的，例如配置审计日志的位置、日志大小、记录的信息格式等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 查看审计相关的变量。
mysql> show variables like '%server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           |                       |
| server_audit_excl_users       |                       |
| server_audit_file_path        | server_audit.log      |
| server_audit_file_rotate_now  | OFF                   |
| server_audit_file_rotate_size | 1000000               |
| server_audit_file_rotations   | 9                     |
| server_audit_incl_users       |                       |
| server_audit_loc_info         |                       |
| server_audit_logging          | OFF                   |
| server_audit_mode             | 1                     |
| server_audit_output_type      | file                  |
| server_audit_query_log_limit  | 1024                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+
16 rows in set (0.01 sec)

一些变量说明。

变量名 获取源包名
server_audit_logging 控制审计功能的开关；
server_audit_events 指定记录的事件类型； 如果设置指定值，则审核日志只记录在设定值范围内的事件类型； 如果未设置，则每个事件类型都会记录到审核日志中（默认值选项）。
server_audit_file_path 当server_audit_output_type=file时，设置日志文件的路径和文件名； 默认文件路径为datadir，默认文件名为server_audit.log 。
server_audit_file_rotate_now 手工触发审计日志轮换； 即：设置为 ON 或 1 来强制日志文件轮换。
server_audit_file_rotate_size 日志大小限制，达到了阈值，审计日志会自动轮换。
server_audit_file_rotations 指定轮换后所保留的日志文件个数； 如果设置为 0，则日志永远不会轮换； 默认值为 9，即保存最多9个审计日志。
server_audit_incl_users 指定用户列表，在内的用户，将被记录； 注：CONNECT 记录不受此变量的影响。
server_audit_excl_users 指定用户列表，在内的用户，不会被记录； 如果用户在server_audit_incl_users中也指定了，用户活动仍会被记录； 注：CONNECT 记录不受此变量的影响。
server_audit_query_log_limit 限制记录中查询字符串的长度。

配置示例

1
2
3
4
5
6
7
8
# 开启审计功能
mysql> SET GLOBAL server_audit_logging=ON;

# 指定记录内容‘connect,query’，记录用户的连接和查询语句
SET global server_audit_events='connect,query';

# 重命名审计日志
mysql> set global server_audit_file_path='mysql_3306_audit.log';

为了永久生效，必须还要在配置文件中添加如下内容

1
2
3
4
5
# 这里仅定义了两项变量值供参考
[server]
server_audit_logging=ON
server_audit_events=connect,query
…

审计日志结果说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
# 模拟相关操作（查询结果篇幅过长，省略部分内容）
mysql> show databases;
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
mysql> select count(*) from sbtest1;
mysql> select * from sbtest1 limit 3;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k      | c                                                                                                                       | pad                                                         |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 498670 | 31451373586-15688153734-79729593694-96509299839-83724898275-86711833539-78981337422-35049690573-51724173961-87474696253 | 98996621624-36689827414-04092488557-09587706818-65008859162 |
|  2 | 497778 | 21472970079-70972780322-70018558993-71769650003-09270326047-32417012031-10768856803-14235120402-93989080412-18690312264 | 04776826683-45880822084-77922711547-29057964468-76514263618 |
|  3 | 498956 | 49376827441-24903985029-56844662308-79012577859-40518387141-60588419212-24399130405-42612257832-29494881732-71506024440 | 26843035807-96849339132-53943793991-69741192222-48634174017 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> exit
Bye

[root@10-186-65-155 plugin]# /data/mysql/3306/base/bin/mysql -utest -p -S /data/mysql/3306/data/mysqld.sock
Enter password:
mysql> show databases;
mysql> use occdb
mysql> show tables;
mysql> create table abc like occ_application_config;
Query OK, 0 rows affected (10.09 sec)
mysql> exit
Bye

# 查看datadir下审计日志文件
[root@10-186-65-155 data]# cd /data/mysql/3306/data/ && ll -h | grep 'audit.log'
-rw-r----- 1 mysql mysql  711 Apr 19 15:30 mysql_3306_audit.log
-rw-r----- 1 mysql mysql  329 Apr 19 15:26 server_audit.log

# 查看审计日志`mysql_3306_audit.log`记录的内容
20220419 15:26:04,10-186-65-155,root,localhost,6,6,QUERY,,'set global server_audit_file_path=\'mysql_3306_audit.log\'',0
20220419 15:29:30,10-186-65-155,root,localhost,6,7,QUERY,,'show databases',0
20220419 15:29:38,10-186-65-155,root,localhost,6,8,QUERY,,'SELECT DATABASE()',0
20220419 15:29:38,10-186-65-155,root,localhost,6,10,QUERY,test,'show databases',0
20220419 15:29:38,10-186-65-155,root,localhost,6,11,QUERY,test,'show tables',0
20220419 15:29:43,10-186-65-155,root,localhost,6,17,QUERY,test,'show tables',0
20220419 15:30:04,10-186-65-155,root,localhost,6,18,QUERY,test,'select count(*) from sbtest1',0
20220419 15:30:25,10-186-65-155,root,localhost,6,19,QUERY,test,'select * from sbtest1 limit 3',0
20220419 15:36:14,10-186-65-155,root,localhost,6,0,DISCONNECT,test,,0
20220419 15:36:24,10-186-65-155,test,localhost,7,0,CONNECT,,,0
20220419 15:36:24,10-186-65-155,test,localhost,7,21,QUERY,,'select @@version_comment limit 1',0
20220419 15:36:28,10-186-65-155,test,localhost,7,22,QUERY,,'show databases',0
20220419 15:36:36,10-186-65-155,test,localhost,7,23,QUERY,,'SELECT DATABASE()',0
20220419 15:36:36,10-186-65-155,test,localhost,7,25,QUERY,occdb,'show databases',0
20220419 15:36:36,10-186-65-155,test,localhost,7,26,QUERY,occdb,'show tables',0
20220419 15:36:39,10-186-65-155,test,localhost,7,28,QUERY,occdb,'show tables',0
20220419 15:36:55,10-186-65-155,test,localhost,7,29,QUERY,occdb,'create table abc like occ_application_config',0
20220419 15:37:11,10-186-65-155,test,localhost,7,0,DISCONNECT,occdb,,0

补充：下发的错误语句，或者连接失败等语句，审计日志依然会记录在内。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 模拟错误语句和连接失败情景
mysql> show creates table abc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creates table abc' at line 1
mysql> exit
Bye
[root@10-186-65-155 plugin]# /data/mysql/3306/base/bin/mysql -utest -p -S /data/mysql/3306/data/mysqld.sock
Enter password:
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: NO)

# 查看审计日志记录的相关内容
……
20220419 15:42:16,10-186-65-155,test,localhost,9,45,QUERY,occdb,'show creates table abc',1064
20220419 15:42:34,10-186-65-155,test,localhost,9,0,DISCONNECT,occdb,,0
20220419 15:42:48,10-186-65-155,test,localhost,10,0,FAILED_CONNECT,,,1045

根据以上记录的内容可以总结审计日志的记录格式

1
[timestamp],[serverhost],[username],[host],[connectionid],[queryid],[operation],[database],[object],[retcode]

优点

  • 使用xml格式和内容明确且详细；
  • 审计粒度细：例如可通过server_audit_events和server_audit_incl_users等参数指定需要审计的内容；
  • 审计日志维护管理方便：自带审计日志切割轮换参数；
  • 审计日志会记录下发的错误语句、连接失败信息等。

缺点

  • 对MySQL性能有一定影响。

McAfee审计插件【推荐】

所属McAfee公司McAfee审计插件，专门为MySQL提供审计功能。其设计重点是安全和审计要求。可以独立的作为审计插件，实现MySQL的审计功能，实现步骤与MariaDB审计插件类似。

下载审计插件libaudit_plugin.so并解压，这里注意要下载对应mysql版本的插件。

1
2
3
4
# 下载地址：https://github.com/mcafee/mysql-audit/releases

# 本地解压后，在解压目录下找到审计插件libaudit_plugin.so
./audit-plugin-mysql-5.7-1.1.11-985/lib/libaudit_plugin.so

将审计插件server_audit.so复制到MySQL的plugin目录下，并授权

1
2
3
4
$ cp ./audit-plugin-mysql-5.7-1.1.11-985/lib/libaudit_plugin.so /data/mysql/6666/base/lib/plugin/

$ chown -R mysql:mysql libaudit_plugin.so
$ chmod 755 libaudit_plugin.so

进入MySQL终端，安装插件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 安装插件前需要检查你mysql服务器selinux是否关闭，没有关闭的话用下面命令关闭一下
$ setenforce 0

# 安装插件
mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
Query OK, 0 rows affected (1.08 sec)
 
mysql> show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
……
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
| rpl_semi_sync_slave        | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
| AUDIT                      | ACTIVE   | AUDIT              | libaudit_plugin.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
47 rows in set (0.00 sec)

同Mariadb的审计插件类似，当安装完审计插件之后，MySQL中将有一些新的全局变量，用于配置审计日志相关属性。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
mysql> show variables like '%audit%';
+---------------------------------+-----------------------------------------------------------------------------------------------                  
| Variable_name                   | Value                                                                                                           
+---------------------------------+-----------------------------------------------------------------------------------------------                  
| audit_before_after              | after                                                                                                           
| audit_checksum                  |                                                                                                                 
| audit_client_capabilities       | OFF                                                                                                             
| audit_delay_cmds                |                                                                                                                 
| audit_delay_ms                  | 0                                                                                                               
| audit_force_record_logins       | OFF                                                                                                             
| audit_header_msg                | ON                                                                                                              
| audit_json_file                 | OFF                                                                                                             
| audit_json_file_bufsize         | 1                                                                                                               
| audit_json_file_flush           | OFF                                                                                                             
| audit_json_file_retry           | 60                                                                                                              
| audit_json_file_sync            | 0                                                                                                               
| audit_json_log_file             | mysql-audit.json                                                                                                
| audit_json_socket               | OFF                                                                                                             
| audit_json_socket_name          | /var/run/db-audit/mysql.audit__data_mysql_6666_data_6666                                                        
| audit_json_socket_retry         | 10                                                                                                              
| audit_json_socket_write_timeout | 1000                                                                                                            
| audit_offsets                   |                                                                                                                 
| audit_offsets_by_version        | ON                                                                                                              
| audit_password_masking_cmds     | CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER,UPDATE                        
| audit_password_masking_regex    | 
| audit_record_cmds               |                                                                                                                 
| audit_record_objs               |                                                                                                                 
| audit_sess_connect_attrs        | ON                                                                                                              
| audit_socket_creds              | ON                                                                                                              
| audit_uninstall_plugin          | OFF                                                                                                             
| audit_validate_checksum         | ON                                                                                                              
| audit_validate_offsets_extended | ON                                                                                                              
| audit_whitelist_cmds            | BEGIN,COMMIT,PING                                                                                               
| audit_whitelist_users           |                                                                                                                 
+---------------------------------+-----------------------------------------------------------------------------------------------                  
30 rows in set (0.01 sec)

部分变量说明

变量名 获取源包名
audit_json_file 是否开启审计 ON/OFF； 默认值：OFF。
audit_json_log_file 设置日志文件的路径和文件名； 默认值：mysql-audit.json，默认路径：datadir。
audit_record_cmds 设置要记录的语句类型，例如：delete,update,create_table,create_db,drop_db,drop_table ； 默认记录所有的命令。
audit_record_objs 设置要记录到审计跟踪的对象（表），例如：database.table，可以使用通配符。
audit_whitelist_cmds 设置不需要记录的语句类型。
audit_whitelist_users 设置不需要记录的用户。
audit_before_after 控制是在执行当前 SQL 语句之前还是之后写入其日志记录，或两者兼而有之。可能的值为’before’、’after’或’both’，默认为’after’。
audit_json_file_flush 手工进行轮换日志。
audit_json_file_sync 控制日志刷写周期json，如果此变量的值为N（N≠0），则审计日志将在每N次写入后同步到磁盘。默认值：0 。

配置示例

1
2
3
4
5
mysql> set global audit_json_file=on;
Query OK, 0 rows affected (0.01 sec)

mysql> set global audit_json_log_file='mysql_audit.out';
Query OK, 0 rows affected (0.00 sec)

为了永久生效，必须还要在配置文件中添加如下内容:

1
2
3
4
5
6
7
8
9
10
11
12
13
# 这里仅定义部分配置仅供参考
[mysqld]

######### 审计日志相关的配置 ################
# 加载的审计插件名称,就是我们下载的审计插件
plugin-load=AUDIT=libaudit_plugin.so
# 开启审计日志记录到文件
audit_json_file=on
# 审计日志记录哪些操作
audit_record_cmds='delete,update,create_table,create_db,drop_db,drop_table,alter_table,grant,truncate,set_option'
# 审计日志输出的名称，这个文件保存在了mysql的data数据目录下了
audit_json_log_file='mysql_audit.out'
......

审计日志结果说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
# 模拟相关操作（查询结果篇幅过长，省略部分内容）
[root@10-186-65-155 plugin]# /data/mysql/6666/base/bin/mysql -uroot -p -S /data/mysql/6666/data/mysqld.sock
Enter password:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
mysql> select count(*) from mysql.user;
mysql> create database test;
mysql> use test;
Database changed
mysql> create table aaa(id int,name varchar(20),primary key(id));
mysql> insert into aaa values (1,'zhangsan'),(2,'lisi');
mysql> exit
Bye
[root@10-186-65-155 plugin]# /data/mysql/6666/base/bin/mysql -utest -p -S /data/mysql/6666/data/mysqld.sock
Enter password:
mysql> use test;
mysql> select * from aaa;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+

 # 查看datadir下的审计日志记录内容
 {"msg-type":"activity","date":"1650361253128","thread-id":"11","query-id":"0","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1650361253129","thread-id":"11","query-id":"145","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"select","query":"select @@version_comment limit 1"}
{"msg-type":"activity","date":"1650361255866","thread-id":"11","query-id":"146","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"4","status":"0","cmd":"show_databases","objects":[{"db":"information_schema","name":"/data/mysql/6666/tmp/#sql_1b0f_0","obj_type":"TABLE"}],"query":"show databases"}
{"msg-type":"activity","date":"1650361283561","thread-id":"11","query-id":"147","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"select","objects":[{"db":"mysql","name":"user","obj_type":"TABLE"}],"query":"select count(*) from mysql.user"}
{"msg-type":"activity","date":"1650361313234","thread-id":"11","query-id":"148","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","status":"0","cmd":"create_db","query":"create database test"}
{"msg-type":"activity","date":"1650361318055","thread-id":"11","query-id":"149","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"select","query":"SELECT DATABASE()"}
{"msg-type":"activity","date":"1650361318055","thread-id":"11","query-id":"150","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"Init DB","objects":[{"db":"test","obj_type":"DATABASE"}],"query":"Init DB"}
{"msg-type":"activity","date":"1650361318057","thread-id":"11","query-id":"151","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"5","status":"0","cmd":"show_databases","objects":[{"db":"information_schema","name":"/data/mysql/6666/tmp/#sql_1b0f_0","obj_type":"TABLE"}],"query":"show databases"}
{"msg-type":"activity","date":"1650361318057","thread-id":"11","query-id":"152","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","status":"0","cmd":"show_tables","objects":[{"db":"information_schema","name":"/data/mysql/6666/tmp/#sql_1b0f_0","obj_type":"TABLE"}],"query":"show tables"}
{"msg-type":"activity","date":"1650361361491","thread-id":"11","query-id":"153","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","status":"0","cmd":"create_table","objects":[{"db":"test","name":"aaa","obj_type":"TABLE"}],"query":"create table aaa(id int,name varchar(20),primary key(id))"}
{"msg-type":"activity","date":"1650361424702","thread-id":"11","query-id":"154","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"2","status":"0","cmd":"insert","objects":[{"db":"test","name":"aaa","obj_type":"TABLE"}],"query":"insert into aaa values (1,'zhangsan'),(2,'lisi')"}
{"msg-type":"activity","date":"1650361432156","thread-id":"11","query-id":"155","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5151","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5151","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","cmd":"Quit","query":"Quit"}
{"msg-type":"activity","date":"1650361439497","thread-id":"12","query-id":"0","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","cmd":"Connect","query":"Connect"}
{"msg-type":"activity","date":"1650361439498","thread-id":"12","query-id":"156","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"select","query":"select @@version_comment limit 1"}
{"msg-type":"activity","date":"1650361443135","thread-id":"12","query-id":"157","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"select","query":"SELECT DATABASE()"}
{"msg-type":"activity","date":"1650361443136","thread-id":"12","query-id":"158","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"Init DB","objects":[{"db":"test","obj_type":"DATABASE"}],"query":"Init DB"}
{"msg-type":"activity","date":"1650361443138","thread-id":"12","query-id":"159","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"5","status":"0","cmd":"show_databases","objects":[{"db":"information_schema","name":"/data/mysql/6666/tmp/#sql_1b0f_0","obj_type":"TABLE"}],"query":"show databases"}
{"msg-type":"activity","date":"1650361443139","thread-id":"12","query-id":"160","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"1","status":"0","cmd":"show_tables","objects":[{"db":"information_schema","name":"/data/mysql/6666/tmp/#sql_1b0f_0","obj_type":"TABLE"}],"query":"show tables"}
{"msg-type":"activity","date":"1650361454500","thread-id":"12","query-id":"162","user":"test","priv_user":"test","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"5162","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql","pid":"5162","os_user":"root","appname":"/data/mysql/6666/base/bin/mysql","rows":"2","status":"0","cmd":"select","objects":[{"db":"test","name":"aaa","obj_type":"TABLE"}],"query":"select * from aaa"}

根据以上的记录内容可总结审计日志的记录格式:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 审计日志使用的是json格式记录，如下为一条记录内容
{
	"msg-type": "activity",
	"date": "1650361454500",
	"thread-id": "12",
	"query-id": "162",
	"user": "test",
	"priv_user": "test",
	"ip": "",
	"host": "localhost",
	"_os": "linux-glibc2.12",
	"_client_name": "libmysql",
	"_pid": "5162",
	"_client_version": "5.7.25",
	"_platform": "x86_64",
	"program_name": "mysql",
	"pid": "5162",
	"os_user": "root",
	"appname": "/data/mysql/6666/base/bin/mysql",
	"rows": "2",
	"status": "0",
	"cmd": "select",
	"objects": [{
		"db": "test",
		"name": "aaa",
		"obj_type": "TABLE"
	}],
	"query": "select * from aaa"
}

优点

  • 使用json格式和内容明确且详细；

  • 审计粒度细：例如可通过audit_record_cmds和audit_record_objs等指定需要审计的内容；

  • 记录连接失败的语句（audit_force_record_logins参数值为on条件下）；

  • 审计日志刷写策略可控，可通过audit_json_file_sync参数控制。

缺点

  • 对MySQL性能有一定影响；

  • 不记录下发错误的语句；

  • 审计日志维护管理不便，无自带审计日志切割轮换参数。

多实例审计日志收集及监控

思路及效果展示

我的实现思路是:

  1. 先用前面提到的McAfee审计插件实现开始mysql的操作审计日志记录。
  2. 然后服务器实例安装Fluentd日志收集服务，收集审计插件生成的审计日志写入到es中。
  3. grafana对接es的数据源，然后导入仪表盘生成监控仪表。

来上个效果图吧

开始安装

1
2
3
注意

我原先环境已经对mysql服务器开启了McAfee审计功能并记录到日志、已经安装了Es6.8.1、已经安装了Grafana。下列文档中不提到这些环境安装操作，直接开始如何收集日志到es、grafana对接es数据源、导入仪表盘的操作。

Mysql服务器安装Fluentd日志收集端

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
# 安装td-agent
curl -L https://toolbelt.treasuredata.com/sh/install-redhat-td-agent3.sh

# 安装fluentd的es插件
$ td-agent-gem install fluent-plugin-elasticsearch

# 备份默认的td-agent配置文件
$ cd /etc/td-agent/ && cp td-agent.conf{,.bak}


# 生成收集mysql审计日志到es的td-agent配置文件，注意修改你的审计日志保存目录，你的es地址。
$ cat >td-agent.conf<<EOF
# ############## 统默认配置 ###################
<match td.*.*>
  @type tdlog
  @id output_td
  apikey YOUR_API_KEY

  auto_create_table
  <buffer>
    @type file
    path /var/log/td-agent/buffer/td
  </buffer>

  <secondary>
    @type file
    path /var/log/td-agent/failed_records
  </secondary>
</match>

<match debug.**>
  @type stdout
  @id output_stdout
</match>

<source>
  @type forward
  @id input_forward
</source>

<source>
  @type http
  @id input_http
  port 8888
</source>

## live debugging agent
<source>
  @type debug_agent
  @id input_debug_agent
  bind 127.0.0.1
  port 24230
</source>


########## 默认配置END ##############


############ 收集mysql审计日志 ###############
# 自定义收集本地mysql审计日志的数据源
<source>
  # 通过tail插件收集文件的日志
  @type tail
  # 要收集的本地日志文件路径
  path /data/mysql/mysql_audit.out
  # 是否从文件的最开始收集，默认为false，运行后只收集新检测到的日志数据，文件中的历史数据不会收集
  read_from_head true
  # 设置已经收集的日志偏移量，当上面参数为true 这个pos为空时，才从文件开始收集日志，注意给这个文件权限为777
  pos_file /data/mysql/mysql_audit.out.pos
  # 给当前的数据源打一个标签，后面会根据此标签匹配来进行相应的操作
  tag mysql.audit.dev

  # 数据源日志的解析配置
  <parse>
    # 数据源的格式，这里我的日志文件中是json格式日志数据
    @type json
    # 解析json字段
    json_parser json
  </parse>
</source>

# 匹配前面标签输出源，这里输出到es，注意安装es插件
<match mysql.audit.**>
  # 输出源类型为es
  @type elasticsearch
  # 验证es的版本，默认为7，如果你es版本为6这里改为false，下面配置改成6
  verify_es_version_at_startup false
  # 默认的es版本，如果你是7的es可以不设置，默认是7
  default_elasticsearch_version 6
  # 通过logstash类型的索引设置
  logstash_format true
  # logstash索引格式的前缀 fluentd-mysqlaudit-年.月.日
  logstash_prefix fluentd-mysqlaudit
  # 输出到es是否包含时间戳字段
  include_timestamp true
  # es地址
  hosts 192.168.99.125
  # es的端口号
  port 9200
  # 是否添加一个tag_key字段
  include_tag_key true
  # 这里设置tag_key的字段名称
  tag_key tag_key
  # 解决大量日志时会导致一段时间就无法写入es的问题
  reload_connections false
  reconnect_on_error true
  reload_on_failure true
  <buffer>
    chunk_limit_size 2M
    queue_limit_length  32
    flush_interval 3s
    retry_max_times 30
  </buffer>
</match>
############ Mysql审计日志收集END ###############
EOF

创建pos_file文件，并赋予权限777

1
2
3
4
5
# 文件文件为你配置文件中post_file配置字段的路径
$ touch /data/mysql/mysql_audit.out.pos

# 赋予777权限
$ chmod 777 /data/mysql/mysql_audit.out.pos

启动fluentd服务

1
2
3
4
5
# 启动服务
$ systemctl enabled td-agent && systemctl start td-agent

# 查看日志是否有报错
$ tail -f /var/log/td-agent/td-agent.log

服务启动成功后此时应该就能在es看到对应的索引创建（fluentd-mysqlaudit-年.月.日）及里面的日志数据了。

配置grafana下mysql审计日志的elasticsearch数据源

下载grafana的仪表盘json文件到你本地

1
https://www.putianhui.cn/package/script/grafana/grafana-mysql-audit.json

导入下载的仪表盘json文件到grafana中

导入仪表盘之后就能打开仪表盘看效果了

运维
#Mysql
Mysql添加操作审计日志功能的实现
https://johnnysxy.github.io/2023/05/24/Mysql添加操作审计日志功能的实现/
作者
Johnny Song
发布于
2023年5月24日
许可协议