通过审计插件可以记录服务器活动，记录谁连接到服务器，运行了什么查询，访问了哪些表，能存储到日志文件或发送到本地syslogd守护进程。
一、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：指定记录事件的类型，可以用逗号分隔的多个值(connect,query,table)，如果开启了查询缓存(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：因错误没有记录的日志条目数
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>
<RECORD>36965_2014-07-30T15:12:27</RECORD>
<TIMESTAMP>2014-07-30T15:12:53 UTC</TIMESTAMP>
<COMMAND_CLASS>show_variables</COMMAND_CLASS>
<CONNECTION_ID>1</CONNECTION_ID>
<STATUS>0</STATUS>
<SQLTEXT>show variables like '%audit%'</SQLTEXT>
<USER>root[root] @ localhost []</USER>
<HOST>localhost</HOST>
<OS_USER></OS_USER>
<IP></IP>
</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;
| AUDIT | ACTIVE | AUDIT | libaudit_plugin.so | GPL |
安装插件:
方式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、
2、
3、
4、
