MySQL审计之插件
通过审计插件可以记录服务器活动，记录谁连接到服务器，运行了什么查询，访问了哪些表，能存储到日志文件或发送到本地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，查找插件所在位置
一、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：因错误没有记录的日志条目数
参数设置
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 ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, k INTEGER UNSIGNED DEFAULT "0" NOT NULL, c CHAR(120) DEFAULT "" NOT NULL, pad CHAR(60) DEFAULT "" NOT NULL, PRIMARY KEY (id) ) /*! 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
个人总结（因文章较长，个人使用总结就显示这里了）：
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.so3，加载插件
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> <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 ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, k INTEGER UNSIGNED DEFAULT "0" NOT NULL, c CHAR(120) DEFAULT "" NOT NULL, pad CHAR(60) DEFAULT "" NOT NULL, PRIMARY KEY (id) ) /*! 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
声明：该文观点仅代表作者本人，入门客AI创业平台信息发布平台仅提供信息存储空间服务，如有疑问请联系rumenke@qq.com。
- 上一篇：没有了
- 下一篇：没有了
