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;