MySQL审计之插件

来源：互联网发布：淘宝卖虚拟物品货源编辑：程序博客网时间：2024/05/22 05:01

通过审计插件可以记录服务器活动，记录谁连接到服务器，运行了什么查询，访问了哪些表，能存储到日志文件或发送到本地syslogd守护进程。目前可用的插件有mariaDB的插件、percona的插件、和macfee的插件，还有github或者开源中间上个人提交的插件，比如mysql_audit。审计不需要依赖binlog和general_log，不过，根据审计范围的不同，也多多少少会影响mysql的运行效率。

个人总结（因文章较长，个人使用总结就显示这里了）：
1，mysql_audit不会记录无法执行的sql，而且会过滤掉无用的执行操作。可以一试。
2，mariadb的server_audit.so会保存所有的操作，包括字符集设置，错误的SQL。就生产环境来说，就没必要显示这么多无用的信息了，但是功能相对较多。

3，macfee的mysql-audit听说崩过，还没用过。

一、mysql_audit
这里介绍mysql_audit，是个人提供的插件。有一点需要注意，so文件是动态库文件，类似于windows下的dll，是无法看到源码的。
1，查找插件所在位置

mysql> show variables like '%plugin_dir%';  +---------------+------------------------------+  | Variable_name | Value                        |  +---------------+------------------------------+  | plugin_dir    | /usr/local/mysql/lib/plugin/ |  +---------------+------------------------------+

2，将audit_版本号.so插件下载后放到plugin_dir位置，并改为audit.so

mv audit_版本号.so  audit.so

3，加载插件

install plugin audit  SONAME 'audit.so';

4，卸载插件

uninstall plugin audit;

5，查看插件状态

mysql> show variables like '%audit%';  +----------------+----------------------+  | Variable_name  | Value                |  +----------------+----------------------+  | audit_logfile  | /tmp/mysql_audit.log |  | audit_myswitch | OFF                  |  | audit_num      | 0                    |  | audit_sql      | all_sql              |  | audit_user     | all_user             |  +----------------+----------------------+  5 rows in set (0.01 sec)

6，参数设置

mysql> set global audit_logfile='/tmp/mysql_audit_1.log';----只读变量，审计仅指定在/tmp/mysql_audit.log文件，保障权限可以写  ERROR 1238 (HY000): Variable 'audit_logfile' is a read only variable    set global audit_sql='DELETE;delete;ALTER;alter;DROP;drop;TRUNCATE;truncate';   -----这些审计关键字用;分开，这里需要注意，匹配的是执行SQL的关键字set global audit_user='user2;user3';         ----审计用户用;隔开  set global num =40;                          ----审计sql影响的最少行数  set global audit_myswitch=on|off|ON|OFF|1|0;       -----开启关闭审计

7，查看日志

[root@localhost] tail /tmp/mysql_audit.log

一、mariadb

mariadb的审计插件能工作在mariadb、mysql和percona server。拷贝到server_audit.so到适当的目录，然后安装插件：

root@[(none)] 21:52:32>INSTALL PLUGIN server_audit SONAME 'server_audit';

Query OK, 0 rows affected (0.00 sec)

查看插件目录使用以下命令：

root@[(none)] 21:57:58>SHOW VARIABLES LIKE 'plugin_dir';

+---------------+-----------------------------------------------+

| Variable_name | Value |

+---------------+-----------------------------------------------+

| plugin_dir | /opt/mariadb-10.0.12-linux-x86_64/lib/plugin/ |

+---------------+-----------------------------------------------+

1 row in set (0.00 sec)

mariadb审计插件记录内容：

connection：记录关于连接或断开连接用户

query；发布的查询和结果集

table：执行查询涉及哪些表

系统变量和状态参数：

root@[(none)] 21:58:43>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 | OFF |

| server_audit_mode | 0 |

| server_audit_output_type | file |

