mysql审计日志-ProxySQL

MySQL审计概述：
出于对数据安全的考虑，很多公司要求对MySQL的操作进行审计，这就要求我们对所有MySQL的操作都进行记录，并且相关信息要齐全（账号，时间，语句等）。
 
1、general_log：这样虽然可以记录所有的操作日志，但很遗憾，缺少账号等必要信息，而且IO消耗非常大。
2、init-connect：这个其实就是在用户连接的时候，插入一条当前用户，thread id，用户，时间的数据到审计表，结合binlog日志，就可以进行简单的定位。  简单粗暴，性能影响不大。不会记录超级用户。
3、第三方插件：方便好用。对数据库性能有一定的影响。
4、通过在代理或者代理前通过MySQL协议解析，不影响mysql性能，但要多一个中间层，如果代理本身支持最优。推荐方案：ProxySQL，代理maxscale暂不支持。
 
1.audit_log：MySQL自5.5版本起，实现了audit api，可以专门用于编写审计插件，Oracle官方就实现了一个叫做audit_log的插件，但这个插件既不免费也不开源，而是作为其企业服务的一部分，只供给付费用户使用.
2.安全厂商Macfee也有一个MySQL audit插件，但好久没有更新了，而且实现方式很奇特，在此不做讨论。
3.MariaDB出品的 MariaDB Audit Plugin，经过我的测试，安装和使用都非常简单。
4.percona公司出品的插件。
 
3种可用的MySQL插件：mariadb、percona、macfee
 
 
一、init-connect审计方法：
 
1。创建用于存放连接信息的表
 
create database audit_db default charset utf8;
use audit_db;
create table accesslog (id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,connection_id int(11) DEFAULT NULL,conn_user varchar(30) DEFAULT NULL,priv_match_name varchar(30) DEFAULT NULL,login_time timestamp NULL DEFAULT NULL); 
 
2。保证所有的用户对此表有写权限
 
insert into db (Host,Db,User,Insert_priv) values ('%','audit_db','','Y');
flush privileges;
 
3。设置init-connect
在my.cnf 中的 [mysqld] 的block 添加以下配置；
init-connect='insert into audit_db.accesslog (connection_id,conn_user,priv_match_name,login_time) values(connection_id(),user(),current_user(),now());'
log-bin=bin.log
 
4。重启数据库生效
service mysqld restart
 
假设想知道 是谁把tmp_2.tmp这个表的数据全删了。可以用binlog来定位。
/usr/local/mysql/3308/bin/mysqlbinlog /log/binlog/3308/bin.000006 -v | grep 'tmp' -B 10
 
COMMIT/*!*/;
# at 1136
#160720 14:40:03 server id 3308161  end_log_pos 1201 CRC32 0xa779d0c3 GTID last_committed=3 sequence_number=4
SET @@SESSION.GTID_NEXT= 'e08d636f-47de-11e6-af3d-0050569e70f2:29'/*!*/;
# at 1201
#160720 14:40:03 server id 3308161  end_log_pos 1274 CRC32 0x45a0e6b7 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1468996803/*!*/;
BEGIN
/*!*/;
# at 1274
#160720 14:40:03 server id 3308161  end_log_pos 1324 CRC32 0xb2514b75 Table_map: `tmp_2`.`tmp` mapped to number 109
# at 1324
#160720 14:40:03 server id 3308161  end_log_pos 1377 CRC32 0xed68bd31 Delete_rows: table id 109 flags: STMT_END_F
 
BINLOG '
wxyPVxOBejIAMgAAACwFAAAAAG0AAAAAAAEABXRtcF8yAAN0bXAAAgMPAloAA3VLUbI=
wxyPVyCBejIANQAAAGEFAAAAAG0AAAAAAAEAAgAC//wBAAAAA3BwcPwBAAAAA3JycjG9aO0=
'/*!*/;
### DELETE FROM `tmp_2`.`tmp`
### WHERE
###   @1=1
###   @2='ppp'
### DELETE FROM `tmp_2`.`tmp`
 
可以上看，删除tmp_2,tmp表的线程是：thread_id=11
 
mysql> select * from accesslog where connection_id=11;
+----+---------------+----------------+-----------------+---------------------+
| id | connection_id | conn_user      | priv_match_name | login_time          |
+----+---------------+----------------+-----------------+---------------------+
|  2 |            11 | test@localhost | test@localhost  | 2016-07-20 14:39:42 |
+----+---------------+----------------+-----------------+---------------------+
1 row in set (0.02 sec)
 
