1).到网站(https://bintray.com/version/files/mcafee/mysql-audit-plugin/release/1.1.7-805)下载插件audit-plugin-mysql-5.7-1.1.7-805-linux-x86_64.zip
2).上传到MySQL机器并解压缩：
#unzip audit-plugin-mysql-5.7-1.1.7-805-linux-x86_64.zip
Archive: audit-plugin-mysql-5.7-1.1.7-805-linux-x86_64.zip
creating: audit-plugin-mysql-5.7-1.1.7-805/
creating: audit-plugin-mysql-5.7-1.1.7-805/lib/
inflating: audit-plugin-mysql-5.7-1.1.7-805/lib/libaudit_plugin.so
inflating: audit-plugin-mysql-5.7-1.1.7-805/COPYING
inflating: audit-plugin-mysql-5.7-1.1.7-805/THIRDPARTY.txt
inflating: audit-plugin-mysql-5.7-1.1.7-805/README.txt
inflating: audit-plugin-mysql-5.7-1.1.7-805/plugin-name.txt
creating: audit-plugin-mysql-5.7-1.1.7-805/utils/
inflating: audit-plugin-mysql-5.7-1.1.7-805/utils/offset-extract.sh
3).查看mysql的插件目录:
mysql> show global variables like 'plugin_dir';
+---------------+-------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------+
| plugin_dir | /usr/local/mysql-5.7.24/lib/plugin/ |
+---------------+-------------------------------------+
1 row in set (0.01 sec)
4).拷贝libaudit_plugin.so到mysql插件目录:
# cp lib/libaudit_plugin.so /usr/local/mysql-5.7.24/lib/plugin/
5).安装libaudit_plugin.so插件：
mysql> install plugin audit soname 'libaudit_plugin.so';
Query OK, 0 rows affected (3.97 sec)
6).开启审计功能:
mysql> set global audit_json_file=1;
Query OK, 0 rows affected (0.00 sec)
7).在mysql的数据文件目录里生成审计日志：
mysql> show variables like 'datadir';
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| datadir | /home/mysql/data/ |
+---------------+-------------------+
1 row in set (0.01 sec)
8).查看审计日志内容:
#less /home/mysql/data/mysql-audit.json
{"msg-type":"header","date":"1550816633651","audit-version":"1.1.7-805","audit-protocol-version":"1.0","hostname":"test2","mysql-version":"5.7.24-log","mysql-program":"/usr/local/mysql-5.7.24/bin/mysqld","mysql-socket":"/tmp/mysql.sock","mysql-port":"3306","server_pid":"6485"}
{"msg-type":"activity","date":"1550816633651","thread-id":"126897","query-id":"3356369","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"13108","_client_version":"5.7.24","_platform":"x86_64","program_name":"mysql"},"pid":"13108","os_user":"root","appname":"mysql","status":"0","cmd":"set_option","query":"set global audit_json_file=1"}
{"msg-type":"activity","date":"1550816634816","thread-id":"126952","query-id":"0","user":"monitor","priv_user":"","ip":"192.168.140.52","host":"192.168.140.52","connect_attrs":{"_os":"Linux","_client_name":"libmariadb","_pid":"21686","_client_version":"2.3.1","_platform":"x86_64","program_name":"proxysql_monitor"},"status":"1045","cmd":"Failed Login","query":"Failed Login"}
{"msg-type":"activity","date":"1550816634816","thread-id":"126952","query-id":"0","user":"monitor","priv_user":"","ip":"192.168.140.52","host":"192.168.140.52","connect_attrs":{"_os":"Linux","_client_name":"libmariadb","_pid":"21686","_client_version":"2.3.1","_platform":"x86_64","program_name":"proxysql_monitor"},"cmd":"Connect","query":"Connect"}
................................................................................................................................................................
................................................................................................................................................................
................................................................................................................................................................
................................................................................................................................................................
9).查看加载的审计插件:
mysql> select * from INFORMATION_SCHEMA.PLUGINS where PLUGIN_NAME like '%AUDIT%';
+-------------+----------------+---------------+-------------+---------------------+--------------------+------------------------+---------------+--------------------------------------------------------------+----------------+-------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR | PLUGIN_DESCRIPTION | PLUGIN_LICENSE | LOAD_OPTION |
+-------------+----------------+---------------+-------------+---------------------+--------------------+------------------------+---------------+--------------------------------------------------------------+----------------+-------------+
| AUDIT | 1.0 | ACTIVE | AUDIT | 4.1 | libaudit_plugin.so | 1.6 | McAfee Inc | AUDIT plugin, creates a file mysql-audit.log to log activity | GPL | ON |
+-------------+----------------+---------------+-------------+---------------------+--------------------+------------------------+---------------+--------------------------------------------------------------+----------------+-------------+
1 row in set (0.00 sec)
10).查看MySQL审计相关参数:
mysql> show global variables like '%audit%';
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| audit_before_after | after |
| audit_checksum | |
| audit_client_capabilities | OFF |
| 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 | /var/run/db-audit/mysql.audit__home_mysql_data_3306 |
| audit_json_socket_retry | 10 |
| audit_json_socket_write_timeout | 1000 |
| audit_offsets | |
| audit_offsets_by_version | ON |
| audit_password_masking_cmds | CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER,UPDATE |
| audit_password_masking_regex | identified(?:/\*.*?\*/|\s)*?by(?:/\*.*?\*/|\s)*?(?:password)?(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?\((?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"](?:/\*.*?\*/|\s)*?\)|password(?:/\*.*?\*/|\s)*?(?:for(?:/\*.*?\*/|\s)*?\S+?)?(?:/\*.*?\*/|\s)*?=(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"] |
| audit_record_cmds | |
| audit_record_objs | |
| audit_sess_connect_attrs | ON |
| audit_socket_creds | ON |
| audit_uninstall_plugin | OFF |
| audit_validate_checksum | ON |
| audit_validate_offsets_extended | ON |
| audit_whitelist_cmds | BEGIN,COMMIT,PING |
| audit_whitelist_users | |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
30 rows in set (0.01 sec)