| server_audit_syslog_facility | LOG_USER |

| server_audit_syslog_ident | mysql-server_auditing |

| server_audit_syslog_info | |

| server_audit_syslog_priority | LOG_INFO |

+-------------------------------+-----------------------+

14 rows in set (0.00 sec)

参数说明：

server_audit_output_type：指定日志输出类型，可为SYSLOG或FILE

server_audit_logging：启动或关闭审计

server_audit_events：指定记录事件的类型，可以用逗号分隔的多个值(CONNECTION,QUERY,TABLE,QUERY_DDL,QUERY_DML)，如果开启了查询缓存(query cache)，查询直接从查询缓存返回数据，将没有table记录

server_audit_file_path：如server_audit_output_type为FILE，使用该变量设置存储日志的文件，可以指定目录，默认存放在数据目录的server_audit.log文件中

server_audit_file_rotate_size：限制日志文件的大小

server_audit_file_rotations：指定日志文件的数量，如果为0日志将从不轮转

server_audit_file_rotate_now：强制日志文件轮转

server_audit_incl_users：指定哪些用户的活动将记录，connect将不受此变量影响，该变量比server_audit_excl_users优先级高

server_audit_syslog_facility：默认为LOG_USER，指定facility

server_audit_syslog_ident：设置ident，作为每个syslog记录的一部分

server_audit_syslog_info：指定的info字符串将添加到syslog记录

server_audit_syslog_priority：定义记录日志的syslogd priority

server_audit_excl_users：该列表的用户行为将不记录，connect将不受该设置影响

server_audit_mode：标识版本，用于开发测试

root@[(none)] 22:01:32>show status like '%audit%';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| Server_audit_active | OFF |

| Server_audit_current_log | |

| Server_audit_last_error | |

| Server_audit_writes_failed | 0 |

+----------------------------+-------+

4 rows in set (0.00 sec)

server_audit_active：为ON时表示审计开启

server_audit_current_log：当前日志使用的日志文件或syslog参数

server_audit_last_error：错误消息

server_audit_writes_failed：因错误没有记录的日志条目数

参数设置

set global server_audit_file_path='/tmp/server_audit.log';  set global server_audit_events='connect,query,table,query_ddl,query_dml';  set global server_audit_logging='on'

mariadb记录日志为文本文件：

20140730 22:35:01,myhost02,sysbench,10.0.37.122,38,90900,WRITE,test,sbtest1,

20140730 22:35:01,myhost02,sysbench,10.0.37.122,38,90900,QUERY,test,'INSERT INTO sbtest1(k, c, pad) VALUES(487325, \'02406205850-57731136173-10289043172-68098052884-05459262539-14230474912-32776899800-01942629773-13617645720-29012926348\', \'75186689450-94564783220-49090733990-46810772846-74872449862\'),(504523, \'14086705181-76356819617-17196741311-14417217692-76015636577-69135551839-16061133493-59433832946-32921431508-88203986331\', \'21817741267-80312360982-96951080242-39189613278-51457417980\'),(502609, \'90774128376-34149632869-50828471630-05656457905-71360453529-77692188954-71900381782-31975883713-92444112376-62625968878\', \'19606057516-05912781671-83426367427-48781275105-41851238891\'),(497317, \'37305716033-96462825526-89425751659-01849313602-91307069305-45115796595-94640309054-36929589931-95553285055-20383387405\'',0

二、Percona server

监控和记录连接，查询活动，存储为xml日志文件，每个事件有NAME字段，有唯一的RECORD_ID字段和TIMESTAMP字段，实现类似官方mysql的审计插件。

记录以下事件：

1、Audit：审计日志表明审计记录开启或完成，当开始记录日志时，NAME字段为Audit，当记录日志完成为NoAudit，审计日志也包含服务器版本和命令行参数

2、Connect/Disconnect：记录当用户登入、登录错误或当连接关闭退出，NAME为Connect，包括connection_id,status,user,priv_user,os_login,proxy_user,host,ip；status为0表明成功登录