Q：使用init-connect会影响服务器性能吗？
A：理论上，只会在用户每次连接时往数据库里插入一条记录，不会对数据库产生很大影响。除非连接频率非常高（当然，这个时候需要注意的就是如何进行连接复用和控制，而非是不是要用这种方法的问题了）
 
Q：access-log表如何维护?
A: 由于是一个log系统，推荐使用archive存储引擎，有利于数据厄压缩存放。如果数据库连接数量很大的话，建议一定时间做一次数据导出，然后清表。
Q：表有其他用途么？
A：有！access-log表当然不只用于审计，当然也可以用于对于数据库连接的情况进行数据分析，例如每日连接数分布图等等。
Q：会有遗漏的记录吗？
A：会的，init-connect 是不会在super用户登录时执行的。所以access-log里不会有数据库超级用户的记录，这也是为什么我们不主张多个超级用户，并且多人使用的原因。
 
 
二、mcafee出品的MySQL插件
github:https://github.com/mcafee/mysql-audit
 
二进制版本下载地址: https://bintray.com/mcafee/mysql-audit-plugin/release/1.0.9-585/
本次下载：audit-plugin-mysql-5.7-1.0.9-585-linux-x86_64.zip
 
[root@test11 ~]# ls audit-plugin-mysql-5.7-1.0.9-585
COPYING  lib  README.txt  THIRDPARTY.txt
[root@test11 ~]# ls audit-plugin-mysql-5.7-1.0.9-585/lib/
libaudit_plugin.so
 
确定mysql的插件目录。
mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.13-log |
+------------+
1 row in set (0.00 sec)
 
mysql> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+-----------------------------------+
| Variable_name | Value                             |
+---------------+-----------------------------------+
| plugin_dir    | /usr/local/mysql/3308/lib/plugin/ |
+---------------+-----------------------------------+
1 row in set (0.00 sec)
 
cp插件的so文件到mysql的插件目录。
[root@test11 ~]# cp audit-plugin-mysql-5.7-1.0.9-585/lib/libaudit_plugin.so /usr/local/mysql/3308/lib/plugin/
 
mysql> 
mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
ERROR 1123 (HY000): Can't initialize function 'AUDIT'; Plugin initialization function failed.
失败。
查看错误日志：
2016-07-20T15:57:54.985752+08:00 18 [Warning] option 'audit-json-file-bufsize': signed value 0 adjusted to 1
2016-07-20T15:57:54.985824+08:00 18 [Warning] option 'plugin-audit-json-file-bufsize': signed value 0 adjusted to 1
2016-07-20T15:57:54.986058+08:00 18 [Note] Audit Plugin: starting up. Version: 1.0.9 , Revision: 585 (64bit). AUDIT plugin interface version: 1025 (0x401). MySQL Server version: 5.7.13-log.
2016-07-20T15:57:54.986084+08:00 18 [Note] Audit Plugin: setup_offsets audit_offsets: (null) validate_checksum: 1 offsets_by_version: 1
2016-07-20T15:57:57.317648+08:00 18 [Note] Audit Plugin: mysqld: /usr/local/mysql/3308/bin/mysqld (1663d3c6234242c71b3ed6a2521194da)
2016-07-20T15:57:57.317731+08:00 18 [Note] Audit Plugin: Couldn't find proper THD offsets for: 5.7.13-log
2016-07-20T15:57:57.317747+08:00 18 [ERROR] Plugin 'AUDIT' init function returned error.
2016-07-20T15:57:57.317759+08:00 18 [ERROR] Plugin 'AUDIT' registration as a AUDIT failed.
2016-07-20T15:57:57.317785+08:00 18 [Note] Shutting down plugin 'AUDIT'
2016-07-20T15:57:57.317802+08:00 18 [Note] Audit Plugin: deinit
 
Couldn't find proper THD offsets for: 5.7.13-log查不到合适的偏移值。
 
这个问题，已经有人遇到：https://github.com/mcafee/mysql-audit/issues/2
简单来说就是使用一个脚本，提取偏移量，然后配置在my.cnf中。
目录下offset-extract.sh就是本次脚本。
 
chmod +x offset-extract.sh
 
[root@test11 ~]# ./offset-extract.sh /usr/local/mysql/3308/bin/mysqld
//offsets for: /usr/local/mysql/3308/bin/mysqld (5.7.13)
{"5.7.13","1663d3c6234242c71b3ed6a2521194da", 7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964},
 
