標籤：style http 使用 os 檔案 io 資料 for
通過審計外掛程式可以記錄伺服器活動，記錄誰串連到伺服器，運行了什麼查詢，訪問了哪些表，能儲存到記錄檔或發送到本地syslogd守護進程。
一、mariadbmariadb的審計外掛程式能工作在mariadb、mysql和percona server。拷貝到server_audit.so到適當的目錄，然後安裝外掛程式：[email protected][(none)] 21:52:32>INSTALL PLUGIN server_audit SONAME ‘server_audit‘;Query OK, 0 rows affected (0.00 sec)
查看外掛程式目錄使用以下命令：[email protected][(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：執行查詢涉及哪些表
系統變數和狀態參數：[email protected][(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或FILEserver_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，指定facilityserver_audit_syslog_ident：設定ident，作為每個syslog記錄的一部分server_audit_syslog_info：指定的info字串將添加到syslog記錄server_audit_syslog_priority：定義記錄日誌的syslogd priorityserver_audit_excl_users：該列表的使用者行為將不記錄，connect將不受該設定影響server_audit_mode：標識版本，用於開發測試
[email protected][(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等
安裝外掛程式：[email protected][(none)] 22:41:00>INSTALL PLUGIN audit_log SONAME ‘audit_log.so‘;Query OK, 0 rows affected (0.01 sec)
系統參數：[email protected][(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.zipunzip 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.shsh 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.soaudit_offsets=8624, 8672, 4376, 5016, 88, 2720, 96, 0, 32, 104
[email protected][(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‘;
系統變數：[email protected][(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)
[email protected][(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 socketaudit_uninstall_plugin：在命令列或設定檔啟用或關閉AUDIT uninstall 外掛程式audit_validate_checksum：啟用或關閉mysqld二進位校正和確認audit_checksum：在命令列或設定檔啟用mysqld校正和確認，當設定audit_offsetsaudit_record_cmds：逗號分隔的標記記錄哪些命令到審計日誌，如insert、update、deleteaudit_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：使用PCRERegex屏蔽密碼，僅僅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
來自為知筆記(Wiz)