3、Query：记录查询语句，status非0表明错误，sqltext字段记录sql语句，密码信息将重写，除非使用--log-raw选项，NAME字段可能的值为Query,Prepare,Execute,Change user等

安装插件：

root@[(none)] 22:41:00>INSTALL PLUGIN audit_log SONAME 'audit_log.so';

Query OK, 0 rows affected (0.01 sec)

系统参数：

root@[(none)] 22:55:14>show variables like '%audit%';

+--------------------------+--------------+

| Variable_name | Value |

+--------------------------+--------------+

| audit_log_buffer_size | 1048576 |

| audit_log_file | audit.log |

| audit_log_flush | OFF |

| audit_log_format | OLD |

| audit_log_policy | ALL |

| audit_log_rotate_on_size | 0 |

| audit_log_rotations | 0 |

| audit_log_strategy | ASYNCHRONOUS |

+--------------------------+--------------+

8 rows in set (0.00 sec)

audit_log_strategy：设置审计日志策略，ASYNCHRONOUS(默认，使用内存buffer，如果buffer满，不删除消息)，PERFORMANCE(使用内存buffer，如果buffer满删除消息)，SEMISYNCHRONOUS(日志直接到文件，没有刷新和同步)，SYNCHRONOUS(日志直接到文件，每个事件都要刷新和同步)

audit_log_file：指定日志文件，可以指定绝对路径，没有指定路径，将存储在数据目录

audit_log_flush：设置为ON，能关闭和重新打开日志，用于手工进行日志轮转

audit_log_buffer_size：指定审计日志的内存buffer，当audit_log_strategy为ASYNCHRONOUS，PERFORMANCE时

audit_log_format：指定审计日志格式，OLD记录日志为xml属性，NEW记录日志为XML标签

audit_log_policy：指定记录哪些事件，ALL(所有事件)，LOGINS(仅仅用户登录事件)，QUERIES(仅仅查询语句)，NONE(不记录任何事件)

audit_log_rotate_on_size：设置审计日志的大小，当日志到设置值，将自动轮转

audit_log_rotations：指定审计日志文件数量

使用OLD格式的日志：

<AUDIT_RECORD

"NAME"="Query"

"RECORD"="12_2014-07-30T14:52:47"

"TIMESTAMP"="2014-07-30T14:54:02 UTC"

"COMMAND_CLASS"="show_processlist"

"CONNECTION_ID"="389"

"STATUS"="0"

"SQLTEXT"="SHOW PROCESSLIST"

"USER"="sysbench[sysbench] @ [127.0.0.1]"

"HOST"=""

"OS_USER"=""

"IP"="127.0.0.1"

使用NEW格式的日志：

<AUDIT_RECORD>

<NAME>Query</NAME>

<COMMAND_CLASS>show_variables</COMMAND_CLASS>

<CONNECTION_ID>1</CONNECTION_ID>

<SQLTEXT>show variables like '%audit%'</SQLTEXT>

<USER>root[root] @ localhost []</USER>

<HOST>localhost</HOST>

<OS_USER></OS_USER>

</AUDIT_RECORD>

percona server(5.6.19-67.0-log Percona Server (GPL), Release 67.0, Revision 618)安装mariadb的审计插件：

cp ../mariadb-10.0.12-linux-x86_64/lib/plugin/server_audit.so lib/mysql/plugin/

INSTALL PLUGIN server_audit SONAME 'server_audit.so';

三、macfee mysql-audit

下载二进制版本：https://bintray.com/mcafee/mysql-audit-plugin/release

percona server (5.6.19-67.0-log Percona Server (GPL), Release 67.0, Revision 618)安装mysql-audit插件:

wget http://dl.bintray.com/mcafee/mysql-audit-plugin/1.0.5/audit-plugin-mysql-5.6-1.0.5-479-linux-x86_64.zip