这里需要的偏移量就是7800开始的内容。
[root@test11 ~]# vi /usr/local/mysql/3308/my.cnf
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets=7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964
 
mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
Query OK, 0 rows affected (0.73 sec)
 
mysql> SHOW GLOBAL STATUS LIKE 'AUDIT_version';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| Audit_version | 1.0.9-585 |
+---------------+-----------+
1 row in set (0.00 sec)
 
安装成功
6.开启audit功能
  SET GLOBAL audit_json_file=ON;
 
7.执行任何语句(默认会记录任何语句)，然后去mysql数据目录查看mysql-audit.json文件(默认为该文件)
 
mysql> SHOW GLOBAL VARIABLES LIKE '%audi%';
+---------------------------------+--------------------------------------------------------------------------
| Variable_name                   | Value                                                                                                   
+---------------------------------+--------------------------------------------------------------------------
| audit_delay_cmds                |                                                                                                           
| audit_delay_ms                  | 0                                                                                                         
| audit_force_record_logins       | OFF                                                                                               
| audit_header_msg                | ON                                                                                                    
| audit_json_file                 | ON                                                                                                       
| 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          | /tmp/mysql.audit__data_mysql_3308_3308                                   
| audit_json_socket_retry         | 10                                                                                                     
| audit_offsets                   | 7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964        
| audit_offsets_by_version        | ON                                                                                                                                                                                                                                                                   
| audit_password_masking_cmds     | CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER 
| audit_password_masking_regex    |。。。。。。。
| audit_record_cmds               |                                                                  
| audit_record_objs               |                                                                                                                                                                                                                                                               
| audit_uninstall_plugin          | OFF                                                                                                                                                                                                                                       
| audit_validate_offsets_extended | ON                                                                                                                                                                                                                       
| audit_whitelist_cmds            | BEGIN,COMMIT                                          
+---------------------------------+------------------------------------
25 rows in set (0.00 sec)
 
其中我们需要关注的参数有：
1. audit_json_file 是否开启audit功能，on，off
2. audit_json_log_file   记录文件的路径和名称信息。
3. audit_record_cmds audit记录的命令,默认为记录所有命令。
可以设置为任意dml、dcl、ddl的组合， 如：audit_record_cmds=select,insert,delete,update
还可以在线设置set global audit_record_cmds=NULL   (表示记录所有命令)。
4. audit_record_objs    audit记录操作的对象,默认为记录所有对象,
可以用SET GLOBAL audit_record_objs=NULL设置为默认。
也可以指定为下面的格式
audit_record_objs=,test.*,mysql.*,information_schema.*
5. audit_whitelist_users  用户白名单
 
还有offsets参数的设置,如果开启audit_offsets_by_version=ON，则必须设置audit_offsets。
 
查看mysql data目录下mysql-audit.json的日志，格式如下：
{"msg-type":"header","date":"1469002978327","audit-version":"1.0.9-585","audit-protocol-version":"1.0","hostname":"test11","mysql-version":"5.7.13-log","mysql-program":"/usr/local/mysql/3308/bin/mysqld","mysql-socket":"/usr/local/mysql/3308/mysql.sock","mysql-port":"3308"}
{"msg-type":"activity","date":"1469003001877","thread-id":"18","query-id":"182","user":"root","priv_user":"root","host":"localhost","ip":"","cmd":"show_variables","objects":[{"db":"information_schema","name":"/tmp/#sql_63d4_0","obj_type":"TABLE"}],"query":"SHOW GLOBAL VARIABLES LIKE '%audi%'"}
{"msg-type":"activity","date":"1469003010787","thread-id":"18","query-id":"183","user":"root","priv_user":"root","host":"localhost","ip":"","cmd":"show_variables","objects":[{"db":"information_schema","name":"/tmp/#sql_63d4_0","obj_type":"TABLE"}],"query":"SHOW GLOBAL VARIABLES LIKE '%audi%'"}
 
注意这里的时间戳：1469003010787多了3位，描述到了毫秒。
date -d @1469003010 +"%Y-%m-%d %H:%M:%S"
2016-07-20 16:23:30
 
 
 
三、mariadb出品的MySQL插件
 
mysql> select @@version;
+---------------------+
| @@version           |
+---------------------+
| 10.0.20-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)
 
