尘封网

让学习成为一种习惯!

mysql5.7添加日志审计插件audit-plugin

来自McAfee的MySQL插件,为MySQL提供审计功能,重点是安全性和审计要求。该插件可以用作独立的审核解决方案,也可以配置为将数据提供给外部监视工具。

插件下载地址:

https://bintray.com/mcafee/mysql-audit-plugin/release/1.1.4-725#files

首先查看mysql的插件保存目录:

mysql> show global variables like 'plugin_dir'; 
+---------------+---------------------------------------------+ 
| Variable_name | Value                                       | 
+---------------+---------------------------------------------+ 
| plugin_dir    | /usr/local/mysql-5.7.17/lib64/mysql/plugin/ | 
+---------------+---------------------------------------------+ 
1 row in set (0.00 sec)

把上面下载的插件复制到上面的目录下:

unzip audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip 
cd audit-plugin-mysql-5.7-1.1.4-725 
cp lib/libaudit_plugin.so /usr/local/mysql-5.7.17/lib64/mysql/plugin/

进入mysql命令窗口,安装插件:

mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so'; 
Query OK, 0 rows affected (0.06 sec)

查看mysql当前已经加载了哪些插件:

mysql> show plugins; 
+----------------------------+----------+--------------------+--------------------+---------+ 
| Name                       | Status   | Type               | Library            | License | 
+----------------------------+----------+--------------------+--------------------+---------+ 
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     | 
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL               | GPL     | 
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL               | GPL     | 
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     | 
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     | 
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     | 
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     | 
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     | 
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     | 
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     | 
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     | 
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     | 
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     | 
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL               | GPL     | 
| ngram                      | ACTIVE   | FTPARSER           | NULL               | GPL     | 
| AUDIT                      | ACTIVE   | AUDIT              | libaudit_plugin.so | GPL     | 
+----------------------------+----------+--------------------+--------------------+---------+ 
45 rows in set (0.00 sec)

查看安装后的插件版本:

mysql> show global status like 'AUDIT_version'; 
+---------------+-----------+ 
| Variable_name | Value     | 
+---------------+-----------+ 
| Audit_version | 1.1.4-725 | 
+---------------+-----------+ 
1 row in set (0.00 sec)

开启audit功能:

mysql> SET GLOBAL audit_json_file=ON; 
Query OK, 0 rows affected (0.00 sec)

可以查看插件有哪些可配置的参数:

mysql>  SHOW GLOBAL VARIABLES LIKE '%audi%';

详细的参数说明,可以直接访问官方说明:

https://github.com/mcafee/mysql-audit/wiki/Configuration

最后为了保证重启数据库,配置不丢失,修改my.cnf 配置文件,将下面的配置添加到[mysqld]中,所以在配置文件中my.cnf加入参数:

audit_json_file=on #保证mysql重启后自动启动插件
plugin-load=AUDIT=libaudit_plugin.so #防止删除了插件,重启后又会加载
audit_record_cmds=’insert,delete,update,create,drop,alter,grant,truncate’ #要记录哪些命令语句,因为默认记录所有操作;

保存重启即可看到效果。

提醒:本文最后更新于 641 天前,文中所描述的信息可能已发生改变,请谨慎使用。