unzip audit-plugin-mysql-5.6-1.0.5-479-linux-x86_64.zip

cp audit-plugin-mysql-5.6/lib/libaudit_plugin.so /opt/Percona-Server-5.6.19-rel67.0-618.Linux.x86_64/lib/mysql/plugin/

wget https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh

sh offset-extract.sh /opt/Percona-Server-5.6.19-rel67.0-618.Linux.x86_64/bin/mysqld

//offsets for: /opt/Percona-Server-5.6.19-rel67.0-618.Linux.x86_64/bin/mysqld (5.6.19-67.0)

{"5.6.19-67.0","e418cec1b2eb1cf29350d99229fac09f", 8624, 8672, 4376, 5016, 88, 2720, 96, 0, 32, 104},

[mysqld]

plugin-load=AUDIT=libaudit_plugin.so

audit_offsets=8624, 8672, 4376, 5016, 88, 2720, 96, 0, 32, 104

root@[(none)] 00:12:11>show plugins;

安装插件:

方式1、官方推荐，修改my.cnf配置文件，添加plugin-load=AUDIT=libaudit_plugin.so

方式2、INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';

系统变量：

root@[(none)] 00:14:33>show variables like '%audit%';

+---------------------------------+--------------------------------------------------+

| Variable_name | Value |

+---------------------------------+--------------------------------------------------+

| audit_checksum | |

| audit_delay_cmds | |

| audit_delay_ms | 0 |

| audit_json_file | OFF |

| audit_json_file_flush | OFF |

| audit_json_file_sync | 0 |

| audit_json_log_file | mysql-audit.json |

| audit_json_socket | OFF |

| audit_json_socket_name | /tmp/mysql-audit.json.sock |

| audit_offsets | 8624, 8672, 4376, 5016, 88, 2720, 96, 0, 32, 104 |

| audit_offsets_by_version | ON |

| audit_record_cmds | |

| audit_record_objs | |

| audit_uninstall_plugin | OFF |

| audit_validate_checksum | ON |

| audit_validate_offsets_extended | ON |

| audit_whitelist_users | |

+---------------------------------+--------------------------------------------------+

17 rows in set (0.00 sec)

root@[(none)] 00:14:40>show status like '%audit%';

+------------------------+-----------+

| Variable_name | Value |

+------------------------+-----------+

| Audit_protocol_version | 1.0 |

| Audit_version | 1.0.5-479 |

+------------------------+-----------+

2 rows in set (0.00 sec)

参数说明：

audit_json_log_file：json日志文件名，如果audit_json_file选项启用，将写审计跟踪到该设置的文件，可以设置为绝对路径

audit_json_file：启用或关闭json 日志文件

audit_json_file_sync：json文件同步周期，如果大于0，将每多少次写后同步到磁盘

audit_json_file_flush：将刷新日志文件，关闭和重新打开文件，能手动进行轮转日志

audit_json_socket_name：json unix socket name，如果audit_json_socket选项启用，将写审计日志到该unix套接字

audit_json_socket：启用或关闭json unix socket

audit_uninstall_plugin：在命令行或配置文件启用或关闭AUDIT uninstall 插件

audit_validate_checksum：启用或关闭mysqld二进制校验和确认

audit_checksum：在命令行或配置文件启用mysqld校验和确认，当设置audit_offsets

audit_record_cmds：逗号分隔的标记记录哪些命令到审计日志，如insert、update、delete

audit_record_objs：逗号分隔的哪些对象记录到审计日志，格式为database.table，支持通配符

audit_whitelist_users：逗号分隔的白名单用户，将不记录到审计日志

audit_header_msg：启用或关闭记录消息头，1.0.6引入

audit_password_masking_cmds：逗号分隔的命令，将隐藏密码，如CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER，1.0.6引入