如果你是MySQL官方版本，需要下载这个插件或是直接从mariadb的目录下cp过去（目录下也有server_audit.so 文件）。解压后就是一个server_audit.so，将这个文件拷贝到mysql的plugin目录
[root@test12 ~]# ls /usr/local/mysql/3306/lib/plugin/server_audit.so 
/usr/local/mysql/3306/lib/plugin/server_audit.so
 
mariadb自带该插件，所以直接运行即可：
mysql> INSTALL PLUGIN server_audit SONAME 'server_audit';
Query OK, 0 rows affected (0.06 sec)
 
然后执行：
SET GLOBAL server_audit_logging=on;
打开日志记录。
 
mysql> show variables like '%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_logging          | ON                    |
| server_audit_mode             | 0                     |
| 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              |
+-------------------------------+-----------------------+
15 rows in set (0.01 sec)
 
也可以直接在my.cnf中指定：
[root@test11 ~]# vi /usr/local/mysql/3308/my.cnf
plugin-load=server_audit=server_audit.so
 
详细参数：
https://mariadb.com/kb/en/mariadb/server_audit-system-variables/
 
默认日志文件在MySQL datadir目录下面的server_audit.log文件。
[root@test12 ~]# cat /data/mysql/3306/server_audit.log
20160720 21:06:51,test12,root,localhost,14,795,QUERY,,'SET GLOBAL server_audit_logging=on',0
20160720 21:07:09,test12,root,localhost,14,796,QUERY,,'show variables like \'%audit%\'',0
20160720 21:20:22,test12,root,localhost,14,797,QUERY,,'show variables like \'%audit%\'',0
20160720 21:20:28,test12,root,localhost,14,0,DISCONNECT,,,0
 
percona出品插件，详情查看目录下文档。
 
 
四、ProxySQL审计日志
 
ProxySQL 2.0.5 引入了审计日志。此功能允许跟踪某些连接活动。要启用此功能，需要配置变量 mysql-auditlog_filename，也就是审计日志的文件名。此变量的默认值为空，也就是默认情况下不启用日志记录。
 
启用后，将记录以下事件：
 
在 MySQL 模块上：成功认证、认证失败、正常断开、封闭连接、更改架构（COMINITDB）
 
在管理模块上：成功认证、认证失败、正常断开、封闭连接
 
变量
mysql-auditlog_filename：此变量定义记录审核事件的审计日志的基本名称。日志文件的文件名将是基本名称，后跟一个 8 位数的逐行编号。 默认值为空字符串（）。
mysql-auditlog_filesize ：此变量定义关闭当前文件并创建新文件时审计日志的最大文件大小。 默认值为 104857600（100MB）。
 
当前实现仅支持一种日志记录格式：JSON。
 
 
属性：
  client_addr ：连接到 ProxySQL 的客户端的地址（IP：port）
  proxy_addr ：ProxySQL 正在侦听的绑定接口的地址（IP：端口）（仅适用于 MySQL 模块）
  event：事件类型。当前可能的值：
    MySQLClientConnect_OK ：成功连接到 MySQL 模块
    MySQLClientConnect_ERR ：与 MySQL 模块的连接失败
    MySQLClientClose ：MySQL 会话被关闭
    MySQLClientQuit：客户端向 COM_QUITMySQL 模块发送显式信息
    MySQLClientInitDB：客户端向 COMINIT_DBMySQL 模块发送显式信息
    AdminConnectOK ：成功连接到管理模块
    AdminConnectERR ：与管理模块的连接失败
    Admin_Close ：管理员会话已关闭
    AdminQuit：客户端向 COMQUIT 管理模块发送显式信息
  time ：事件发生时的人类可读时间，以毫秒为单位
  timestamp ：纪元时间（以毫秒为单位）
  ssl ：布尔值，指定是否使用 SSL
  schemaname：用于成功建立连接的当前模式
  username：客户的用户名
  threadid：分配给客户端的 threadid（会话 ID）
  creation_time ：创建会话时，信息仅在会话关闭时可用
  duration ：创建会话以来的时间（以毫秒为单位），此信息仅在会话关闭时可用
  extra_info：提供其他信息的属性。当前仅用于描述会话在代码的哪一部分关闭。
 
