一、下载audit插件
因为社区版不带审计插件(Audit Plugin),我们可以安装MariaDB的插件。
我们可以百度一下你安装的MySQL版本相当于MariaDB的哪个版本?然后下载相对应的MariaDB版本。我们这里以MySQL5.7.30版本为例,这个版本相当于MariaDB 10.2的版本,在MariaDB 10.2版本中找一个靠后的版本,我这里下载的是MariaDB 10.2.38.
下载地址:https://repo.huaweicloud.com/mariadb/mariadb-10.2.38/bintar-linux-x86_64/mariadb-10.2.38-linux-x86_64.tar.gz
这个包有点大,可以先下载到本地然后再上传到服务器。
二、安装audit插件
2.1、查看当前MySQL的插件安装目录
mysql> show variables like "%plugin_dir%";
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)
mysql>
MySQL插件安装目录为/usr/lib64/mysql/plugin/
2.2、将MariaDB插件server_audit.so,拷贝到MySQL的plugin安装目录
可以用install命令,也可以直接cp一份到plugin安装目录,然后给与执行权限。
[root@rshine src]# cd mariadb-10.2.38-linux-x86_64 # 切换到解压后的mariadb安装包目录
[root@rshine mariadb-10.2.38-linux-x86_64]# ls
bin COPYING CREDITS data include INSTALL-BINARY lib man mysql-test README.md README-wsrep scripts share sql-bench support-files THIRDPARTY
[root@rshine mariadb-10.2.38-linux-x86_64]# install lib/plugin/server_audit.so /usr/lib64/mysql/plugin/
[root@rshineg mariadb-10.2.38-linux-x86_64]# ls /usr/lib64/mysql/plugin/
adt_null.so connection_control.so ha_example.so keyring_udf.so locking_service.so mysqlx.so semisync_master.so validate_password.so
authentication_ldap_sasl_client.so debug innodb_engine.so libmemcached.so mypluglib.so rewrite_example.so semisync_slave.so version_token.so
auth_socket.so group_replication.so keyring_file.so libpluginmecab.so mysql_no_login.so rewriter.so server_audit.so
[root@rshine mariadb-10.2.38-linux-x86_64]#
2.3、安装 server_audit.so插件
mysql> install plugin server_audit SONAME 'server_audit.so';
Query OK, 0 rows affected (0.00 sec)
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| SERVER_AUDIT | ACTIVE | AUDIT | server_audit.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
mysql>
2.4、查看audit配置
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_loc_info | |
| server_audit_logging | OFF |
| server_audit_mode | 1 |
| 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 |
+-------------------------------+-----------------------+
16 rows in set (0.00 sec)
mysql>
2.5、配置audit永久有效
在配置文件中配置audit,使得MySQL启动时自动加载audit。在MySQL配置文件my.cnf中[mysqld]下添加以下配置
# audit log # 注释以下为audit配置
server_audit_logging=ON # 随MySQL启动自动加载
server_audit_events=connect,table,query_ddl,query_dcl,query_dml_no_select # 指定要记录SQL语句,除查询语句外
#server_audit_events=connect,query,table,query_ddl,query_dml,query_dcl,query_dml_no_select
server_audit_file_rotate_now=ON # 开启日志轮转
server_audit_file_rotate_size=100000000 # 日志文件达到100M就轮转
server_audit_file_path=/data0/logs/logs_audit/server_audit.log # 审计日志文件存储路径
重启MySQL,使得配置文件生效。
查看最终生效的配置
mysql> show variables like "%audit%";
+-------------------------------+-------------------------------------------------------+
| Variable_name | Value |
+-------------------------------+-------------------------------------------------------+
| server_audit_events | CONNECT,TABLE,QUERY_DDL,QUERY_DCL,QUERY_DML_NO_SELECT |
| server_audit_excl_users | |
| server_audit_file_path | /data0/logs/logs_audit/server_audit.log |
| server_audit_file_rotate_now | ON |
| server_audit_file_rotate_size | 100000000 |
| server_audit_file_rotations | 9 |
| server_audit_incl_users | |
| server_audit_loc_info | |
| server_audit_logging | ON |
| server_audit_mode | 1 |
| 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 |
+-------------------------------+-------------------------------------------------------+
16 rows in set (0.01 sec)
mysql>