audit_password_masking_regex：使用PCRE正则表达式屏蔽密码，仅仅audit_password_masking_cmds指定的命令，1.0.6引入

日志格式：

{"msg-type":"activity","date":"1406738125594","thread-id":"30","query-id":"0","user":"sysbench","priv_user":"sysbench","host":"","ip":"127

.0.0.1","cmd":"Connect","query":"Connect"}

{"msg-type":"activity","date":"1406738125594","thread-id":"30","query-id":"114","user":"sysbench","priv_user":"sysbench","host":"","ip":"127.0.0.1","cmd":"select","query":"select @@version_comment limit 1"}

{"msg-type":"activity","date":"1406738125595","thread-id":"30","query-id":"115","user":"sysbench","priv_user":"sysbench","host":"","ip":"127.0.0.1","cmd":"show_slave_status","query":"SHOW SLAVE STATUS"}

{"msg-type":"activity","date":"1406738125595","thread-id":"30","query-id":"116","user":"sysbench","priv_user":"sysbench","host":"","ip":"127.0.0.1","cmd":"Quit","query":"Quit"}

{"msg-type":"activity","date":"1406738137154","thread-id":"31","query-id":"0","user":"sysbench","priv_user":"sysbench","host":"","ip":"10.0.37.122","cmd":"Connect","query":"Connect"}

{"msg-type":"activity","date":"1406738137154","thread-id":"31","query-id":"117","user":"sysbench","priv_user":"sysbench","host":"","ip":"10.0.37.122","cmd":"create_table","objects":[{"db":"test","name":"sbtest1","obj_type":"TABLE"}],"query":"CREATE TABLE sbtest1 (\nid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,\nk INTEGER UNSIGNED DEFAULT '0' NOT NULL,\nc CHAR(120) DEFAULT '' NOT NULL,\npad CHAR(60) DEFAULT '' NOT NULL,\nPRIMARY KEY (id)\n) /*! ENGINE = innodb MAX_ROWS = 1000000 */"}

{"msg-type":"activity","date":"1406738151588","thread-id":"42","query-id":"188807","user":"sysbench","priv_user":"sysbench","host":"","ip":"10.0.37.122","cmd":"begin","query":"BEGIN"}

{"msg-type":"activity","date":"1406738151588","thread-id":"32","query-id":"188811","user":"sysbench","priv_user":"sysbench","host":"","ip":"10.0.37.122","cmd":"select","objects":[{"db":"test","name":"sbtest1","obj_type":"TABLE"}],"query":"SELECT c FROM sbtest1 WHERE id=536838"}

{"msg-type":"activity","date":"1406738151588","thread-id":"40","query-id":"188812","user":"sysbench","priv_user":"sysbench","host":"","ip":"10.0.37.122","cmd":"select","objects":[{"db":"test","name":"sbtest1","obj_type":"TABLE"}],"query":"SELECT c FROM sbtest1 WHERE id=503827"}

{"msg-type":"activity","date":"1406738151588","thread-id":"46","query-id":"188815","user":"sysbench","priv_user":"sysbench","host":"","ip":"10.0.37.122","cmd":"select","objects":[{"db":"test","name":"sbtest1","obj_type":"TABLE"}],"query":"SELECT c FROM sbtest1 WHERE id=498813"}

{"msg-type":"activity","date":"1406738151589","thread-id":"47","query-id":"188816","user":"sysbench","priv_user":"sysbench","host":"","ip":"10.0.37.122","cmd":"commit","query":"COMMIT"}

三种插件从功能上进行对比：

参考：

1、http://www.percona.com/doc/percona-server/5.6/management/audit_log_plugin.html

2、https://github.com/mcafee/mysql-audit/wiki

3、https://mariadb.com/kb/en/mariadb/mariadb-documentation/mariadb-plugins/server_audit-mariadb-audit-plugin/

4、https://github.com/mcafee/mysql-audit/wiki/Troubleshooting

0 0