审计日志示例：
{\"client_addr\":\"10.0.200.179:51543\",\"event\":\"MySQL_Client_Connect_OK\",\"proxy_addr\":\"0.0.0.0:6033\",\"schemaname\":\"information_schema\",\"ssl\":false,\"thread_id\":8,\"time\":\"2020-08-13 16:08:24.960\",\"timestamp\":1597306104960,\"username\":\"dbmgr\"}
 
2、通用操作日志
管理员账户登录 ProxySQL：
 
mysql> SET mysql-eventslog_filename='all_queries.log';
mysql> LOAD MYSQL VARIABLES TO RUNTIME;
mysql> SAVE MYSQL VARIABLES TO DISK;
 
日志存储路径是：/var/lib/proxysql
 
接下来，创建查询规则以匹配需要记录的日志记录。如果需要记录所有，则一个简单的规则就可以：
mysql\> INSERT INTO mysql_query_rules (rule_id, active, match_digest, log,apply) VALUES (1,1,\'.\',1,0);
 
请注意，并不是所有查询都由查询处理器处理。一些特殊，比如：commit、rollback 和 set autocommit 都是在查询处理器之前被处理的。如果要记录此类查询操作，则需要启用全局记录。
 
mysql> SET mysql-eventslog_default_log=1;
mysql> LOAD MYSQL VARIABLES TO RUNTIME;
mysql> SAVE MYSQL VARIABLES TO DISK;
 
精确记录：记录 web_opr 这个账户的所有操作记录
mysql\> INSERT INTO mysql_query_rules (rule_id, active, username, log, apply) VALUES (1, 1, \'web_opr\', 1, 0);
 
记录对 T2 表的所有 INSERT 语句：
INSERT INTO mysql_query_rules (rule_id, active, match_digest, log, apply) VALUES (1, 1, \'INSERT.\*t2, 1, 0);
 
记得要让规则生效，需要载入 RUNTIME，要保存就要 SAVE 到磁盘
 LOAD MYSQL QUERY RULES TO RUNTIME;
 SAVE MYSQL QUERY RULES TO DISK;   
 
 
在 2.0.6 版中，新变量 mysql-eventslog_format 控制查询日志的记录格式：
 
变量值：
 
1：默认值：查询记录在二进制格式文件 请注意，在 2.0.6 版本更好的支持下，引入了一种预处理语句，记录 rowsaffected 和 rowssent。因此，需要使用更新的版本 eventslogreadersample 来读取这些文件。 2 ：查询以 JSON 格式记录。
 
JSON 格式记录
 
要启用 JSON 格式的日志记录，需要设置 mysql-eventslog_format=2。
 
SET mysql-eventslog_format=2;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
 
示例：
{"client":"127.0.0.1:6966","digest":"0x0CA2979885DD8D2A","duration_us":26126,"endtime":"2020-08-13 15:34:05.674394","endtime_timestamp_us":1597304045674394,"event":"COM_QUERY","hostgroup_id":10,"query":"insert into hrttest.t2 values (123123)","rows_affected":1,"rows_sent":0,"schemaname":"information_schema","server":"10.0.53.210:3307","starttime":"2020-08-13 15:34:05.648268","starttime_timestamp_us":1597304045648268,"thread_id":6,"username":"web_opr"}
 
{"client":"127.0.0.1:6966","digest":"0x0CA2979885DD8D2A","duration_us":2235,"endtime":"2020-08-13 15:34:15.999281","endtime_timestamp_us":1597304055999281,"event":"COM_QUERY","hostgroup_id":10,"query":"insert into hrttest.t2 values (456456)","rows_affected":1,"rows_sent":0,"schemaname":"information_schema","server":"10.0.53.210:3307","starttime":"2020-08-13 15:34:15.997046","starttime_timestamp_us":1597304055997046,"thread_id":6,"username":"web_opr"}
 
在目前的版本，这些日志还无法实现保存在数据库表中，但是官方已经计划在后期的版本中实现。但这些日志可以通过脚本，写入ES，Doris，Starrocks中。
 
操作总结
mysql> SET mysql-eventslog_filename='all_queries.log';  # 可以指定日志存储绝对路径： set mysql-eventslog_filename='/data/ProxySQL/log/sql.log'；
mysql> SET mysql-eventslog_default_log=1;
mysql> SET mysql-eventslog_format=2;
 
mysql> LOAD MYSQL VARIABLES TO RUNTIME;
mysql> SAVE MYSQL VARIABLES TO DISK;
 
posted @   cdrcsy  阅读(253)  评论(0)  编辑  收藏  举报
相关博文：
· MySQL sys性能监控
· MySQL网络安全-防syn攻击防暴力攻击
· ProxySQL 审计
· MySQL审计插件-MariaDB Audit Plugin
· MySQL8-中文参考-十四-
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！
点击右上角即可分享
微信分享提示